GALAXY TECH
Chuyên gia Công nghệ
Thứ tư, 12/06/2019 | 00:00 GMT+7

Cách tối ưu hóa MySQL với Bộ đệm truy vấn trên Ubuntu 18.04

Bộ nhớ đệm truy vấn là một tính năng MySQL nổi bật giúp tăng tốc độ truy xuất dữ liệu từ database . Nó đạt được điều này bằng cách lưu trữ các SELECT MySQL SELECT cùng với bản ghi đã truy xuất được đặt trong bộ nhớ, sau đó nếu khách hàng yêu cầu các truy vấn giống hệt nhau, nó có thể phục vụ dữ liệu nhanh hơn mà không cần thực hiện lại lệnh từ database .

So với dữ liệu đọc từ đĩa, dữ liệu được lưu trong bộ nhớ đệm từ RAM (Bộ nhớ truy cập ngẫu nhiên) có thời gian truy cập ngắn hơn, giúp giảm độ trễ và cải thiện hoạt động nhập / xuất (I / O). Ví dụ: đối với một trang web WordPress hoặc một cổng thương mại điện tử có số lần gọi đọc cao và thay đổi dữ liệu không thường xuyên, cache truy vấn có thể tăng đáng kể hiệu suất của server database và làm cho nó có thể mở rộng hơn.

Trong hướng dẫn này, trước tiên bạn sẽ cấu hình MySQL mà không có cache truy vấn và chạy các truy vấn để xem chúng được thực thi nhanh như thế nào. Sau đó, bạn sẽ cài đặt cache truy vấn và kiểm tra server MySQL của bạn khi nó được bật để cho thấy sự khác biệt về hiệu suất.

Lưu ý: Mặc dù cache truy vấn không được chấp nhận kể từ MySQL 5.7.20 và bị loại bỏ trong MySQL 8.0, nó vẫn là một công cụ mạnh mẽ nếu bạn đang sử dụng các version được hỗ trợ của MySQL. Tuy nhiên, nếu bạn đang sử dụng các version MySQL mới hơn, bạn có thể áp dụng các công cụ của bên thứ ba thay thế như ProxySQL để tối ưu hóa hiệu suất trên database MySQL của bạn .

Yêu cầu

Trước khi bắt đầu, bạn cần những thứ sau:

Bước 1 - Kiểm tra tính khả dụng của bộ nhớ đệm truy vấn

Trước khi cài đặt cache truy vấn, bạn sẽ kiểm tra xem version MySQL của bạn có hỗ trợ tính năng này hay không. Đầu tiên, ssh vào server Ubuntu 18.04 của bạn:

  • ssh user_name@your_server_ip

Sau đó, chạy lệnh sau để đăng nhập vào server MySQL với quyền là user root :

  • sudo mysql -u root -p

Nhập password root server MySQL của bạn khi được yêu cầu và sau đó nhấn ENTER để tiếp tục.

Sử dụng lệnh sau để kiểm tra xem cache truy vấn có được hỗ trợ không:

  • show variables like 'have_query_cache';

Bạn sẽ nhận được kết quả tương tự như sau:

Output
+------------------+-------+ | Variable_name    | Value | +------------------+-------+ | have_query_cache | YES   | +------------------+-------+ 1 row in set (0.01 sec)

Bạn có thể thấy giá trị của have_query_cache được đặt thành YES và điều này nghĩa là cache truy vấn được hỗ trợ. Nếu bạn nhận được kết quả cho thấy rằng version của bạn không hỗ trợ bộ nhớ cache truy vấn, vui lòng xem ghi chú trong phần Giới thiệu để biết thêm thông tin.

Đến đây bạn đã kiểm tra và xác nhận version MySQL của bạn hỗ trợ cache truy vấn, bạn sẽ chuyển sang kiểm tra các biến kiểm soát tính năng này trên server database của bạn .

Bước 2 - Kiểm tra các biến bộ nhớ đệm truy vấn mặc định

