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

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

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

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 system files when a dedicated file to contain the doublewrite buffer is

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:

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 -

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

I very much like the fact that MySQL allows you to embed comments into SQL statements. These comments are extremely convenient, because they are written into MySQL log files as part of the query. This includes the general log, the binary log and the slow query log. Maatkit includes tools which interact with these logs, including mk-query-digest. This tool, in particular, has a very nice option called --embedded-attributes which can process data embedded in query comments.

The support for embedded attributes makes some cool tricks possible. Peter and I co-presented a talk at this past MySQL Conference and Expo. In this talk I presented my