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:
Examples of time-series data that are a good fit:
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:
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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.