Bộ nhớ đệm trong MySQL

7.1 Bộ nhớ đệm phía DB

MySQL sử dụng các thuật toán có khả năng mở rộng cao khi làm việc với các bảng, vì vậy MySQL có thể chạy ngay cả với lượng bộ nhớ nhỏ. Đương nhiên, để có hiệu suất tốt hơn, bạn cần nhiều RAM hơn.

Để xem các cài đặt hiện tại, hãy kết nối với cơ sở dữ liệu

#mysq -u root -p

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

Trước tiên, hãy đảm bảo rằng bộ nhớ đệm được bật. Biến đổi:

• query_cache_typephải BẬT (1) hoặc CẦU (2)
• query_cache_limit- xác định kích thước tối đa của kết quả sẽ vào bộ đệm
• query_cache_sizekhác không. Khi sử dụng DEMAND, chỉ những yêu cầu chứa lệnh mới được lưu vào bộ đệmSQL_CACHE;
• query_cache_min_res_unitkích thước tối thiểu của khối bộ nhớ được phân bổ để lưu trữ kết quả của truy vấn được lưu trong bộ nhớ cache. MySQL không lưu trữ bộ đệm trong một khối bộ nhớ lớn, thay vào đó, nó phân bổ các khối có kích thước tối thiểu query_cache_min_res_unit(= 4KB theo mặc định) theo yêu cầu. Khối cuối cùng như vậy được cắt bớt theo kích thước dữ liệu và bộ nhớ còn lại được giải phóng.

Tác dụng của bộ nhớ đệm là khi máy chủ nhận được yêu cầu, nó sẽ xem liệu hàm băm của yêu cầu có trong bộ đệm hay không. Nếu hàm băm khớp - máy chủ sẽ ngay lập tức trả về kết quả - mà không cần phân tích cú pháp yêu cầu, tối ưu hóa, v.v. tổng phí - kèm theo cơ chế lưu trữ - duyệt bộ đệm, ghi kết quả truy vấn vào bộ đệm, v.v.

Và nếu bạn có nhiều yêu cầu nhỏ trong bộ đệm, thì điều này có thể dẫn đến sự phân mảnh bộ nhớ do có nhiều khối trống. Và điều này lại khiến các mục trong bộ nhớ đệm bị xóa do thiếu bộ nhớ. Trong trường hợp này, việc giảm giá trị của query_cache_min_res_unit. Nếu hầu hết các truy vấn của bạn tạo ra kết quả lớn thì việc tăng cài đặt này có thể cải thiện hiệu suất.

Hãy thử đánh giá hiệu quả. Chúng tôi xem xét cách bộ đếm lần truy cập bộ đệm thay đổi (Qcahe_hits), số lượng yêu cầu được tuyên bố là không hợp lệ do thiếu bộ nhớ (Qcache_lowmem_prunes), tổng số yêu cầu thuộc loại CHỌN (và chỉ chúng được lưu vào bộ đệm):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Trạng thái bộ đệm hiện tại

Để giám sát bộ đệm truy vấn được sử dụng SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blockshiển thị có bao nhiêu khối trống trong bộ đệm (sẽ giảm khi các yêu cầu được lưu trong bộ đệm tăng lên);
• Qcache_total_blocks- số khối bị chiếm đóng;
• Qcache_free_memory- hiển thị bộ nhớ "có sẵn" miễn phí cho bộ nhớ đệm;
• Qcache_hits- số lượng yêu cầu, kết quả được lấy từ bộ đệm mà không thực sự truy cập cơ sở dữ liệu;
• Qcache_inserts- số lượng yêu cầu đã được thêm vào bộ đệm;
• Qcache_lowmem_prunes- số lượng yêu cầu đã bị xóa khỏi bộ đệm do thiếu bộ nhớ;
• Qcache_not_cached- số lượng yêu cầu không được ghi vào bộ đệm do sử dụng các chức năng quản lý thời gian, v.v.;
• Qcache_queries_in_cache- số lượng yêu cầu trong bộ đệm.

Bạn có thể xem tổng số truy vấn CHỌN:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Hiệu suất bộ nhớ đệm

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Giá trị hiện tại có thể được xem như thế này:

SHOW VARIABLES LIKE 'query_cache_size';

Một lần nữa, câu hỏi đặt ra: làm thế nào để chọn một giá trị thích hợpquery_cache_size?

