4 ms·

Optimizing a large database (billions of rows) has been the bane of my existence over the last couple months. But, when you finally get that 10x / 100x performa

Optimizing a large database (billions of rows) has been the bane of my existence over the last couple months. But, when you finally get that 10x / 100x performance improvement, it’s all worth it... until the next thing.

kjeetgill 7y ago
I've recently started working on a service with a MySQL database of a few billion rows. I'm used to developing on lower KVs like RocksDB or something. Any broad, general advice? Cool tricks you've learned? Bulk deletes and a relatively high continuous write rate are killing me right now. I kinda hate how manual batching deletes and then having to verify them takes.
- etaioinshrdlu 7y ago
  Fewer, but larger queries/statements. Think about the query to add any indexes that may help. Less concurrency may help. I bet you have deadlock problems with high continuous writes and deletes... maybe you can queue or otherwise delay your writes and then insert with a single thread... Use a lower transaction isolation level if possible.
- ploxiln 7y ago
  Deleting lots of rows is perhaps the biggest weakness you are running into. What I do for some cases is "LIMIT 4000", sleep for 1 second, and repeat until no rows affected (tweak the numbers for your situation obviously) (usually this is for a periodic expiry/cleanup task).
  - _vertigo 7y ago
    It’s probably worth mentioning that at least in MySQL, this is dangerous if using statement based replication. https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.... https://dev.mysql.com/doc/refman/8.0/en/replication-features-limit.html https://dev.mysql.com/doc/refman/8.0/en/replication-features...
    - ploxiln 7y ago
      Great point. I happen to use row-format mixed (default on RDS), so that can make it OK. Also, often I have some other related stuff to cleanup, so the pattern is more like: SELECT id, stuff FROM table WHERE expired LIMIT 1000; do cleanup; if all successful: DELETE FROM table WHERE id IN (id, id, ...); so the delete is deterministic for statement-based replication.
- ivanhoe 7y ago
  - avoid subqueries if possible (not sure if this still holds true with the latest versions of mysql), - avoid using offset and limit for pagination, it's a killer on big datasets, - on huge datasets sometimes breaking a complex query into 2-3 smaller ones can speed up things a lot, so always use explain and common sense when writing queries, - even with billion rows if query is really slow, it's probably missing a proper index in like 9 of 10 times
  - akie 7y ago
    I always put an index on nearly every field that could potentially be used to do a SELECT or an ORDER BY on. I'm not sure I've ever run into a serious problem doing this. Usually, the opposite is true - not having an index can cause serious problems. In a way, I look at indexes like I used to look at these "turbo" buttons (1) on older PCs: no reason not to press it. (1) https://en.wikipedia.org/wiki/Turbo_button https://en.wikipedia.org/wiki/Turbo_button
    - zambal 7y ago
      Indexes mostly have an impact on write performance and space used on disk, so if both are not a concern for your use case, creating lots of indexes is fine.
    - ivanhoe 7y ago
      Oh, yes, turbo button, the only thing more useless than caps lock :) Usually compound indexes are the best idea, but you need first to see what queries are running against DB to know what to add.
- yardstick 7y ago
  Is the data deleted because it is too old? Partition your tables by time, and truncate/drop the old tables instead of delete.
  - kjeetgill 7y ago
    We'd considered it, but partitioning hurts other things like turning a single row query into # of partitions look up.

kjeetgill 7y ago

I've recently started working on a service with a MySQL database of a few billion rows. I'm used to developing on lower KVs like RocksDB or something. Any broad, general advice? Cool tricks you've learned? Bulk deletes and a relatively high continuous write rate are killing me right now. I kinda hate how manual batching deletes and then having to verify them takes.

etaioinshrdlu 7y ago
Fewer, but larger queries/statements. Think about the query to add any indexes that may help. Less concurrency may help. I bet you have deadlock problems with high continuous writes and deletes... maybe you can queue or otherwise delay your writes and then insert with a single thread... Use a lower transaction isolation level if possible.
ploxiln 7y ago
Deleting lots of rows is perhaps the biggest weakness you are running into. What I do for some cases is "LIMIT 4000", sleep for 1 second, and repeat until no rows affected (tweak the numbers for your situation obviously) (usually this is for a periodic expiry/cleanup task).
- _vertigo 7y ago
  It’s probably worth mentioning that at least in MySQL, this is dangerous if using statement based replication. https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html#replication-sbr-rbr-sbr-disadvantages https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.... https://dev.mysql.com/doc/refman/8.0/en/replication-features-limit.html https://dev.mysql.com/doc/refman/8.0/en/replication-features...
  - ploxiln 7y ago
    Great point. I happen to use row-format mixed (default on RDS), so that can make it OK. Also, often I have some other related stuff to cleanup, so the pattern is more like: SELECT id, stuff FROM table WHERE expired LIMIT 1000; do cleanup; if all successful: DELETE FROM table WHERE id IN (id, id, ...); so the delete is deterministic for statement-based replication.
ivanhoe 7y ago
- avoid subqueries if possible (not sure if this still holds true with the latest versions of mysql), - avoid using offset and limit for pagination, it's a killer on big datasets, - on huge datasets sometimes breaking a complex query into 2-3 smaller ones can speed up things a lot, so always use explain and common sense when writing queries, - even with billion rows if query is really slow, it's probably missing a proper index in like 9 of 10 times
- akie 7y ago
  I always put an index on nearly every field that could potentially be used to do a SELECT or an ORDER BY on. I'm not sure I've ever run into a serious problem doing this. Usually, the opposite is true - not having an index can cause serious problems. In a way, I look at indexes like I used to look at these "turbo" buttons (1) on older PCs: no reason not to press it. (1) https://en.wikipedia.org/wiki/Turbo_button https://en.wikipedia.org/wiki/Turbo_button
  - zambal 7y ago
    Indexes mostly have an impact on write performance and space used on disk, so if both are not a concern for your use case, creating lots of indexes is fine.
  - ivanhoe 7y ago
    Oh, yes, turbo button, the only thing more useless than caps lock :) Usually compound indexes are the best idea, but you need first to see what queries are running against DB to know what to add.
yardstick 7y ago
Is the data deleted because it is too old? Partition your tables by time, and truncate/drop the old tables instead of delete.
- kjeetgill 7y ago
  We'd considered it, but partitioning hurts other things like turning a single row query into # of partitions look up.