A new Percona Toolkit series has been released: Percona Toolkit 2.2 for MySQL 5.6. Several months in the making and many new features, changes, and improvements make this a great new series. It replaces the 2.1 series for which we plan to do only one more bug fix release (as 2.1.10) then retire. 2.2 is [...]
MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
While preparing the webinar I will deliver this Friday, I ran into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice exercise for checking the performance and inner workings of one [...]
MySQL Wish for 2013 – Better Memory Accounting
December 31, 2012 By 6 Comments
With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re [...]
The Math of Automated Failover
September 18, 2012 By 9 Comments
There are number of people recently blogging about MySQL automated failover, based on production incident which GitHub disclosed. Here is my take on it. When we look at systems providing high availability we can identify 2 cases of system breaking down. First is when the system itself has a bug or limitations which does not [...]
How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
One question I often get is how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system but how do we know whenever there is any possibility to make this query [...]
Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
I presented a webinar this week to give an overview of several Full Text Search solutions and compare their performance. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. During my webinar, a number of attendees asked some good questions. Here are their questions and my [...]
Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication. For those who don’t know, it gives you: Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node Automatic cluster synchronization, both incremental and full restores The ability to read [...]
Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to degrade. The first thing you need [...]
Joining many tables in MySQL – optimizer_search_depth
April 20, 2012 By 8 Comments
Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was [...]
Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
We’re told that foreign key constraints are the best way to enforce logical referential integrity (RI) in SQL, preventing rows from becoming orphaned. But then we learn that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0 to disable enforcement of RI when the overhead is too high. But [...]
