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

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

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

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

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

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.

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

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

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

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at.

Index Condition Pushdown

Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example,

For about the past year I’ve been formulating a series of tools and practices that can provide deep insight into system performance simply by looking at TCP packet headers, and when they arrive and depart from a system. This works for MySQL as well as a lot of other types of systems, because it doesn’t require any of the contents of the packet. Thus, it works without knowledge of what the server and client are conversing about. Packet headers contain only information that’s usually regarded as non-sensitive (IP address, port, TCP flags, etc), so it’s also very easy to get access to this data even in highly secure environments.

I’ve finally written up a paper that shows some of my techniques for detecting problems in a system, which can be an easy way to answer questions such as “is there something we

In this post, I’ll cover the new MySQL monitoring plugins we created for Nagios, and explain their features and intended purpose.

I want to add a little context. What problem were we trying to solve with these plugins? Why yet another set of MySQL monitoring plugins?

The typical problem with Nagios monitoring (and indeed with most other monitoring systems) is the alert spam. I’ve spoken to a lot of users with monitoring systems over the years, and everyone says that they get a lot of false alarms, and that they have email filters set up for alerts. Recently I spoke to one person who gets about thirty thousand alerts per week (!!!) in his email. I’m sure you can see that this makes the monitoring system much less effective. The usual cause of

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 see which version of DBD::mysql a system has, execute:

perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, "\n";'

This bug may affect other Percona Toolkit tools, but currently pt-table-checksum 2.0 is the only victim. This bug does not affect pt-table-checksum 1.0, and it cannot be worked around in

In my previous post I explained how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the recovery process more straightforward. Now the scenario is worse because we don’t have a backup or the backup restore process doesn’t work. How can I recover deleted rows?

We’re going to follow the same example as in my previous post so we need to delete the records of the employee 10008 from the table “salaries”. After the “accidental” deletion of rows you should stop MySQL, take a copy of the salaries.ibd and start it again. Later, we’ll extract those deleted rows from the ibd file and import them into the

In response to the release of our new MySQL monitoring plugins on Friday, one commenter asked why the new Nagios plugins don’t use caching. It’s worth answering in a post rather than a comment, because there is an important principle that needs to be understood to monitor servers correctly. But first, some history.

When I wrote a set of high-quality Cacti templates for MySQL a few years ago (which are now replaced by the new project), making the Cacti templates use caching was important for two reasons:

We’ve released a new set of monitoring plugins for MySQL servers and related software. With these plugins, you can set up world-class graphing and monitoring for your MySQL servers, using your own on-premises Cacti and Nagios software. The Cacti plugins are derived from an existing set of templates we’ve been using for several years, but the Nagios check plugins are brand new. They are informed by the research we did into the causes and preventions of MySQL downtime.

Like all Percona software, the plugins are open-source and free, licensed under the GNU GPL.

Wouldn’t it be nice if you could get expert advice on your SQL queries to find problems in them, the same way that programmers can use lint-check tools to warn about bugs in their C?

if ( execute = 1 ) {
   launch_missile();
}

Such a simple mistake, but it’s the kind of thing that James Bond movies are made of, isn’t it? Well, a lot of SQL queries have similar bugs, and thanks to Miguel Trias, now there’s a tool to help you find them. This is the second addition to our online suite of tools for MySQL users. You paste a query, it tells you what’s wrong with it. Simple as that.

Find the bug in this query:

select * from t1
   left join t2 using(id)
where t2.created_date 


Do you see it?

So lets say you upgraded from MySQL 5.1 to Percona Server 5.5 and instead of expected performance improvement you see your performance being worse. What should you do ?
First if you followed MySQL upgrade best practices such as testing your workload with pt-upgrade the chances of this happening are rather slim. But lets assume you have not followed these recommendations to the book or some things just slipped through.

First lets talk about what you should not do. You should not panic and go change all kind of configuration options in case you get lucky. Sometimes you do but more often you just waste your time. Instead try to understand what has changed

A couple of users of our very popular MySQL Configuration Wizard have submitted feedback such as “I’d love to input my existing server settings and get suggestions on how to improve it.”

This sounds like it would be great, doesn’t it? We’ve considered doing this, and even partially implemented it. But during our pre-release testing, we found a lot of potentially serious problems with the idea. It turns out to be very subtle — perhaps too subtle to be done with any computer program, no matter how smart, because there might be a lot of assumptions it’s forced to make, which could turn out to be dangerous. Every approach we considered carried a high risk of de-optimizing a server so it performs worse than before.

So in the end, although our tool

