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 …

The Percona Live MySQL Conference & Expo is going to be awesome! Great speakers, an A-list of sponsors, countless opportunities to engage with the community, and an enthusiastic crowd of MySQL users ensure this is going to be a great event. The conference features 72 breakout sessions, keynotes by leading industry luminaries, an optional day of 16 tutorial sessions, a bustling exhibit hall, and numerous opportunities to connect with other community members.

I am pleased to announce the conference Birds of a Feather sessions and Lightning Talks. Birds of a Feather sessions will be Tuesday and Wednesday nights following the evening receptions. Lightning Talks will be …

This is the second 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 aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look at what this optimization actually is and what benefits it brings.

Multi Range Read

With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key lookups for column values that do not belong to the secondary index definition can result in a lot of Random I/O. The …

One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as
innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other problems.

This simple problem on the surface becomes challenge with MySQL because there are no clear resource usage metrics available, and so in most cases you do not know where exactly memory is allocated. This was not much of the problem in MySQL 3.23 when there would only handful of places where memory could be allocated but it is a lot larger problems with MySQL 5.5 with addition of user variables, stored procedures, prepared statements etc which can be a memory hog.

My intent with this post is dual. I would encourage MySQL Team at Oracle, MariaDB team or …

Next Wednesday, March-21, 11:00am Pacific Time, Baron and me will be co-presenting with Virident webinar: “How to Turbocharge Your MySQL Performance Using Flash Storage” (From Virident side: Shridar Subramanian and Shirish Jamthe).

Running MySQL on SSD in interesting topic and on the webinar we will cover:

• Configuration and optimization techniques to fully leverage flash-based storage solutions in MySQL environments
• Evaluation criteria and techniques for selecting the suitable flash-storage technology for the relevant MySQL workloads
• Price/performance advantages (ROI) when flash storage is used appropriately for MySQL workloads
• Approaches for scaling MySQL instances on fewer servers while delivering optimal performance using flash drives

The registration is free and …