21 MySQL Optimization Suggestions (Summary of Experience)

Today, a friend of mine asked me how to optimize MySQL, and I sorted out 1 of them according to my thinking, which can be roughly divided into 21 directions. There are a few more details (table cache, table design, index design, program side caching, etc.), but for a system, it would be a good system to do the following initially.

Make sure you have enough memory

Database can run efficiently, the most important factors need more memory, can cache the data, update can also be completed in memory first. However, different businesses have different intensity of memory requirements. 1. It is recommended that memory should account for 15-25% of the data.

2. Need more and faster CPU

MySQL 5.6 can make use of 64 cores, while MySQL can only run on 1 CPU per query, so more CPU is required, and faster CPU is better for concurrency.

3. Choose the right operating system

According to the official suggestion, the most recommended one is Solaris. From the perspective of actual production, both CentOS and REHL are good choices. CentOS and REHL versions after 6 are recommended, while Oracle Linux is also a good choice. Although Windows has been optimized since MySQL 5.5, windows is not recommended for high-concurrency environments.

4. Reasonably optimize the parameters of the system

Change the file handle ulimit? n default 1024 is too small
ulimit?? u?? Different versions are not the same
NUMA numctl? interleave=all

5. Select the appropriate memory allocation algorithm

The default memory allocation is malloc for c and now there are many optimized memory allocation algorithms:

jemalloc and tcmalloc

Support for declaring in-store methods from MySQL 5.5.

[mysqld_safe]

malloc-lib = tcmalloc

Or go directly to the so file

[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

6. Use a faster storage device, ssd, or a solid state card

10 points of storage media affects the random read and write update speed of MySQL. The appearance of the new generation of solid-state ssd and solid-state card also makes MySQL shine, and it is also a good battle that Taobao has done in IOE.

7. Choose a good file system

XFS, Ext4 are recommended. If you are still using ext2,ext3, please upgrade as soon as possible. XFS is recommended as it will support one file system for the next 1 day.

File system strongly recommended: XFS

8. Optimize the parameters for mounting the file system

Mount XFS parameters:

(rw, noatime,nodiratime,nobarrier)

Mount ext4 parameters:

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

If using SSD or solid-state disks consider:

• innodb_page_size = 4K
• Innodb_flush_neighbors = 0

9. Select the appropriate IO schedule

deadline is noop by default

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

10. Select the appropriate Raid card Cache policy

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

11. Query Cache is banned

QueryCache is a bit awkward in Innodb. The data of Innodb itself can be cached in Innodb buffer pool. Query Cache belongs to the result set cache.

Querycache is banned in MySQL5.6.

Use Thread Pool

At present, there are more than 5 App scenarios for each data, but MySQL has a feature that degrades as the number of connections increases, so for scenarios with more than 200 connections, please consider using thread pool. This is a great invention.

Adjust your memory properly

13.1 Reduce memory allocation for connections
Connections can be cached with thread_cache_size, and the view is less powerful than thread pool. The memory allocated by the database in 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 the larger buffer pool

Allocate 60-80% of the memory to innodb_buffer_pool_size. Do not allocate more than 80% of the memory to swap.

14. Select LOG refresh mechanism reasonably
Redo Logs:

 �  innodb_flush_log_at_trx_commit  = 1 //  The most secure 
 �  innodb_flush_log_at_trx_commit  = 2 //  A good performance 
 �  innodb_flush_log_at_trx_commit  = 0 //  The best emotion 

binlog :

binlog_sync = 1 requires group commit support. If not, consider binlog_sync=0 for better performance.

Data file:

innodb_flush_method = O_DIRECT

15. Please use the Innodb table

More resources are available and the online alter operation has improved. Currently, non-Chinese full text and Memcache API are also supported. It is also one of the MySQL's best engines.

If you are still in MyISAM consider a quick conversion.

16. Set the larger Redo log

Previously, when Percona 5.5 competed with the official MySQL 5.5, the winning Tips was allocated more than 4ES235en, while the official ES237en5.5 redo could not exceed 4G. From MySQL 5.6, it can exceed 4G, usually adding up to 500M. You can observe the production of redo log and allocate the amount of Redo log greater than 1 hour.

17. Optimize disk IO

innodb_io_capactiy just configure 800 under sas 15000 RPM and above 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 separate table Spaces

For now, the new features are standalone tablespace support:
truncate table table space recovery
Table space transfer
Better to optimize fragmentation and other management performance increases,
As a whole, using separate table Spaces is useless.

19. Configure reasonable concurrency

innodb_thread_concurrency = concurrency This parameter is also the one that changes most frequently in Innodb. For different versions, there may be changes for different minor versions. 1 General recommendation:

In the case of thread pool:
innodb_thread_concurrency = 0.
In the absence of thread pool:
5.5 innodb_thread_concurrency =16 ° 32
5.6 Recommend innodb_thread_concurrency = 36

Optimize the transaction isolation level

The default is Repeatable read
It is recommended to use Read committed binlog or Row
Lower isolation level = better performance

21. Focus on monitoring

Any environment can not do without monitoring, if there is no monitoring, it is possible to fall into the blind man and the elephant. zabbix+mpm build monitoring is recommended.