Best Practices for Partitioned Collections and Tables in TokuDB and TokuMX
In my last post, I gave a technical explanation of the performance characteristics of partitioned collections in TokuMX 1.5 (which is right around the corner) and partitioned tables in relational databases. Given those performance characteristics, in this post, I will present some best practices when using this feature in TokuMX or TokuDB. Note that these best practices are designed for TokuMX and TokuDB only, which use Fractal Tree indexes. They do not necessarily apply to other B-tree based storage products.
Best practice #1: Make sure your collection should be partitioned by picking a good partition key
To use a partitioned collection, your data should have the following properties such that you can pick a good partition key. You will notice that time-series data is generally a wonderful fit.
Select a partition key such that your application will have the following properties:
- New data will likely be inserted into the last partition (e.g. time-series data with a timestamp partition key).
- Data will be deleted, or purged, based on a range of the partition key (e.g. deleting February’s data, which resides in the oldest partition). This allows you to efficiently delete data by dropping a partition, likely the oldest one.
- Many queries include a filter based on the partition key, so that queries don’t need to query all partitions. If your partition key does not have this property, then query performance may suffer and you may want to reconsider partitioning in the first place.
Examples of time-series data that are a good fit:
- Website traffic logs for which you maintain the last 6 months of data and perform analysis based on periods of time. You partition based on the timestamp of logs, and drop partitions storing the oldest month’s data on a monthly basis.
- Oplog data in TokuMX. In TokuMX 1.4, we use a partitioned collection to store oplog data. We partition on a daily basis, by default store 14 days worth of data (this is configurable), and drop the oldest partition daily. The partition key is the GTID, on which all queries are run.
Best practice #2: Choose the right partition granularity
By partition granularity, we mean, how often should you partition? For time-series data, should you partition daily? Weekly? Monthly? Here are tips on how to decide:
- Don’t concern yourself with keeping partitions small enough such that the last partition (and therefore insertions) fit in memory. This is needed for B-tree based databases, not Fractal Tree based databases. TokuMX and TokuDB insertion speed will keep up even as data flows out of memory.
- Before trying to pick a small granularity to help query performance, ask yourself if better indexing won’t achieve the same goal? See the next best practice below. If so, you can avoid using queries as a reason for having many small partitions.
- Try to pick the granularity for which you would like to delete data, because that is likely why you are partitioning. Suppose you want to keep a year’s worth of data in a time-series application. Are you willing to delete your data at 10% chunks? Then partitioning monthly is fine, and weekly is not necessary. If, on the other hand, you want to keep a tight reign on space usage, perhaps you want to partition weekly.
Best practice #3: Still follow proper indexing strategy
Suppose you are partitioning on a timestamp field. Don’t use that fact as an excuse to not include the timestamp in necessary indexes. If nearly all your queries include the timestamp field, then all your indexes ought to likely have the timestamp field as well. Follow practices for indexing as though the collection were not partitioned. This talk I gave a while back on indexing in MySQL is helpful.