Trong MySQL, một số biến kiểm soát bộ nhớ cache truy vấn. Trong bước này, bạn sẽ kiểm tra các giá trị mặc định đi kèm với MySQL và hiểu những gì mỗi biến kiểm soát.

Bạn có thể kiểm tra các biến này bằng lệnh sau:

  • show variables like 'query_cache_%' ;

Bạn sẽ thấy các biến được liệt kê trong kết quả của bạn :

Output
+------------------------------+----------+ | Variable_name                | Value    | +------------------------------+----------+ | query_cache_limit            | 1048576  | | query_cache_min_res_unit     | 4096     | | query_cache_size             | 16777216 | | query_cache_type             | OFF      | | query_cache_wlock_invalidate | OFF      | +------------------------------+----------+ 5 rows in set (0.00 sec)

Giá trị query_cache_limit xác định kích thước tối đa của các kết quả truy vấn riêng lẻ có thể được lưu vào bộ nhớ đệm. Giá trị mặc định là 1,048,576 byte và giá trị này tương đương với 1MB.

MySQL không xử lý dữ liệu được lưu trong bộ nhớ cache trong một đoạn lớn; thay vào đó nó được xử lý trong các khối. Lượng bộ nhớ tối thiểu được cấp cho mỗi khối được xác định bởi biến query_cache_min_res_unit . Giá trị mặc định là 4096 byte hoặc 4KB.

query_cache_size kiểm soát tổng dung lượng bộ nhớ được cấp cho cache truy vấn. Nếu giá trị được đặt thành 0, điều đó nghĩa là cache truy vấn bị vô hiệu hóa. Trong hầu hết các trường hợp, giá trị mặc định có thể được đặt thành 16,777,216 (khoảng 16MB). Ngoài ra, hãy nhớ rằng query_cache_size cần ít nhất 40KB để phân bổ cấu trúc của nó. Giá trị được phân bổ ở đây được căn chỉnh với khối 1024 byte gần nhất. Điều này nghĩa là giá trị được báo cáo có thể hơi khác so với giá trị bạn đã đặt.

MySQL xác định các truy vấn vào bộ nhớ cache bằng cách kiểm tra biến query_cache_type . Đặt giá trị này thành 0 hoặc OFF sẽ ngăn chặn việc lưu vào bộ nhớ đệm hoặc truy xuất các truy vấn đã lưu trong bộ nhớ cache. Bạn cũng có thể đặt nó thành 1 để bật bộ nhớ đệm cho tất cả các truy vấn ngoại trừ những truy vấn bắt đầu bằng SELECT SQL_NO_CACHE . Giá trị 2 cho MySQL biết chỉ truy vấn cache bắt đầu bằng SELECT SQL_CACHE .

Biến query_cache_wlock_invalidate kiểm soát liệu MySQL có nên truy xuất kết quả từ cache hay không nếu bảng được sử dụng trên truy vấn bị khóa. Giá trị mặc định là OFF .

Lưu ý: Biến query_cache_wlock_invalidate không được chấp nhận kể từ version MySQL 5.7.20. Do đó, bạn có thể không thấy điều này trong kết quả của bạn tùy thuộc vào version MySQL bạn đang sử dụng.

Sau khi xem xét các biến hệ thống kiểm soát cache truy vấn MySQL, bây giờ bạn sẽ kiểm tra cách MySQL hoạt động mà không cần bật tính năng này trước.

Bước 3 - Kiểm tra server MySQL của bạn mà không cần bộ nhớ đệm truy vấn

Mục tiêu của hướng dẫn này là tối ưu hóa server MySQL của bạn bằng cách sử dụng tính năng cache truy vấn. Để thấy sự khác biệt về tốc độ, bạn sẽ chạy các truy vấn và xem hiệu suất của chúng trước và sau khi triển khai tính năng này.

Trong bước này, bạn sẽ tạo một database mẫu và chèn một số dữ liệu để xem MySQL hoạt động như thế nào mà không có cache truy vấn.

