Mysql buffer and cache settings

Mysql buffer and cache settings

MySQL Adjustable settings can be applied to the entire mysqld process, or to a single client session.

Server Settings

Each table can be represented as a file on the disk. It must be opened first and then read. To speed up the process of reading data from files, mysqld caches these open files. The maximum number of these files is specified by table_cache in/etc/mysqld. conf. Listing 4 shows how to display activities related to opening a table.

Listing 4. Show the activity of opening a table

mysql> SHOW STATUS LIKE 'open%tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables  | 5000 || Opened_tables | 195  |+---------------+-------+2 rows in set (0.00 sec)

Listing 4 shows that 5,000 tables are currently opened, and 195 tables need to be opened, because no available file descriptor is available in the cache (because the statistics have been cleared before, therefore, there may be 5,000 open records in 195 open tables ). If Opened_tables rapidly increases with the re-running of the show status Command, the cache hit rate is insufficient. If Open_tables is much smaller than table_cache, it indicates that the value is too large (but it is not a bad thing to increase the space ). For example, you can use table_cache = 5000 to adjust the table cache.

Similar to the table cache, a thread also has a cache. Mysqld will generate a thread as needed when receiving connections. On a busy server with fast connection changes, caching threads for later use can speed up the initial connection.

Listing 5 shows how to determine whether enough threads are cached.

Listing 5. display thread usage statistics

mysql> SHOW STATUS LIKE 'threads%';+-------------------+--------+| Variable_name   | Value |+-------------------+--------+| Threads_cached  | 27   || Threads_connected | 15   || Threads_created  | 838610 || Threads_running  | 3   |+-------------------+--------+4 rows in set (0.00 sec) 

The important value here is Threads_created. This value increases every time mysqld needs to create a new thread. If this number increases rapidly when the show status command is executed continuously, you should try to increase the thread cache. For example, thread_cache = 40 can be used in my. cnf to achieve this goal.

The keyword buffer stores the index block of the MyISAM table. Ideally, requests for these blocks should come from memory, not from disks. Listing 6 shows how to determine how many blocks are read from the disk and how many are read from the memory.

Listing 6. Determining keyword Efficiency

mysql> show status like '%key_read%';+-------------------+-----------+| Variable_name   | Value   |+-------------------+-----------+| Key_read_requests | 163554268 || Key_reads     | 98247   |+-------------------+-----------+2 rows in set (0.00 sec)

Key_reads indicates the number of requests that hit the disk, and Key_read_requests indicates the total number. The number of read requests that hit the disk divided by the total number of read requests is the ratio of not hit-in this example, each 1,000 requests, about 0.6 do not hit the memory. If the number of disks hit by each 1,000 requests exceeds 1, you should consider increasing the keyword buffer. For example, if key_buffer = 384 M, the buffer is set to 384 MB.

Temporary tables can be used in more advanced queries. data must be saved to temporary tables before further processing (for example, the GROUPBY statement). Ideally, create a temporary table in the memory. However, if the temporary table is too large, it needs to be written to the disk. Listing 7 shows the statistics related to creating a temporary table.

Listing 7. determine the use of temporary tables

mysql> SHOW STATUS LIKE 'created_tmp%';+-------------------------+-------+| Variable_name      | Value |+-------------------------+-------+| Created_tmp_disk_tables | 30660 || Created_tmp_files    | 2   || Created_tmp_tables   | 32912 |+-------------------------+-------+3 rows in set (0.00 sec) 

Created_tmp_tables is increased each time a temporary table is used, and Created_tmp_disk_tables is increased for a disk-based table. There are no strict rules for this ratio, because it depends on the involved query. After a long period of observation, Created_tmp_disk_tables will display the ratio of the created disk table. You can determine the efficiency of the settings. Both tmp_table_size and max_heap_table_size can control the maximum size of the temporary table. Therefore, make sure that both values are set in my. cnf.

Settings for each session

The following settings apply to each session. Exercise caution when setting these numbers because these options indicate a large amount of memory when multiplied by the number of possible connections! You can use the code to modify the numbers in a session, or modify these settings for all sessions in my. cnf.

When MySQL needs to sort data, it will allocate a sort buffer when reading data from the disk to store the data rows. If the data to be sorted is too large, the data must be saved to the temporary files on the disk and sorted again. If the sort_merge_passes status variable is large, it indicates disk activity. Listing 8 lists some status counters related to sorting.

Listing 8. Displaying sorting statistics

mysql> SHOW STATUS LIKE "sort%";+-------------------+---------+| Variable_name   | Value  |+-------------------+---------+| Sort_merge_passes | 1    || Sort_range    | 79192  || Sort_rows     | 2066532 || Sort_scan     | 44006  |+-------------------+---------+4 rows in set (0.00 sec) 

