21 Mysql Optimization Recommendations (experience summary) _mysql

Today, a friend asked me how to optimize MySQL, I follow the thinking of a bit, probably rough can be divided into 21 directions. There are some details (table cache, table design, index design, program-side caching, etc.) first, for a system, the initial can be done below is also a good system.

1. To ensure that there is enough memory

The database can run efficiently, the most important factors need more memory, can cache the data, the update can be completed in memory first. But different business to the memory needs of the intensity is not the same, a recommended memory to account for the proportion of the data 15-25%, particularly hot data, the basic memory to reach the database of 80% size.

2. Need more and faster CPUs

MySQL 5.6 can take advantage of 64 cores, and MySQL each query can only run on one CPU, so require more CPU, faster CPU will be more conducive to concurrency.

3. To select the appropriate operating system

In the official recommendation estimates most recommended Solaris, from the actual production to see CentOS, Rehl is a good choice, recommend the use of CentOS, Rehl version of 6, 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. Reasonable optimization of the parameters of the system

Change file handle ulimit–n default 1024 too small
Number of processes limit ulimit–u different versions
To ban Numa Numctl–interleave=all.

5. Select the appropriate memory allocation algorithm

The default memory allocation is C malloc now there are many optimized memory allocation algorithms:

Jemalloc and Tcmalloc

The declarative storage method is supported after MySQL 5.5.

Copy Code code as follows:

[Mysqld_safe]
Malloc-lib = Tcmalloc

Or just point to so file

Copy Code code as follows:

[Mysqld_safe]

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

6. Use a faster storage device SSD or solid state card

Storage media is very affecting MySQL random read, write update speed. New generation of storage devices solid-state SSD and solid-state card appearance also let MySQL shine, is also Taobao in to IoE in a beautiful battle.

7. Choose a good file system

Recommended XFS, EXT4, if you are still using EXT2,EXT3 students please raise the level as soon as possible. Recommended XFS, this is also the next time Linux will support a file system.

File systems Highly recommended: XFS

8. Optimize the parameters of the mounted file system

To mount the XFS parameter:

Copy Code code as follows:

(RW, Noatime,nodiratime,nobarrier)

Mount Ext4 Parameters:

Copy Code code as follows:

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 the appropriate IO scheduling

Normally please use deadline default is NoOp

Copy Code code as follows:

Echo Dealine >/sys/block/{dev-name}/queue/scheduler

10. Select the appropriate RAID card cache policy

Use live raid and enable writeback, which is good for speeding up redo log, binary log, and data file.

11. Disable Query Cache

Query cache in InnoDB a bit of chicken, INNODB data itself can be cached in the InnoDB buffer pool, query cache belongs to the result set caching, if you open query cache update write to check query Cache instead increases the cost of writing.

In MySQL 5.6, query cache is banned.

12. Use Thread Pool

Now one of the data corresponds to more than 5 app scenarios, but MySQL has a feature that decreases performance as the connection increases, so consider using thread pool for later scenarios where the connection is over 200. This is a great invention.

13. Reasonable Adjustment of memory

13.1 Reduce the memory allocation of the connection
the connection can use the Thread_cache_size cache, the view check belongs to compare is inferior to the thread pool to force. The database is allocated on the attached memory as follows:

Copy Code code 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

To give the 60-80% memory to the innodb_buffer_pool_size. This does not exceed the size of the data, and also do not allocate more than 80% or you will use swap.

14. Reasonable selection log refresh mechanism
Redo Logs:

Copy Code code as follows:

–innodb_flush_log_at_trx_commit = 1//safest

–innodb_flush_log_at_trx_commit = 2/Good performance

–innodb_flush_log_at_trx_commit = 0/Best Love

Binlog:

Binlog_sync = 1 requires group commit support, and if this feature is not available, binlog_sync=0 can be considered for better performance.

Data files:

Copy Code code as follows:

Innodb_flush_method = O_direct

15. Please use the InnoDB table

More resources are available to improve the online alter operation. Full text in non-Chinese is also currently supported, while Memcache API access is supported. MySQL is currently the best engine.

If you are still in MyISAM please consider a quick conversion.

16. Set the larger redo log

Previously Percona 5.5 and the official MySQL 5.5 competition performance, one of the winning tips is to allocate more than 4G of redo log, while the official MySQL5.5 redo log can not exceed 4G. From MySQL 5.6 can exceed 4G, usually built redo log added up to more than 500M. Can be checked redo log production, allocate redo log is more than one hour of the amount can be.

17. Optimize the IO of the disk

Innodb_io_capactiy in the SAS 15000 RPM configuration 800 on it, under the SSD configuration of more than 2000.

In MySQL 5.6:

Copy Code code as follows:

Innodb_lru_scan_depth = innodb_io_capacity/innodb_buffer_pool_instances

Innodb_io_capacity_max = min (2 * innodb_io_capacity)

18. Use of independent table space

For now, the new features are independent tablespace support:
TRUNCATE TABLE Space Recycling
Table Space Transport
Better to optimize the growth of management performance such as fragmentation,
In general, it is useless to use independent tablespaces.

19. Configure a reasonable concurrency

Innodb_thread_concurrency = concurrency is one of the most frequent parameters in InnoDB. Different versions, there may be different iterations also have changes. General recommendation:

In the case of using thread pool:
Innodb_thread_concurrency = 0 is OK.
If you do not have a thread pool:
5.5 Recommended: Innodb_thread_concurrency =16–32
5.6 Recommended innodb_thread_concurrency = 36

20. Optimizing Transaction Isolation Levels

The default is repeatable read
Read Committed Binlog format is recommended for use with mixed or row
Lower isolation level = better performance

21. Focus on monitoring

Environment can not be separated from monitoring, if less monitoring, it is possible to fall into the elephant. Recommended ZABBIX+MPM build monitoring.