Summary of database MySQL tuning practices and mysql tuning practices

Summary of database MySQL tuning practices and mysql tuning practices

MySQL databases are widely used, with excellent stability and security. They have been verified by countless companies. It is far from enough to install and use MySQL. MySQL requires constant parameter adjustment or optimization settings to maximize its role. The content below is a summary of my work experience and work notes. It would be better if I could help comrades who need it. MySQL optimization can be done from the following aspects:

I. Architecture

1. Master Slave replication.
2. read/write splitting.

Ii. System Level

1. Increase the memory.
2. SSD is used for the hard drive.
3. Add RAID 0 or RAID 5 to the disk to increase the read/write speed.
4. You can Remount the disk and add the noatime parameter to reduce disk I/O.

Iii. MySQL Optimization

1. If master-slave synchronization is not configured, you can disable the bin-log function to reduce disk I/O.
2. Add skip-name-resolve to my. cnf to avoid slow SQL Execution of M due to host name resolution delay.
3. Adjust several key buffers and caches. The basis for adjustment is mainly based on the database status. For more information about tuning, seeV..
4. select an appropriate storage engine based on specific scenarios.

Iv. Application Level

View slow query logs and query the SQL statements in the slow query log optimization program, such as adding an index

5. Adjust the key buffer and cache

1. key_buffer_size

First, you can set it according to the system memory size. The approximate reference value is 128 M for memory below 1 GB; 2G/256 M; 4G/384 M; 8G/1024 M; 16G/2048 M. You can check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better (the above status value can be used)Show status like 'key _ read %'). Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!

2. table_open_cache

When opening a table, the data in the table is temporarily put in this part of the memory, which is usually set to 1024. The size of the table can be measured in this way: if you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding STATUS values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.

3. sort_buffer_size

The buffer size that can be used to query sorting. The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 4 = 400 MB. Therefore, we recommend that you set the size of a 4 GB server to 4-8 Mb.

4. read_buffer_size

The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

5. join_buffer_size

The buffer size that can be used by the Joint query operation. The same as sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

6. myisam_sort_buffer_size

This buffer zone is mainly used to fix the memory used for sorting indexes during the table process or the memory size used for sorting indexes during index creation. Generally, the memory size is 4 GB to 64 MB.

7. query_cache_size

Adjust the buffer size of the MySQL query operation by using the following methods: show status like 'qcache % '; If Qcache_lowmem_prunes, the number of queries with this parameter record is removed from the query cache due to insufficient memory. With this value, you can adjust the cache size appropriately. If this value is very large, it indicates that the buffer is often insufficient. You need to increase the cache size Qcache_free_memory: query the cache memory size, with this parameter, you can clearly see whether the current system's query memory is sufficient. If it is too much or not, we can make adjustments based on the actual situation. Generally, the 4 GB memory is set to 64 MB.

8. thread_cache_size

Indicates the number of threads that can be reused in the cache. For details, refer to the following values: 1G-> 8; 2G-> 16; 3G-> 32; 3G-> 64.
In addition, there are several key parameters

9. thread_concurrency

Set this value to 2 times the number of CPU cores.

10. wait_timeout

It indicates the idle connection timeout time. The default value is 28800 s. this parameter is used with interactive_timeout. That is to say, to make wait_timeout take effect, you must set interactive_timeout at the same time, we recommend that both of them be set to 10.

11. max_connect_errors

Is a security-related counter value in MySQL. It is used to prevent clients from too many failed attempts to prevent brute-force password cracking. It has little to do with performance. To avoid some errors, we generally set a large value, such as 10000.

12. max_connections

The maximum number of connections. Set the value to 500 Based on the Service Request volume.

13. max_user_connections

The maximum number of connections that a single account can connect to the mysql service at the same time. 0 indicates no restriction. Usually we set it to 100 enough.

----- To be updated -----