MySQL 5.5.8 and Percona Server: being adaptiveVadim Tkachenko
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 MySQL 5.5 code line. The answer: Yes, we are. My benchmarks here are based on Percona Server 5.5.7. (You can get the source code from lp:~percona-dev/percona-server/5.5.7 , but it is very beta quality at the moment.)
For this post, I made tpcc-runs on our Dell PowerEdge R900 box, using RAID10 over 8 disks and a FusionIO 320GB MLC card.
First,Â the results for tpcc-mysql, 500w (around 50GB of data) on RAID10. I used innodb_buffer_pool_size=24G, innodb_log_file_size=2000M (innodb_log_files_in_group=2), and innodb_flush_log_at_trx_commit=2. Also, innodb_adaptive_flushing (ON) / innodb_adaptive_checkpoint (estimate) were the default values.
The raw results, full config files, and scripts areÂ in ourÂ Benchmarks Wiki.
Although it takes a decent time for the Percona Server results to stabilize, for MySQL 5.5.8 we have regular dipsÂ (3 times per hour) from 24900 NOTPM to 17700 NOTPM (dips of around 30%).
Next, the second run on the FusionIO card. There I should say that we were not able to get stable results with the existing adaptive_checkpoint or adaptive_flushing algorithms. So, Yasufumi invested a lot of research timeÂ and came up with the new innodb_adaptive_checkpoint=”keep_average” method. This method requires settingÂ innodb_flush_neighbor_pages=0 , to disable flushing of neighborhood pages (not available in MySQL 5.5.8). The problem with flushing neighborhood pages is that it makes an exact calculation of how many pages were handled impossible. The flushing neighborhoods feature wasÂ created as an optimization for hard drives, since InnoDB tries to combine writing as many pagesÂ as possible into a single sequential write, which means that a single I/O may have a size of 32K, 64K, 96K, …, etc. And again, that makes a prediction of how many I/O operationsÂ thereÂ are impossible. Furthermore, this optimization is not needed for flash devices, like FusionIO or Virident cards.
An additional optimization we have for SSDs is big log files. For this run, I used innodb_log_file_size=4G (innodb_log_files_in_group=2) for Percona Server. That gave 8GB in total sizeÂ for log files (MySQL 5.5.8 has aÂ 4GB limit). In additional to increasing log_size we added option innodb_log_block_size which allows to change IO block size for logs files. Default is 512 bytes, in test with FusionIO I use 4096 bytes, to align IO with internal FusionIO size.
You can see that MySQL 5.5.8 has periodic drops here, too. TheÂ margin between Percona Server and MySQL is about 2500-2800 NOTPM (~15% difference).
MySQL 5.5.8 now has featuresÂ related to havingÂ several buffer pool instancesÂ that are supposed to fix the buffer poolÂ scalability issue. Let’s see how MySQL performance changes for the last workload if we set innodb_buffer_pool_instances=8 or 16.
As you see, having several buffer pools makes the dips deeper and longer. It seems that for Percona Server the best choice is innodb_buffer_pool_instances=1, as we implemented buffer pool scalability in a different way.
As you see there is no improvements from bigger innodb_io_capacity, and it also concurs with my previous experience, that with bigger io_capacity you rather getting worse results.
For reference, here is the config file used for benchmarks on FusionIO:
innodb_flush_method = O_DIRECT
innodb_read_ahead = none
innodb_flush_neighbor_pages = 0
(post edited by Fred Linhoss)