Trong khi vẫn đăng nhập vào server MySQL của bạn, hãy tạo database và đặt tên là sample_db bằng cách chạy lệnh sau:

  • Create database sample_db; 
Output
Query OK, 1 row affected (0.00 sec)

Sau đó chuyển sang database :

  • Use sample_db; 
Output
Database changed

Tạo một bảng có hai trường ( customer_id và customer_name ) và đặt tên là customers :

  • Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB; 
Output
Query OK, 0 rows affected (0.01 sec)

Sau đó, chạy các lệnh sau để chèn một số dữ liệu mẫu:

  • Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE'); 
  • Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE'); 
  • Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE'); 
  • Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE'); 
  • Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE'); 
  • Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE'); 
  • Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH'); 
  • Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS'); 
  • Insert into customers(customer_id, customer_name) values ('9', 'JANE POE'); 
  • Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE'); 
Output
Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...

Bước tiếp theo là khởi động MySQL profiler , là một dịch vụ phân tích để theo dõi hiệu suất của các truy vấn MySQL. Để bật cấu hình cho phiên hiện tại, hãy chạy lệnh sau, đặt nó thành 1 , được bật:

  • SET profiling = 1; 
Output
Query OK, 0 rows affected, 1 warning (0.00 sec)

Sau đó, chạy truy vấn sau để truy xuất tất cả khách hàng:

  • Select * from customers;

Bạn sẽ nhận được kết quả sau:

Output
+-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ |           1 | JANE DOE      | |           2 | JANIE DOE     | |           3 | JOHN ROE      | |           4 | MARY ROE      | |           5 | RICHARD ROE   | |           6 | JOHNNY DOE    | |           7 | JOHN SMITH    | |           8 | JOE BLOGGS    | |           9 | JANE POE      | |          10 | MARK MOE      | +-------------+---------------+ 10 rows in set (0.00 sec)

Sau đó, chạy lệnh SHOW PROFILES để truy xuất thông tin hiệu suất về truy vấn SELECT mà bạn vừa chạy:

  • SHOW PROFILES;

Bạn sẽ nhận được kết quả tương tự như sau:

Output
+----------+------------+-------------------------+ | Query_ID | Duration   | Query                   | +----------+------------+-------------------------+ |        1 | 0.00044075 | Select * from customers | +----------+------------+-------------------------+ 1 row in set, 1 warning (0.00 sec)

Kết quả hiển thị tổng thời gian MySQL đã sử dụng khi truy xuất bản ghi từ database . Bạn sẽ so sánh dữ liệu này trong các bước tiếp theo khi cache truy vấn được bật, vì vậy hãy lưu ý Duration của bạn. Bạn có thể bỏ qua cảnh báo trong kết quả vì điều này chỉ đơn giản là lệnh SHOW PROFILES sẽ bị xóa trong bản phát hành MySQL trong tương lai và được thay thế bằng Lược đồ hiệu suất .

Tiếp theo, thoát khỏi Giao diện dòng lệnh MySQL.

  • quit;

Bạn đã chạy một truy vấn với MySQL trước khi bật cache truy vấn và ghi lại Duration thời gian hoặc thời gian dành để truy xuất bản ghi. Tiếp theo, bạn sẽ kích hoạt bộ nhớ cache truy vấn và xem liệu có tăng hiệu suất khi chạy cùng một truy vấn hay không.

Bước 4 - Cài đặt bộ nhớ cache truy vấn

Ở bước trước, bạn đã tạo dữ liệu mẫu và chạy SELECT trước khi bật cache truy vấn. Trong bước này, bạn sẽ kích hoạt cache truy vấn bằng cách chỉnh sửa file cấu hình MySQL.

Sử dụng nano để chỉnh sửa file :

  • sudo nano /etc/mysql/my.cnf

Thêm thông tin sau vào cuối file của bạn:

