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.



75 comments:

  1. 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


  2. 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
  3. 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
  4. 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
  5. 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
  6. Informative and educative post you have shared with us . A lot of thanks for this posting . Image Masking Service | Cut Out Photo | Photo Cut Out

    ReplyDelete
  7. great java tips At SynergisticIT we offer the best java bootcamp in bay area

    ReplyDelete
  8. Nice and interesting post. Thank You! For sharing such a great article, I like to read your information you have mentioned in this article are helpful for me. for More Information Click Here: Outdoor Advertising Company in Jaipur

    ReplyDelete
  9. The logistics performance index of Finland is 3.62. It indicates a satisfactory performance - in general, traffic is handeled well, some flaws in certain areas are possible, but overall the logistics system performs reliably and is ready to handle predictable amounts of traffic. http://www.confiduss.com/en/jurisdictions/finland/infrastructure/

    ReplyDelete
  10. Satta King
    "This is the best blog with all the related information which helps us alot and yes I would also thank to the author for sharing such a wonderful and most impotently the helping information.
    Thank you."
    If Want Play online Satta King Game Click Satta King :-

    ReplyDelete
  11. Excellent post! Your post is very useful and I felt quite interesting reading it. Expecting more post like this. Thanks for posting such a good post. laptop service in home. To service your laptop with offer prices, Please visit : Laptop service center in Navalur

    ReplyDelete
  12. The way you describe this blog is really nice. Thanks Shearing I recommend you visit my site which gives you free online typing speed test and exciting typing games and keyboarding practice. Check your WPM for free now! With us here : typingspeedtest

    ReplyDelete
  13. sites that provide the updates you want, please visit now. prediksi togel

    ReplyDelete
  14. sites that provide the updates you want, please visit now.

    prediksi togel korea hari ini

    ReplyDelete
  15. click on one of the sites below to get a variety of the best tips and tricks in life.

    paito warna pcso

    ReplyDelete
  16. Company formation in Denmark involves a number of stages of varying complexity and therefore requires professional assistance. The Confidus Solutions team will prepare legal documents, assist with registration and finalise the incorporation procedure. http://www.confiduss.com/en/jurisdictions/denmark/business/company-formation/

    ReplyDelete
  17. click on one of the sites below to get a variety of the best tips and tricks in life.

    master togel88

    ReplyDelete
  18. click on one of the sites below to get a variety of the best tips and tricks in life.

    data pengeluaran togel

    ReplyDelete
  19. click on one of the sites below to get a variety of the best tips and tricks in life master togel88

    ReplyDelete
  20. It was really an interesting time reading this article, we appreciate every article you've made. This is the website where you can get to learn more Text to Speech advantages and disadvantages. Here you can get all tips an tricks related to text to speech .So please visit the site to read it's latest post text to speech converter.

    ReplyDelete
  21. It was really an interesting time reading this article, we appreciate every article you've made. This is the website where you can get to learn more the greatest cloud-based gaming. Here you can get all tips an tricks related to gaming. So please visit the site to read it's latest post RBX codes.

    ReplyDelete
  22. It was really an interesting time reading this article, we appreciate every article you've made. This is the website where you can get to learn more Test typing speed. Here you can get all tips an tricks related to typing. So please visit the site to read it's latest post Typing speed.

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. Read the article above. It's very informative and clear, it explains everything in depth. Thanks and best wishes on your upcoming articles The article on Mouse Left Click Not Working can provide more information about quick fix for mouse left click issue. You can learn more here. I appreciate your attention.

    ReplyDelete
  25. Your blog writing skills contains heights of creativity, effort and hard work. I guess almost every visitor impressed with information shared here. Thanks for publishing it for us. help with programming assignment

    ReplyDelete
  26. Very interesting, good job and thanks for sharing such a good blog. Your article is so convincing that I never stop myself to say something about it. You’re doing a great job.Keep it up.
    This is the article where you will learn about fast auto clicker for mac So if you want to learn more about this you can go through the full article name Auto Clicker for Mac. Visit and be a tech-knowie.

    ReplyDelete
  27. Thank you for posting such a great article. Keep it up mate.

    Gati Shakti Yojana 2021

    ReplyDelete
  28. Nice Blog , This is what I exactly Looking for , Keep sharing more blog .
    PHP framework Developers in India

    ReplyDelete
  29. Thanks for the information there are all wonderful and interesting. Thanks for sharing with us your ideas. is fudutsinma post utme form out

    ReplyDelete
  30. Era Internet membuat para pecinta togel dengan sangat mudah mendapatkan informasi seputar DATA HK yang sangat penting.

    ReplyDelete
  31. Setiap harinya kunjungi blog DATA HK untuk mendapatkan pengetahuan lengkap seputar pengeluaran angka togel hongkong.

    ReplyDelete
  32. Team terbaik dan paling layak untuk memberikan rekomendasi BANDAR TOGEL TERPERCAYA di Indonesia yang sudah kami pastikan valid.

    ReplyDelete
  33. Pusat rekomendasi AGEN TOGEL TERPERCAYA Indonesia yang di rekap dari tahun ke tahun sehingga menjadikan suatu kesatuan yang valid.

    ReplyDelete
  34. Link menuju BANDAR ONLINE terbaik dan terpercaya yang kami kumpulkan dalam 1 tempat agar mudah di akses.

    ReplyDelete
  35. Kata kunci SITUS TOGEL TERPERCAYA merupakan hal yang sangat penting di mesin pencarian dan populer, oleh karena itu kami membuat situsnya agar kalian dapat menemukan semuanya dengan mudah.

    ReplyDelete
  36. Setiap hari kami berikan untuk anda BANDAR TOGEL terpercaya yang sudah kami rangkum dengan sangat teliti.

    ReplyDelete
  37. Portal digital LIVE DRAW HK terbaik nomor 1 di Indonesia yang di dirikan dengan tujuan membantu para pecinta Togel Indonesia.

    ReplyDelete
  38. Di kalangan para pecinta Togel Indonesia, LIVE DRAW SDY merupakan salah satu hal yang penting yang dapat memberikan hiburan dalam bermain tebak angka tersebut.

    ReplyDelete
  39. Dapatkan hasil pengeluaran angka togel sydney prize 1 Indonesia yang biasa di sebut dengan : DATA SDY di blog kami hanya dengan 1x klik.

    ReplyDelete
  40. Para pecinta togel Indonesia sangat gemar mencari DATA SGP di Internet dengan mengetikan kata kuncinya dengan mesin pencarian.

    ReplyDelete
  41. Angka togel keluaran sydney merupakan angka yang sangat di cari dan di butuhkan bagi para pecinta Togel Indonesia, maka dari itu team kami membuat suatu blog yang memperbaharui keluaran togel sydney setiap hari bagi anda yang kami sebut dengan : DATA SYDNEY

    ReplyDelete
  42. Bagi para pecinta togel hongkong, kami sarankan hanya menyaksikan pengeluaran angka togel LIVE DRAW HK setiap hari di situs yang kami rekomendasikan. Mengapa demikian, karena situs yang kami rekomendasikan merupakan situs yang paling cepat dan valid dalam memperbaharui angka setiap harinya.

    ReplyDelete
  43. Fantastic and useful information.
    I'm glad to hear you're doing well.
    Thanks for providing this useful information! Please keep us posted.
    Here you go. Thanks for sharing.
    wondershare pdfelement crack key
    360 total security crack
    r drive image crack
    4k video downloader crack

    ReplyDelete
  44. My first choice seems like a useful program at first glance.
    In short, depending on the location of the user, loading and storing your information is supported.
    I really appreciate your smart writing, like the design of your blog.
    Problems with payment or did you pick it up yourself?
    Either way, it's best to keep a record.
    nero burning rom 2021 crack
    bzzt image editor pro crack
    anydvd hd crack
    mailbird pro crack

    ReplyDelete
  45. It's fascinating to visit this website and read all of your friends' opinions.
    While I am interested in the subject of this piece of writing, I am also excited to
    acquire familiarity
    magix vegas movie studio crack
    graphics converter pro crack
    express vpn crack
    windows 7 ultimate crack

    ReplyDelete
  46. Oh, God! Wonderful friend article! Thanks. However, I have problems with your RSS feed.
    I don't understand why I can't join. Does anyone have the same problems with RSS? Who knows the answer, could you answer me? Thanks!!
    nikon camera control pro crack
    3delite duplicate audio finder crack
    4k video downloader crack
    g data clean up crack

    ReplyDelete

  47. You have a great site, but I wanted to know if you know.
    Any community forum dedicated to these topics.
    What was discussed in this article? I really want to be a part of it.
    A society in which I can obtain information from others with knowledge and interest.
    Let us know if you have any suggestions. I appreciate this!

    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack
    auslogics disk defrag ultimate crack

    ReplyDelete
  48. I would like to thank you for the effort you put into writing this page.
    I also hope that you will be able to check the same high-quality content later.
    Really, your creative writing skills have inspired me to create my own blog now😉
    anno1800 crack
    anno1800 full pc game crack
    anno1800 crack
    anno1800 full pc game crack torrent

    ReplyDelete
  49. Music and Performing Arts today is growing to be fields that students would like to consider as professional education options. All professional courses In Music and Performing Arts are available through us. Coaching And Mentoring In Music & Performing Arts is also our forte.

    ReplyDelete
  50. I am very impressed with your post because this post is very beneficial for me and provide a new knowledge to me. this blog has detailed information, its much more to learn from your blog post.I would like to thank you for the effort you put into writing this page.
    I also hope that you will be able to check the same high-quality content later.Good work with the hard work you have done I appreciate your work thanks for sharing it. It Is very Wounder Full Post.This article is very helpful, I wondered about this amazing article.. This is very informative.
    “you are doing a great job, and give us up to dated information”.
    vovsoft text edit plus crack
    nero burning rom crack
    audials one crack
    iobit driver booster pro crack
    movavi video editor plus crack

    ReplyDelete
  51. I am very impressed with your post because this post is very beneficial for me and provide a new knowledge to me.
    Thanks for sharing this post is an excellent article. Keep it up. I use the same blogging platform that you have and have.
    it Is Very Informative Thanks For Sharing. I have also Paid This sharing. I am ImPressed For With your Post Because This post is very.
    minitool power data recovery
    4k video downloader
    microsoft office 2016 product key
    microsoft office 2019 crack
    windows 8 enterprise crack

    ReplyDelete
  52. This is the sort of clever work and exposure! Keep up the awesome writing. It’s a wonderful review and help to many who would need this. Thank you for sharing this needful article. Also visit ekounimed approved school fees and acceptance fee

    ReplyDelete
  53. This is a great article. It's a real information source. We would love to see more articles. Looking forward to a more great post from you. Thanks for sharing. unilag school of post-graduate studies application deadline

    ReplyDelete
  54. This comment has been removed by the author.

    ReplyDelete
  55. Thanks for posting such detailed informations on SQL and Pruning.
    Here i am sharing our site for Tata ACE Van Branding Service in Chennai

    ReplyDelete
  56. "Thanks for sharing this valuable content!

    I'm glad I found RatSMS Messaging Service which is helpful, and I hope it helped you increase sales and customer satisfaction.

    Click to know more:
    bulk sms service
    bulk sms provider
    sms blaster
    "

    ReplyDelete
  57. This comment has been removed by the author.

    ReplyDelete

  58. Kinemaster Pro Crack Full Version
    Kinemaster Pro Crack has user-friendly design ensures fluid publishing experiences by making it simple to browse throughout program’s capabilities.

    ReplyDelete
  59. This comment has been removed by the author.

    ReplyDelete
  60. This one is an amazing blog, and I would like to share one more information. Please check below.
    best ai institute in chennai

    ReplyDelete
  61. Dental merchant processing facilitates secure electronic transactions for dental practices, streamlining payment procedures and enhancing patient experience. Tailored to the unique needs of dental professionals, it offers seamless integration with practice management systems, enabling swift and accurate billing. With robust encryption and compliance with industry standards such as HIPAA, it ensures the confidentiality of sensitive patient information.

    Flexible payment options, including credit cards, debit cards, and electronic funds transfers, accommodate diverse patient preferences. Additionally, features like recurring billing and appointment scheduling optimize administrative efficiency. By providing reliable, efficient payment solutions, Dental merchant processing supports the financial health and operational effectiveness of dental practices.

    ReplyDelete