Percona is glad to announce the release of Percona XtraBackup 2.0.5 on January 23rd, 2012. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.0 series.

New Features:

• New option --defaults-extra-file has been introduced. This option specifies from what extra file to read the default MySQL options before the standard defaults-file. It can be used to load the user/password combination for the dedicated backup user from a separate configuration file, to avoid storing it in the crontab or a script somewhere in the system.

Bugs

Percona Server Wins Developer Week 2013 Award; Named Top Innovator in SQL Technologies Category

Percona Server has won a 2013 DeveloperWeek Best of Tech award, being named Top Innovator in the SQL Technologies category!

We’re proud to accept this award, which is determined by a combination of peer voting and review from an independent panel of judges. Thank you to everyone who voted for Percona Server!

From our press release:

Percona is glad to announce the release of Percona Server 5.1.66-14.2 on January 15th, 2013 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.1.66, including all the bug fixes in it, Percona Server 5.1.66-14.2 is now the current stable release in the 5.1 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.1.66-14.2 milestone at Launchpad.

New Features:

• Multiple bitmap file

In preparing Percona Server 5.5.29 (not yet released, but soon), I filed MySQL bug 68045 (is marked private as it’s a security bug). This bug is in relation to the Oracle fix for CVE-2012-4414 and a problem I found with it. The MariaDB fix (which we incorporated into Percona Server 5.5.28-29.3) is not affected.

When the MariaDB team fixed CVE-2012-4414 they created a test case named

Percona Toolkit 2.1.8

Percona Toolkit 2.1.8 was released today and the new version comes with some pretty cool news: Beta support for MySQL 5.6 (which as you might know is also in beta).

In addition to various bug fixes, Percona Toolkit 2.1.8 also offers beta support for Percona XtraDB Cluster (PXC). Version 2.1.8 continues the trend of solid bug fix releases, and all 2.1 users are encouraged to upgrade.

