I’ve seen this a handful of times: someone has trouble with their database performance, and they have heard that XtraDB is much faster than InnoDB. They build a custom-compiled server with XtraDB.

This is unfortunately missing the point a bit. If you have a server that is the same as normal MySQL, but you’ve replaced InnoDB by XtraDB, what do you have? Depending on the version of MySQL you’re using, you have somewhere between, say, 1.5x and 15x performance improvement, at best. Compared to what you could be getting, that is not much, because you’re missing the most important improvement in Percona Server: the ability to measure the server’s activity. In other words, with a faster server that you still can’t measure and diagnose easily, you have just painted yourself into a faster corner. Your application’s workload is likely to grow 1.5x in no time; you have barely put off needing to diagnose why it is slow.

…

In my recent benchmarks, such as this one about the Virident TachIon card, I used different values for innodb_buffer_pool_size, like 13GB, 52GB, and 144GB, for testing the tpcc-mysql database with size 100G. This was needed in order to test different memory/dataset size ratios. But why is it important, and how does it affect how InnoDB works internally? Let me show some details.

Internally, InnoDB uses two lists for flushing (writing pages from the Innodb buffer pool memory to disk): the LRU list and the flush list. You can see a tracking of these lists in SHOW ENGINE INNODB STATUS:

...
Pending writes: LRU 0, flush list 129, single page 0
...

It is important to understand which list is being used for flushing, because that defines what MySQL InnoDB tuning …

Percona Server version 5.1.54-12.5 is now available for download. It is now the current stable release version.

Functionality Added or Changed

• Percona Server 5.1.54-12.5 is based on MySQL 5.1.54.
• New Features Added: None
• Other Changes:
  • Added system variable innodb_log_block_size and new value “keep_average” to system variable adaptive_checkpoint in Improved InnoDB I/O Scalability. (Yasufumi Kinoshita)
  • Added new value “ALL_O_DIRECT” to system variable …

I’ve posted a new white paper about the implementation and benefits of Percona Server’s fast-restart capabilities. Briefly, after shutting down and restarting or rebooting the server, it can be back to full performance in a couple of minutes. That’s minutes, not hours or days. This matters a lot for keeping uptime high and reducing hardware requirements. There are a ton of benefits when you don’t have to obsess over how long it’s going to take to get the server back into production. Hot buzzword-compliant use cases definitely include cloud computing, because now you can get lots of memory in the cloud, but you still get terrible I/O performance so MySQL restarts take an eternity.

Read the white paper for the details; it is posted in the white paper section of our site and as always, is free to download and share with friends.

In my post MySQL 5.5.8 and Percona Server: being adaptive I mentioned that I used innodb-log-block-size=4096 in Percona Server to get better throughput, but later Dimitri in his article MySQL Performance: Analyzing Percona's TPCC-like Workload on MySQL 5.5 sounded doubt that it really makes sense. Here us quote from his article:

"Question: what is a potential impact on buffered 7MB/sec writes if we'll use 4K or 512 bytes block size to write to the buffer?.. )
There will be near no or no impact at all as all writes are managed by the filesystem, and filesystem will use its own block size.. - Of course the things may change if …

A couple of days ago, Dimitri published a blog post, Analyzing Percona's TPCC-like Workload on MySQL 5.5, which was  a response to my post, MySQL 5.5.8 and Percona Server: being adaptive. I will refer to Dimitri's article as article [1]. As always, Dimitri has provided a very detailed and thoughtful article, and I strongly recommend reading if you want to understand how InnoDB works. In his post, Dimitri questioned some of my conclusions, so I decided to take a more detailed look at my findings. Let me show you my results.

Article [1] recommends using the innodb_max_dirty_pages_pct and innodb_io_capacity parameters to …

Percona Server version 5.1.53-12.4 is now available for download. It is now the current stable release version.

Functionality Added or Changed

•  Percona Server 5.1.53-12.4 is based on MySQL 5.1.53.
•  New Features Added:
  • Precompiled UDFs for Maatkit (FNV and MurmurHash hash functions to provide faster checksums) are now included in distributions. Fixes feature request #689992. (Aleksandr Kuzminsky)
•  Other Changes:
  • innodb_doublewrite_file - It's no longer necessary to recreate your database and InnoDB …

In my previous posts I highlighted that one of improvements in Percona Server is support of innodb_log_file_size > 4G. This test was done using Percona Server 5.5.7, but the same performance expected for InnoDB-plugin and MySQL 5.5.

The valid question how long is recovery in this case, so let's test it. I took the same tpcc-mysql 1000W workload with 52GB and 144GB innodb_buffer_pool_size with data located on Virident tachIOn card and killed mysqld after 30 mins of work.

The recovery time after start is:
for 52GB innodb_buffer_pool_size:

PLAIN TEXT CODE:

1. 101220 …

This is to follow up on my previous post and show the results for MySQL 5.5.8 and Percona Server on the fastest hardware I have in our lab: a Cisco UCS C250 server with 384GB of RAM, powered by a Virident tachIOn 400GB SLC card.

To see different I/O patterns, I used different innodb_buffer_pool_size settings: 13G, 52G, an 144G on a tpcc-mysql workload with 1000W (around 100GB of data). This combination of buffer pool sizes gives us different data/memory ratios (for 13G - an I/O intensive workload, for 52G - half of the data fits into the buffer pool, for 144G - the data all fits into memory). For the cases when the …

As we can see, MySQL 5.5.8 comes with great improvements and scalability fixes. Adding up all the new features, you have a great release. However, there is one area I want to touch on in this post. At Percona, we consider it important not only to have the best peak performance, but also stable and predictable performance. I refer you to Peter's post, Performance Optimization and Six Sigma.

In Percona Server (and actually even before that, in percona-patches builds for 5.0), we added adaptive checkpoint algorithms, and later the InnoDB-plugin included an implementation of  "adaptive flushing". This post shows the differences between them and MySQL.

The post also answers the question of whether we are going to have releases of Percona Server/XtraDB based on the …