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.
After learning about the issue, we struggled to find a way to fix the performance issue. There are 2 proposed solutions
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.
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 TABLEpt
EXCHANGE PARTITIONp
WITH TABLEnt
;
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.
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
ReplyDeleteI 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
satta king record chart result galiis an amazingly pleasant game, and furthermore is basically innocuous.
ReplyDeleteIt offers happiness, pleasure, energy, amusement and furthermore makes individuals tycoons and furthermore extremely rich people
satta king record chart result gali
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
ReplyDeletehttp://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/
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
ReplyDeletename 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
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
ReplyDeletecan I send money from cash app to paypal
ReplyDeletecan money from cash app be send through mobile number
cash app not working
cash app business account
cash app direct deposit
great java tips At SynergisticIT we offer the best java bootcamp in bay area
ReplyDeleteSBI Clerk
ReplyDeleteSBI PO
IBPS Clerk
government jobs 12th pass
upsc calendar 2021
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
ReplyDeleteSBI PO mock test
ReplyDeleteIBPS Clerk mock test
IBPS PO mock test
Upcoming Bank Exams
Upcoming Government Exams
Upcoming Government Jobs
list of competitive exams for government jobs
Manifold valves
ReplyDeleteNeedle valve
Ball valve
Globe valve
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/
ReplyDeleteSatta King
ReplyDelete"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 :-
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
ReplyDeleteGood written article you have posted here . I liked it. Photo Masking Guide
ReplyDeleteWeb Image Editing
Neck Joint Service
Cut Out Background
Image Editing Guideline
Clipping Path
Masking Help
Photoshop Editing
Neck Joint Tutorial
Photography Help
Croatia
ReplyDeleteBest Nike Shoes
Best Running Shoes
Above Ground Pools
Antalya All-Inclusive Resorts
Buckingham Palace
secret spots in London
Berlin in 3 days
People in Mexico
Dangerous in Mexico
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
ReplyDeletesites that provide the updates you want, please visit now. prediksi togel
ReplyDeletesites that provide the updates you want, please visit now.
ReplyDeleteprediksi togel korea hari ini
click on one of the sites below to get a variety of the best tips and tricks in life.
ReplyDeletepaito warna pcso
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/
ReplyDeleteclick on one of the sites below to get a variety of the best tips and tricks in life.
ReplyDeletemaster togel88
live draw sgp
ReplyDeleteprediksi wla
paito warna sydney
آموزش طراحی سایت
ReplyDeleteطراحی فروشگاه اینترنتی
click on one of the sites below to get a variety of the best tips and tricks in life.
ReplyDeletedata pengeluaran togel
google 1330
ReplyDeletegoogle 1331
google 1332
google 1333
google 1334
google 1335
click on one of the sites below to get a variety of the best tips and tricks in life master togel88
ReplyDeletegoogle 3380
ReplyDeletegoogle 3381
google 3382
google 3383
google 3384
google 3385
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.
ReplyDeleteIt 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.
ReplyDeleteIt 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.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteRead 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.
ReplyDeleteYour 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
ReplyDeleteVery 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.
ReplyDeleteThis 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.
So useful and informative blog content you have written here. Liked and appreciated it. Image Retouching
ReplyDeletePhoto Editing Helps
Neck Joint
Photoshop Guideline
Clipping Path Tutorial
Drop Shadow Helps
Background Remove
Image Editing
Image Cut Out
Photo Manipulation
Organic Chemistry Tutor
ReplyDeleteOrganic Chemistry Teacher
Thank you for posting such a great article. Keep it up mate.
ReplyDeleteGati Shakti Yojana 2021
Nice Blog , This is what I exactly Looking for , Keep sharing more blog .
ReplyDeletePHP framework Developers in India
Thanks for the information there are all wonderful and interesting. Thanks for sharing with us your ideas. is fudutsinma post utme form out
ReplyDeleteEra Internet membuat para pecinta togel dengan sangat mudah mendapatkan informasi seputar DATA HK yang sangat penting.
ReplyDeleteSetiap harinya kunjungi blog DATA HK untuk mendapatkan pengetahuan lengkap seputar pengeluaran angka togel hongkong.
ReplyDeleteTeam terbaik dan paling layak untuk memberikan rekomendasi BANDAR TOGEL TERPERCAYA di Indonesia yang sudah kami pastikan valid.
ReplyDeletePusat rekomendasi AGEN TOGEL TERPERCAYA Indonesia yang di rekap dari tahun ke tahun sehingga menjadikan suatu kesatuan yang valid.
ReplyDeleteLink menuju BANDAR ONLINE terbaik dan terpercaya yang kami kumpulkan dalam 1 tempat agar mudah di akses.
ReplyDeleteKata 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.
ReplyDeleteSetiap hari kami berikan untuk anda BANDAR TOGEL terpercaya yang sudah kami rangkum dengan sangat teliti.
ReplyDeletePortal digital LIVE DRAW HK terbaik nomor 1 di Indonesia yang di dirikan dengan tujuan membantu para pecinta Togel Indonesia.
ReplyDeleteDi kalangan para pecinta Togel Indonesia, LIVE DRAW SDY merupakan salah satu hal yang penting yang dapat memberikan hiburan dalam bermain tebak angka tersebut.
ReplyDeleteDapatkan hasil pengeluaran angka togel sydney prize 1 Indonesia yang biasa di sebut dengan : DATA SDY di blog kami hanya dengan 1x klik.
ReplyDeletePara pecinta togel Indonesia sangat gemar mencari DATA SGP di Internet dengan mengetikan kata kuncinya dengan mesin pencarian.
ReplyDeleteAngka 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
ReplyDeleteBagi 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.
ReplyDeleteFantastic and useful information.
ReplyDeleteI'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
My first choice seems like a useful program at first glance.
ReplyDeleteIn 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
It's fascinating to visit this website and read all of your friends' opinions.
ReplyDeleteWhile 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
Oh, God! Wonderful friend article! Thanks. However, I have problems with your RSS feed.
ReplyDeleteI 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
ReplyDeleteYou 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
I would like to thank you for the effort you put into writing this page.
ReplyDeleteI 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
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.
ReplyDeleteI 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.
ReplyDeleteI 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
I am very impressed with your post because this post is very beneficial for me and provide a new knowledge to me.
ReplyDeleteThanks 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
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진도출장샵
ReplyDelete구미출장샵
구미출장샵
신안출장샵
순천출장샵
순천출장샵
김천출장샵
ReplyDelete진주출장샵
진주출장샵
상주출장샵
무안출장샵
상주출장샵
창원출장샵
문경출장샵
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
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks for posting such detailed informations on SQL and Pruning.
ReplyDeleteHere i am sharing our site for Tata ACE Van Branding Service in Chennai
"Thanks for sharing this valuable content!
ReplyDeleteI'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"
This comment has been removed by the author.
ReplyDelete
ReplyDeleteKinemaster 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.
This comment has been removed by the author.
ReplyDeleteThis one is an amazing blog, and I would like to share one more information. Please check below.
ReplyDeletebest ai institute in chennai
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.
ReplyDeleteFlexible 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.
This blog post outlining the mobile app development lifecycle by Attract Group is informative and thorough. It breaks down the process into clear stages, making it easy to understand for those new to app development. The inclusion of key considerations and tips at each step adds value to the content. Overall, it serves as a helpful guide for anyone looking to navigate the mobile app development process successfully.
ReplyDeleteTierbestattungen Hechingen located in Balingen provides compassionate and respectful services for pet owners who have lost their beloved companions. With a focus on honoring the bond between humans and animals, this facility offers a dignified way to say goodbye to your furry friends.
ReplyDelete