You can download Percona Toolkit 2.1.8 here.  (

pt-stalk recipes: Gather forensic data about MySQL when a server problem occurs

It happens to us all from time to time: a server issue arises that leaves you scratching your head. That’s when Percona Toolkit’s pt-stalk comes into play, helping you diagnose the problem by capturing diagnostic data that helps you pinpoint what’s causing the havoc hitting your database.

From the documentation (http://www.percona.com/doc/percona-toolkit/pt-stalk.html):

pt-stalk watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it

How easy is it to identify and debug Percona XtraDB Cluster replication problem ?

If you are using PXC, you may have already seen in your datadirectory several log files starting with GRA_

Those files correspond to a replication failure. That means the slave thread was not able to apply one transaction. For each of those file, a corresponding warning or error message is present in the mysql error log file.

Those error can also be false positive like a bad DDL statement (DROP a table that doesn’t exists for example) and therefore nothing to worry about. However it’s always recommended to understand what’s is happening.

As the GRA files contain binlog events in ROW format representing the failed transaction this post explains how to proceed.

The first step to be able to analyze your GRA files is to

Recently there have been discussions on several vulnerabilities in MySQL and closely related projects such as MariaDB and Percona Server. Usually we have inherited security fixes from MySQL when we have updated Percona Server to be based off a new Oracle MySQL release. In this case however, Oracle has been incredibly quiet.

We’ve been examining how these affect current Percona Server versions and will shortly be making security releases and more information available (we wish to ensure what we say is correct in regards to Percona Server).

As seems to often be the case, we already had a release in the pipeline – Percona Server 5.5.28-29.2 which contains many bug fixes that affect users (see our release notes). We’re

I had a customer recently who needed to reduce their database size on disk quickly without a lot of messy schema redesign and application recoding.  They didn’t want to drop any actual data, and their index usage was fairly high, so we decided to look for unused indexes that could be removed.

It’s quite easy to collect statistics about index usage in Percona Server (and others) using the User Statistics patch.  By enabling ‘userstat_running’, we start to get information in the INFORMATION_SCHEMA.INDEX_STATISTICS table.  This data collection does add some overhead to your running server, but it’s important to leave this running for a good long while to get a good dataset that is representative of as much

The Web And PHP Magazine just published an article describing Percona XtraDB Cluster written by Liz van Dijk and Kenny Gryp. It is a pretty high-level introduction to the technology, so definitely give it a read if you’ve been wanting to give it a closer look.

Go to their website http://www.webandphp.com and download Issue number 9 for free!

On Monday I will deliver with Seppo Jaakola from Codership a tutorial on Percona XtraDB Cluster (Percona Server + Galera).

Already in London and you don’t know how to consume the hotel’s bandwidth ? If you attend this tutorial, you can download the Virtual Box appliance we will use during the session.

Two versions are available:

• PLUK 2012 Appliance (32-bit)
• PLUK 2012 Appliance (64-bit)

How to test them:

If you are interested by a great and revolutionary HA solution for MySQL or even if you already have some experience with PXC and/or Galera, I invite you to attend the tutorial I will deliver in Percona Live MySQL Conference London 2012 (PLUK2012), December 3rd : Percona XtraDB Cluster in a nutshell.

Small amount of theory and a lot of exercises and examples to really tame the beast that PXC is !

A Codership engineer will also be present and happy to help me answering your questions.

Note to attendees: please bring your

The Percona Toolkit team is happy to announce the release of Percona Toolkit version 2.1.6. This is the sixth stable release in the 2.1 series, and primarily a bug-fix release; We suggest that users upgrade to the latest version of the tools.

The complete list of changes is on the Launchpad milestone for 2.1.6, but here are some highlights the release:

• pt-online-schema-change can now handle column renames
• pt-online-schema-change’s DELETE trigger no longer fails when altering a primary key
• pt-stalk got a plugin hook interface
• pt-show-grant support for column-level grants
• pt-table-checksum will warn if slaves have a differing binlog_format

Remember that if you need help with Percona Toolkit, we provide flat-fee, unlimited

The idea to use SSD/Flash as a cache is not new, and there are different solutions for this, both OpenSource like L2ARC for ZFS and Flashcache from Facebook, and proprietary, like directCache from Fusion-io.
They all however have some limitations, that’s why I am considering to have L2 cache on a database level, as an extension to InnoDB buffer pool.
Fortunately, there is a project in progress Flash_Cache_For_InnoDB by David which implements this.
David helped us to port his work to the latest Percona Server and you can get it from our launchpad Percona Server

Percona is glad to announce the release of Percona XtraBackup 2.0.3 on October 1st, 2012. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.0 series.

New Features:

• innobackupex now supports new –move-back option that can be used instead of –copy-back in case there isn’t enough free disk space on the server to copy files. As this option removes backup files, it must be used with caution.

Bugs Fixed:

• Symlink for innobackupex-1.5.1 binary has been broken in the previous version of

The Percona Toolkit team is happy to announce the release of Percona Toolkit version 2.1.4. This is the fifth stable release in the 2.1 series, and primarily a bug-fix release; We suggest that users upgrade to the latest version of the tools.

The complete list of changes is on the Launchpad milestone for 2.1.4, but here are some highlights the release:

• pt-table-checksum now works with Percona XtraDB Cluster
• The “Version Check” feature, explained at length here.
• –defaults-file is now used when connecting to discovered slaves in pt-table-checksum

All in all, a solid bug-fix release, with the addition of some new

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 converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

This can result in every row being deemed out of sync when each server is using in it’s own timezone.  Fortunately, there is a workaround for this as a result of how

Not so long ago Fusion-io announced an SDK which provides direct API access to Fusion ioMemory(tm) in addition to providing a native filesystem (directFS) with a goal to avoid overhead from kernel and regular Linux filesystems: ext4 and xfs. This requires a support from application, it should use special calls for IO. With help from Fusion-io, we provide source code of Percona Server which uses direct API access. The main idea that with this functionality you can disable “innodb-doublewrite”, retain ACID compliance by using atomic writes, and in IO intensive workloads gain an additional 30-50% in throughput when compared to workloads on the same ioMemory™ device using an unmodified Percona Server. Further benchmarking results on latency variability reduction expected soon.
Percona Server 5.5.27 with Fusion-io

Yesterday, over at my personal blog, I blogged about the impact of the MySQL slow query log. Since we’re working on Percona Server 5.6, I did wonder if this was a good opportunity to re-examine how we could provide slow query log type functionality to our users.

The slow query log code inside the MySQL server does several things that aren’t good for concurrency on modern systems. For starters, it takes a mutex while both doing formatting of what to write and while doing the write(2) system call itself. This is, in fact, entirely unneccesary.

From the man page for the write system call:

If the file was open(2)ed with O_APPEND, the file offset is first set to the end of the file before writing. The adjustment of the file offset

Recently there has been a storm of bugs and problems in all variants of MySQL including MySQL, Percona Server, and MariaDB. To list a few:

• MySQL 5.5.25
  UPDATE on InnoDB table enters recursion, consumes all disk space
• All MariaDB and MySQL versions up to 5.1.61, 5.2.11, 5.3.5, 5.5.22
  Security vulnerability in MySQL/MariaDB sql/password.c
• MySQL 5.1.61
  DSA-2429-1 mysql-5.1 — several vulnerabilities
• MySQL 5.5.25a
  Slave crash regression
• Percona Server 5.5.27-28.0 regression
  Server
  

I continue my benchmarks of Intel SSD 910, the raw IO results are available in my previous experiment. Now I want to test this card under MySQL workload to see if the card is suitable to use with MySQL.

I wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema and the overall performance. There are different possibilites and we’ll explore the two most common here. User Statistics from Percona Server and pt-index-usage.

User Statistics

User Statistics is an improvement on Percona Server that adds some tables to Information Schema with useful information to understand the server activity and identify the source of

Having duplicate keys in our schemas can hurt the performance of our database:

In this post I’m going to explain the different types of duplicate indexes and how to find and remove them.

Duplicate keys on the same column

This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example:

This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and it could serve as a reference architecture.

You will need 4 virtual machines. 3 for PXC and 1 for the client, which will have HaProxy. I used CentOS 6 as the operating system, the instructions are similar for any Linux distribution.

The client node will have HaProxy installed and it will redirect requests to PXC nodes. This approach works well in real-world scenarios too. Running HaProxy on the application servers instead of having them as dedicated entities gives you benefits like you don’t need

Contrary to initial reports here and here, further investigation has revealed that under some specific and limited circumstances, Percona Server and Percona XtraDB Cluster binaries, similar to other MySQL variants, are susceptible to the security vulnerability in MySQL/MariaDB sql/password.c:

• 64bit Ubuntu Oneiric (11.10) binaries are vulnerable in Percona Server ONLY on some hardware/virtualization platforms (confirmed on Amazon EC2 but not on HP Cloud).
• Neither older nor newer Ubuntu versions are affected.

Version 1.0.1 of the Percona Monitoring Plugins is released. This is a routine bug-fix release that addresses several minor issues.

The Percona Monitoring Plugins are high-quality components to add enterprise-grade MySQL monitoring and graphing capabilities to your existing in-house, on-premises monitoring solutions. The components are designed to integrate seamlessly with widely deployed solutions such as Nagios and Cacti, and are delivered in the form of templates, plugins, and scripts.

The plugins are fully supported for customers with a Percona Support contract, and free installation services are provided as part of some contracts. You

The Percona Toolkit team is happy to announce the release of Percona Toolkit versions 2.0.5 and 2.1.2. These are bug-fix releases in the 2.0 and 2.1 series, respectively. These releases fix many dozens of bugs, and we suggest that users upgrade to the latest versions of the tools.

The complete list of changes is on Launchpad for 2.0.5 and 2.1.2. Here are some highlights of the changes in version 2.1.2, which is the current development focus:

• We fixed several bugs in pt-table-sync that prevented it from working properly, and disabled the –lock-and-rename feature when using it with MySQL versions that don’t support it reliably.
• We enhanced pt-table-checksum and

Many of you heard of this nasty security vulnerability in MySQL, and as we are getting a lot of inquiries how does it affect Percona Server, I decided to address it in this post.

• The issue exists in the source code of MySQL 5.5.23 or earlier and MySQL 5.1.62 or earlier. The same is true for Percona Server, as we share the same code base.
• However binaries provided by Percona do not have this problem, as in our build process we do not use sse-optimized glibc memcmp. This is true for any version of Percona and all tar.gz, RPM and DEB packages. Once again, if you use binary builds, provided by Percona from our official download area, or from our repositories,

I am happy to announce next version Percona XtraDB Cluster 5.5.24, which is mostly jump to the current version of Percona Server.

It includes an important bug fix:

• table with no PK but still having UK, can cause crash in slave using PA

Binaries are available from downloads area or from our repositories.

For this release we provide binaries for Ubuntu 12.04.

If you want to know more how to migrate to XtraDB Cluster, you can check our recent webinar Migrating To Percona XtraDB Cluster.

This is an General Availability release. We did our best to eliminate bugs and problems

One of our customers asked me whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables.

If you’re not familiar with this feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets you run queries to see which tables get the most reads and writes, for example. Another popular use for it is to find unused indexes.

I knew that some of our customers were using the feature enabled in production all the time, and I knew that Google and Facebook and others (the