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 also some details (table cache, table design, index design, terminal cache, etc. ) will not be listed now. For a system, it is also a good system to be able to complete the following in the early stage.
The most critical factor for the database to run efficiently is sufficient memory. If it is large, data can be cached, 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.
MySQL 5.6 can utilize 64 cores, but each MySQL query can only run on one CPU, so it requires more CPUs. A faster CPU would be better for concurrency.
According to official recommendations, Solaris is the most recommended. From actual production, CentOS and REHL are good choices. It is recommended to use CentOS and REHL versions. For 6 and later, of course Oracle Linux is 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.
Change the file handle ulimit -n default 1024 Too small
Process number limit ulimit -u Different versions are different
Disable NUMA numctl -interleave=all
The default memory allocation is c's malloc. Now there are many optimized memory allocation algorithms:
jemalloc and tcmalloc
From MySQL 5.5 onwards, the declared internal storage method is supported.
[mysqld_safe]
malloc-lib = tcmalloc
Or point directly to the so file
[mysqld_safe]
malloc- lib=/usr/local/lib/libtcmalloc_minimal.so
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.
We recommend XFS and Ext4. If you are still using ext2 or 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: XFS
,nobarrier)
Mount ext4 parameters:
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. Select the appropriate IO schedule
echo dealine >/sys/block/{DEV-NAME}/queue/scheduler
10. Select the appropriate Raid card Cache strategy
11. Disable Query Cache
Query cache is disabled in MySQL 5.6.
12. Using Thread Pool
13. Properly adjust memory
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 …
)
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.
Redo Logs:
- innodb_flush_log_at_trx_commit = 1 // The safest
- innodb_flush_log_at_trx_commit = 2 // Better performance
- innodb_flush_log_at_trx_commit = 0 // Best performance
Binlog:
Binlog_sync = 1 Requires group commit support. If you don’t have this function, you can consider binlog_sync=0 to get better results. Best performance.
Data file:
innodb_flush_method = O_DIRECT
More resources can be utilized, and the online alter operation has 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 in MyISAM, please consider switching quickly.
In the past, when Percona 5.5 and the official MySQL 5.5 competed for performance, the winning tip was to allocate more than 4G Redo log, and the official MySQL5.5 redo The log cannot exceed 4G. Starting from MySQL 5.6, 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.
Innodb_io_capactiy Just configure 800 under sas 15000 rpm, and configure 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)
At present, the new features are independent table space support:
Truncate table table space recycling
Table space transmission
Better to optimize fragmentation When management performance increases,
Overall, it is useless to use independent table spaces.
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
Default is Repeatable read
It is recommended to use Read committed Binlog format uses mixed or Row
Lower isolation level = better performance
Any environment is inseparable from monitoring. If there is no monitoring, it may be a blind man trying to figure out the elephant. It is recommended to build monitoring with zabbix+mpm.
The above is the detailed content of 21 tips for optimizing MySQL. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
