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 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.

1. Make sure there is enough memory

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.

2. Need more and faster CPUs

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.

　3. To choose the appropriate operating system

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.

　4. Properly optimize system parameters

Change the file handle ulimit -n default 1024 Too small

Process number limit ulimit -u Different versions are different

Disable NUMA numctl -interleave=all

5. Choose the appropriate memory allocation algorithm

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

6. Use a 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

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

8. Optimize the parameters for mounting the file system

,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

If normal, please use deadline. The default is noop

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

10. Select the appropriate Raid card Cache strategy

Please use a powered Raid and enable it WriteBack 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 belongs to the result set cache. If Query Cache is turned on, update and write Checking the query cache will increase the writing overhead.

Query cache is disabled in MySQL 5.6.

　12. Using 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 for those with more than 200 connections Please consider using thread pool in future scenarios. 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 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. Reasonable selection of LOG refresh mechanism

　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

15. Please use Innodb table

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.

16. Set up a larger Redo log

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.

17. Optimize disk IO

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)

18. Use independent table space

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.

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 transactions Isolation level

Default is Repeatable read

It is recommended to use Read committed Binlog format uses mixed or Row

Lower isolation level = better performance

　21. Pay attention to monitoring

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!