MySQL service optimization parameter settings reference

For details about MySQL service optimization parameter settings, refer to l general class: key_buffer_size. Meaning: the buffer size used for index blocks. Add indexes that can be better processed (for all read and multi-Rewrite ). Impact: the impact on the MyISAM table is not great. MyISAM uses the system cache to store data. Therefore, the machine memory that uses the MyISAM Table heavily will soon be exhausted. However, if you set this value to too large (for example, 50% greater than the total memory), the system will convert it to a page and become extremely slow. MySQL depends on the operating system to execute the file system cache for Data Reading. Therefore, you must leave some space for the file system cache. Suggestion: first set it to 25% of the memory to observe performance changes. Table_open_cache indicates the number of tables opened for all threads. Impact: increase this value to increase the number of file descriptors required by mysqld. This avoids overhead caused by frequent data table opening. Opening a table may be costly because MyISAM will mark the file header of the MYI file as being used, so it is better to do this operation in the memory ., Because each thread needs to open the table, the larger the number of connections, the larger the value. Suggestion: if we have more than 300 tables, it's about 2048. Thread_cache_size indicates the number of reusable threads in the cache. Impact: this parameter sets the thread cache, And the thread creation and destruction overhead may be large, because the connection/disconnection of each thread is required. If an application has a large number of skip concurrent connections and a large number of threads, it must increase its value. It does not need to create a new thread in common operations. Suggestion: usually set to at least 16. Query_cache_size indicates the buffer size of MySQL query results. Impact: This is useful if the application has a large number of reads without application-level caching. However, do not set too large because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Suggestion: usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. Under a certain load pressure, if the cache hit rate is too low, enable it. If the hit rate is good, you can reduce it. For 2 GB memory, increase from 16 MB to doubled until the hit rate is stable. Query_cache_limit indicates the upper limit of the result set of a single SQL statement. The default value is 4 kb. Impact: When the result set returned by an SQL statement is greater than this limit, it will not be cached by MySQL. Suggestion: Set it to 1 MB. Query_cache_min_res_unit: The minimum memory size of each data set stored in the cache. The default value is 4 kb. Impact: if it is too small, MySQL will frequently access the memory block to obtain information. If it is too large, memory will be wasted. Suggestion: if the result set returned by the SQL statement is small, you can reduce the parameter to avoid Memory waste. If most of the result sets are larger than 4 kb, increase the parameter. Back_log meaning: Maximum number of connection requests that can be stored in the MySQL connection request waiting queue. The default value is 50. Impact: if the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. Suggestion: We recommend that you set the value to an integer smaller than 512 in Linux. Sort_buffer_size indicates that a buffer of this size is allocated to each thread that needs to be sorted. Influence: add this value to accelerate the order by or group by operation. However, the allocation 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 × sort _ buffer_size. Suggestion: generally set it to 2 m to observe the changes and then adjust it. Read_buffer_size indicates the buffer size that can be used by sequential query operations. Impact: Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection. Suggestion: generally set it to 2 MB and then observe the changes. Read_rnd_buffer_size indicates the buffer size that can be used by the random query operation. Impact: Each thread is exclusive. Suggestion: generally set it to 2 MB and then observe the changes. Query_cache_type indicates the Query Buffer type. Impact: if it is set to 1, the buffer is used. If it is set to 2, the buffer is used only when SQL _CACHE is used. For buffering, data is not real-time and has a certain latency. However, it is not cost-effective to execute queries with low real-time requirements multiple times in a short period of time, and cache is required at this time. In addition, the cache is case-sensitive and case-sensitive. If the case is not the same as that of the space, it is considered a different SQL statement and will not be used in the cache. Although no Query Buffer is set, it may cause performance loss, but some SQL statements need to query data in real time or are not frequently used (it may be executed once or twice a day ), in this case, you need to disable the buffer. You can use the temporarily closed method SELECT SQL _NO_CACHE. Suggestion: generally set to 1. L MyISAM class: the buffer required for re-sorting when the myisam_sort_buffer_size MyISAM table changes. Generally, 64 MB is enough. L InnoDB class: innodb_buffer_pool_size has a great impact on InnoDB efficiency. Because InnoDB will cache as much data and indexes as possible in the Buffer zone, this is similar to the Buffer Pool of Oracle: If InnoDB is used only, you can increase this parameter by about 70% of the memory. Of course, if the data volume does not increase rapidly and is not very large, this parameter should not be too large or a waste of space. Innodb_additional_pool_size does not have a significant impact on database performance. At least there will be no impact on machines with enough memory. Innodb_log_file_size is very important when there is a high write load, especially when the dataset is large. The higher the value, the better, but it may increase data recovery. I set it to 128 M. Innodb_log_buffer_size is set by default to provide performance when writing load and short transaction processing are moderate. However, if there are a large number of update operations or a large load, it is necessary to slowly increase the value of this parameter. But do not set too large, it will waste memory. It refresh every second, so you do not need to set the memory space more than 1 s. 16 m is enough. The innodb_flush_logs_at_trx_commit parameter is important to InnoDB. If it is not set properly, it will be 1000 times slower than MyISAM! The default value is 1, which means that each update transaction will be committed to the disk, which consumes a lot of resources. The speed difference between the hard disk and the memory is an obvious order of magnitude. Setting 0 is the fastest, but it is not safe. It is all in the cache, and all power loss occurs. It is not good to set it to 1. It is unnecessary to write the hard disk every time. It is better to set it to 2. If the log is not flushed to the disk, it is only refreshed to the operating system cache. Then write the cache every second. Compared with SQL statements of about 4 K lines per second in the current database, the performance can be improved a lot.