/etc/mysql/my.cnf
... [mysqld] query_cache_type=1 query_cache_size = 10M query_cache_limit=256K

Tại đây, bạn đã kích hoạt bộ nhớ cache truy vấn bằng cách đặt query_cache_type thành 1 . Bạn cũng đã cài đặt kích thước giới hạn truy vấn riêng lẻ thành 256K và hướng dẫn MySQL phân bổ 10 megabyte để truy vấn cache bằng cách đặt giá trị của query_cache_size thành 10M .

Lưu file bằng cách nhấn CTRL + X , Y , sau đó ENTER . Sau đó, khởi động lại server MySQL của bạn để áp dụng các thay đổi :

  • sudo systemctl restart mysql

Đến đây bạn đã bật bộ nhớ cache truy vấn.

Khi bạn đã cấu hình cache truy vấn và khởi động lại MySQL để áp dụng các thay đổi, bạn sẽ tiếp tục và kiểm tra hiệu suất của MySQL với tính năng được bật.

Bước 5 - Kiểm tra server MySQL của bạn với Cache truy vấn được bật

Trong bước này, bạn sẽ chạy cùng một truy vấn mà bạn đã chạy ở Bước 3 để kiểm tra cách cache truy vấn đã tối ưu hóa hiệu suất của server MySQL của bạn.

Trước tiên, hãy kết nối với server MySQL của bạn với quyền là user gốc :

  • sudo mysql -u root -p

Nhập password gốc của bạn cho server database và nhấn ENTER để tiếp tục.

Bây giờ hãy xác nhận cài đặt cấu hình của bạn ở bước trước đảm bảo bạn đã bật bộ nhớ cache truy vấn:

  • show variables like 'query_cache_%' ;

Bạn sẽ thấy kết quả sau:

Output
+------------------------------+----------+ | Variable_name                | Value    | +------------------------------+----------+ | query_cache_limit            | 262144   | | query_cache_min_res_unit     | 4096     | | query_cache_size             | 10485760 | | query_cache_type             | ON       | | query_cache_wlock_invalidate | OFF      | +------------------------------+----------+ 5 rows in set (0.01 sec)

Biến query_cache_type được cài đặt để ON ; điều này xác nhận bạn đã bật bộ nhớ cache truy vấn với các tham số được xác định ở bước trước.

Chuyển sang database sample_db mà bạn đã tạo trước đó.

  • Use sample_db;

Khởi động profile MySQL:

  • SET profiling = 1;

Sau đó, chạy truy vấn để truy xuất tất cả khách hàng ít nhất hai lần để tạo đủ thông tin profile .

Lưu ý , khi bạn đã chạy truy vấn đầu tiên, MySQL sẽ tạo một bộ nhớ cache của các kết quả và do đó, bạn phải chạy truy vấn hai lần để kích hoạt bộ nhớ cache:

  • Select * from customers; 
  • Select * from customers;

Sau đó, liệt kê thông tin profile :

  • SHOW PROFILES;

Bạn sẽ nhận được một kết quả tương tự như sau:

Output
+----------+------------+-------------------------+ | Query_ID | Duration   | Query                   | +----------+------------+-------------------------+ |        1 | 0.00049250 | Select * from customers | |        2 | 0.00026000 | Select * from customers | +----------+------------+-------------------------+ 2 rows in set, 1 warning (0.00 sec)

Như bạn thấy thời gian thực hiện để chạy truy vấn đã giảm đáng kể từ 0.00044075 (không có bộ nhớ cache truy vấn ở Bước 3) xuống 0.00026000 (truy vấn thứ hai) trong bước này.

Bạn có thể thấy sự tối ưu hóa từ việc bật tính năng cache truy vấn bằng cách cấu hình chi tiết truy vấn đầu tiên:

  • SHOW PROFILE FOR QUERY 1; 
