{"id":591,"date":"2015-03-10T08:36:59","date_gmt":"2015-03-10T08:36:59","guid":{"rendered":"http:\/\/adriangrigoras.com\/blog\/?p=591"},"modified":"2015-03-10T08:36:59","modified_gmt":"2015-03-10T08:36:59","slug":"closer-percona-server-5-6","status":"publish","type":"post","link":"https:\/\/adriangrigoras.com\/blog\/closer-percona-server-5-6\/","title":{"rendered":"A closer look at Percona Server 5.6"},"content":{"rendered":"<p><strong>General performance improvements <\/strong><br \/>\nMySQL 5.6 by itself comes with a great list of performance fixes, however what we discovered that their focus was\u00a0on small datasets that fit into memory. In other words, mostly on CPU-bound workloads.<\/p>\n<p>In our research we found that in IO-bound cases there is still room for improvement so we took action:<\/p>\n<ul>\n<li>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.<\/li>\n<li>Implemented \u201cpriority\u201d mutexes and rw-locks in InnoDB, check <a href=\"https:\/\/blueprints.launchpad.net\/percona-server\/+spec\/xtradb-priority-mutex\" target=\"_blank\" rel=\"nofollow\">https:\/\/blueprints.launchpad.net\/percona-server\/+spec\/xtradb-priority-mutex<\/a>. Free list priority refill now gets priority in obtaining mutexes.<\/li>\n<li>Implemented \u201cThread scheduling.\u201d Now it is possible to <a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/performance\/xtradb_performance_improvements_for_io-bound_highly-concurrent_workloads.html#relative-thread-scheduling-priorities-for-xtradb\" target=\"_blank\">change priority for InnoDB Cleaner thread<\/a>. This helps to stabilize performance, as we found that in IO-heavy workloads, the cleaner thread is \u201cstarving\u201d and not getting enough CPU time to do its job.<\/li>\n<li>Implemented new <a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/performance\/xtradb_performance_improvements_for_io-bound_highly-concurrent_workloads.html#backoff-for-sync-preflushes\" target=\"_blank\">waiting algorithms with exponential wait<\/a> on the access to shared resources.<\/li>\n<li><a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/performance\/page_cleaner_tuning.html\" target=\"_blank\">Additional tunings<\/a> to Page Cleaner thread.<\/li>\n<\/ul>\n<p>For performance improvements and testing I would like to give credit to Percona engineers Laurynas Biveinis and Alexey Stroganov.<\/p>\n<p><strong>Diagnostics via TABLE\/INDEX\/USER STATISTIC and slow query log<\/strong><br \/>\nEven with MySQL 5.6\u2019s rich PERFORMANCE_SCHEMA information, we decided to keep our diagnostics.\u00a0Why? Because it is very easy to use. Check <a href=\"http:\/\/dom.as\/2013\/04\/17\/on-performance-schemas\/\" target=\"_blank\" rel=\"nofollow\">Domas\u2019 post<\/a> on this topic.<\/p>\n<p><strong>Integration with Percona XtraBackup: \u201cReal\u201d incremental backups and Archive logs backups<\/strong><br \/>\nPercona Server comes with following backup features:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/management\/changed_page_tracking.html\" target=\"_blank\">Changed page tracking<\/a> AKA \u201cReal\u201d incremental backups. Using this feature will avoid full table scans for incremental backups, and information on changed pages is now available in bitmap files.<\/li>\n<li><a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/management\/log_archiving.html\" target=\"_blank\">Archive Logs<\/a>. An alternative way to perform incremental backups by copying InnoDB transactional logs and applying them to backup.<\/li>\n<\/ul>\n<p>These features are <strong>unique<\/strong> to Percona Server, and, in combination with <a href=\"http:\/\/www.percona.com\/software\/percona-xtrabackup\" target=\"_blank\">Percona XtraBackup<\/a>, they allow users to achieve greater flexibility in backup schemas.<\/p>\n<p><strong>Statement timeouts<\/strong><br \/>\n<a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/management\/statement_timeout.html\" target=\"_blank\">This feature<\/a>\u00a0was ported from <a href=\"https:\/\/github.com\/twitter\/mysql\" target=\"_blank\" rel=\"nofollow\">Twitter\u2019s fork of MySQL<\/a> and allows users to control execution time of statements.<\/p>\n<p>You are welcome to review what has changed in <a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/changed_in_56.html\" target=\"_blank\">Percona Server 5.6 in our summary<\/a>, compare with <a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/ps-versions-comparison.html\" target=\"_blank\">previous Percona Server releases<\/a>, or <a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/feature_comparison.html\" target=\"_blank\">compare with MySQL 5.6<\/a>.<\/p>\n<p><strong>How do we do QA of Percona Server<\/strong><br \/>\nFor QA testing I want to give credit to Roel Van De Paar and his <a href=\"https:\/\/code.launchpad.net\/randgen\" target=\"_blank\" rel=\"nofollow\">Random Query Generator<\/a> 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.<br \/>\nAlso, we found, reported and fixed <a href=\"http:\/\/www.percona.com\/doc\/percona-server\/5.6\/upstream-bug-fixes.html\" target=\"_blank\">quite a large number of bugs for upstream MySQL<\/a>.<\/p>\n<p><strong>Performance results<\/strong><br \/>\nAnd of course I want to share benchmark results. What kind of performance gain can we expect with all these performance improvements I explained above?<\/p>\n<p>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.<\/p>\n<p>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).<\/p>\n<p>So let\u2019s compare Percona Server 5.6 and MySQL 5.6 in this workload.\u00a0The graph shows timeline for 30 mins run with 1 sec resolution.<br \/>\n<strong>Throughput (more is better)<\/strong>:<br \/>\n<a href=\"http:\/\/www.percona.com\/blog\/wp-content\/uploads\/2013\/10\/PS-thr.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-18383\" src=\"http:\/\/www.percona.com\/blog\/wp-content\/uploads\/2013\/10\/PS-thr.png\" alt=\"PS-thr\" width=\"758\" height=\"482\" \/><\/a><br \/>\n<strong>95% Response time (less is better)<\/strong>:<br \/>\n<a href=\"http:\/\/www.percona.com\/blog\/wp-content\/uploads\/2013\/10\/PS-rt.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-18382\" src=\"http:\/\/www.percona.com\/blog\/wp-content\/uploads\/2013\/10\/PS-rt.png\" alt=\"PS-rt\" width=\"758\" height=\"482\" \/><\/a><\/p>\n<p>We find that Percona Server 5.6 provides 2x better performance (in both throughput and response time) with much less variance.<\/p>\n<p>This is possible to achieve by decreasing internal contention in InnoDB and prioritizing page cleaner thread and free list refill.<\/p>\n<p>Now let me share configuration files for this run:<\/p>\n<div id=\"crayon-54feaca70e307182976547\" class=\"crayon-syntax crayon-theme-familiar crayon-font-monaco crayon-os-pc print-yes notranslate\" data-settings=\" minimize scroll-mouseover\">\n<div class=\"crayon-toolbar\" data-settings=\" show\">\n<div class=\"crayon-tools\">\n<div class=\"crayon-button crayon-nums-button crayon-pressed\" title=\"Toggle Line Numbers\"><\/div>\n<div class=\"crayon-button crayon-plain-button\" title=\"Toggle Plain Code\"><\/div>\n<div class=\"crayon-button crayon-wrap-button\" title=\"Toggle Line Wrap\"><\/div>\n<div class=\"crayon-button crayon-copy-button\" title=\"Copy\"><\/div>\n<div class=\"crayon-button crayon-popup-button\" title=\"Open Code In New Window\"><\/div>\n<p><span class=\"crayon-language\">Shell<\/span><\/div>\n<\/div>\n<div class=\"crayon-plain-wrap\"><\/div>\n<div class=\"crayon-main\">\n<table class=\"crayon-table\">\n<tbody>\n<tr class=\"crayon-row\">\n<td class=\"crayon-nums \" data-settings=\"show\">\n<div class=\"crayon-nums-content\">\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-1\">1<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-2\">2<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-3\">3<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-4\">4<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-5\">5<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-6\">6<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-7\">7<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-8\">8<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-9\">9<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-10\">10<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-11\">11<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-12\">12<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-13\">13<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-14\">14<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-15\">15<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-16\">16<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-17\">17<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-18\">18<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-19\">19<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-20\">20<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-21\">21<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-22\">22<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-23\">23<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-24\">24<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-25\">25<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-26\">26<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-27\">27<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-28\">28<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-29\">29<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-30\">30<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-31\">31<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-32\">32<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-33\">33<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-34\">34<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-35\">35<\/div>\n<div class=\"crayon-num crayon-striped-num\" data-line=\"crayon-54feaca70e307182976547-36\">36<\/div>\n<div class=\"crayon-num\" data-line=\"crayon-54feaca70e307182976547-37\">37<\/div>\n<\/div>\n<\/td>\n<td class=\"crayon-code\">\n<div class=\"crayon-pre\">\n<div id=\"crayon-54feaca70e307182976547-1\" class=\"crayon-line\"><span class=\"crayon-sy\">[<\/span><span class=\"crayon-v\">mysqld<\/span><span class=\"crayon-sy\">]<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-2\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_data_file_path<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-v\">ibdata1<\/span><span class=\"crayon-o\">:<\/span><span class=\"crayon-cn\">10M<\/span><span class=\"crayon-o\">:<\/span><span class=\"crayon-e\">autoextend<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-3\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_log_files_in_group<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">2<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-4\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_log_file_size<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">2G<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-5\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_buffer_pool_size<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">25GB<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-6\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_lru_scan_depth<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">4000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-7\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_flush_neighbors<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">0<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-8\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_log_buffer_size<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">256M<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-9\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_io_capacity<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-cn\">25000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-10\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_io_capacity_max<\/span><span class=\"crayon-o\">=<\/span> <span class=\"crayon-cn\">50000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-11\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_flush_log_at_trx_commit<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-cn\">1<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-12\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_buffer_pool_instances<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">15<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-13\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_file_format<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-e\">Barracuda<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-14\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_checksum_algorithm<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-e\">crc32<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-15\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_file_per_table<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-t\">true<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-16\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_doublewrite<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">1<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-17\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_flush_method<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-e\">O_DIRECT_NO_FSYNC<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-18\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_purge_threads<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">4<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-19\" class=\"crayon-line\"><span class=\"crayon-v\">table_open_cache<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">15000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-20\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">open_files_limit<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">15000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-21\" class=\"crayon-line\"><span class=\"crayon-v\">max_connections<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">15000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-22\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_read_io_threads<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-cn\">8<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-23\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_write_io_threads<\/span> <span class=\"crayon-o\">=<\/span> <span class=\"crayon-cn\">8<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-24\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_change_buffering<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-e\">all<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-25\" class=\"crayon-line\"><span class=\"crayon-v\">loose<\/span><span class=\"crayon-o\">&#8211;<\/span><span class=\"crayon-v\">innodb_sync_array_size<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">16<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-26\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">sync_binlog<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">0<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-27\" class=\"crayon-line\"><span class=\"crayon-v\">query_cache_type<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-e\">OFF<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-28\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">thread_cache_size<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">1000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-29\" class=\"crayon-line\"><span class=\"crayon-v\">back_log<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">2000<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-30\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">connect_timeout<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">15<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-31\" class=\"crayon-line\"><span class=\"crayon-v\">loose<\/span><span class=\"crayon-o\">&#8211;<\/span><span class=\"crayon-v\">metadata_locks_hash_instances<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">256<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-32\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">max_prepared_stmt_count<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">1048560<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-33\" class=\"crayon-line\"><span class=\"crayon-v\">loose<\/span><span class=\"crayon-o\">&#8211;<\/span><span class=\"crayon-v\">performance_schema<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">0<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-34\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-c\"># &#8212; below is Percona Server Specific &#8212; <\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-35\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_sched_priority_cleaner<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">39<\/span><span class=\"crayon-h\">\u00a0\u00a0<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-36\" class=\"crayon-line crayon-striped-line\"><span class=\"crayon-v\">innodb_log_block_size<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">4096<\/span><\/div>\n<div id=\"crayon-54feaca70e307182976547-37\" class=\"crayon-line\"><span class=\"crayon-v\">innodb_adaptive_hash_index_partitions<\/span><span class=\"crayon-o\">=<\/span><span class=\"crayon-cn\">65<\/span><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<p>And some comments on it:<\/p>\n<p>1. Please note that we are using fully durable settings:<br \/>\n<code>innodb_flush_log_at_trx_commit = 1<br \/>\ninnodb_doublewrite=1<br \/>\ninnodb_checksum_algorithm = crc32<br \/>\n<\/code><br \/>\nThis is different from the results provided by MySQL, where, to get better numbers, they disable data protection.<\/p>\n<p>2. <code>innodb_checksum_algorithm = crc32<\/code>. 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)<\/p>\n<p>3. Percona Server only specific settings:<br \/>\n<code>innodb_sched_priority_cleaner=39<\/code> \u2013 to give highest priority to page cleaner thread<br \/>\n<code>innodb_log_block_size=4096<\/code> \u2013 to use 4096 block size for InnoDB logs<br \/>\n<code>innodb_adaptive_hash_index_partitions=65<\/code> \u2013 to enable partitioning of adaptive hash index, otherwise quite often this is a contention point.<\/p>\n<p>And some variables which are used by default in Percona Server.<br \/>\n<code><br \/>\ninnodb_foreground_preflush=exponential_backoff<br \/>\ninnodb_empty_free_list_algorithm=backoff<br \/>\ninnodb_cleaner_lsn_age_factor=high_checkpoint<br \/>\n<\/code><\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>You are welcome to <a href=\"http:\/\/www.percona.com\/software\/percona-server\/downloads\/\" target=\"_blank\">try Percona Server 5.6<\/a> yourself and give us your feedback!<\/p>\n<p><strong>What is in the future?<\/strong><br \/>\nWe are not stopping here. Expect new improvements and more features.<\/p>\n<ul>\n<li>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<\/li>\n<li>TokuDB support. We will ship TokuDB in one of the next Percona Server 5.6 releases<\/li>\n<li>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<\/li>\n<li>Percona XtraDB Cluster 5.6, based on Percona Server 5.6 with all of its improvements, will be available in few months<\/li>\n<\/ul>\n<p>source:\u00a0percona.com<\/p>\n","protected":false},"excerpt":{"rendered":"<p>General performance improvements MySQL 5.6 by itself comes with a great list of performance fixes, however what we discovered that their focus was\u00a0on 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:\u2026 <span class=\"read-more\"><a href=\"https:\/\/adriangrigoras.com\/blog\/closer-percona-server-5-6\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[30],"tags":[],"class_list":["post-591","post","type-post","status-publish","format-standard","hentry","category-architecture"],"_links":{"self":[{"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/posts\/591","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/comments?post=591"}],"version-history":[{"count":1,"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/posts\/591\/revisions"}],"predecessor-version":[{"id":592,"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/posts\/591\/revisions\/592"}],"wp:attachment":[{"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/media?parent=591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/categories?post=591"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adriangrigoras.com\/blog\/wp-json\/wp\/v2\/tags?post=591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}