Speaking at HighLoad conference, Moscow, Russia, Training and Hiring

I’m going to be speaking on Highload++ conference October 3,4 in Moscow, Russia. This is a great conference which gathers amazing quality of speakers from Russia and around the world and I usually learn a lot and enjoy talking to a lot of great people on this event.

My talk is going to be about new developments in MySQL Server 5.5, 5.6, Percona Server, MariaDB and Drizzle as it relates to high volume/large scale projects. There is a lot of really cool things happening in MySQL space recently and I would love to share those with you.

I’m also doing a training session 5th of October, which will be in depth training session based on Percona’s Training for MySQL Developers. In fact this is a great learning experience both for MySQL Developers and DBAs and this is unique opportunity to attend this course
given in Russian. Bring your laptop if you have one for more Hands On experience.

If you would like to do business with Percona I will be available for business development and consulting meetings on Thursday and Friday next week.

Finally note We’re Hiring worldwide for most of positions, so if you’re interested joining Percona Team, come and talk to me. We have both technical and non technical positions, in particular we’re looking for Sales and Business Development person in Russia.

Why so late announcement. Ah if you must know I did not know if I will be able to make it until very last minute. My passport was stalled in the British Consulate getting visa to UK for Percona Live London in late October. It is great we could get the documents back in time for my travel.


This week’s TGIF Percona Live ticket giveaway

It’s Friday again (already?) and as usual, we have a free ticket for Percona Live London. This time Tokutek is doing the honors of running the contest and selecting the winner. Instructions for entering the contest are on their blog, at the top of my recent guest post about covering indexes.


Intel 320 SSD write performance – contd.

I wrote about Intel 320 SSD write performance before, but I was not satisfied with these results.

Somewhat each time on Intel 320 SSD I was getting different write performance, so it made me looking into this with details.

So let’s run experiment as in previous post, this is sysbench fileio random write on different file size, from 10GiB to 140GiB with 10GiB step. I use ext4 filesystem, and I perform filesystem format before increasing filesize.

The results are pretty much as in previous post, the throughput drops as we increase filesize:

However, there is when interesting stuff begin. Now when we run the same iterations again, the result will look like:

As you see, second time the throughput is much worse, even on medium size files. Just after 50GiB size, throughput gets below 40MiB/sec And this is with the fact, that I perform filesystem format before each run.

This leads me to conclusion that write performance on Intel 320 SSD is decreasing in time, and actually it is quite unpredictable in each given point of time. Filesystem format does not help, and only secure erase procedure allows to return to initial state. There are commands for this procedure for reference.

hdparm --user-master u --security-set-pass Eins /dev/sd$i
hdparm --user-master u --security-erase Eins /dev/sd$i

Discussing this problem with engineers working with Intel 320 SSD drives I was advised to use artificial space provisioning, about 20%. Basically we create partition which takes only 80% of space.

So let’s try this. The experiment the same as previously, with difference that I use 120G partition, and max filesize is 110GiB.

You can see that throughput in first iteration is basically the same as with full drive, but second iteration performs much better. Throughput never drops below 40MiB/sec, and stays on about 50MiB/sec level.

So, I think, this advise to use space provisioning is worth to consider if you want to have some kind of protection and maintain throughput on some level.

Raw results and used scripts as always you can find on our Benchmarks Launchpad


Queries Without Table Access

The previous installment demonstrated how to use an index to cluster table rows together. The examples showed that even “anywhere” LIKE expressions can be tuned when the column is put into the index to avoid the table access.

Todays installment extends this concept and shows queries that don’t need to access the table at all. It’s about the so-called index-only scan, or, less descriptive, covering index.


Percona XtraBackup 1.6.3

Percona is glad to announce the release of Percona XtraBackup 1.6.3 on 22 September, 2011 (Downloads are available here and from the Percona Software Repositories).

This release is purely composed of bug fixes and is the current stable release of Percona Xtrabackup.

If the innodb_file_per_table server option is being used and DDL operations, TRUNCATE TABLEDROP/CREATE the_same_table or ALTER statements on InnoDB tables are executed while taking a backup, an upgrade to XtraBackup 1.6.3 is strongly recommended. Under this scenario, if the server version is prior to 5.5.11 in 5.5 series or prior to 5.1.49 in 5.1 series, a server upgrade is also recommended.

All of Percona’s software is open-source and free, all the details of the release and its development process can be found in the 1.6.3 milestone at Launchpad.

