pt-online-schema-change is simple to use, but internally it is complex. Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities. Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs. The tool has two options to help answer this question: –dry-run and –print. When ran with –dry-run and –print, pt-online-schema-change changes nothing [...]
Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
I recently worked through an issue with a client trying to detect data drift across some servers that were located in different timezones. Unfortunately, several of the tables had timestamp fields and were set to a default value of CURRENT_TIMESTAMP. From the manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns are [...]
Logging Deadlock errors
September 19, 2012 By 4 Comments
The principal source of information for InnoDB diagnostics is the output of SHOW ENGINE INNODB STATUS but there are some sections that are not very useful. For example, LATEST DETECTED DEADLOCK only shows, as the name implies, the latest error detected. If you have 100 deadlocks per minute you will be able to see only [...]
Here’s a quick way to Foresee if Replication Slave is ever going to catch up and When!
August 29, 2012 By 11 Comments
If you ever had a replication slave that is severely behind, you probably noticed that it’s not catching up with a busy master at a steady pace. Instead, the “Seconds behind master” is going up and down so you can’t really tell whether the replica is catching up or not by looking at just few [...]
SQL Injection Questions Followup
July 26, 2012 By 2 Comments
I presented a webinar today about SQL Injection, to try to clear up some of the misconceptions that many other blogs and articles have about this security risk. You can register for the webinar even now that I’ve presented it, and you’ll be emailed a link to the recording, which will be available soon. During [...]
Impact of memory allocators on MySQL performance
July 5, 2012 By 11 Comments
MySQL server intensively uses dynamic memory allocation so a good choice of memory allocator is quite important for the proper utilization of CPU/RAM resources. Efficient memory allocator should help to improve scalability, increase throughput and keep memory footprint under the control. In this post I’m going to check impact of several memory allocators on the [...]
Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this [...]
Free ticket to MySQL User Conference on SF Meetup with talk “Percona XtraDB Cluster”
March 16, 2012 By Leave a Comment
Just a reminder that I will be giving a talk “Percona XtraDB Cluster” on coming San Francisco MySQL Meetup, next Wednesday, March-21, 2012. Along with talk I will give away one full ticket (tutorials + sessions + expo hall) to Percona Live MySQL User Conference 2012 and three copies our new book “High Performance MySQL, [...]
DBD::mysql 4.014 breaks pt-table-checksum 2.0
February 21, 2012 By Leave a Comment
DBD::mysql 4.014 breaks pt-table-checksum 2.0. The cause is unknown, but the effect is a lot of errors like: DBD::mysql::st execute failed: called with 2 bind variables when 6 are needed [for Statement "..." with ParamValues: ...] at ./pt-table-checksum line 7216. The fix is simple: upgrade (or even downgrade) DBD::mysql to any version except 4.014. To [...]
How To Test Your Upgrades – pt-upgrade
December 29, 2011 By 7 Comments
Upgrades are usually one of the biggest part of any database infrastructure maintenance. Even with enough planning something else can go bad after sending your production application to the version you’ve upgraded to. Let’s look at how one Percona Toolkit tool, pt-upgrade can help you identify what to expect and test your upgrades better which [...]
