MySQL Tuning basics

MySQL tuning can be done in several ways:

1. Architecture Layer:
Do from the library, to achieve read and write separation;

2. System level:
Increased memory;
RAID0 or RAID5 the disk to increase the read and write speed of the disk;
You can re-mount the disk and add the Noatime parameter, which reduces the I/O to the disk;

3. mysql itself tuning:
(1) If master-slave synchronization is not configured, the Bin-log function can be turned off to reduce disk I/O
(2) Add skip-name-resolve to my.cnf to avoid slow MySQL execution due to delayed parsing of host names
(3) Adjust several key buffer and cache. The basis of adjustment, mainly according to the state of the database debugging. You can refer to 5 for tuning.

4. Application level:
View the slow query log, based on the SQL statements in the slow query log optimizer, such as increasing the index

5. Adjust several key buffer and cache

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 values key_read_requests and key_reads to see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server down!


2) Table_open_cache open a table, will temporarily put the data inside the table in this part of memory, generally set to 1024 is enough, its 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 be obtained using show status like ' Open%tables '). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may 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! If there are 100 connections, the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, the recommended setting for a server that has around 4GB is 4-8m.


4) read_buffer_size The buffer size that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!


5) join_buffer_size The buffer size that can be used by the Federated query operation, as with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!


6) Myisam_sort_buffer_size This buffer is primarily used to fix the memory used by the sort index during the table process or the memory size used to sort the index when indexing, 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 buffering is often insufficient, and the cache size needs to be increased; Qcache_free_memory: Query cache memory size, through this parameter can be very clear know the current system query memory is sufficient, is more, or not enough, we can make adjustments according to the actual situation. In general, 4G memory setting 64M is sufficient.


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-> 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 used with Interactive_timeout, that is, for wait_timeout to take effect, you must set the Interactive_ Timeout, it is recommended that both of them are set to 10


One) max_connect_errors is a security-related counter value in MySQL that is responsible for blocking excessive attempts by clients that fail to prevent brute-force password violations. is not much related to performance. In order to avoid some errors, we generally set a larger, for example, 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 to indicate no limit. Usually we set it to 100 enough

Original: http://xxr007.blog.51cto.com/10602872/1879583

MySQL Tuning basics