21 tips for optimizing MySQL

Today a friend asked me how to optimize MySQL. I sorted it out according to my thinking, and it can be roughly divided into 21 directions. There are some details (table cache, table design, index design, terminal cache, etc.) that are not listed here. For a system, it is a good system to be able to complete the following in the early stage.

　1. Make sure there is enough memory

The database can run efficiently. The most critical factor is that the memory needs to be larger enough to cache data and updates can be completed in memory first. However, different businesses have different memory requirements. It is recommended that the memory should account for 15-25% of the data. For particularly hot data, the memory should basically reach 80% of the database size.

　2. More and faster CPUs are needed

　MySQL 5.6 can utilize 64 cores, but each MySQL query can only run on one CPU, so more CPUs are required, and faster CPUs will be more beneficial. concurrent.

　3. To choose the appropriate operating system

　 According to the official recommendations, Solaris is the most recommended. From actual production, CentOS and REHL are good choices. CentOS is recommended, and REHL version is 6 or later, and of course Oracle Linux Also a good choice. Although Windows has been optimized since MySQL 5.5, it is not recommended to use windows in a high-concurrency environment.

　4. Properly optimize system parameters

　 Change the file handle ulimit -n The default 1024 is too small

　 Number of processes limit ulimit -u Different versions are different

Disable NUMA numctl -interleave=all

5. Choose the appropriate memory allocation algorithm

The default memory allocation is malloc of c. Now there are many optimized memory allocation algorithms: jemalloc and tcmalloc

　The declarative internal storage method is supported from MySQL 5.5 onwards.

　　[mysqld_safe]

　 malloc-lib = tcmalloc

　 Or point directly to the so file

　 [mysqld_safe]

　 malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

　 ​​6. Use faster Storage device SSD or solid-state card

Storage media greatly affects MySQL’s random reading, writing and updating speed. The emergence of a new generation of storage devices, solid-state SSDs and solid-state cards, has also made MySQL shine, and Taobao has made a good fight in the IOE.

　7. Choose a good file system

　Recommend XFS, Ext4. If you are still using ext2, ext3, please upgrade as soon as possible. XFS is recommended, this is also a file system that Linux will support in the future.

　 Highly recommended file system:

　ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)

　If you use SSD or solid-state disk, you need to consider:

　• innodb_page_size = 4K

　• Innodb_flush_neighbors = 0

　9. Choose appropriate IO scheduling

Please use deadline for normal operation. The default is noop

　echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

　10. Choose the appropriate Raid card Cache strategy

　 Please use a powered Raid and enable WriteBack , which is good for accelerating redo log, binary log, and data file.

　11. Disable Query Cache

Query Cache is a bit useless in Innodb. Innodb data itself can be cached in Innodb buffer pool. Query Cache is a result set cache. If you enable Query Cache to update and write, you have to check the query cache instead. Increased writing overhead.

　Query cache is disabled in MySQL 5.6.

　12. Use Thread Pool

　 Now one data corresponds to more than 5 App scenarios, but MySQL has a feature that the performance will decrease as the number of connections increases, so please consider using thread pool for future scenarios with more than 200 connections. This is a great invention.

　13. Properly adjust memory

　13.1 Reduce the memory allocation of connections

　Connections can be cached using thread_cache_size, which is not as powerful as thread pool. The memory allocated by the database on the connection is as follows:

　max_used_connections * (

　read_buffer_size +

　read_rnd_buffer_size +

　join_buffer_size +

　sort_buffer_size +

　binlog_cache_size +

　thread_stack +

　2 * net_buffer_length …

　)

　13.2 To make a larger buffer pool

, allocate 60-80% of the memory to innodb_buffer_pool_size. This should not exceed the data size, and do not allocate more than 80%, otherwise swap will be used.

　14. Choose a reasonable LOG refresh mechanism

　　Redo Logs:

　　 - innodb_flush_log_at_trx_commit = 1 // Most secure

　 - innodb_flush_log_at_trx_commit = 2 // Better performance

　 - innodb_flush_log_at_trx_commit = 0 // Best The emotion

　binlog:

　binlog_sync = 1 requires group commit Supported. If this function is not available, you can consider binlog_sync=0 to obtain better performance.

　Data file:

　innodb_flush_method = O_DIRECT

　15. Please use Innodb table

　More resources can be utilized, and online alter operations have been improved. Currently, non-Chinese full text is also supported, and Memcache API access is also supported. It is currently the best engine for MySQL.

　 If you are still using MyISAM, please consider switching quickly.

　16. Set up a larger Redo log

　 When Percona 5.5 and official MySQL 5.5 competed for performance in the past, one of the winning tips was to allocate more than 4G Redo log, while the official MySQL5.5 redo log cannot exceed 4G. From MySQL 5.6 After that, it can exceed 4G. Usually the total size of the redo log will exceed 500M. You can observe the amount of redo log generated and allocate the amount of redo log greater than one hour.

　17. Optimize disk IO

　Innodb_io_capactiy can be configured with 800 under sas 15000 rpm, and more than 2000 under ssd.

　In MySQL 5.6:

　innodb_lru_scan_depth = innodb_io_capacity / innodb_buffer_pool_instances

　 innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)

　18. Use independent table space

At present, the new features are independent table space support:

Truncate table table space recycling

　Table space transfer

　It is better to optimize the increase of management performance such as fragmentation,

　On the whole, it is useless to use independent table space.

　19. Configure reasonable concurrency

　 innodb_thread_concurrency = Concurrency This parameter is also the most frequently changed parameter in Innodb. Different versions may have changes in different minor versions. General recommendation:

　 When using thread pool:

　 innodb_thread_concurrency = 0 is fine.

　If there is no thread pool:

　5.5 Recommendation: innodb_thread_concurrency =16 – 32

　5.6 Recommend innodb_thread_concurrency = 36

　20. Optimize transaction isolation level

　The default is Repeatable read

It is recommended to use Read committed binlog format Use mixed or Row

Lower isolation level = better performance

21. Pay attention to monitoring

Any environment cannot do without monitoring. If there is no monitoring, it may be a blind man and an elephant. It is recommended to build monitoring with zabbix+mpm.