I am having a performance problem with my current single instance database as the volumes of data grow. The application I am working on receives inserts once a month, and the oldest month is deleted at the same time. The number of rows grows in the hundred millions for the most accessed table (one row per item per month per store) and is joined on a table storing the data specific to items regardless of time (like its label).

A normal query acts as follow: Do a aggregation either per month, store or article over a different amount of store/months and categories.

I was looking at some sharding possibilities that would allow me to keep the queries as they are (I can't afford to rewrite everything for now) and run the queries exactly as if I had only one server but in the end that server would run the query on multiple shards/replications to speed things up.

Can I expect any good speed increase on currenly slow queries? So far my understanding of cluster/sharding is mostly to help reduce loads from many read/writes but this is not a problem at all for me at this time since there is not a lot of queries going on (rarely multiple queries at the same time on the server instance).

Thank you

EDIT: I'd like to point out that today, I am coping with ok speeds by having the full table in memory so I've removed the disk read times, so simply storing the data on different disks wouldn't solve all...

  • did you think to partition the data? dev.mysql.com/doc/refman/5.7/en/partitioning.html. Sharding mysql is not so simple. You can spread the read load with replication, but it is not your case. The first thing is to optimize queries as much as possible.
    – Giovanni
    Mar 26 '16 at 11:09
  • @Giovanni thank you, you seem to have pointed out an interesting option that I did not yet see... It seems that partitioning would be very helpful on my monthly INSERT/DELETE as each month could be a partition, and then since the user never requires 3 years of data, but usually up to 12 months it would divide the seeks by 3 minimum... I'll check that, I was expecting to have to need simply more horsepower but reducing the work the server has to do is even better.
    – Salketer
    Mar 26 '16 at 12:46
  • Sharding requires a bunch of changes to the infrastructure, connections, etc. The product "Spider" makes most of that transparent.

  • Sharding may improve scaling and concurrency for simple queries. But for table scans and JOINs, it is likely to make performance worse.

  • Partitioning is excellent for "deleting old data". See my blog. Otherwise, the DELETE competes with other queries, making the whole system suffer when you do the purging. (For 3 years, I would PARTITION BY RANGE(TO_DAYS(...)) and hav 38 partitions.) DROP PARTITION for the old month is essentially instantaneous, regardless of size.

  • Loading data can be a bottleneck. Do you use LOAD DATA? That is probably the best. Second best would be batched INSERTs of 100-1000 rows per batch.

  • If you just need read scaling, then adding Slaves gives you unlimited read scaling.

  • If you need write scaling, a Galera-based cluster might help some, but Sharding is the only real solution.

  • A few Summary tables, as you allude to, is an excellent way to get performance from a Data Warehouse setup. I blog on that, too.

  • "I can't afford to rewrite everything for now" -- Any shortcut will take just as long. And then you will still have to "rewrite everything" to take the next step.

  • Manually splitting data onto different devices is not as good as RAID striping of all the drives into one large drive.

  • A RAID controller with Battery Backed Write Cache makes writes appear to be instantaneous, without losing data.

  • Normalization can be used to keep the tables smaller, hence cacheable. Are you doing a reasonable amount of that, and using reasonably small ids (eg, SMALLINT UNSIGNED instead of BIGINT where appropriate)?

  • Slow queries -- There are too many variables to make a general statement. Let's see some specifics.

I would be happy to discuss any of these further. Since you have not said what you really need, I don't know what to focus on.

  • Thank you for your answer. You pointed out a lot of options I need to dig in to figure out what I'll do.
    – Salketer
    Mar 28 '16 at 15:44
  • @Salketer - It's been nearly 2 years; are you an expert in this area now?
    – Rick James
    Jan 25 '18 at 2:20
  • Hello, thanks for asking. I have got a bit better but did not get the possibility to implement anything... I since moved to another new project.
    – Salketer
    Jan 26 '18 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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