MySQL parameter tuning

We must tune it after we install MySQL, and if the configuration file does not do well, MySQL may occupy a large

Memory, and the performance of the server is greatly reduced, then how do we tune, tuning the common options and methods are

, let's see it together.

Here I refer to a mysql5.6 version of the online configuration file/etc/my.cnf template, detailed configuration can refer to the pen

Another blog post, not to repeat here, let's take a look at the options noted during tuning:

1) Key_buffer_size first can be based on the system's memory size set it, probably a reference value: 1G or less memory settings

128m;2g/256m; 4g/384m;8g/1024m;16g/2048m. This value can be checked by checking the status value Key_read_requests and

Key_reads, you can know whether the key_buffer_size setting is reasonable. Proportional key_reads/key_read_requests should be as

Low, at least 1:100,1:1000 better (the above status values can be obtained using show status like ' key_read% '). Attention:

This parameter value setting is too large to be the overall efficiency of the server down!


2) Table_open_cache open a table, the data in the table will be temporarily placed in this part of memory, generally set to 1024

That's enough, it's size we can measure by this way: if you find Open_tables equals Table_cache, and

Opened_tables is growing, you need to increase the value of Table_cache (the above status values can use show status

Like ' open%tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, you can

Can cause insufficient file descriptors, resulting in performance instability or connection failures.

3) Sort_buffer_size the size of the buffer that can be used when the query is sorted, the allocated memory for that parameter is per-connection exclusive!

100 connections, the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, for the internal presence of about 4GB of the service

The recommended setting for 4-8m.


4) read_buffer_size The buffer size that can be used by the read query operation. As with sort_buffer_size, this parameter corresponds to the sub-

With memory is also exclusive for every connection!


5) join_buffer_size The buffer size that can be used by the Federated query operation, as with Sort_buffer_size, which corresponds to the

Allocating memory is also exclusive for every connection!


6) Myisam_sort_buffer_size This buffer is primarily used to repair the memory used by the sort index during the table process or when indexing is established

The size of the memory used to sort the index, generally 4G memory to 64M.


7) query_cache_size MySQL query operation buffer size, adjusted by the following practices: SHOW STATUS like

' Qcache% '; If qcache_lowmem_prunes this parameter records how many queries are removed from the query cache because of insufficient memory.

With this value, the user can adjust the cache size appropriately. If the value is very large, it indicates that there is often insufficient buffering to

Increase the cache size; Qcache_free_memory: The memory size of the query cache, this parameter can be very clear to know the current system

Query memory is enough, is more, or not enough, we can make adjustments according to the actual situation. 4G memory settings in general 64M

That's enough.

8) thread_cache_size indicates that the number of threads stored in the cache can be re-used, referring to the following values:1g-> 8 2g-> 16

3g-> 3g-> 64
In addition, there are a few more key parameters:

9) Thread_concurrency This value is set to twice times the number of CPU cores


Wait_timeout indicates idle connection timeout time, default is 28800s, this parameter is with Interactive_timeout

Use, that is, to make wait_timeout effective, you must set the Interactive_timeout at the same time, suggest that both of them are set

Reset to 10


One) max_connect_errors is a security-related counter value in MySQL that is responsible for preventing too many clients that are trying to fail to

Prevent brute force password-breaking situations. is not much related to performance. In order to avoid some errors, we generally set a larger, such as

10000


Max_connections Maximum number of connections, according to the amount of business request to adjust appropriately, set 500 enough


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 indicates no limit

System. Usually we set it to 100 enough

This article is from "The Miracle Teenager" blog, please be sure to keep this source http://raffaelexr.blog.51cto.com/8555551/1746866

MySQL parameter tuning