性能資料webinar-sep2013-optimizing mysql configuration for mysql 5 6_W

1、Optimizing MySQLConfiguration for MySQL 5.6Percona Technical Webinars Sep 25,2013Peter Zaitsev CEO, PAgendaMySQL Configuration Tuning BasicsWhats new with MySQL 5.6Looking at Most Important O2Things to Know About MySQLConfigurationDefault configuration is po

2、or MySQL does not scale it with server size MySQL 5.6 default changes are not enoughUnderstand what youre changing Google Copy/Paste without thinking can be badAvoid obsessive tuning disorder Setting 10 settings will give 95% of possible performance in 95% 3Most Options Do Not Sc

3、aleGoing to Server with 8x memory you cant justmultiply all configuration variables 8x16GB of memory to 128GB of memory sort_buffer_size 4MB to 32MB is bad 4Know Scope and Unitsort_buffer_size=16G Wrong! sort_buffer_size is set per connectiontable_cache_size=64M Wrong!table_cache

4、_size is set in elements notmemory 5Set Variables LocallyMany variables are SESSION Can be set for current session onlySet variable value for session doing complexqueries instead of setting it globally:mysql set session sort_buffer_size=16*1024*1024; Query OK, 0 rows affected (0.

5、00 sec)6Avoid Basic MistakesSetting variables in the wrong config file /etc/mysql/f instead of /etc/f These depend on Linux Distro, BewareDuplicating options Last option will override previously setNot knowing synonyms table_cache is same as table_open_cacheUsing wrong secti

6、on for options Server reads mysqld, client 7Config Management PracticesKeep Config files in sync on different servers Out of Sync config files is a frequent cause of mistakes and confusionKeep record of changesConfig files under version control is greatAt least keep your changes

7、8Do Not Let MySQL SwapAllocating too much memory and having MySQL swapping is a lot worse than not using all memoryMonitor swapping (si/so from vmstat closely) Start with safe buffer values and increase them gradually if a lot of memory stays freepzubuntu:$ vmstat 5procs -me

8、mory- -swap- -io- -system- -cpu-r bswpd free buff cache si sobiboin cs us sy id wa1000000000000 2725708 253216 5135720 2725700 253216 5135960 2725700 253216 5135960 2725700 253216 5135960 2725700 253216 5135960 2725700 253216 513596000000000000100000103000207270707070227374740 0 1000 0 1000 0 99 100

9、00 1000 1000 10000074 072 09AgendaMySQL Configuration Tuning BasicsWhats new with MySQL 5.6Looking at Most Important O10Better DefaultsChanges to defaults values Many Variables are now computed based on other variables Most important changesinnodb_file_per_table=1

10、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=4M11Auto Resizing Innodb Log filesCumbersome to change innodb_log_file_sizebefore MySQL 5.6Now Automatic!InnoDB: Resizing redo log fr

11、om 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: Renam

12、ing log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=162600712Improved Performance Schema“Mixed” Configuration mode Configuration file and tablesskip-performance-schema Disable if not using performance schemaLimits have to be set statically performance_sche

13、ma_events_stages_history_long_size=10000Can configure instruments/consumers performance-schema-instrument=wait/synch/cond/%=countedCheck out Webinar on Performance Schema configuration and usage http:/bit.ly/ZWhQV13New Meaning for some variablesjoin_buffer_size used for BKAwww.percon

14、14MySQL 5.6 top options to considerinnodb_io_capacity=2000innodb_io_capacity_max=6000innodb_lru_scan_depth=2000 relay-log-info-repository=TABLE master-info-repository=TABLE table_open_cache_instances = 16 join_buffer_size=16Minnodb_checksum_algorithm=crc32 innodb_flush_neighbors=0 innodb_monito

15、r_enable = %15AgendaMySQL Configuration Tuning BasicsWhats new with MySQL 5.6Looking at Most Important O16Lets Look at the Options NowDifferent classes of options:General OptionsMyISAMInnodbVisibility and L17Getting Status VariablesWe refer to

16、 SHOW GLOBAL STATUS output inmany descriptionspt-mext from Percona Toolkit is helpfulpt-mext -r - mysqladmin ext -i100 -c4Aborted_clients Aborted_connects Binlog_cache_disk_use Binlog_cache_use12890932628570000580976141788600004591131018617Bytes_received Bytes_sent1465189026811202983049426www.percon

17、18General Optionsmax_connectionsHow many connections to allow?Watch max_used_connections status valuethread_cacheCache to prevent excessive thread creationAuto set in MySQL 5.6Otherwise 50-100 is good value. Watch threads_createdtable_cache/table_open_cacheCache of opened table instancesSingle

18、table may have multiple entries Watch opened_tables status value Start with 4096MySQL will only use as needed anyway.table_open_cache_instances=16If have heavy contention on table 19General Optionsopen_files_limitMyISAM tables require up to 2 file handlers Each connection is file

19、 handler too Safe to set to 65535 in most systemstable_definition_cacheCache table definitions (CREATE TABLE)Only one entryper tableWatch Opened_table_definitionsSet to number of tables + 10% unless 50K+ tables meta_data_locks_hash_instances=256 When we have contention on meta data locks Only helps

