MySQL Query Cache - Tạo bộ nhớ đệm cache cho MySQL

Đối với những website hiện nay thì database hay còn gọi là cơ sở dữ liệu (CSDL) là một phần quan trọng không thể thiếu. Các website với nhiều nội dung (bài viết, sản phẩm,…) thì database sẽ ngày càng “phình to” dẫn đến việc truy xuất trở nên chậm chạp đáng kể, tiêu tốn nhiều tài nguyên CPU/RAM của máy chủ và nguy hiểm hơn là dẫn đến việc “crash” database.

https://i.imgur.com/IL0lGiw.jpg

Có rất nhiều cách để tối ưu database chạy trên nền tảng MySQL và một trong số đó là bật tính năng “Query Caching” (MySQL query cache) được tích hợp sẵn trong MySQL từ phiên bản 4.0.1. Query Caching cho phép lưu lại những câu truy vấn SQL trên RAM để khi có các yêu cầu xử lý truy vấn tương tự vào các lần tiếp theo, máy chủ sẽ không dùng tài nguyên để xử lý nữa mà lấy thông tin đã lưu trữ trên RAM (cache) trước đó trả kết quả ngay.
Bây giờ mình sẽ hướng dẫn các bạn cấu hình Query Caching (MySQL query cache) trên MariaDB (tương tự đối với MySQL)
Đầu tiên các bạn truy cập vào dòng lệnh MySQL với quyền root. Sau đó kiểm tra tính năng Query Caching đã có sẵn trong MySQL hay chưa bằng câu lệnh sau:

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
– Nếu kết quả là “YES” thì phiên bản MySQL bạn đang sử dụng đã hỗ trợ sẵn tính năng này. Tuy nhiên mặc định nó sẽ không được bật lên.
– Nếu kết quả là “NO” thì bạn cần cài lại phiên bản MySQL phù hợp hơn.

Tiếp theo các bạn sẽ gán “query_cache_size” chính là dung lượng lưu trữ các truy vấn đã được cache. Lưu ý dung lượng này nằm trên RAM không phải trên Disk các bạn nhé. Vì vậy tùy thuộc vào cấu hình máy chủ mà các bạn cân đối cho phù hợp. Trong bài viết này mình sẽ gán 64 MB cho cache bằng câu truy vấn sau:
MariaDB [(none)]> SET GLOBAL query_cache_size = 62914560;
Query OK, 0 rows affected (0.00 sec)
Tiếp theo các bạn bật tính năng Query Caching bằng câu truy vấn sau:
MariaDB [(none)]> SET GLOBAL query_cache_type = 1;
Query OK, 0 rows affected (0.00 sec)

query_cache_type có ba tùy chọn sử dụng:
– query_cache_type = 0 là tắt cache
– query_cache_type = 1 hoặc query_cache_type = ON là bật cache
– query_cache_type = DEMAND chỉ cache những truy vấn nào có tùy chọn SQL_CACHE. Dành cho các trường hợp các bạn chỉ muốn cache một vài truy vấn cụ thể nào đó và không cache toàn bộ truy vấn còn lại.

Đến đây thì đã hoàn thành rồi đấy, các bạn kiểm tra lại kết quả bằng truy vấn sau:
MariaDB [(none)]> SHOW STATUS LIKE 'Qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 61763936 |
| Qcache_hits | 1660 |
| Qcache_inserts | 400 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 11 |
| Qcache_queries_in_cache | 130 |
| Qcache_total_blocks | 289 |
+-------------------------+----------+
Trong đó:
– Qcache_free_memory: là dung lượng còn trống được gán bởi tham số query_cache_size phía trên.
– Qcache_inserts: là số lượng query đã thực thi xong và được đưa vào cache.
– Qcache_hits: là số lượng query đã được lấy từ cache.
– Qcache_lowmem_prunes: là số lượng các query không thể cache do thiếu RAM.
Lưu ý:
– Các truy vấn có kèm tùy chọn “SQL_NO_CACHE” thì mặc nhiên câu truy vấn đó sẽ không bị cache.
– Các truy vấn INSERT / UPDATE / DELETE sẽ được thực thi vào trong database mà không bị cache.

– Nếu khởi động lại MySQL các cấu hình trên sẽ mất tác dụng. Để áp dụng vĩnh viễn các bạn cần phải khai báo vào file config của MySQL tại đường dẫn sau:
[root@server ~]# nano /etc/my.cnf
Thêm nội dung sau:
[mysqld]
query_cache_size = 500M
query_cache_type = 1
Một lưu ý nhỏ là các bạn phải thêm nội dung bên dưới mục [mysqld] nhé, nếu thêm nội dung vào sai vị trí sẽ làm MySQL không thể khởi động được.

Sau đó thực hiện khởi động lại MySQL để áp dụng cấu hình mới
[root@server ~]# systemctl restart mysqld

Như vậy có thể thấy rằng khi truy cập vào một bài viết có cùng truy vấn thì Query Caching sẽ giúp giảm tải đi đáng kể tài nguyên máy chủ.
Các truy vấn được cache lại cũng sẽ trả kết quả nhanh hơn so với truy vấn không được cache. Các bạn hay thử tối ưu và trải nghiệm nhé.

- Kiểm tra các thông số cache sau khi cài đặt (thực hiện lệnh trong cửa sổ mysql, mariadb):
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache%';
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer%';
MariaDB [(none)]> SHOW VARIABLES LIKE '%cache%';
MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';
- cài đặt tổng cache + buffer = 1/2 tổng số ram của vps, server sao cho Available Memory còn lại dao động khoảng 20-30% tổng số ram của vps, server trở lên để phục vụ các tác vụ khác