1、1Copyright © 2013, Oracle and/or its affiliates.50 Tips for Boosting MySQL PerformanceArnaud ADANTMySQL Principal Support Engineer2 Copyright © 2013, Oracle and/or its affiliates.Safe Harbour StatementTHE FOLLOWING IS INTENDED TO OUTLINE OUR GENERAL PRODUCT DIRECTION. IT ISINTENDED FOR INF

2、ORMATION PURPOSES ONLY, AND MAY NOT BE INCORPORATED INTOANY CONTRACT. IT IS NOT A COMMITMENT TIVER ANY MATERIAL, CODE, ORFUNCTIONALITY, AND SHOULD NOT BE RELIED UPON IN MAKING PURCHASINGDECISIONS. THE DEVELOPMENT, RELEASE, ANDOF ANY FEATURES ORFUNCTIONALITY DESCRIBED FOR ORACLES PRODUCTS REMAINS AT

3、THE SOLEDISCRETION OF ORACLE.3Copyright © 2013, Oracle and/or its affiliates.Program Agenda§ Introduction§ 50 MySQL Performance Tips§ Q & A4Copyright © 2013, Oracle and/or its affiliates.Introduction : Who I amArnaud ADANT§§ 10 year+ Development§ MySQL Sup

4、port for 3 years§ MySQL Performance§ I love my job !5Copyright © 2013, Oracle and/or its affiliates.Introduction : Why 50 ?50 items is an effective format !§ Effective C+, Meyers, 1991 55 items§ Effective Java, Bloch, 2001 57 items6Copyright © 2013, Oracle and/or its af

5、filiates.Tip 0. Never trust anyone, benchmarkHW and SW vendors are all lying !§ Test, test, test§ Benchmark, benchmark, benchmark§ Monitor, monitor, monitor§ One knob at a time§ Use sysbench, mysqlslap, monitoring tools11Copyright © 2013, Oracle and/or its affiliates.Ti

6、p 1. Make sure you have enough RAMDepends on your active data and connectionsThe active data should fit in the buffer pool MySQL connections and caches take memory ECC RAM recommendedExtra RAM for§§§§FS cache MonitoringRAM disk (tmpfs)13Copyright © 2013, Oracle and/or its af

7、filiates.Tip 2. Use fast and multi-core processorsFast CPU is required for single thRecent servers have 32 to 80 cores.ed performance§§§§§§Enable hyper-thingMySQL can only scale to 16 cores in 5.5 and 32-48 cores in 5.6 Same core count in fewer sockets is betterFaster c

8、ores better than more but slower cores14Copyright © 2013, Oracle and/or its affiliates.Tip 3. Use fast and reliable storageWill always helpGood for IO bound loads§§§§§§HDD for sequentials and writesBus-attached SSD for random Big sata or other disk for log files Se

9、veral disks !Life times and writes15Copyright © 2013, Oracle and/or its affiliates.Tip 4. Choose the right OSMySQL is excellent on LinuxL of LAMPGood on SolarisOracle invests on WindowsFor pure performance, favor Linux§§§§18Copyright © 2013, Oracle and/or its affiliates

10、.Tip 5. Adjust the OS limitsOS limits are extremely important !Max open files per process§ulimit nlimits the number of file handles (connections, open tables, )Max ths per user§ulimit ulimits the number of ths (connections, event scheduler, shutdown)On Windows, MaxUserPort for TCP/IP, for

11、2003 and earlier§19Copyright © 2013, Oracle and/or its affiliates.Tip 6. Consider using alternative mallocSome malloc libraries are optimized for multi-core environmentjemalloc is a good malloc replacement§mysqld_safemalloc-lib=/usr/lib64/libjemalloc.so.1tcmalloc shipped on Linux with

12、 MySQL§mysqld_safemalloc-lib=tcmalloc20Copyright © 2013, Oracle and/or its affiliates.Tip 6. Consider using alternative mallocSysbench OLTP ROHigh concurrency21Copyright © 2013, Oracle and/or its affiliates.Tip 7. Set CffiOn Linux and Windows, Cffihelps concurrent WLtasksetd on Linux&

13、#167;taskset -c 1-4 pidof mysqldtaskset -c 1,2,3,4 pidof mysqldOn Windows :START /AFFI START /AFFI§0x1111 binmysqld console15 binmysqld console22Copyright © 2013, Oracle and/or its affiliates.Tip 8. Choose the right file systemXFS for experts, ext4 or ext3 xfs is excellent With innodb_flus

