4 ms·

"We would DELETE from those tables every few hours because we were only interested in the last 2 weeks worth of rows, so the dataset size was constrained/bounde

"We would DELETE from those tables every few hours because we were only interested in the last 2 weeks worth of rows, so the dataset size was constrained/bounded, so slow downs weren't a result of tables getting larger." Yeah, this workload is hard on any LSM store, unless compaction is carefully tuned so that the tombstones are cleaned up often enough. Most people would recommend using partitioning...

markpapadakis 9y ago
Yes, we 'd insert new rows every 30 minutes or so. Also, we did try partitioning one of the 3 tables (which made sense) into multiple tables -- it didn't improve the situation at all. Again, there are probably tunables and practices specific to MyRocks that we just didn't consider. We just didn't want to pursue this any further.
- Shelnutt2 9y ago
  I believe hendzen was referring to using partition on a table in mysql. Mariadb has an example walkthrough on this: https://mariadb.com/kb/en/library/partition-maintenance/#partition-maintenance-for-the-time-series-case https://mariadb.com/kb/en/library/partition-maintenance/#par... . The only change that I'd recommend considering, is we use partition by list, instead of range, and keep a weeks worth of daily future partitions. This makes it so you don't have to reorganize the table. On the other hand it means if you get future data it can fail and if your partition jobs have issues for a week you can fail to load data all together. Using partitions for time series data help also due to partition pruning, https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning... .
  - markpapadakis 9y ago
    Thank you -- we didn't use table partitioning because we didn't have good results in the past, though we should probably have tried it, and maybe it'd have worked well. We just kind of gave up on mySQL use for that specific problem after failing (again, maybe its entirely because of our inability to use mySQL "properly") to get good results and moved on to something else.

markpapadakis 9y ago

Yes, we 'd insert new rows every 30 minutes or so. Also, we did try partitioning one of the 3 tables (which made sense) into multiple tables -- it didn't improve the situation at all. Again, there are probably tunables and practices specific to MyRocks that we just didn't consider. We just didn't want to pursue this any further.

Shelnutt2 9y ago
I believe hendzen was referring to using partition on a table in mysql. Mariadb has an example walkthrough on this: https://mariadb.com/kb/en/library/partition-maintenance/#partition-maintenance-for-the-time-series-case https://mariadb.com/kb/en/library/partition-maintenance/#par... . The only change that I'd recommend considering, is we use partition by list, instead of range, and keep a weeks worth of daily future partitions. This makes it so you don't have to reorganize the table. On the other hand it means if you get future data it can fail and if your partition jobs have issues for a week you can fail to load data all together. Using partitions for time series data help also due to partition pruning, https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning... .
- markpapadakis 9y ago
  Thank you -- we didn't use table partitioning because we didn't have good results in the past, though we should probably have tried it, and maybe it'd have worked well. We just kind of gave up on mySQL use for that specific problem after failing (again, maybe its entirely because of our inability to use mySQL "properly") to get good results and moved on to something else.