Oct
08
2013
--

A closer look at Percona Server 5.6

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
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:

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):
PS-thr
95% Response time (less is better):
PS-rt

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:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=2G
innodb_buffer_pool_size=25GB
innodb_lru_scan_depth=4000
innodb_flush_neighbors=0
innodb_log_buffer_size=256M
innodb_io_capacity = 25000
innodb_io_capacity_max= 50000
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_instances=15
innodb_file_format = Barracuda
innodb_checksum_algorithm = crc32
innodb_file_per_table = true
innodb_doublewrite=1
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_purge_threads=4
table_open_cache=15000
open_files_limit=15000
max_connections=15000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_change_buffering=all
loose-innodb_sync_array_size=16
sync_binlog=0
query_cache_type=OFF
thread_cache_size=1000
back_log=2000
connect_timeout=15
loose-metadata_locks_hash_instances=256
max_prepared_stmt_count=1048560
loose-performance_schema=0
# --- below is Percona Server Specific ---
innodb_sched_priority_cleaner=39
innodb_log_block_size=4096
innodb_adaptive_hash_index_partitions=65

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.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com