14、h_method = O_DIRECT supported by Oracle on OEL less stable recently ext4 best choice for speed and ease of use fsyncs a bit slower than ext3 more reliable ext3 is also a good choice DYI nfs is problematic with InnoDB25Copyright © 2013, Oracle and/or its affiliates.Tip 9. Mount optionsFor perfor

15、mance ext4 (rw,noatime,nodiratime,nobarrier,data=ordered) xfs (rw, noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k) SSD specific trim innodb_page_size = 4K Innodb_flush_neighbors = 026Copyright © 2013, Oracle and/or its affiliates.Tip 10. Choose the best I/O schedulerUse deadline or noop on

16、 Linux§ deadline is generally the best I/O scheduler§ echo deadline > /sys/block/DEVICE-NAME/queue/scheduler§ the best value is HW and WL specific noop on high end controller (SSD, good RAID card ) deadline otherwise27Copyright © 2013, Oracle and/or its affiliates.Tip 11. Use

17、a battery backed disk cacheA good investment !§ Usually faster fsyncs innoDB redo logs binary logs data files§ Crash safety§ Durability§ Applies to SSD28Copyright © 2013, Oracle and/or its affiliates.Tip 12. Balance the load on several disks90 % of customers use a single dis

18、k !§ One disk is not a good idea§ Especially for HDD,§ Separate : datadir innodb_data_file_pathand writeRandom, SSD innodb_undo_directory innodb_log_group_home_dir log-bin tmpdirSequential, spinningRandom, SSD, tmpfs29Copyright © 2013, Oracle and/or its affiliates.13. Turn Off th

19、e Query CacheSingle thed bottleneck, only on low concurrency systems§ Only if ths_running <= 4§ Becomes fragmented Cache should be in the App ! Off by default from 5.6 query_cache_type = 0 query_cache_size =032Copyright © 2013, Oracle and/or its affiliates.Tip 13. Turn Off the Quer

20、y Cache5000040000300002000010000Sysbench OLTP ROQPS drops withconnections and QCTPS QC = onTPS QC = offQcache_blocksqcache_ 5-10k_blocks >QPS0124816connections33Copyright © 2013, Oracle and/or its affiliates.Tip 13. Turn Off the Query CacheHow to see there is a problem ?qcache_blocks > 5-

21、10k§§stage/sql/Waiting for query cache lock34Copyright © 2013, Oracle and/or its affiliates.Tip 14. Use the ThPoolStabilize TPS for high concurrencyUseful ifths_running >hardware thsDecrease context switchesSeveral connections for one executionth35Copyright © 2013, Oracle and/

22、or its affiliates.Tip 15. Configure table cachingMySQL has at least one file per table : the FRM filetable_open_cache§not too small, not too big, used to size PSopened_tables / sectable_definition_cache§do not forget to increaseopened_table_definitions / sectable_cache_instances = 8 or 16i

23、nnodb_open_files mdl_hash_instances = 256§§§36Copyright © 2013, Oracle and/or its affiliates.Tip 15. Configure table cachingconcurrencyconcurrencyTPS16 instances1 instance37Copyright © 2013, Oracle and/or its affiliates.Tip 16. Cache the thsTh§creation / initialization

24、is expensiveth_cache_sizedecreases ths_created ratecapped by max user processes (see OS limits)5.7.2 refactors this code§§38Copyright © 2013, Oracle and/or its affiliates.Tip 17. Reduce per thmemory usageMemory allocation is expensivemax_used_connections * (_buffer_size +_rnd_buffer_s

25、ize + join_buffer_size + sort_buffer_size +binlog_cache_size +§th_stack +2 * net_buffer_length )39Copyright © 2013, Oracle and/or its affiliates.Tip 18. Beware of sync_binlog = 1sysbench RWsync_binlog = 1 was a performance killer in 5.55.6 binlog group commit fixed itBetter with SSD or bat

26、tery backed disk cache41Copyright © 2013, Oracle and/or its affiliates.Tip 19. Move your tables to InnoDBInnoDB is the most advanced MySQL storage engineScalable99% of MyISAM use cases coveredOnline alter operations Full text engineMemcached API for high performance§§§§§

27、;42Copyright © 2013, Oracle and/or its affiliates.Tip 20. Use a large buffer pool50 80% of the total RAMinnodb_buffer_pool_size Not too large for the data Do not swap !Beware of memory crash if swap§§§§§is disabledActive data <= innodb_buffer_pool_size <= 0.8 * RA

