Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "mysql common configuration interpretation". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

key_buffer_size

Set this variable to allocate all specified space for the key buffer at once. However, the operating system does not actually allocate memory immediately, but only when it is used. For example, setting the key buffer size to 1 GB does not mean that the server immediately allocates 1 GB of memory.

mysql allows multiple key caches to be created. If this variable is set to 0 for non-default key caches,mysql discards cached indexes in that key cache in favor of the default key cache, and deletes the key cache when there are no more references. Setting this variable for a non-existent key cache will create a new key cache. Setting a non-zero value to an existing key cache causes the contents of that key cache to be flushed. This blocks all attempts to access the key cache until the flush is complete.

table_cache_size

Set this variable to the number of cached tables. This variable will not take effect immediately, but will be delayed until the next thread opens the table. MySQL checks the value of this variable when a thread opens the table. If the value is greater than the number of tables in the cache, the thread can place the newly opened table in the cache, and if the value is less than the number of tables in the cache,mysql will remove the infrequently used table from the cache.

thread_cache_size

Thread cache setting, this variable does not take effect immediately-it will take effect the next time the connection is closed. When a connection is closed,mysql checks to see if there is room in the cache for the thread. If there is space, it caches the thread for reuse on the next connection; if there is no space, it destroys the thread without caching. In this scenario, the number of threads in the cache and the memory used by the thread cache do not decrease immediately.mysql adds threads to the cache only when a connection is closed, and removes threads from the cache when a new connection is created.

query_cache_size

MySQL allocates and initializes this memory at startup, which is a query cache. If you modify this variable, mysql immediately deletes all query cache data from the cache. Reallocate the cache to the specified size. And reinitialize memory, which takes a lot of time. The server cannot provide service until initialization is complete,mysql is a gradual cache cleanup, not a one-time cleanup.

read_buffer_size

is the MySQL read buffer size. A request for a sequential scan of a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If sequential scans of tables are requested very frequently and you think frequent scans are too slow, you can improve performance by increasing the value of this variable and the memory buffer size.

read_rnd_buffer_size

This parameter is used after a sort query to ensure that the data is retrieved in a sequential manner. If you have a lot of order by queries, increasing this value can improve performance. "To be honest, this parameter is very similar to read_buffer_size, read_buffer_size is only used for MyISAM engines, and I subconsciously think read_rnd_buffer_size is also only used for MyISAM engines.

It's not like that. read_rnd_buffer_size is used not only for MyISAM engines, but for all engines. It is used after a query to optimize reading real data. It works like this:

After sorting, the row pointer is obtained, which exists in the form of key-value. For MyISAM, it is the offset of the data. For innodb, it is the primary key or stores the full amount of data for re-query (beneficial for small pieces of data).

Assuming that sorted data uses row pointers and that fields in rows can be converted to fixed sizes (except BLOB/TEXT fields), MySQL can optimize data reading using read_rnd_buffer_size.

Because sorted data exists in key-value form, using these row pointers to read data will read data in the physical order of pointer data, largely randomly. MySQL reads these rows of pointer data from sort_buffer, and then stores them in read_rnd_buffer after sorting through pointers, and then reads the data through pointers, basically reading them sequentially.

read_rnd_buffer_size is a very important parameter, especially in the following scenarios:

* sort_buffer stores row pointers rather than the data to be queried.

* The fields of the query contain Blob/Text fields.

* There are a lot of rows after sort (limit 10 doesn't help you, because MySQL gets rows through pointers)

If you take a few fields (less than max_length_for_sort_data), the row data will be stored entirely in sort buffer, so read_rnd_buffer_size will not be needed. If the fields you are looking for are long (they probably contain Text/Blob fields), longer than max_length_for_sort_data, read_rnd_buffer_size will come in handy.

sort_buffer_size

Is a connection-level parameter that allocates memory for settings at one time for each connection the first time it needs to use the buffer. It is not the bigger the better. Because it is a connection-level parameter, too large a setting + high concurrency may exhaust system memory resources.

The content of "mysql commonly used configuration interpretation" is introduced here. Thank you for reading it. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.