mysql 버퍼링 및 캐시 설정에 대한 자세한 설명

Mysql 관계형 데이터베이스 관리 시스템

MySQL은 스웨덴 MySQL AB 회사에서 개발한 오픈 소스 소규모 관계형 데이터베이스 관리 시스템입니다. MySQL은 인터넷상의 중소규모 웹사이트에서 널리 사용되고 있습니다. 작은 크기, 빠른 속도, 낮은 총 소유 비용, 특히 오픈 소스의 특성으로 인해 많은 중소 웹 사이트에서는 웹 사이트 총 소유 비용을 줄이기 위해 MySQL을 웹 사이트 데이터베이스로 선택합니다.

이 글에서는 주로 mysql 최적화 과정에서 더 중요한 두 가지 매개변수 버퍼링과 캐시 설정에 대해 설명하겠습니다.

MySQL 조정 가능 설정은 전체 mysqld 프로세스 또는 개별 클라이언트 세션에 적용될 수 있습니다.

서버측 설정

각 테이블은 디스크의 파일로 표시될 수 있으며 먼저 열어서 읽어야 합니다. 파일에서 데이터를 읽는 프로세스 속도를 높이기 위해 mysqld는 /etc/mysqld.conf의 table_cache에 지정된 최대 수까지 이러한 열린 파일을 캐시합니다. 목록 4는 테이블 열기와 관련된 활동을 표시하는 방법을 보여줍니다.

목록 4. 열린 테이블에 대한 표시 활동

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

목록 4에서는 현재 5,000개의 테이블이 열려 있고 캐시에 사용 가능한 파일 설명자가 없기 때문에 195개의 테이블을 열어야 함을 보여줍니다. 이전에 통계가 삭제되었기 때문에 오픈 테이블 5,000개 중 오픈 레코드는 195개만 있을 수 있습니다. SHOW STATUS 명령을 다시 실행하여 Opened_tables가 급격히 증가하면 캐시 적중률이 부족하다는 의미입니다. Open_tables가 table_cache 설정보다 훨씬 작으면 값이 너무 큰 것입니다(그러나 성장할 공간이 있다는 것은 결코 나쁜 것이 아닙니다). 예를 들어 table_cache =5000을 사용하여 테이블 캐시를 조정합니다.

테이블 캐시와 마찬가지로 스레드를 위한 캐시도 있습니다. mysqld는 연결을 수신할 때 필요에 따라 스레드를 생성합니다. 연결이 빠르게 변경되는 사용량이 많은 서버에서는 나중에 사용하기 위해 스레드를 캐싱하면 초기 연결 속도를 높일 수 있습니다.

목록 5에서는 충분한 스레드가 캐시되었는지 확인하는 방법을 보여줍니다.

목록 5. 스레드 사용 통계 표시

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)

여기에서 중요한 값은 Threads_created입니다. 이 값은 mysqld가 새 스레드를 생성해야 할 때마다 증가됩니다. 연속적인 SHOW STATUS 명령을 실행할 때 이 숫자가 급격히 증가하면 스레드 캐시를 늘려야 합니다. 예를 들어 my.cnf에서 thread_cache = 40을 사용하여 이를 달성할 수 있습니다.

키워드 버퍼는 MyISAM 테이블의 인덱스 블록을 보유합니다. 이상적으로는 이러한 블록에 대한 요청이 디스크가 아닌 메모리에서 이루어져야 합니다. Listing 6은 디스크에서 읽은 블록 수와 메모리에서 읽은 블록 수를 확인하는 방법을 보여줍니다.

목록 6. 키워드 효율성 결정

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는 디스크에 도달하는 요청 수를 나타내고, Key_read_requests는 총 요청 수를 나타냅니다. 디스크에 도달한 읽기 요청 수를 총 읽기 요청 수로 나눈 값이 누락 비율입니다. 이 경우 요청 1,000개당 메모리에서 약 0.6개의 누락이 발생합니다. 디스크 적중 횟수가 요청 1,000개당 1회를 초과하는 경우 키워드 버퍼 증가를 고려해야 합니다. 예를 들어 key_buffer =384M은 버퍼를 384MB로 설정합니다.

임시 테이블은 추가 처리(예: GROUPBY 절) 전에 데이터를 임시 테이블에 저장해야 하는 고급 쿼리에서 사용할 수 있으며 임시 테이블은 메모리에 생성됩니다. 그러나 임시 테이블이 너무 커지면 디스크에 기록해야 합니다. Listing 7은 임시 테이블 생성과 관련된 통계를 제공한다.

목록 7. 임시 테이블 사용량 결정

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가 늘어나고 디스크 기반 테이블도 Created_tmp_disk_tables가 늘어납니다. 관련된 쿼리에 따라 달라지므로 이 비율에 대한 엄격한 규칙은 없습니다. 시간 경과에 따른 Created_tmp_disk_tables를 관찰하면 생성된 디스크 테이블의 비율이 표시되고 설정의 효율성을 확인할 수 있습니다. tmp_table_size와 max_heap_table_size는 모두 임시 테이블의 최대 크기를 제어하므로 my.cnf에 두 값이 모두 설정되어 있는지 확인하세요.

세션별 설정

다음 설정은 각 세션마다 다릅니다. 존재할 수 있는 연결 수를 곱하면 이 옵션이 많은 양의 메모리를 나타내기 때문에 이 숫자를 설정할 때 매우 주의하십시오! 코드를 통해 세션 내에서 이러한 숫자를 수정하거나 모든 세션에 대해 my.cnf에서 이러한 설정을 수정할 수 있습니다.

MySQL은 정렬을 해야 하는 경우 디스크에서 데이터를 읽을 때 데이터 행을 저장하기 위해 정렬 버퍼를 할당합니다. 정렬할 데이터의 양이 너무 많으면 디스크의 임시 파일에 데이터를 저장한 후 다시 정렬해야 합니다. sort_merge_passes 상태 변수가 큰 경우 이는 디스크 활동을 나타냅니다. Listing 8은 정렬 관련 상태 카운터 정보를 제공합니다.

목록 8. 정렬 통계 표시

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中文网（m.sbmmt.com）！