It is known that MySQL due internal limitations is not able to utilize
all CPU and IO resources available on modern hardware.
Idea is to run multiple instances of MySQL to gain better performance on Fusion-io ioDrive card.

Full report is available in PDF

For tests we used tpcc-mysql package, which generates TPCC-like workload on MySQL systems.

• Server hardware: Dell PowerEdge R815
• Storage: Fusion-io ioDrive Duo 640GB MLC. Fusion-io driver version: 2.3.1 build 123; Firmware v5.0.7, rev 101971
• Software: Percona Server 5.5.15
• Client hardware: IBM x3650

Fusion-io ioDrive Duo 640GB MLC card was provided by Fusion-io.

More details on hardware and software …

An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual:

1. Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS)
2. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed

…

Codership team published beta2 of MySQL 5.5.15 with Galera replication
https://launchpad.net/codership-mysql
and we made port to Percona Server:

source code:
lp:~percona-dev/percona-server/percona-server-galera-5.5.15
binaries for RedHat/CentOS 6:
http://www.percona.com/downloads/TESTING/Galera/Percona-XtraDB-Galera-5.5.15.tar.gz

What difference between Percona Server+Galera and MySQL 5.5.15 ?
First of course, Percona Server+Galera is based on our XtraDB engine.
Second, we provide wsrep_sst_xtrabackup script, which allows to use Percona XtraBackup for node provisioning.
Percona Server+Galera is still on early stage, and we make it available so you can play it to gain some …

It’s Friday again (so soon!) and time for our TGIF contest, to give away a free ticket to Percona Live London. Before we do that, though, just what in the world does this output from SHOW INNODB STATUS mean?

Mutex spin waits 5870888, rounds 19812448, OS waits 375285

To understand this text, you have to understand how InnoDB handles mutexes. It tries a two-step approach to getting a lock on a mutex. First, a thread tries to lock the mutex. If the mutex is locked by someone else, then the thread does a so-called spin wait. This means that it repeatedly checks “are you free? are you free? are you free?” in a loop. If this doesn’t work after a while, it gives up and goes to sleep until the mutex is free. I’m simplifying this a lot, perhaps too much, but it’s a topic that would take a long blog post to explain correctly in detail. The related source …

Dealing with MySQL you might need to deal with RAID recovery every so often. Sometimes because of client lacking the proper backup or sometimes because recovering RAID might improve recovery, for example you might get point in time recovery while backup setup only takes you to the point where last binary log was backed up. I wanted for a chance to write instructions for recovery for long time
and finally I had gotten the problems with my ReadyNAS Pro 6 which I was setting up/testing at home for use for backups. I got it doing initial sync while it spotted the problem with one other drive and as such RAID volume failed. ReadyNAS has Debian inside and as you can get root login via SSH it can be recovered as any generic Linux server.

When you restart the system RAID5 volume …

Core file can be quite helpful to troubleshoot MySQL Crashes yet it is not always easy to get, especially with recent Linux distributions which have security features to prevent core files to be dumped by
setuid processes (and MySQL Server is most commonly ran changing user from “root” to “mysql”). Before you embark on enabling core file you should consider two things – disk space and restart time. The core file will dump all MySQL Server memory content including buffer pool which can be tens on even hundreds GB of disk space. It can also take very long time to write this amount of data to the disk. If you are using “pid” with core files, which you probably should, as getting different samples often help developers to find what is wrong easier, you may be looking at many times the amount of memory MySQL consumes worth of disk space.

You have to do couple of changes to enable core files. First you need …

It’s Friday again, and time for another TGIF give-away of a Percona Live London ticket! But first, what’s new with the MySQL query cache? You may know that it still has the same fundamental architecture that it’s always had, and that this can cause scalability problems and locking, but there have been some important changes recently. Let’s take a look at those.

The first important change is that both Percona and Oracle actually built some code improvements into the query cache and the interface between it and MySQL. It’s now possible to completely disable it, for example. This used to be possible only by eliminating it at compile time. If you didn’t do that, then there was still a query-cache single choke-point in the server. Now that’s gone. As of MySQL 5.5, the query cache mutex isn’t hit at all if …

One of the typical problems I see setting up ext2/3/4 file system is sticking to defaults when it comes to behavior on errors. By default these filesystems are configured to Continue when error (such as IO error or meta data inconsistency) is discovered which can continue spreading corruption. This manifests itself in a worst way when device have some “flapping” problems returning errors every so often as this would cause some random pieces of data and meta data to be lost. Not good for system running mySQL Server. As far as I understand this problem is limited to EXT2/3/4 while over systems like XFS will not continue if consistency problems are discovered.

So how can you check what error behavior mode your file system has ? Run dumpe2fs /dev/sda1 and you will get something like this:

dumpe2fs 1.41.14 (22-Dec-2010)
Filesystem volume name:
Last mounted on: /mnt/data
Filesystem UUID: …

I see this message on our forums, and I think it’s a great question: “Which version of Percona Server is currently recommended?” It’s really the same question as “Which version of MySQL is currently recommended?” I’ll respond here and then post a link in the forum as a reply.

In my opinion, it’s important to qualify this question by understanding whether we’re talking about an existing MySQL installation, or a new one. The answer is different for each case. (There are other qualifying questions I’d ask too, but this is the biggest distinguisher).

For an existing MySQL database server, I’d encourage not jumping on a new version immediately when it comes out. Let some early adopters try it out first, and when it gets more broad deployment, then consider it. The reason I say this is that the …

One common theme in the questions our MySQL Support customers ask is Replication Lag. The story is typically along the lines everything is same as before and for some unknown reason the slave is started to lag and not catching up any more. I always smile at “nothing has changed” claim as it usually wrong, and one should claim I’m not aware of any change. Digging deeper we would find some change, though often subtle as computers are state machines and with no external and internal changes they behave the same way. First let me start pointing out common causes of replication lag

Hardware Faults Hardware faults may cause reduced performance causing replication to lag. Failed hard drive getting RAID in degraded mode could be one common example. Sometimes it is not fault per say but operating mode change may cause the problem. Many RAID controllers …