The recording and slides for my webinar on zero-downtime schema changes with MySQL are available now. Don’t miss Vadim’s webinar tomorrow!

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 to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many cases. Whatever powerful and well tuned system you have if you put too heavy of concurrent load on it the response times will increase and user experience will suffer.

So what you can do to prevent this problem from happening ? The answer is easy. Throttle the side load so it does not consume too much system resources. Here are some specific techniques to use.

Do push concurrency too high Many developers will test script with …

The recording of yesterday’s webinar, as well as a PDF of my slide deck, is now available. You can watch the recording and get the slides here.

I got a lot of questions, and did not have enough time to answer all of them, so let me try to answer some of the remaining questions here.

Q: Isn’t it possible to take all the locks required in the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that would cause blocking that would defeat the purpose of the tool.

Q: When you say bail out, what does that mean? Does the tool clean up (i.e., remove table / triggers?) About cleanup.. What about manual aborting a run. will that cleanup ? Q: dose the tool exit gracefully while interupted? The tool doesn’t clean up the temporary table or triggers. It is designed …

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 very good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance.

Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. …

I’m proud to announce the GA release of version 2.1 of Percona Toolkit. Percona Toolkit is the essential suite of administrative tools for MySQL.

With this release we introduce a new version of pt-online-schema-change, a tool that enables you to ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change performs the ALTER without any locking. Client applications can continue reading and writing the table with no interruption.

With this new version of the tool, one of the most painful things anyone experiences with MySQL is significantly alleviated. If you’ve ever delayed a project’s schedule because the release involved an ALTER, which …

This is the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5, and MariaDB 5.5 has introduced some additional optimizations which we will also look at, in this post.

Now let me briefly explain these optimizations.

Batched Key Access

Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating in the joins are read, and then for each of these rows an index lookup is performed on the second table. This means many point queries, say for example if table1 yields 1000 …

I’m excited by all of the recent developments surrounding the Percona Live MySQL Conference and Expo! Our own Baron Schwartz will moderate the Diamond Keynote Panel entitled “Future Perfect: The Road Ahead for MySQL” which will feature a panel of MySQL industry leaders, including: Sundar Raghavan, director product management at Amazon; Paul Mikesell, CEO of Clustrix; a representative from HP; and, a representative from McAfee. The Diamond Sponsor Keynote Panel will take place at 9:30 a.m. on Thursday, April 12th and provide insight into the future of MySQL technology, adoption, and the ecosystem landscape. I am also very pleased to introduce two new sponsors including McAfee which recently joined as a Diamond Sponsor and AOL which …

Most of the Nagios plugins included with the Percona Monitoring Plugins are purpose-built for specific scenarios that I found in my research to be frequent, severe, or subtle causes of problems in MySQL systems. They are intentionally not generic because I wanted to focus on doing one thing with each plugin, and doing it excellently. This makes the plugins easier to set up and use, and I believe it should help avoid some of the common problems such as spammy alerts.

There is one “generic, flexible” plugin, however, which you can use for fairly arbitrary checks of status counters and variables. This is the …

Many backup tools including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which server has to be made read only is very short, just few seconds, yet the impact of FLUSH TABLES WITH READ LOCK can be quite large because of the time it may take to complete this statement. Lets look at what the problem is.

As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement, and as such it has to wait for any SELECT statement to complete in order to complete locking tables. This means if you have …

Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, but did you know there are some cases when you can get table entirely missing from the backup if you use this technique ?

The problem comes from the fact how MySQL’s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified structure, copy data to that table and when drop original table and rename such temporary table to original name.

How does data visibility works in this case ? DDLs are not transactional and as such the running transaction will not see the contents of old table once it is dropped, transaction also will see the new table which was created after transaction was started, including table created by ALTER TABLE …