1

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.

Improve this question
3
  • 1
    Does MariaDB have anything like the SQL Server text in row option? Otherwise I'd deffinitely recommend putting vertically partitioning your table to have this string in a separate table.
    – Michael Green
    Commented Oct 18, 2014 at 10:13
  • I agree with Michael that this would be the best option. But since this require more changes in the application, you could try first adding indexes for the aggregation queries that are slow. Even just a simple index on all the columns (except the long text one) might worth a try. Could you add the CREATE TABLE statement in the question?
    – ypercubeᵀᴹ
    Commented Oct 18, 2014 at 22:11
  • @jpj Please provide a sample query being slow, and the exact structure of the table (including its JSON structure, if it is relevant)
    – jynus
    Commented Oct 19, 2014 at 7:52

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • Please note that this suggestion is in general not recommended, as in the default engine, InnoDB, large blobs are stored on a separate extent, thus getting "internal structure partitioning" automatically, while now you are adding some burden (it is way more efficient than the join using a BTREE). In that case, unless you are doing SELECT * or some other specific operations, you may not get the intended advantage.
    – jynus
    Commented Oct 19, 2014 at 7:49
  • This was a workable solution bar the view, which is non-editable. I re-factored the app to split the two and am seeing exactly the performance benefits I wanted. As a bonus, for my scenario, I switched to TokuDB for the table with the JSON to take advantage of it's compression.
    – buckaroo1177125
    Commented Nov 3, 2014 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.