Saturday, 8 April 2017

MySQL Partition Pruning

Recently, we learned an expensive lesson about MySQL partition pruning. There, it is better to share it here so that others will not repeat our mistake.

Background


In our system, there is a big stats table that does not have primary key and indexes. This table is partitioned, but the lack of indexes often causes the full partition or even full table scan when query. To make things worse, the system still continues writing to this table, making it slower every day.

To fix performance issue, we want to clean the legacy data and add new indexes. However, this is not easy because the table is too big. Therefore, we chose the long approach by migrating only the wanted data from this old table to a new table with proper schema.

Partition by hash


It would have been fine if we only did what we originally intended to do. However, we changed the partition type for convenient and that made the new table slower.

In the original table, the partition is based on a timestamp column that represents the time as a number of hours from epoch. For example, the first second of the year 2017 in GMT is 1483228800 seconds from epoch. To get the number of hours, we divide the number by 3600 to get 1483228800 div 3600) = 412008.

Because of the partition by range type, we need to have a maintenance script that creates the monthly partition for next year. This way of partition is not very ideal because the partition size is big and not even. Hence, we converted monthly to weekly partition but too lazy to define each range and switched from partition by range to partition by hash.

This is a short version of how hash definition will look like if we do the partition by range
PARTITION BY RANGE (hour_epoch)
(PARTITION pOct2016 VALUES LESS THAN (419304),
 PARTITION pNov2017 VALUES LESS THAN (420024) ENGINE = InnoDB,
 PARTITION pDec2017 VALUES LESS THAN (420768) ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
And this is how the partition definition will look like if we do partition by hash
partition by hash (hour_epoch div 168) partitions 157;
The partition by hash type did more than just shorten the syntax. MySQL will try to split records evenly by applying modulo function to select a partition. However, to make the duration of one partition one week, we divide hour_epoch number by 168 to effectively get week_epoch.

With the new table schema, we were happy with smaller partitions, shorter description, and more indexes.

Performance issue


Because of the huge volume of data, we could not fully migrate data to the new schema to verify performance. We only did the preliminary performance test with the data of 2 weeks and did not detect any performance issue. However, in the final testing, we were surprised to observe mixed result. Most of the queries are faster as expected, but some are slower.

After investigating, we realized that instead of scanning only a few partitions, MySQL does the full table scanning for time range query. It is even stranger that this behavior only happens with the date range smaller than 3 weeks. Totally surprised by this result, we overcame our procrastination to read up MySQL document carefully and realize why.

"For tables that are partitioned by HASH or [LINEAR] KEY, partition pruning is also possible in cases in which the WHERE clause uses a simple = relation against a column used in the partitioning expression"

As the document clearly explained, the partition pruning only works with the equal condition for partition by hash type.  However, we did not detect this issue earlier because of the query optimizer will auto convert range condition to equal condition if the number of distinct values in between of the range condition is short enough. Unfortunately, in our early test, the data of 2 weeks is short enough for the query optimizer to hide the problem from us.

Solution


After learning about the issue, we struggled to find a way to fix the performance issue. There are 2 proposed solutions

  • Trick the query optimizer to do the work by splitting a big range to multiple small ranges, each fit one partition. In this way, the query optimizer will work on each individual small ranges.
  • Rebuild the schema again with the proper partition type. 
The first solution is quick but dirty while the second solution is too time-consuming. Eventually, we almost decided to launch the new table with the first solution until finding a quick way to implement the second solution.

We have dug through MySQL document and learned that re-parititioning is basically a copy and paste operation. However, MySQL also has another command that allows us to do some partition change without too much effort.
ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

In this command, MySQL allows us to exchange partition between a table and a partition of another table. Even when this is not a direct exchange between 2 partitions of 2 tables, it is just a matter of inconvenience to do one more middle swap to a temp table.

This is how our partition swapping looks like

ALTER TABLE origin_table EXCHANGE PARTITION p1 WITH TABLE temp_table;
ALTER TABLE final_table EXCHANGE PARTITION p1 WITH TABLE temp_table;

Even though this is not as fast as you may guess as MySQL will do a row by row validation to ensure every record of temp table is elligible for storing in the final table partition. If we use MySQL 5.7, this validation can be turned off by adding "WITHOUT VALIDATION" to the end of the second command.

Because we use Aurora, which only support MySQl 5.6, it still took us 2 days to fully update the partition type. However, this would have been one month if we do not use partition exchange.

Fortunately, we managed to recover from the mistake this time. We hope that you learn from our mistake and do remember to read the document carefully before using any fancy method.



14 comments:

  1. Thank you for sharing your thoughts and knowledge on this topic. This is really helpful and informative, as this gave me more insight to create more ideas and solutions for my plan. I would love to see more updates from you.

    Web Hosting Services

    ReplyDelete
  2. Practice all that you can. Football may look easy play bazaar satta king when watching it on television, but that's far from the truth. It's a very physically demanding sport that also take a lot of brain power. You need to remember patterns and think on your feet with little notice to succeed. All of this takes practice.play bazaar satta king

    ReplyDelete


  3. I am very amazed by the information of this blog and i am glad i had a look over the blog. thank you so much for sharing such great information
    clipping path service|Photo Retouching services|Vector Tracing

    ReplyDelete
  4. Hello Admin!

    Thanks for the post. It was very interesting and meaningful. I really appreciate it! Keep updating stuffs like this. If you are looking for the Advertising Agency in Chennai / Printing in Chennai , Visit us now..

    ReplyDelete
  5. United States of America commonly known as US. US holding 50 states and the all states having own value in every perspectives , If you are planing for the visit as a tourist in USA. Then you have many options to visit USA. If you want to know where to visit people mostly? Then you can see by these give below pictures. Most of the people visit to these destinations and love it.
    Cheap Flights
    For Cheap Flights
    Cheap Flights to Florida
    Cheap Flights to Miami
    Cheap Flights to Amsterdam
    Cheap Flights to Wichita
    Cheap Flights to Moscow
    Cheap Flights to Denver
    Cheap Flights to Atlanta
    Cheap Flights to Buenos Aires
    Cheap Flights to Boston
    Cheap Flights to Chicago
    Cheap Flights to Sao Paulo
    Cheap Flights to Thailand
    Cheap Flights to Las Vegas
    Cheap Flights to San Francisco
    Cheap Flights to Seattle
    Cheap Flights to Los Angeles

    ReplyDelete
  6. satta king record chart result galiis an amazingly pleasant game, and furthermore is basically innocuous.
    It offers happiness, pleasure, energy, amusement and furthermore makes individuals tycoons and furthermore extremely rich people
    satta king record chart result gali

    ReplyDelete
  7. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    court marriage in delhi ncr
    court marriage in delhi
    court marriage in noida
    court marriage in ghaziabad
    court marriage in gurgaon
    court marriage in faridabad
    court marriage in greater noida
    name change online
    court marriage in chandigarh
    court marriage in bangalore

    ReplyDelete
  8. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    http://chennaitraining.in/test-complete-training-in-chennai/
    http://chennaitraining.in/load-runner-training-in-chennai/
    http://chennaitraining.in/jmeter-training-in-chennai/
    http://chennaitraining.in/soapui-testing-training-in-chennai/
    http://chennaitraining.in/mobile-application-testing-training-in-chennai/
    http://chennaitraining.in/html-training-in-chennai/

    ReplyDelete
  9. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    birth certificate in delhi
    name add in birth certificate
    birth certificate in gurgaon
    birth certificate correction
    birth certificate in noida
    birth certificate
    birth certificate in ghaziabad
    birth certificate in india
    birth certificate apply online
    birth certificate in bengaluru

    ReplyDelete


  10. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    death certificate in delhi
    death certificate in bangalore
    death certificate in mumbai
    death certificate in faridabad
    death certificate in gurgaon
    death certificate in noida
    duplicate driving licence delhi
    death certificate online
    death certificate
    death certificate apply online

    ReplyDelete
  11. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    name change procedure in ghaziabad
    name change procedure delhi
    name change procedure gurgaon
    name change in faridabad
    name change in noida
    name change
    name change in india
    name change procedure in bangalore
    name change procedure in rajasthan
    name change procedure in maharashtra

    ReplyDelete
  12. best site for satta king result, leak number  all game record charts. We provide 100% fix number direct from Satta king gali company which includes all famous games like Satta king Desawar, Gali Satta, Ghaziabad, Faridabad, Shri Ganesh Satta, Taj Satta King, charminar and other games of Satta Market Matka is also a simple game and essentially is a form of old lottery games. Ratan Khatri was the founder of this game in the 70 century and was become popular up until the 90 century. The game is not played that much anymore mostly in the regions of North India and Pakistan. Instead, many enjoy the lottery games Satta king result  more so these days.
    Here is an example card. satta-king.online is the no1 satta king site where you can get the fastest Satta result, Satta king leak number (confirm jodi), Old Satta King ghaziabad, Daily leak Jodi, Desawar Jodi, Satta king faridabad, Satta record chart, Satta king taj, Gali Satta result, Ghaziabad Satta Result, Satta Bazar result and 100% passing fix Jodi today.

    ReplyDelete