Optimizing MySQL Configuration

1 .Optimizing MySQL Configuration 10 November ,2016 Peter Zaitsev CEO, Percona www.percona.com 

2 . Agenda • MySQL Configuration Tuning Basics • What’s new with MySQL • Looking at Most Important Options 2 www.percona.com 

3 . Things to Know About MySQL Configuration • Default configuration is poor • MySQL does not scale it with server size • MySQL default changes are not enough • Understand what you're changing • Google Copy/Paste without thinking can be bad • Avoid obsessive tuning disorder • Setting 10 settings will give 95% of possible performance in 95% cases 3 www.percona.com 

4 . Most Options Do Not Scale • Going to Server with 8x memory you can't just multiply all configuration variables 8x • 16GB of memory to 128GB of memory – sort_buffer_size 4MB to 32MB is bad idea. 4 www.percona.com 

5 . Know Scope and Unit • sort_buffer_size=16G – Wrong! sort_buffer_size is set per connection • table_cache_size=64M – Wrong! table_cache_size is set in elements not memory size. 5 www.percona.com 

6 . Set Variables Locally • Many variables are SESSION – Can be set for current session only • Set variable value for session doing complex queries instead of setting it globally: mysql> set session sort_buffer_size=16*1024*1024; Query OK, 0 rows affected (0.00 sec) 6 www.percona.com 

7 . Avoid Basic Mistakes • Setting variables in the wrong config file • /etc/mysql/my.cnf instead of /etc/my.cnf • These depend on Linux Distro, Beware • Duplicating options • Last option will override previously set • Not knowing synonyms • table_cache is same as table_open_cache • Using wrong section for options • Server reads [mysqld], client [mysql] 7 www.percona.com 

8 . Config Management Practices • Keep Config files in sync on different servers • Out of Sync config files is a frequent cause of mistakes and confusion • Keep record of changes • Config files under version control is great • At least keep your changes documented 8 www.percona.com 

9 . Do Not Let MySQL Swap • Allocating too much memory and having MySQL • swapping is a lot worse than not using all memory • Monitor swapping (si/so from vmstat closely) • Start with safe buffer values and increase them gradually if a lot of memory stays free pz@ubuntu:~$ vmstat 5 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 1 0 0 2725708 253216 513572 0 0 1 1 20 22 0 0 100 0 0 0 0 2725700 253216 513596 0 0 0 0 72 73 0 0 100 0 0 0 0 2725700 253216 513596 0 0 0 3 70 74 0 0 99 1 0 0 0 2725700 253216 513596 0 0 0 0 70 74 0 0 100 0 0 0 0 2725700 253216 513596 0 0 0 0 70 74 0 0 100 0 0 0 0 2725700 253216 513596 0 0 0 0 70 72 0 0 100 0 9 www.percona.com 

10 . Agenda • MySQL Configuration Tuning Basics • What’s new with MySQL • Looking at Most Important Options 10 www.percona.com 

11 . Better Defaults • Changes to defaults values – Many Variables are now computed based on other variables – Most important changes • innodb_file_per_table=1 • innodb_buffer_pool_instances=8 • innodb_log_file_size=48M • innodb_old_blocks_time=1000 • innodb_stats_on_metadata=off • thread_cache_size=8+ • max_allowed_packet=4M 11 www.percona.com 

12 . Auto Resizing Innodb Log files • Cumbersome to change innodb_log_file_size before MySQL Now Automatic! InnoDB: Resizing redo log from 2*3072 to 2*32768 pages, LSN=1626007 InnoDB: Starting to delete and rewrite log files. InnoDB: Setting log file ./ib_logfile101 size to 512 MB InnoDB: Progress in MB: 100 200 300 400 500 InnoDB: Setting log file ./ib_logfile1 size to 512 MB InnoDB: Progress in MB: 100 200 300 400 500 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=1626007 12 www.percona.com 

13 . Improved Performance Schema • “Mixed” Configuration mode – Configuration file and tables • skip-performance-schema – Disable if not using performance schema • Can configure instruments/consumers – performance-schema- instrument='wait/synch/cond/%=counted‘ • Check out Webinar on Performance Schema configuration and usage – http://bit.ly/ZWhQVi 13 www.percona.com 

14 . New Meaning for some variables • join_buffer_size used for BKA 14 www.percona.com 

15 . MySQL top options to consider • innodb_io_capacity=2000 • innodb_io_capacity_max=6000 • innodb_lru_scan_depth=2000 • relay-log-info-repository=TABLE • master-info-repository=TABLE • table_open_cache_instances = 16 • join_buffer_size=16M • innodb_checksum_algorithm=crc32 • innodb_flush_neighbors=0 • innodb_monitor_enable = '%' 15 www.percona.com 

16 . Agenda • MySQL Configuration Tuning Basics • What’s new with MySQL Looking at Most Important Options 16 www.percona.com 

17 . Lets Look at the Options Now • Different classes of options: • General Options • MyISAM • Innodb • Visibility and Logging 17 www.percona.com 

