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 features too.

Remember that if you need …

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 pt-table-checksum/pt-table-sync calculate their …

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 atomic writes is available from Launchpad repository: …

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 and the write operation are performed as an atomic step.

So we can …

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
  …

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.

• Server: Dell PowerEdge R710
• CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
• Memory: 192GB
• Storage: Fusion-io ioDrive 640GB, Intel SSD 910 (software RAID over 2x200GB devices)
• Filesystem: ext4

• OS: Ubuntu 12.04.1
• MySQL Version: Percona Server 5.5.27-28.1

• Benchmark name: tpcc-mysql
• Scale factor: 2500W (~250GB of data)
• Benchmark …

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 the load. …

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

• They make the optimizer phase slower because MySQL needs to examine more query plans.

• The storage engine needs to maintain, calculate and update more index statistics

• DML and even read queries can be slower because MySQL needs update fetch more data to Buffer Pool for the same load

• Our data needs more disk space so our backups will be bigger and slower

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:

mysql> alter table t add index(name);
mysql> alter table t add index(name);
…

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 an extra network roundtrip because of a loadbalancer and scalability of PXC’s load balancing layer scales …

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.
• Oneiric is not a LTS distribution. Most …