MySQL/MariaDB – Best Place to Store Very Large Text Fields

compressionjsonmariadb

I have an app where a queue is being processed, Each time an entry is processed the result is stuck in a separate table. The queue table gets inserts and updates, the result table inserts only. The result table has a text column containing a complete context of how the queue entry was handled encoded in JSON, average length 28,000 characters.

The table is now about 800k rows and 37gigs in size and really slow to deal with when running aggregates. But this particular field with the JSON data is rarely used – kept there just in case there is some important data not parsed into it's own field already.

Moving that field to another table is not out of the question if that is the right way to go, but it will require multiple application changes. I've moved the data from MySQL 5.1 into MariaDB 10.0.14 and put it in TokuDB but found no benefit in speed, but it's compression brought the size down by about 10 gigs though.

I've made no other significant changes to any settings. If there is any tip on how to handle this without making structural changes that would be great.

Best Answer

I'd suggest you split this large column off into its own table. While you're there take out any other columns which are only used once in a blue moon. (The smaller your row the faster your queries will run.)

I'll take your current table as MyData. Rename it to, say, MyData_Crucial. Let's call the new table MyData_Sometimes. Create this view:

create view MyData
select
    <all the columns>
from MyData_Crucial
inner join MyData_Sometimes
    on <primary key> = <primary key>

Now, assuming your optimizer is at least a little bit clever, you will not need to change your application code at all.

Related Solutions

MySQL Optimization – Speeding Up 400GB MyISAM Table

Assuming you are SELECTing only by the PRIMARY KEY, then the following will speed it up:

NO PARTITIONing. It only slows down such queries, especially because of 1024 partitions.
Switch to InnoDB. The PRIMARY KEY is "clustered" with the data; this will save a disk hit on each row fetched. Shrink key_buffer_size to 50M and raise innodb_buffer_pool_size to 70% of available RAM. InnoDB also avoids table locks.
Compress, in the client, the JSON text; the datatype needs to be BLOB (as it mistakenly is now).

Note: InnoDB will expand the disk space by a factor of 2-3; the compression will get that space back. (Please test this with, say, a million rows. Experiment with the different ROW_FORMATs; I don't know which one will work best for your situation. And validate the SELECT speed and the lack of interference between reads and writes.)

Do you really have 5000 connections at the same time? They are probably stumbling over each other and slowing down each other.

You have half turned off the Query cache; also do query_cache_size=0

PostgreSQL – Structure for Storing Data, Lists, Text, Pictures in JSON Field

If you're looking for alternatives other than JSON, and you just need arrays of text name-value pairs collections, you could simply use PostgreSQL's arrays:

CREATE TYPE mydoc AS (content_type text, names text[], values text[]);
CREATE TABLE documentData(id int primary key, documentName text, docs mydoc[]);
INSERT INTO documentData(id,documentName,docs) VALUES (1, 'DocumentA', '{"(Table,\"{ColA,ColB,ColC}\",\"{val1,val2,val3}\")","(Table,\"{ColA,ColB,ColC}\",\"{val4,val5,val6}\")"}'::mydoc[])

SELECT d.id, d.documentName, dd.*
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true);

This won't get a significant advantage over JSON, other than portability to older PostgreSQL versions, but unnest and =ANY type of operations are often much simpler than the JSON functions, so it may be easier to query and manipulate, e.g.:

SELECT d.id, d.documentName, dd.content_type, pos, name, dd.values[pos] AS value
FROM documentData d
LEFT JOIN LATERAL unnest(d.docs) dd ON (true)
LEFT JOIN LATERAL unnest(dd.names) WITH ORDINALITY AS y (name, pos) ON (true)
WHERE dd.content_type = 'Table' AND name ~ '^Col[AB]';

Best Answer

Related Solutions

MySQL Optimization – Speeding Up 400GB MyISAM Table

PostgreSQL – Structure for Storing Data, Lists, Text, Pictures in JSON Field

Related Question