18 . Getting Status Variables • We refer to SHOW GLOBAL STATUS output in many descriptions • pt-mext from Percona Toolkit is helpful • pt-mext -r -- mysqladmin ext -i100 -c4 Aborted_clients 128 0 0 Aborted_connects 909 0 0 Binlog_cache_disk_use 3 0 0 Binlog_cache_use 262857 0 0 Bytes_received 146518902681 580976 459113 Bytes_sent 1202983049426 1417886 1018617 18 www.percona.com 

19 . General Options • max_connections • How many connections to allow? • Watch max_used_connections status value • thread_cache • Cache to prevent excessive thread creation • Auto set in MySQL 5.6 • Otherwise 50-100 is good value. Watch threads_created • table_cache/table_open_cache • Cache of opened table instances • Single table may have multiple entries • Watch opened_tables status value • Start with 4096 • MySQL will only use as needed anyway. • table_open_cache_instances=16 • If have heavy contention on table cache 19 www.percona.com 

20 . General Options • open_files_limit • MyISAM tables require up to 2 file handlers • Each connection is file handler too • Safe to set to 65535 in most systems • table_definition_cache • Cache table definitions (CREATE TABLE) • Only one entry per table • Watch Opened_table_definitions • Set to number of tables + 10% unless 50K+ tables 20 www.percona.com 

21 . General Options • back_log • Need adjustment if many connections/sec • MySQL 5.6 Auto tunes but might not go high enough • 2048 is reasonable value – Note OS TCP/IP stack might need configuration • max_allowed_packet • Limits maximum size of query • Limits internal string variable size • 16MB is a good value • max_connect_errors • Can cause “Host Blocked” error messages – Especially running on unreliable networks • Value around 1000000 is good 21 www.percona.com 

22 . General Options • skip_name_resolve • Avoid DNS lookup on connection. Faster and safer • Do not use host names in GRANTs • old_passwords • Should NOT be enabled. Will cause insecure password hash to be used. 22 www.percona.com 

23 . General Options • log_bin • Enable for replication and point in time recovery • Set to “mysql-bin” to avoid default naming • sync_binlog • Make Binlog durable. Set to 1 if have RAID with BBU or Flash • Can be a real performance killer with slow drives. • sync_relay_log – Same for relay log starting MySQL 5.6 • expire_log_days • Purge old binary logs after this number of days • 14 (2 weeks) is a good value with weekly backups. 23 www.percona.com 

24 . General Options • tmp_table_size • max_heap_table_size • Typically set to same value (workload based) • Created_tmp_disk_tables status variable • Beware BLOB/TEXT fields cause on disk table with any size. • query_cache_size • Enable query cache only if it is tested to provide significant gains • Often causes stalls and contention • Do not set above 512MB 24 www.percona.com 

25 . General Options • sort_buffer_size • In memory buffer used for sorting • Watch sort_merge_passes • Consider setting for session for large queries • Default lowered to 256K on MySQL 5.6 – Large values hurt performance of small queries • join_buffer_size • Helps performance of Joins with no indexes – Better get rid of such Joins! • Also used with BKA in MySQL 5.6 • 8MB can be reasonable value • default_storage_engine • Use this engine for tables if not specified • Also check default-tmp-storage-engine 25 www.percona.com 

26 . General Options • read_rnd_buffer_size • Buffer for reading rows in sorted offer • Specifies maximum value • Values around 16MB often make sense • Also used as a buffer for MRR in 5.6 • Do not mix with read_buffer_size • Tmpdir • Specify location of temporary directory • Tmpfs often good choice unless very large temporary space is needed. • May cause Innodb to refuse using native AIO, when it is not worth it • tmpdir=/dev/shm 26 www.percona.com 

27 . Optimizer Switch • Many optimizer specific options are moved to optimizer_switch • Defaults generally good. Might need to adjust if getting bad query plans mysql> SELECT @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on,index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on,mrr_cost_based=on, block_nested_loop=on,batched_key_access=off, materialization=on, semijoin=on,loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use _index_extensions=on 27 www.percona.com 

28 . MyISAM • MyISAM ? What MyISAM • You do not want to use MyISAM with MySQL 5.6 • It will be still used for “mysql” database and on disk TMP tables • key_buffer_size=32M • May be larger if large TMP tables are used • myisam_recover=BACKUP,FORCE • In case tables in “mysql” database get corrupted 28 www.percona.com 

29 . Innodb – Memory Settings • innodb_buffer_pool_size • The most important setting. Often 80%+ of memory is allocated here. • innodb_buffer_pool_instances • Reduce contention. Default of 8 in MySQL 5.6 • innodb_log_buffer_size • Buffer for log files. Good Values 4MB-128MB • Not only reduce writes but help contention • innodb_change_buffer_max_size • Control size of Insert buffer. Default is ¼ of Buffer pool. Smaller values are good for SSD 29 www.percona.com