Bugs Fixed

  • Streaming backups did not work for compressed InnoDB tables due to missing support for compressed pages in tar4ibd. Bug Fixed: #665210 (Alexey Kopytov).
  • XtraBackup failed when innodb_flush_method in the server configuration file was set to ALL_O_DIRECT. Bug Fixed: #759225 (Alexey Kopytov).
  • Due to a regression introduced in XtraBackup 1.6.2, innobackupex --copy-back did not work if the xtrabackup binary was not specified explicitly with the --ibbackupoption. Bug Fixed: #817132 (Alexey Kopytov).
  • The --slave-info option now works correctly with --safe-slave-backup when either --no-lock or --incremental is also specified. Bug Fixed: #834657 (Alexey Kopytov).
  • tar4ibd could fail with an error when processing doublewrite pages. Bug Fixed: #810269 (Alexey Kopytov).
  • Unsupported command line options could cause a tar4ibd crash. Such options have been removed. Bug Fixed: #677279 (Alexey Kopytov).
  • Executing DDL operations, TRUNCATE TABLEDROP/CREATE the_same_table or ALTER statements on InnoDB tables while taking a backup could lead to a xtrabackup failure due to a tablespace ID mismatch when using per-table tablespaces. Note that this fix may not work correctly with MySQL 5.5 or Percona Server 5.5 prior to version 5.5.11. 5.1 releases from 5.1.49 or higher have been confirmed not to be affected. If the innodb_file_per_table option is being used, an upgrade to XtraBackup 1.6.3 isstrongly recommended. Under this scenario, if the server version is prior to 5.5.11 in 5.5 series or prior to 5.1.49 in 5.1 series, a server upgrade is alsorecommended. Bug Fixed: #722638 (Alexey Kopytov).

Other Changes

  • Improvements and fixes on the XtraBackup Test Suite: #855035#787966 (Alexey Kopytov)

More Information

For more information, please see the following links:


NDB tutorial at Percona Live London + Free TIcket Give Away

