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.