Side effects of setting variables in MySQL

Dynamic Setting of variables may cause unexpected side effects, such as refreshing dirty blocks from the buffer. Be careful with the settings that can be modified online, because they may cause the database to do a lot of work.

Sometimes, you can infer the role of a variable by name. For example, the function of max_heap_table_size is just like this: it specifies the maximum allowable size of the implicit memory temporary table. However, naming conventions are not exactly the same, so you cannot always guess the effect of a variable by name.

Let's take a look at some common variables and the effects of dynamic modification:

Key_buffer_size

This variable can be set to allocate all specified space to the key buffer (also called the key cache) at a time. However, the operating system does not actually allocate memory immediately, but is actually allocated only when used. For example, setting the size of the key buffer to 1 GB does not mean that the server allocates 1 GB of memory immediately.

MySQL allows you to create multiple key caches. If the non-default key cache variable is set to 0, MySQL will discard the indexes in the key cache and use the default key cache instead, this key cache will be deleted when no reference is available. Set this variable for a nonexistent key cache and a new key cache will be created. Setting a non-zero value for an existing key cache will refresh the content of the key cache. This will block all operations that attempt to access the key cache. The refresh operation is complete.

Table_cache_size

Setting this variable does not take effect immediately-it will be delayed until the next time a thread opens the table. When a thread opens a table, MySQL checks the value of this variable. If the number of tables in the cache is greater than that in the cache, the thread can put the latest opened tables into the cache. If the value is smaller than that in the cache, MySQL will delete infrequently used tables from the cache.

Thread_cache_size

Setting this variable does not take effect immediately-it will take effect when the next connection is closed. When a connection is closed, MySQL checks whether there is space in the cache to cache the thread. If there is space, the thread will be cached for reuse by the next connection. If there is no space, the thread will be destroyed and no longer cached. In this scenario, the number of threads in the cache and the memory used by the thread cache will not be immediately reduced; it will be reduced only after a new connection deletes a thread in the cache and is used. MySQL adds a thread to the cache only when the connection is closed, and deletes the thread from the cache only when a new connection is created .)

Query_cache_size

MySQL allocates and initializes this memory at one time at startup. If you modify this variable (even if it is set to the same value as the current one), MySQL will immediately delete all cached queries, reassign the cached data to the specified size, and reinitialize the memory. This may take a long time, and the server cannot provide services until Initialization is completed, because MySQL clears cache queries one by one, not all deleted at a time.

Read_buffer_size

MySQL allocates memory for the cache only when the query is required, and allocates all the memory of the specified size of this parameter at a time.

Read_rnd_buffer_size

MySQL allocates memory for the cache only when it needs to be used for query, and only allocates the required memory size instead of all the specified size.

Sort_buffer_size

MySQL allocates memory for the cache only when sorting is required for queries. Then, once sorting is required, MySQL immediately allocates all the memory size specified by this parameter, regardless of whether the sorting requires such a large memory size.

This is not a complete parameter list. The purpose here is to simply tell you what expected behavior will happen when you modify some common variables.

For connection-level settings, do not add their values at the global level unless you confirm that this is correct. Some caches allocate all the memory of the specified size at a time, regardless of whether the cache is actually large or not. Therefore, a large global setting may result in a large amount of memory waste. A better way is to increase these values separately at the connection level when the query is required.

The most common example is sort_buffer_size. This parameter controls the cache size of the sorting operation. It should be configured in the configuration file to be smaller, and when some queries need to be sorted, increase it in the connection. After memory allocation, MySQL performs initialization.

In addition, even for very small sorting operations, the sorting cache will allocate all the memory size. Therefore, if you set the parameter to exceed the average sorting requirement, it will waste a lot of memory, additional memory allocation overhead is added. Many readers think that memory allocation is a simple operation and may be surprised to hear the cost of memory allocation. You don't need to go into many technical details to understand why memory allocation is expensive. memory allocation includes address space allocation, which is relatively expensive. Especially in Linux, memory allocation uses multiple overhead policies based on the size.

In general, setting a large sort cache may have a very high cost, so do not increase the size of the sort cache unless it is determined that it must be so large.

If a query must use a larger sorting cache for better execution, you can add the sort_buffer_size value before execution, and restore the DEFAULT value after execution.

Eg:

SET @@session.sort_buffer_size := <value>;#Execute the querySET @@session.sort_buffer_size := DEFAULT;

Similar code can be encapsulated in functions for ease of use. Other variables that can be set at a single connection level include read_buffer_size, read_rnd_buffer_size, tmp_table_size, and myisam_sort_buffer_size.

Note: This article is from <High Performance MySQL>.

Personal perception: it is very important to understand the impact of variables that can be dynamically changed. If you are not careful, the load may soar, And the CPU usage may soar or even crash.

TIPS: I used to dynamically modify the value of query_cache_szie in the online database. Originally, the server load was 40-50. At that time, I did not consider that much, so I changed it directly, the load soared to more than 200 in an instant ,,,

Good things are shared, so they will be more beautiful !!!

This article is from the Focus on the database Blog, please be sure to keep this source http://lgdvsehome.blog.51cto.com/3360656/1246000