Yesterday we announced the GA release of Percona Server 5.6, the latest release of our enhanced, drop-in replacement for MySQL. Percona Server 5.6 is the best free MySQL alternative for demanding applications. Our third major release, Percona Server 5.6 offers all the improvements found in MySQL 5.6 Community Edition plus scalability, availability, backup, and security features some of which are found only in MySQL 5.6 Enterprise Edition.
Percona Server 5.6 comes with:
General performance improvements
Extensive diagnostics via TABLE/INDEX/USER STATISTIC and slow query log
Thread Pool plugin
Pluggable Authentication Module plugin
Integration with Percona XtraBackup: “Real” incremental backups and Archive logs backups
Statement timeouts
Fully drop-in compatible with MySQL
General performance improvements
MySQL 5.6 by itself comes with a great list of performance fixes, however what we discovered that their focus was on small datasets that fit into memory. In other words, mostly on CPU-bound workloads.
In our research we found that in IO-bound cases there is still room for improvement so we took action:
Ported good old buffer mutex split from Percona Server 5.5 to Percona Server 5.6. This helps to decrease a contention on buffer pool even further.
Implemented “priority” mutexes and rw-locks in InnoDB, check https://blueprints.launchpad.net/percona-server/+spec/xtradb-priority-mutex. Free list priority refill now gets priority in obtaining mutexes.
Implemented “Thread scheduling.” Now it is possible to change priority for InnoDB Cleaner thread. This helps to stabilize performance, as we found that in IO-heavy workloads, the cleaner thread is “starving” and not getting enough CPU time to do its job.
Implemented new waiting algorithms with exponential wait on the access to shared resources.
Additional tunings to Page Cleaner thread.
For performance improvements and testing I would like to give credit to Percona engineers Laurynas Biveinis and Alexey Stroganov.
Diagnostics via TABLE/INDEX/USER STATISTIC and slow query log
Even with MySQL 5.6′s rich PERFORMANCE_SCHEMA information, we decided to keep our diagnostics. Why? Because it is very easy to use. Check Domas’ post on this topic.
Integration with Percona XtraBackup: “Real” incremental backups and Archive logs backups
Percona Server comes with following backup features:
Changed page tracking AKA “Real” incremental backups. Using this feature will avoid full table scans for incremental backups, and information on changed pages is now available in bitmap files.
Archive Logs. An alternative way to perform incremental backups by copying InnoDB transactional logs and applying them to backup.
These features are unique to Percona Server, and, in combination with Percona XtraBackup, they allow users to achieve greater flexibility in backup schemas.
Statement timeouts
This feature was ported from Twitter’s fork of MySQL and allows users to control execution time of statements.
You are welcome to review what has changed in Percona Server 5.6 in our summary, compare with previous Percona Server releases, or compare with MySQL 5.6.
How do we do QA of Percona Server
For QA testing I want to give credit to Roel Van De Paar and his Random Query Generator extensions specific for new Percona Server features. By using RQG together with a new option combinatorics approach (expect a blog post on this soon), we are confident in the quality of Percona Server.
Also, we found, reported and fixed quite a large number of bugs for upstream MySQL.
Performance results
And of course I want to share benchmark results. What kind of performance gain can we expect with all these performance improvements I explained above?
For tests I took sysbench OLTP read-write workload with pareto distribution. The dataset is 32 tables, 10mln rows each, which totals about ~77GB of data. Our interest is intensive IO-bound workload, so buffer_pool size is 25GB and we ran the load in 250 user threads.
For hardware I used a Cisco UCS 250 server with two Intel(R) Xeon(R) CPU X5670, Ubuntu 12.04.3 LTS as the OS and very high-end PCIe SSD storage (capable of 100,000 IOPS in random 16KB writes).
So let’s compare Percona Server 5.6 and MySQL 5.6 in this workload. The graph shows timeline for 30 mins run with 1 sec resolution.
Throughput (more is better):
95% Response time (less is better):
We find that Percona Server 5.6 provides 2x better performance (in both throughput and response time) with much less variance.
This is possible to achieve by decreasing internal contention in InnoDB and prioritizing page cleaner thread and free list refill.
Now let me share configuration files for this run:
And some comments on it:
1. Please note that we are using fully durable settings:
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=1
innodb_checksum_algorithm = crc32
This is different from the results provided by MySQL, where, to get better numbers, they disable data protection.
2. innodb_checksum_algorithm = crc32. New hardware crc32 checksums actually provide much better performance, and we recommend using it whenever possible (Please note this will have an effect only on new created databases, and not for databases created in previous versions of the server)
3. Percona Server only specific settings:
innodb_sched_priority_cleaner=39 – to give highest priority to page cleaner thread
innodb_log_block_size=4096 – to use 4096 block size for InnoDB logs
innodb_adaptive_hash_index_partitions=65 – to enable partitioning of adaptive hash index, otherwise quite often this is a contention point.
And some variables which are used by default in Percona Server.
innodb_foreground_preflush=exponential_backoff
innodb_empty_free_list_algorithm=backoff
innodb_cleaner_lsn_age_factor=high_checkpoint
As a disclaimer I should mention that I expect the difference between Percona Server 5.6 and MySQL 5.6 performance will grow even wider with a larger dataset, more memory and faster storage.
For a small dataset which fits into memory on low-end servers, Percona Server 5.6 performance will be identical to MySQL 5.6 performance. Credit where credit is due: MySQL did a great job optimizing InnoDB for small datasets in memory.
This characteristics of Percona Server 5.6 are actually very important. With our server you are able to scale your workload by a simple hardware upgrade. By increasing CPU speed, increasing memory or upgrading your storage, you get better performance with Percona Server.
You are welcome to try Percona Server 5.6 yourself and give us your feedback!
What is in the future?
We are not stopping here. Expect new improvements and more features.
More InnoDB performance improvements. What we have done so far is only the tip of the iceberg and has opened the door for further research
TokuDB support. We will ship TokuDB in one of the next Percona Server 5.6 releases
Per query variables. We will add a new scope (in additional to global and session) for MySQL variables: per query. You will be able to change some variable only for one specific query
Percona XtraDB Cluster 5.6, based on Percona Server 5.6 with all of its improvements, will be available in few months
Should you need any assistance in planning your Percona Server 5.6 upgrade or migration for your company, we’re here to help. Percona has seasoned support and consulting professionals available around the world that are ready for your call. Contact us today.
The post A closer look at Percona Server 5.6 appeared first on MySQL Performance Blog.
PlanetMySQL Voting:
Vote UP /
Vote DOWN