28、M45Copyright © 2013, Oracle and/or its affiliates.Tip 21. Reduce the buffer pool contentionKey to achieve high QPS / TPSinnodb_buffer_pool_instances >= 8Reduce rows_examined / sec (see Bug #68079)8 is the default value in 5.6 !In 5.5, but even better in 5.6 and 5.7innodb_spin_wait_delay = 96

29、 on high concurrency§§§§§§Useonly tranionswhen possible46Copyright © 2013, Oracle and/or its affiliates.Tip 21. Reduce the buffer pool contention47Copyright © 2013, Oracle and/or its affiliates.Tip 22. Use large redo logsA key parameter for write performanceRe

30、do logs defer the expensive changes to the data files Recovery time is no more an issue innodb_log_file_size = 2047M before 5.6 innodb_log_file_size >= 2047M from 5.6Bigger is better for write QPS stability§§§§§48Copyright © 2013, Oracle and/or its affiliates.Tip 22.

31、 Use large redo logs49Copyright © 2013, Oracle and/or its affiliates.Tip 23. Adjust the IO capacityinnodb_io_capacity should reflect device capacityIO OPS the disk(s) can doHigher for SSDIncrease if several disks for InnoDB IOIn 5.6, innodb_lru_scan_depth is per buffer pool instance so innodb_l

32、ru_scan_depth =innodb_io_capacity / innodb_buffer_pool_instancesDefaultinnodb_io_capacity_max =min(2000, 2 * innodb_io_capacity)§§§§§50Copyright © 2013, Oracle and/or its affiliates.Tip 24. Configure the InnoDB flushingDurability settingsRedo logs :§innodb_flush_lo

33、g_at_trx_commitinnodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit= 1 / best durability= 2 / better performance= 0 / best performanceData files only :§innodb_flush_method = O_DIRECT / Linux, skips the FS cacheIncrease innodb_adaptive_flushing_lwm (fast disk)§51Copyright © 20

34、13, Oracle and/or its affiliates.Tip 25. Enable innodb_file_per_tableinnodb_file_per_table = ON is the default in 5.6Increased manageabilityTruncate reclaims disk spaceBetter with innodb_flush_method = O_DIRECT Easier to optimizeBut §§§§§not so good with many small tables mo

35、re file handles (see OS limits)more fsyncs53Copyright © 2013, Oracle and/or its affiliates.Tip 26. Configure the thconcurrencyinnodb_th_concurrency / innodb_max_concurrency_ticketspool :No th§innodb_th innodb_th align to HW thpool :innodb_th_concurrency = 16 - 32 in 5.5_concurrency = 36 in

36、 5.6 s if less than 32 coresTh§_concurrency = 0 is fineinnodb_max_concurrency_tickets : higher for OLAP, lower for OLTP§54Copyright © 2013, Oracle and/or its affiliates.Tip 27. Reduce the tranion isolationDefault = repeatablesApplication dependentcommitted§§it implies binlog

37、_format = ROWVariable : tranion-isolation§§Lower isolation = higher performance55Copyright © 2013, Oracle and/or its affiliates.Tip 28. Design the tablesChoose the charset, PK and data types carefullyinteger primary keys§avoid varchar, composite for PKlatin1 vs. utf8the smallest

38、varchar for a columnkeep the number of partitions low (< 10)use compression for blob / text data types§§§§58Copyright © 2013, Oracle and/or its affiliates.Tip 29. Add indexesIndexes help decrease the number of rows examinedfor fast access to recordsfor sorting / grou§

39、;§without temporary tablecovering indexes§contain all the selected datasave access to full recordreduce randoms59Copyright © 2013, Oracle and/or its affiliates.Tip 30. Remove unused indexesRedundndexes must be removedToo many indexes hurt performance§Bad for the optimizerMore IO,

40、 more CPU to update all the indexesRemove same prefix indexesUse ps_helper views§§schema_unused_indexes60Copyright © 2013, Oracle and/or its affiliates.Tip 31. Reduce rows_examinedMaybe the most important tip for InnoDB and queries !Rowsfrom the storage engines§§Rows_examine

41、dslow query logP_S statement digests MEM 3.0 query analysis Handler%rows_examined > 10 * rows_sent§missing indexesquery tuning !63Copyright © 2013, Oracle and/or its affiliates.Tip 31. Reduce rows_examinedPer query handlersselect DiffSum§§Handler%64Copyright © 2013, Oracl

42、e and/or its affiliates.Tip 31. Reduce rows_examinedslow query log§show engine innodb status§ps_helper§65Copyright © 2013, Oracle and/or its affiliates.Tip 32. Reduce rows_sentAnother performance killerrows_sent <= rows_examinedNetwork transfersCPU involvedApps seldom need mor

43、e than 100 rows LIMIT !§§§§§66Copyright © 2013, Oracle and/or its affiliates.Tip 33. Reduce lockingMake sure the right execution plan is usedUPDATE, SELECT FOR UPDATE, DELETE, INSERT SELECTUse a PK ref, UK ref to lockAvoid large index range and table scans Reduce rows_e

44、xamined for locking SQL Locking is expensive (memory, CPU)Commit when possible§§§§§§67Copyright © 2013, Oracle and/or its affiliates.Tip 34. Mine the slow query logFind the bad queriesDynamic collectionThe right intervalTop queries§§§Sort by query ti

45、me descperl mysqldumpslow.pl s t slow.logSort by rows_examined desc§Top queries at the 60s range§68Copyright © 2013, Oracle and/or its affiliates.Tip 34. Mine the slow query logLog everything for 60 seconds to the slow query log :69Copyright © 2013, Oracle and/or its affiliates.S

46、ET saved_slow_query_log = slow_query_log; SET saved_long_query_time = long_query_time; SET global slow_query_log = 1;SET global long_query_time = 0; select sleep(60);SET global slow_query_log = saved_slow_query_log; SET global long_query_time = saved_long_query_time;Tip 35. Use the performance_schem

47、aThe performance_schema is a great toolps_helper :§good entry pointy to use viewsIO / latency / waits / statement digestsideal for dev and stagingFor high performance systems, performance_schema = 0§70Copyright © 2013, Oracle and/or its affiliates.Tip 36. Tune the replication thUsuall

48、y replication is a black boxSlow query log with§log-slow-slave-statements is now dynamic (Bug #59860) from 5.6.11Performance_schema (Bug #16750433)§Not instrumented before 5.6.14binlog_format = ROW§show global statuslikeHandler%71Copyright © 2013, Oracle and/or its affiliates.Tip

49、 37. Avoid temporary tables on diskWriting to disk is not scalable !Large temporary tables on disk§handler_writecreated_tmp_disk_tablesmonitor tmpdir usageFrequent temporary tables on disk§High created_tmp_disk_tables / uptimeshow global status like '%tmp%'In 5.6, included in state

50、ment digests and ps_helperAvailable in MEM 3.0§§73Copyright © 2013, Oracle and/or its affiliates.Tip 38. Cache data in the AppSave MySQL resources !Good for CPU / IOCache the immutable !§§referential datamemcachedQuery cache can be disabledIdentify frequent statements§&

51、#167;perl mysqldumpslow.pl s c slow60s.log74Copyright © 2013, Oracle and/or its affiliates.Tip 39. Avoid long running tranionsA frequent cause of performance dropsUsually the oldest tranions§High history_list_length Prevent the purge Decrease performanceKill if abandoned§§§&

52、#167;75Copyright © 2013, Oracle and/or its affiliates.Tip 40.idle connectionsIdle connections consume resources !Either kill or refresh them !§Connection pools : validation query76Copyright © 2013, Oracle and/or its affiliates.Tip 41.prepare statementsPrepare andmust be balancedcom_st

53、mt_prepare com_stmt_= 0§77Copyright © 2013, Oracle and/or its affiliates.Tip 42. Configure Connector / JConnectors must be tuned too !property forum performance :§userConfigs=maxPerformanceUse if the server configuration is stableRemoves frequent SHOW COLLATIONSHOW GLOBAL VARIABLES

54、67;§Fast validation query : /*/§78Copyright © 2013, Oracle and/or its affiliates.Tip 43 - 4543. Do not use the information_schema in your App44. Views are not good for performancetemporary tables (on disk)45.Scale out, shard79Copyright © 2013, Oracle and/or its affiliates.Tip 46.

55、 Monitor the database and OSA monitoring tool is DBAs friend :alertsgraphsavailability and SLAsthe effect of tuning query analysis§§§§§82Copyright © 2013, Oracle and/or its affiliates.MySQL Enterprise Monitor 3.0 is GA !SLA monitoring§§Real-time performance mo

56、nitoring§Alerts & notifications§MySQL best practice advisors"The MySQL Enterprise Monitor is an absolute must for any DBA who takes his work seriously.”- Adrian Baumann, System Specialist Federal Office of Information Technology & Telecommunications83Copyright © 2013, Oracle and/or its affiliates.Query AnalyzerReal-time query performance§§Visual correlation graphs§Find & fix expensive queries§ed query statistics“With the MySQ