MySQL Query Cache - Tối ưu hóa MySQL Query Cache

Khi database của bạn lớn/nhiều hoặc lượng truy cập và liên tục , chắc chắn MySQL sẽ chiếm rất nhiều tài nguyên và khiến server load cao. Để hạn giảm load cho server , chúng ta có thể cache lại kết quả của các lần query vào bộ nhớ RAM. Tính năng này được MySQL hỗ trợ từ phiên bản 4.0.1 trở lên, để enable chức năng này chúng ta cần đặt các giá trị sau vào file /etc/my.cnf :
query_cache_type = 1
query_cache_size = 200M
query_cache_limit = 2M

Trong đó:

• query-cache-type : enable or disable tính năng query cache (enable = 1 ; disable = 0)
• query-cache-size : lượng RAM dành cho việc cache query result.
• query-cache-limit: dung lượng tối đa của một query result mà mình cho phép cache.

- Kiểm tra xem query cache đã được kích hoạt hay chưa :
mysql -u root -p
show variables like '%query_cache%';

+——————————+———–+
| Variable_name | Value |
+——————————+———–+
| have_query_cache | YES |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———–+


- Kiểm tra xem query cache đã hoạt động hay chưa :
mysql -u root -p
show status like '%qcache%';

+————————-+———-+
| Variable_name | Value |
+————————-+———-+
| Qcache_free_blocks | 17034 |
| Qcache_free_memory | 36995488 |
| Qcache_hits | 4455579 |
| Qcache_inserts | 702176 |
| Qcache_lowmem_prunes | 87400 |
| Qcache_not_cached | 76260 |
| Qcache_queries_in_cache | 20198 |
| Qcache_total_blocks | 78340 |
+————————-+———-+


- Vài thông số quan trọng cần chú ý :

• Qcache_free_memory : lượng RAM hiện tại còn trống (chưa sử dụng) cho việc cache. Nếu thông số này quá cao bạn có thể giảm query_cache_size xuống , nếu quá thấp thì nâng query_cache_size lên.
• Qcache_hits : số lượng query được lấy trong cache ra sử dụng thay vì phải thực hiện “real query” đến CSDL.
• Qcache_inserts : số lượng query đã được add vào cache.
• Qcache_lowmem_prunes : mỗi khi vùng nhớ dành cho việc cache bị hết (Qcache_free_memory thấp), khi add thêm query vào cache MySQL cần phải giải phóng 1 ít bộ nhớ để chứa query này. Mỗi lần thu hồi vùng nhớ , con số này tăng lên 1. Nếu số này quá lớn chứng tỏ lượng RAM dành cho việc cache(query_cache_size) không đủ và cần phải tăng lên. Nếu số này bằng 0 thì lượng RAM dành cho cache quá lớn , cần giảm lại.
• Qcache_queries_in_cache : số lượng query hiện tại đang được cache.

- File /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
query_cache_type = 1
query_cache_size = 200M
query_cache_limit = 8M

key_buffer_size = 500M
key_buffer = 500M
table_cache = 4000
sort_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
max_connections = 400
tmp_table_size=20M
max_heap_table_size=20M
thread_cache_size = 64