Output
+--------------------------------+----------+ | Status                         | Duration | +--------------------------------+----------+ | starting                       | 0.000025 | | Waiting for query cache lock   | 0.000004 | | starting                       | 0.000003 | | checking query cache for query | 0.000045 | | checking permissions           | 0.000008 | | Opening tables                 | 0.000014 | | init                           | 0.000018 | | System lock                    | 0.000008 | | Waiting for query cache lock   | 0.000002 | | System lock                    | 0.000018 | | optimizing                     | 0.000003 | | statistics                     | 0.000013 | | preparing                      | 0.000010 | | executing                      | 0.000003 | | Sending data                   | 0.000048 | | end                            | 0.000004 | | query end                      | 0.000006 | | closing tables                 | 0.000006 | | freeing items                  | 0.000006 | | Waiting for query cache lock   | 0.000003 | | freeing items                  | 0.000213 | | Waiting for query cache lock   | 0.000019 | | freeing items                  | 0.000002 | | storing result in query cache  | 0.000003 | | cleaning up                    | 0.000012 | +--------------------------------+----------+ 25 rows in set, 1 warning (0.00 sec)

Chạy lệnh sau để hiển thị thông tin profile cho truy vấn thứ hai, được lưu trong bộ nhớ cache:

  • SHOW PROFILE FOR QUERY 2; 
Output
+--------------------------------+----------+ | Status                         | Duration | +--------------------------------+----------+ | starting                       | 0.000024 | | Waiting for query cache lock   | 0.000003 | | starting                       | 0.000002 | | checking query cache for query | 0.000006 | | checking privileges on cached  | 0.000003 | | checking permissions           | 0.000027 | | sending cached result to clien | 0.000187 | | cleaning up                    | 0.000008 | +--------------------------------+----------+ 8 rows in set, 1 warning (0.00 sec)

Kết quả kết quả từ trình biên dịch cho thấy MySQL mất ít thời gian hơn trong truy vấn thứ hai vì nó có thể truy xuất dữ liệu từ cache truy vấn thay vì đọc từ đĩa. Bạn có thể so sánh hai tập hợp kết quả cho mỗi truy vấn. Nếu bạn xem thông tin profile trên QUERY 2 , trạng thái sending cached result to client cho thấy dữ liệu đã được đọc từ bộ nhớ cache và không có bảng nào được mở vì thiếu trạng thái Opening tables .

Với tính năng cache truy vấn MySQL được bật trên server của bạn, giờ đây bạn sẽ trải nghiệm tốc độ đọc được cải thiện.

Kết luận

Bạn đã cài đặt cache truy vấn để tăng tốc server MySQL của bạn trên Ubuntu 18.04. Sử dụng các tính năng như cache truy vấn của MySQL có thể nâng cao tốc độ trang web hoặc ứng dụng web của bạn. Bộ nhớ đệm giảm bớt việc thực thi không cần thiết cho các câu lệnh SQL và là một phương pháp rất được khuyến khích và phổ biến để tối ưu hóa database của bạn. Để biết thêm về cách tăng tốc server MySQL của bạn, hãy thử hướng dẫn Cách cài đặt database từ xa để tối ưu hóa hiệu suất trang với MySQL trên Ubuntu 18.04 .

Tags:

Các tin liên quan

Cách di chuyển database MySQL sang PostgreSQL bằng pgLoader
2019-05-28
Cách cấu hình SSL / TLS cho MySQL trên Ubuntu 18.04
2019-05-17
Cách thiết lập WordPress với MySQL trên Kubernetes bằng Helm
2019-05-07
Cách cho phép truy cập từ xa vào MySQL
2019-03-07
Cách sửa chữa bảng bị hỏng trong MySQL
2019-03-07
Cách khắc phục sự cố lỗi socket trong MySQL
2019-03-07
Cách giải quyết sự cố trong MySQL
2019-03-07
Cách khắc phục sự cố truy vấn MySQL
2019-03-07
Giới thiệu về Truy vấn trong MySQL
2018-10-17
Cách cài đặt MySQL mới nhất trên Debian 9
2018-09-05