20、with multi table 20General Optionsback_logNeed 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 configurationmax_allowed_packetLimits maximum size of query Limits internal string varia

21、ble size16MB is a good valuemax_connect_errors Can cause “Host Blocked” error messages Especially running on unreliable networks Value around 1000000 is 21General Optionsskip_name_resolve Avoid DNS lookup on connection. Faster and safer Do not use host names in GRANTsold_passwords

22、Should NOT be enabled. Will cause insecure passwordhash to be 22General Optionslog_bin Enable for replication and point in time recovery Set to “mysql-bin” to avoid default namingsync_binlog Make Binlog durable. Set to 1 if have RAID with BBU or Flash Can be a real performance ki

23、ller with slow drives.sync_relay_log Same for relay log starting MySQL 5.6expire_log_days Purge old binary logs after this number of days 14 (2 weeks) is a good value with weekly 23General Optionstmp_table_sizemax_heap_table_sizeTypically set to same value (workload based)Crea

24、ted_tmp_disk_tables status variableBeware BLOB/TEXT fields cause on disk table with any size.query_cache_sizeEnable query cache only if it is tested to provide significantgainsOften causes stalls and contentionDo not set above 512MB24General Optionssort_buffer_sizeIn memory buffer use

25、d for sortingWatch sort_merge_passesConsider setting for session for large queries Default lowered to 256K on MySQL 5.6 Large values hurt performance of small queriesjoin_buffer_sizeHelps performance of Joins with no indexes Better get rid of such Joins!Also used with BKA in MySQL 5.6 8MB can be rea

26、sonable valuedefault_storage_engine Use this engine for tables if not specifiedAlso check 25General Optionsread_rnd_buffer_sizeBuffer for reading rows in sorted offerSpecifies maximum valueValues around 16MB often make sense Also used as a buffer for MRR in 5

27、.6Do not mix with read_buffer_sizeTmpdirSpecify location of temporary directoryTmpfs often good choice unless very large temporary space is needed.May cause Innodb to refuse using native AIO, when it is not worth ittmpdir=/dev/26Optimizer SwitchMany optimizer specific options are m

28、oved tooptimizer_switchDefaults generally good. Might need to adjust if getting bad query 27mysql SELECT optimizer_switchG* 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_c

29、ondition_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=onMyISAMMyISAM ? What MyISAM You do not want to use MyISAM with MySQL 5.6 It will be s

30、till used for “mysql” database and on disk TMP tableskey_buffer_size=32M May be larger if large TMP tables are usedmyisam_recover=BACKUP,FORCE In case tables in “mysql” database get 28Innodb Memory Settingsinnodb_buffer_pool_size The most important setting. Often 80%+ of memo

31、ry is allocated here.innodb_buffer_pool_instances Reduce contention.Default of 8 in MySQL 5.6innodb_log_buffer_size Buffer for log files.Good Values 4MB-128MB Not only reduce writes but help contentioninnodb_change_buffer_max_size Control size of Insert buffer. Default is of Buffer pool. Smaller val

32、ues are good for SSD29Innodb IO Optionsinnodb_flush_log_at_trx_commit Control Durability 1=flush and sync; 2=flush; 0=neitherInnodb_flush_method Controls how Innodb Performs IO O_DIRECT good value for most serversinnodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_dump_now Save and

33、 restore Buffer Pool for faster warmupinnodb_io_capacity Controls Innodb Assumption about Disk Performance. Increase for faster drives. Default of 200 is quite low.Innodb_io_capacity_maxbursts in MySQL 5.630Innodb IO OptionsInnodb_read_io_threadsInnodb_write_io_threadsControl number o

34、f threads doing reads and writesMySQL 5.5+ has async IO so very high values might not be needed4 is good default. Higher for large IO systems.innodb_flush_neighbors=0 Can give better performance especially for SSD31Innodb Undo Table SpacesStore Undo records in Separate TablespaceInit

35、ializing Innodb Instance innodb_undo_tablespaces=2 One or more dedicated tablespacesCan play at run time innodb_undo_logs=8 innodb_undo_directory=/fast/storage Has to be durable !32Other Innodb Optionsinnodb_log_file_size Size of redo log file. Larger logs = better performance but lon

36、ger recovery.innodb_log_files_in_group Leave at 2 which is default.innodb_file_per_table Store each Innodb table in separate file. Usually a good choice. Default in MySQL 5.633Other Innodb Optionsinnodb_data_file_pathSettings for Innodb System Tablespace Use one file. Limit growth, as

37、 you cant shrink itibdata1:10M:autoextend:max:10Ginnodb_lock_wait_timeout How long to wait for row level locks before bailing out?innodb_old_blocks_timeHelps to make buffer pool scan resistant Values around 1000 make sense Default in MySQL 5.634Other Innodb Optionsinnodb_file_formatWh

38、ich file format Innodb will use “Antelope” is default legacy format“Barracuda” allows use of new features like compressioninnodb_stats_on_metadataUpdate statistics on meta data accessSuch as Information_schema queries Typically best disabled for more workloads Set to 0 (Default in MySQL 5.6) Innodb wil