If sort_merge_passes is large, you need to pay attention to sort_buffer_size. For example, sort_buffer_size = 4 M sets the sorting buffer to 4 MB.

MySQL also allocates some memory to read the table. Ideally, the Index provides enough information to read only the required rows, but sometimes the query (poor design or data nature) needs to read a large amount of data in the table. To understand this behavior, you need to know how many SELECT statements are run, and the number of times the data in the next row in the table needs to be read (instead of directly accessed through the index ). The command to implement this function is shown in listing 9.

Listing 9. Determining the table scan rate

mysql> SHOW STATUS LIKE "com_select";+---------------+--------+| Variable_name | Value |+---------------+--------+| Com_select  | 318243 |+---------------+--------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE "handler_read_rnd_next";+-----------------------+-----------+| Variable_name     | Value   |+-----------------------+-----------+| Handler_read_rnd_next | 165959471 |+-----------------------+-----------+1 row in set (0.00 sec)

Handler_read_rnd_next/Com_select returns the table scan rate-in this example, It is 521: 1. If the value exceeds 4000, check read_buffer_size, for example, read_buffer_size = 4 M. If this number exceeds 8 Mb, you should discuss with the developer about tuning these queries!

View database cache configurations

Mysql> show variables like '% query_cache % '; + ---------- + --- + | Variable_name | Value | + ---------- + --- + | have_query_cache | YES |-query whether the cache is available | query_cache_limit | 1048576 |-maximum Value of the cache query result | query_cache_min_res_unit | 4096 | query_cache_size | 599040 |-query cache size | query_cache_type | ON |-blocked or supported query cache | query_cache_wlock_invalidate | OFF | + ---------- + --- +

Configuration method:

Find the following content in the MYSQL configuration file my. ini or my. cnf:

# Query cache is used to cache SELECT results and later returnthem# without actual executing the same query once again. Having thequery# cache enabled may result in significant speed improvements, ifyour# have a lot of identical queries and rarely changing tables.See the# "Qcache_lowmem_prunes" status variable to check if the currentvalue# is high enough for your load.# Note: In case your tables change very often or if your queriesare# textually different every time, the query cache may result ina# slowdown instead of a performance improvement.query_cache_size=0

The above information is the default configuration. The comment means that the MYSQL query cache is used to cache the select query results and the next time the same query request is received, results are directly returned instead of performing actual query processing. The query cache can speed up the query and optimize the query performance, provided that you have a large number of identical or similar queries, this function is rarely used to change the data in the table. You can use the value of the Qcache_lowmem_prunes variable to check whether the current value meets the load of your current system. Note: If the tables you query are updated frequently and there are few identical queries, it is best not to use the query cache.

Specific configuration method:

1. Setquery_cache_sizeThe specific size depends on the actual situation of the query, but it is best to set it to a multiple of 1024, the reference value is 32 M.

2. Add a row:query_cache_type=1

The query_cache_type parameter is used to control the cache type. Note that this value cannot be set randomly and must be set to a number. optional items and descriptions are as follows:

If it is set to 0, you can say that your cache is useless at all, which is equivalent to disabling it. In this case, does the system allocate the size set by query_cache_size? Is this problem to be tested?

If it is set to 1, all results will be cached unless your select statement uses SQL _NO_CACHE to disable the query cache.

If set to 2, the query to be cached is only cached in the select statement using SQL _CACHE.

OK. Some files after configuration are as follows:

query_cache_size=128Mquery_cache_type=1

Save the file, restart the MYSQL service, and check whether the MYSQL service is enabled by the following query:

mysql> show variables like '%query_cache%';+——————————+———–+| Variable_name      |Value  |+——————————+———–+| have_query_cache     |YES   || query_cache_limit     |1048576  || query_cache_min_res_unit  |4096   || query_cache_size     | 134217728|| query_cache_type     |ON    || query_cache_wlock_invalidate | OFF   |+——————————+———–+6 rows in set (0.00 sec)

It mainly depends on whether the values of query_cache_size and query_cache_type are consistent with those we set:

Here, the query_cache_size value is 134217728, and we set 128 M, which is actually the same, but the unit is different. You can convert it to 134217728 = 128*1024*1024.

Query_cache_type is set to 1 and displayed as ON, as mentioned earlier.

In short, the above display indicates that the settings are correct, but whether the query can be cached in the actual query still needs to be manually tested. We can use show statuslike '% Qcache % '; now we have enabled the query cache function. Before executing the query, let's take a look at the values of related parameters:

mysql> show status like '%Qcache%';+————————-+———–+| Variable_name    |Value  |+————————-+———–+| Qcache_free_blocks   |1    || Qcache_free_memory   | 134208800|| Qcache_hits     |0    |