MySQL Partitioning – can save you or kill you

MySQL Partitioning – can save you or kill you:

I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant customer case to illustrate it. First you need to understand how partitions work internally. Partitions are on the low level are separate table. This means when you're doing lookup by partitioned key you will look at one (or some of) partitions, however lookups by other keys will need to perform lookup in all partitions and hence can be a lot slower. The gain from updates typically comes from having smaller BTREE on the active partition(s) which allows for a lot better fit. Having potentially fewer level in BTREE is not that significant issue.

So lets see at example:

PLAIN TEXT

SQL:

1. 
   
   CREATE TABLE `tbl` (
   
   


2. 
   
     `id` bigint(20) UNSIGNED AUTO_INCREMENT NOT NULL,
   
   


3. 
   
     `uu` varchar(255) DEFAULT NULL,
   
   


4. 
   
     `data` bigint(20) UNSIGNED DEFAULT NULL,
   
   


5. 
   
     PRIMARY KEY (`id`),
   
   


6. 
   
     KEY `uu` (`uu`),
   
   


7. 
   
   ) ENGINE=InnoDB
   
   

The access pattern to this table is to lookup data by "uu" which has UUID values and when number of deletes by "id" and bunch of inserts. The deletes are mainly clustered around most recent id values.

The table (and index) is much larger than buffer pool size.

The first problem was replication lag, which are mainly due to modifying the uu index. This is because UUID() spreads values prefix very well effectively giving almost uniform access to all BTREE. To solve this problem partitioning was a good choice - PARTITION BY HASH (id div 10000000) PARTITIONS 32 - This allows to partition data to 32 partitions placing sequential ranges of 10M values in the same partition - very handy if you have very active access to values which ave been added to the table recently.

Using this trip replication could be speed up about 10 times as couple of partitions which were actively used could fit in buffer pool completely so replication became CPU bound (single thread) instead of IO bound.

You could celebrate but hey.... you need to check the impact on master too. Master in its turn was getting a lot of lookups by the uu value which is not part of partitioned key and hence we're looking at 32 logical lookups, one per partition. True only one of the partitions would contain the value but many of them will require physical IO and going down to the leaf key to verify such value does not exist, which reduced performance for random selects by UUID from 400 to 20 per second (from single thread).

Decreasing number of partitions made replication less efficient but the number of selects the table could deliver was increasing and there seems to be a reasonable number which would allow replication to perform better when it is now, while selects still performed in the amount system needs.

What is a take away ? When you're creating partitions think clearly what you're trying to archive. Partitioning is not some magic feature which just makes everything a lot faster. I've seen some people applying partition to basically all of their tables without much a thought and believe me results were not pretty.

---

Entry posted by Peter Zaitsev |
10 comments

Add to: | | | |