Shulou(Shulou.com)06/01 Report--

Mysql profile / etc/my.cnf

The core file is the mysqld part:

Mysql tuning:

Mysql tuning can be done in several ways:

1. Architectural layer:

Do the slave library to achieve the separation of read and write

two。 System level:

Increase memory

Do raid0 or raid5 to the disk to increase the read and write speed of the disk

You can remount the disk and add the noatime parameter, which can reduce the iUnix of the disk.

3.mysql tuning itself:

1. If master-slave synchronization is not configured, you can turn off the bin-log function and reduce the disk iUnix.

two。 Add skip-name-resove to my.cnf to avoid slow execution of mysql due to delay in resolving hostname

3. Adjust several key buffer and cache, adjust the basis, mainly according to the state of the database to debug.

4. Application level:

View the slow log, according to the sql statements in the slow log optimizer, such as adding an index

5. Adjust several key buffer and cache

1. Key_buffer_size can first set it according to the memory size of the system, roughly a reference value: 128m / 2G / 256m below 1g / min; 4G / 384M / 8G / 1024M / 16G / 2048m. This value can be checked to see if the key_buffer_size setting is reasonable by checking the status values Key_read_requests and Key_reads. The key_reads / key_read_requests ratio should be as low as possible, at least 1 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state value). Note: the excessive setting of this parameter value will reduce the overall efficiency of the server!

2. When table_open_cache opens a table, it will temporarily put the data in the table into this part of memory, which is generally set to 1024 is enough, its size can be measured by this way: if you find that open_tables is equal to table_cache, and opened_tables is growing, then you need to increase the value of table_cache (the above status values can be obtained using SHOW STATUS LIKE 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

3. The size of the buffer that can be used when sorting sort_buffer_size queries. The allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the total sort buffer size actually allocated is 100x4 = 400MB. Therefore, it is recommended to set it to 4-8m for servers with about 4GB memory.

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

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

6. Myisam_sort_buffer_size this buffer is mainly used to repair the memory used by the sorting index in the table process or the memory used to build the index. Generally, the 4G memory is given to 64m.

7. The size of the query_cache_size MySQL query operation buffer is adjusted by the following ways: SHOW STATUS LIKE 'Qcache%'; if Qcache_lowmem_prunes this parameter records how many queries are removed from the query cache due to insufficient memory. With this value, the user can adjust the cache size appropriately. If the value is very large, it indicates that the buffer is often insufficient and the cache size needs to be increased. Qcache_free_memory: the memory size of the query cache. Through this parameter, you can clearly know whether the current system has enough query memory, whether it is too much, or not. We can adjust it according to the actual situation. In general, a 4G memory setting of 64m is sufficient.

8. Thread_cache_size indicates that the number of threads saved in the cache can be reused. Refer to the following values: 1G-> 8 2G-> 16 3G-> 32 > 3G-> 64

In addition, there are several key parameters:

9. The value of thread_concurrency can be set to 2 times the number of cpu cores.

10. Wait_timeout indicates the idle connection timeout. The default is 28800s. This parameter is used with interactive_timeout, which means that for wait_timeout to take effect, interactive_timeout must be set at the same time. It is recommended that both of them be set to 10.

11. Max_connect_errors is a security-related counter value in MySQL that is responsible for preventing too many failed clients from breaking passwords violently. It has little to do with performance. In order to avoid some mistakes, we usually set it relatively large, such as 10000.

12. The maximum number of connections in max_connections, which should be adjusted according to the number of business requests.

13. Max_user_connections refers to the maximum number of connections that the same account can connect to the mysql service at the same time. Set to 0 means there is no limit. Usually we set it to 100 is enough

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.