We’ve released Percona Toolkit 2.0.3, with a couple of major improvements and many minor ones. You can download it, read the documentation, and get support for it.

What’s new? You can read the changelog for the details, but here are the highlights:

Brand new pt-diskstats, thanks to Brian Fraser. This tool is completely rewritten, and it’s finally the iostat replacement I always wanted. Not only does it have the functionality I want (interactive, slice

An attendee to Espen’s recent webinar asked how to check tables for corruption. This kind of ties into my recent post on InnoDB’s handling of corrupted pages, because the best way to check for corruption is with CHECK TABLES, but if a page is corrupt, InnoDB will crash the server to prevent access to the corrupt data. As mentioned in that post, this can only be changed by changing InnoDB.

So how are you supposed to check for corruption that might be introduced by bad hardware, a bug, or so forth?

It’s a great question. The answer I would give for most cases is “check your backups for corruption

We’ve released an updated version of the MySQL Configuration Wizard we announced at the end of last year. If you don’t remember that announcement, here’s the short version: this is a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.

We’ve gotten really good feedback on this tool, including this nice mention on Stack Exchange:

Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf’s were put together by highly trained mysql certified dba’s.

So

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit different story.

With Oracle’s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition is stored in the InnoDB shared tablespace (ibdata) and the transaction IDs and log sequence numbers that are stored in the tablespace files also differ between servers. Therefore our example will be very straightforward: we’ll delete some rows from a table in order to recover the table later.

Most of these limitations are solved on Percona

Sometimes you might hear people talk about full backups, and differential backups versus incremental backups. What is the difference?

A full backup is pretty self-explanatory. It makes a copy of all of your MySQL data.

A differential backup, on the other hand, simply records the differences since your last full backup. The advantage of taking a differential backup is usually the space savings. Most databases have a lot of data that does not change from one backup to the next. Not copying this data into your backups can result in significantly smaller backups. In addition, depending on the backup tool used, a differential backup can be less labor-intensive for the server. If a differential backup does not have to scan all of the data to determine what has changed, the differential backup process can be significantly more efficient.

This is about the second great feature – Multi-Master replication, what you get with Percona XtraDB Cluster.

It is recommended you get familiar with general architecture of the cluster, described on the previous post.

By Multi-Master I mean the ability to write to any node in your cluster and do not worry that eventually you get out-of-sync situation, as it regularly happens with regular MySQL replication if you imprudently write to the wrong server.

This is long-waited feature, I’ve seen growing demand for it for last two years or even more.
Percona XtraDB Cluster provides it, and let’s see how it works.

With our

With the recent release of Percona XtraDB Cluster, I am increasingly being asked about MySQL’s semi-synchronous replication. I find that there are often a number of misconceptions about how semi-synchronous replication really works. I think it is very important to understand what guarantees you actually get with semi-synchronous replication, and what you don’t get.

The first thing to understand is that despite the name, semi-synchronous replication is still asynchronous. Semi-synchronous is actually a pretty bad name, because there is no strong coupling between a commit on the master and a commit on the replicas. To understand why, let’s look at what truly synchronous replication means. In truly synchronous replication, when you commit a

Backing up binary logs are essential part of creating good backup infrastructure as it gives you the possibility for point in time recovery. After restoring a database from backup you have the option to recover changes that happend after taking a backup. The problem with this approach was that you had to do periodic filesystem level backups of the binary log files which could still lead to data loss depending on the interval you back them up.
Recently in MySQL 5.6, mysqlbinlog got a new feature addition that supports connecting to remote MySQL instances and dumping binary log data to local disks ( http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html ). This can be used as a foundation of our live binary log backups.

The wrapper script below

In our recent release of Percona Server 5.5.19 we introduced new value for innodb_flush_neighbor_pages=cont.
This way we are trying to deal with the problem of InnoDB flushing.

Actually there is also the second fix to what we think is bug in InnoDB, where it blocks queries while it is not needed (I will refer to it as “sync fix”). In this post I however will focus on innodb_flush_neighbor_pages.

By default InnoDB flushes so named neighbor pages, which really are not neighbors.
Say we want to flush page P. InnoDB is looking in an area of 128 pages around page P, and flushes all the pages in that

There and in coming posts I am going to cover main features of Percona XtraDB Cluster. The first feature is High Availability.

But before jumping to HA, let’s review general architecture of the Percona XtraDB Cluster.

1. The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes too.
2. Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL / Percona Server into Node and roll Cluster using it as base. Or otherwise – you can detach Node from Cluster and use it as just a regular server.
3. Each Node contains the full copy of data. That defines XtraDB Cluster behavior in many ways. And obviously