In a month, the 24th of October, Johan Andersson ( and I will be giving a full day tutorial on NDB cluster which will include both presentations and hands-on. Be ready for a fast ramp-up on NDB! Among items covered:

– Achitecture
– Installation
– Loading data
– Administration (common procedures)
– Node recovery
– NDB for sharding
– Replication
– Online scaling
– Other access methods

If NDB cluster is a technology that interests you and you would like to learn more, this tutorial is for you. Make sure to check out our website to get our full list of tutorials and conference schedules.

For a chance to win a free ticket to Percona Live London simply watch our @Percona Twitter stream and retweet the contest to win a free ticket! One lucky winner will be picked every Friday up until the conference. Have a great weekend and make sure to tweet to enter our TGIF contest for a chance to win! If you don’t win this time, try next Friday or register now and get the Early Bird price! (but don’t wait too long: it expires September 28th). See in London!


Update on CuteFlow Development

As I wrote in a previous post I’m working on a brand new Symfony2 based version of CuteFlow. I was on vacation the last 4 weeks so there was not that much progress at all but I want to inform you about the current status of the project:

  • The basic infrastructure is up and running. That means there is the basic layout, the menues, authentication and user management working.
  • From now on I will work on the feature set. I will keep you informed whats up and coming.
  • I uploaded a new webpage (rough) on the new Main project website ( The VServer the page is running is sponsored by the Filloo GmbH. Thanks for it guys. There is only little content at the moment but it will grow as the version will be more complete. BTW the website is built with the Silex Framework.
  • The Documentation infrastructure is up and running too. The documentation source (written in ReST, converted by Sphinx) is hosted on GitHub. We use ReadTheDocs to automatically build the HTML-Documentation from the Git-Repository. You can find the current version of the docu here.

I would be still more than happy about helping hands. There is some interest and first code writte by others but more is appreciated. You can find some first information in the documentation.

flattr this!


Diagnosing and Fixing MySQL Replication + Early Bird Registration Extended!

Percona is happy to announce that our conference schedule is up online! We are thrilled to be able to offer such a wide variety of talks from so many MySQL experts. Come see Devananda van der Veen a Percona Consultant speak on Replication.

“Replication is one of MySQL’s most widely-used features, and despite significant improvements over the years, it still fails occasionally. When this happens, it can be due to any number of factors, either internal or external to your MySQL database. For those who do not have a thorough understanding of the MySQL binary log format or the slave’s two-phase replication architecture, safely restoring replication while maintaining data integrity can be a daunting task. This talk will show you how to interpret SHOW SLAVE STATUS, how to read MySQL’s binary logs, and how to recover a replication slave from many types of real-world failures.”
Not interested in Replication? Come see others speak on topics from Handler Socket to Programmatic Queries. With 45 sessions not including the keynotes you are bound to find something that will spark an interest.
To share our excitement we are extending our Early Bird Pricing until September 28th! Register now for this conference and save 25% off the Regular Registration price. This offer won’t last long so register today!


White Paper: Flashcache and MySQL on Virident drive

Our latest MySQL white paper is Improving Percona Server performance with Flashcache on the Virident tachIOn Drive. (Virident funded the research, but as always, we wrote the report ourselves.)

The conclusion is that Flashcache can be good for read-heavy workloads, but more research is needed to understand its performance characteristics on write-heavy workloads. We explain the details of exactly how good and under what circumstances. We also developed some guidelines for sizing and pricing, to serve as advice for those interested in deploying Flashcache as a way of getting some of the benefit of flash without all of the cost, or the size limitations.


Disaster: MySQL 5.5 Flushing

We raised topic of problems with flushing in InnoDB several times, some links:

InnoDB Flushing theory and solutions
MySQL 5.5.8 in search of stability

This was not often recurring problem so far, however in my recent experiments, I observe it in very simple sysbench workload on hardware which can be considered as typical nowadays.

Hardware: HP ProLiant DL380 G6, with 72GB of RAM and RAID10 on 8 disks.

I took sysbench multi-tables workload, with 20 tables, 10,000,000 rows each. Total database size ~58GB.
MySQL version: 5.5.16

Initial benchmark, which InnoDB configured for this hardware

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 52G
innodb_log_file_size = 1900M
innodb_log_files_in_group = 2

In my benchmark I measure results each 10 sec, and when we graph it, it looks like:

Basically there two stages: MySQL handles traffic and MySQL stuck. As you see stalls are very severe.
There are 4 mins periods, when MySQL is not able to process query.

Why this happens: referring to previous InnoDB Flushing theory and solutions post, InnoDB during this time is “sync” flushing mode. It allows itself to go in this state,
as we have a lot of memory, and we can do changes in memory much faster than on disk. InnoDB is not able
to catch up with flushing changed data.

With widely adoption of MySQL 5.5 and servers with big memory configuration I expect we will see this problem more and more often on production systems.

How to diagnose it?
This is question I am asked, how do we know that stall we see is related to InnoDB flushing and “sync” state.

Vanilla MySQL does not provide much diagnostic there, but there couple numbers to look into.
If you take “SHOW ENGINE INNODB STATUS”, look into following sections:

Log flushed up to 135550126707
Last checkpoint at 132524978607
Pending writes: LRU 0, flush list 105, single page 0

First, Pending writes “flush list” > 0 says that InnoDB does perform a lot of flushing,
and if this number grow, that means InnoDB flushes more than your IO system can afford.
Second, you need to a little math. (“Log flushed up to” – “Last checkpoint at”) this is our checkpoint age. In our case it is 3025148100 or 2885M . Our summary log size is 3800M. InnoDB usually takes 75% mark as limit for “sync” stage ( 3800M * 0.75 = 2850M ). That is checkpoint age exceeds 75% sync mark, that is signal that InnoDB performs in “sync” flushing mode.
So math formula for this: if (“Log flushed up to” – “Last checkpoint at”) > “total log size” * 0.75 , then InnoDB is in “sync” mode.

What to do?
I whish I could say you should use Percona Server with XtraDB.
If we were using SSD as storage, then I would recommend it. Vanilla MySQL performs equally bad on SSD and
HDD, while for SSD in Percona Server we have “innodb_adaptive_flushing_method = keep_average”.

Unfortunately on spinning disks (as in this case), Percona Server may not show significant improvement. I am going to followup with results on Percona Server.

So first recommendation you may hear in this case from the Oracle/MySQL engineers is to decrease “innodb_max_dirty_pages_pct”, this way InnoDB will try to keep less dirty pages in buffer pool,
and hopefully it will spend less time in “sync” flushing.

Let’s see what we can get if we set


Although maximal throughput decreased and stall period is somewhat shorter, I cannot see this is helpful.

Second action what you may try, is to decrease innodb_log_file_size. How this may help ? InnoDB flushing may kick-in faster, and do not allow to have a lot of modified pages in buffer pool.
Let’s try it:

Well, stability and absolute value of throughput are far from perfect, but, at least, we do not have 4 min stalls.

Will it help if we decrease innodb_buffer_pool (effectively killing idea that more memory is better), the same way InnoDB will not be able to change a lot of data :) .

The following results are with innodb_buffer_pool_size=39G. There I keep Y scale as first graph to show impact on performance by this action.

Finally we got somewhat stable result without stalls, but by loosing about 20x throughput.

These facts give me idea that existing InnoDB flushing algorithm is not suitable for modern hardware with a lot of memory. I hope there is work in progress for MySQL 5.6.

Scripts and raw results you can find there.

Powered by WordPress | Theme: Aeros 2.0 by