Detailed explanation of mysql buffering and cache settings

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

This article mainly explains to you the two important parameters buffering and cache settings in the mysql optimization process. I hope you will like it

MySQL Tunable settings can be applied to the entire mysqld process or to individual client sessions.

Server-side settings

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

Listing 4. Display activity for open tables

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 there are currently 5,000 tables open and 195 tables need to be opened because there are no available file descriptors in the cache. (Since the statistics have been cleared previously, there may be only 195 open records in the 5,000 open tables). If Opened_tables increases rapidly with re-running the SHOW STATUS command, it indicates that the cache hit rate is insufficient. If Open_tables is much smaller than the table_cache setting, the value is too large (but having room to grow is never a bad thing). For example, use table_cache =5000 to adjust the table's cache.

Similar to the table cache, there is also a cache for threads. mysqld generates threads as needed when receiving connections. On a busy server where connections change quickly, 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 will be incremented every time mysqld needs to create a new thread. If this number increases rapidly when executing successive SHOW STATUS commands, you should try to increase the thread cache. For example, you can use thread_cache = 40 in my.cnf to achieve this.

The keyword buffer saves the index block of the MyISAM table. Ideally, requests for these blocks should come from memory rather than from disk. Listing 6 shows how to determine how many blocks were read from disk and how many blocks were read from memory.

List 6. Determine 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 represents the number of requests hitting the disk, and Key_read_requests is the total number. The number of read requests that hit disk divided by the total number of read requests is the miss ratio—in this case, for every 1,000 requests, about 0.6 misses in memory. If the number of disk hits exceeds 1 per 1,000 requests, you should consider increasing the keyword buffer. For example, key_buffer =384M will set the buffer to 384MB.

Temporary tables can be used in more advanced queries, where data must be saved to a temporary table before further processing (such as a GROUPBY clause); ideally, create the temporary table in memory. But if the temporary table becomes too large, it needs to be written to disk. Listing 7 gives statistics related to temporary table creation.

List 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)

Every time a temporary table is used, Created_tmp_tables will increase; disk-based tables will also increase Created_tmp_disk_tables. There are no strict rules for this ratio, as it depends on the query involved. Watching Created_tmp_disk_tables over time will show you the ratio of disk tables created and you can determine the efficiency of your setup. Both tmp_table_size and max_heap_table_size control the maximum size of the temporary table, so make sure both values ​​are set in my.cnf.

Settings for each session

The following settings are for each session. Be very careful when setting these numbers because when multiplied by the number of connections that may exist, these options represent a large amount of memory! You can modify these numbers within a session through code, or modify these settings in my.cnf for all sessions.

When MySQL must sort, it allocates a sort buffer to store the data rows when reading data from disk. If the data to be sorted is too large, the data must be saved to a temporary file on disk and sorted again. If the sort_merge_passes status variable is large, this indicates disk activity. Listing 8 gives some sorting-related status counter information.

List 8. Display 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)

如果 sort_merge_passes 很大，就表示需要注意sort_buffer_size。例如，sort_buffer_size = 4M 将排序缓冲区设置为 4MB。

MySQL也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询（设计不佳或数据本性使然）需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT语句，以及需要读取表中的下一行数据的次数（而不是通过索引直接访问）。实现这种功能的命令如清单 9 所示。

清单 9. 确定表扫描比率

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 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过4000，就应该查看 read_buffer_size，例如read_buffer_size = 4M。如果这个数字超过了8M，就应该与开发人员讨论一下对这些查询进行调优了！

查看数据库缓存配置情况

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 | 599040 | –查询缓存的大小
| query_cache_type | ON | –阻止或是支持查询缓存
| query_cache_wlock_invalidate | OFF |
+——————————+———+

配置方法：

在MYSQL的配置文件my.ini或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

以上信息是默认配置，其注释意思是说，MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。

具体配置方法：

1． 将query_cache_size设置为具体的大小，具体大小是多少取决于查询的实际情况，但最好设置为1024的倍数，参考值32M。

2． 增加一行：query_cache_type=1

query_cache_type参数用于控制缓存的类型，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。但是这种情况下query_cache_size设置的大小系统是否要为其分配呢，这个问题有待于测试？

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

OK，配置完后的部分文件如下：

query_cache_size=128M

query_cache_type=1

保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：

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)

主要看query_cache_size和query_cache_type的值是否跟我们设的一致：

这里query_cache_size的值是134217728，我们设置的是128M，实际是一样的，只是单位不同，可以自己换算下：134217728 = 128*1024*1024。

query_cache_type设置为1，显示为ON，这个前面已经说过了。

总之，看到上边的显示表示设置正确，但是在实际的查询中是否能够缓存查询，还需要手动测试下，我们可以通过show statuslike '%Qcache%';语句来测试，现在我们开启了查询缓存功能，在执行查询前，我们先看看相关参数的值：

mysql> show status like '%Qcache%';

+————————-+———–+

| Variable_name    |Value  |

+————————-+———–+

| Qcache_free_blocks   |1    |

| Qcache_free_memory   | 134208800|

| Qcache_hits     |0    |

以上就是mysql缓冲和缓存设置详解的内容，更多相关内容请关注PHP中文网（www.php.cn）！