Điều này sẽ giúp ích Qcache_lowmem_prunes. Biến này lưu trữ số lượng yêu cầu đã bị xóa khỏi bộ đệm do phải lưu trữ các yêu cầu mới. Cần phải cố gắng đạt được kích thước bộ đệm như vậy mà Qcache_lowmem_prunesnó sẽ chỉ tăng nhẹ. Để làm điều này, nên so sánh sự khác biệt về giá trị Qcache_lowmem_prunesmỗi giờ và số lượng yêu cầu mà mysql nhận được trong cùng một giờ.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (lưu lượng truy cập trong 10 phút) * 1.2 "

Điều này sẽ lưu trữ các yêu cầu trong 10 phút + cung cấp thêm 20% bộ nhớ để phân mảnh bộ đệm và dự trữ bộ nhớ đệm bổ sung

Bạn có thể tính toán số lượng và khối lượng trung bình của một phản hồi cho một yêu cầu bằng cách sử dụng các biến Bytes_sent tương ứng

Và vì vậy query_cache_sizechúng tôi đã tăng các giá trị, sau đó bạn nên chú ý đến các giá trị Qcache_total_blocks, Qcache_free_blocksvà Qcache_queries_in_cache. MySQL lưu trữ bộ đệm trong các khối. Đối với 1 yêu cầu, cần có 2 khối: một cho chính văn bản yêu cầu, khối thứ hai cho kết quả.

Nếu chúng ta xem xét bảng từ giá trịQcache%

Tổng số khối bộ đệmQcache_total_blocks – 28

Yêu cầu 6 hiện được lưu vào bộ đệm, có nghĩa là 6 * 2 = 12 khối đang bận

khối miễn phí Qcache_free_blocks – 10. Càng nhàn rỗi Qcache_free_blocks, mức độ "phân mảnh" của bộ đệm càng lớn.

Nếu hầu hết các truy vấn có một lượng nhỏ dữ liệu kết quả, thì bạn nên giảm kích thước khối bộ nhớ cache tối thiểu query_cache_min_res_unit, theo mặc định là 4 KB.

Nếu hầu hết các yêu cầu trả về nhiều dữ liệu, thì bạn nên tăng kích thước của khối bộ đệm.

Điều chính là để đạt được một giá trị tối thiểu Qcache_free_blocks.

Nếu bộ đếm Qcache_not_cachedlớn, bạn có thể thử tăng biến query_cache_limit- nó sẽ cho phép bạn tăng giới hạn và lưu vào bộ đệm kết quả của các truy vấn "không phù hợp".

Các biến cấu hình sau chịu trách nhiệm sử dụng bộ đệm truy vấn:

• query_cache_size— kích thước của bộ đệm truy vấn. query_cache_size = 0vô hiệu hóa việc sử dụng bộ đệm;
• query_cache_limit- kích thước của mẫu tối đa được lưu trữ trong bộ đệm;
• query_cache_wlock_invalidate- xác định xem dữ liệu sẽ được lấy từ bộ đệm nếu bảng mà chúng thuộc về bị khóa để đọc.
=

Để bật bộ nhớ đệm truy vấn mysql, chỉ cần thêm các dòng sau vào my.cnf (Phần [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Và khởi động lại dịch vụ.

7.4 Trường hợp bộ đệm không được sử dụng

Không lưu trữ:

• Yêu cầu từSQL_NO_CACHE
• Truy vấn đã chuẩn bị(Prepared statements);
• Truy vấn là truy vấn con của truy vấn bên ngoài;
• Truy vấn bên trong các thủ tục và chức năng được lưu trữ;
• Truy vấn sử dụng hàm:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()với một đối số, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()không có đối số, USER (),UUID ();

• Các truy vấn sử dụng các hàm được lưu trữ, biến người dùng hoặc bảng tham chiếu trong cơ sở dữ liệu hệ thống mysql hoặc INFORMATION_SCHEMA;
• • Yêu cầu dưới các hình thức sau:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Các truy vấn có bảng tạm thời hoặc hoàn toàn không sử dụng bảng;
• Yêu cầu phát sinh cảnh báo (warning);

Bạn có thể chống phân mảnh bộ đệm bằng lệnh:

mysql>flush query cache;

Xóa - lệnh:

mysql>flush query cache;

Điều quan trọng nhất

Không bao giờ làm việc trong sản xuất với cài đặt mặc định. Điều này sẽ dẫn đến hầu hết các tài nguyên của máy chủ không được sử dụng. Điều chỉnh MySQL thích hợp có thể cải thiện hiệu suất cơ sở dữ liệu nhiều lần. Điều này sẽ không chỉ tăng tốc ứng dụng mà còn đối phó với tải nặng.