Các cách để sử dụng MySQL Query Profiling?

MySQL query profiling là một kỹ thuật hữu ích khi phân tích hiệu suất tổng thể của ứng dụng sử dụng cơ sở dữ liệu. Khi phát triển một ứng dụng vừa và lớn thường có hàng trăm truy vấn được phân tán trong toàn bộ mã nguồn. Nhiều truy vấn chạy trên cơ sở dữ liệu mỗi giây. 

Nếu không có các kỹ thuật query profiling, việc xác định vị trí và nguyên nhân của các vấn đề chậm chạp, giảm tốc độ ứng dụng trở nên rất khó khăn. Bài viết này sẽ trình bày một số kỹ thuật query profiling hữu ích sử dụng các công cụ được tích hợp sẵn trong máy chủ MySQL.

Nhật ký truy vấn chậm (MySQL slow query log) là gì?

Nhật ký truy vấn chậm nằm trong kỹ thuật MySQL query profiling, đây là một nhật ký mà MySQL sử dụng để ghi lại các truy vấn chậm có thể gây vấn đề. Tính năng ghi nhật ký này đi kèm với MySQL nhưng mặc định là tắt. 

Các truy vấn được ghi vào nhật ký này được xác định bởi các biến cấu hình của máy chủ có thể tùy chỉnh. Chúng cho phép thực hiện việc phân tích truy vấn dựa trên yêu cầu hiệu suất của ứng dụng. 

Thông thường, các truy vấn được ghi vào nhật ký là các truy vấn mất thời gian thực thi hơn bình thường hoặc các truy vấn không tận dụng được các chỉ mục một cách chính xác.

Cài đặt các biến cho việc phân tích truy vấn (profiling)

Các biến chính của máy chủ để thiết lập nhật ký truy vấn chậm của MySQL là:

slow_query_log G slow_query_log_file G long_query_time G / S log_queries_not_using_indexes G min_examined_row_limit G / S

Lưu ý: (G) biến toàn cục, (S) biến phiên

slow_query_log: Giá trị Boolean để bật hoặc tắt nhật ký truy vấn chậm.

slow_query_log_file: Đường dẫn tuyệt đối đến tập tin nhật ký truy vấn. Thư mục chứa tập tin này nên thuộc sở hữu của người dùng mysqld, có quyền đọc và ghi chính xác. Hệ thống mysql daemon có thể đang chạy dưới tên mysql nhưng để xác minh bạn có thể chạy lệnh sau trong terminal Linux:

ps -ef | grep bin/mysqld | cut -d' ' -f1

Kết quả đầu ra có thể hiển thị người dùng hiện tại cũng như người dùng "mysqld". Dưới đây là một ví dụ về việc đặt đường dẫn thư mục là /var/log/mysql:

cd /var/log mkdir mysql chmod 755 mysql chown mysql:mysql mysql

• long_query_time: Thời gian được tính bằng giây, nhằm kiểm tra độ dài của câu truy vấn. Với giá trị là 5, bất kỳ câu truy vấn nào mất hơn 5 giây để thực thi sẽ được ghi vào nhật ký truy vấn chậm.
• log_queries_not_using_indexes: Giá trị Boolean xác định liệu có ghi lại các câu truy vấn không sử dụng chỉ mục hay không. Khi sử dụng kỹ thuật MySQL query profiling để phân tích truy vấn, việc ghi lại các câu truy vấn không sử dụng chỉ mục là rất quan trọng.
• min_examined_row_limit: Thiết lập giới hạn tối thiểu trên số hàng được kiểm tra. Giá trị là 1000 sẽ bỏ qua bất kỳ câu truy vấn nào phân tích ít hơn 1000 hàng.

Các biến máy chủ MySQL có thể được thiết lập trong tệp cấu hình MySQL hoặc tự động thông qua giao diện người dùng MySQL hay dòng lệnh MySQL. Nếu các biến được đặt trong tệp cấu hình, chúng sẽ được duy trì khi máy chủ khởi động lại nhưng cũng sẽ yêu cầu máy chủ khởi động lại để hoạt động. Tệp cấu hình MySQL thường được đặt tại /etc hoặc /usr, thường là /etc/my.cnf hoặc /etc/mysql/my.cnf. Để tìm tệp cấu hình (có thể cần mở rộng tìm kiếm đến nhiều thư mục gốc):

find /etc -name my.cnf find /usr -name my.cnf

Khi tìm thấy tệp cấu hình (conf), chỉ cần thêm các giá trị mong muốn dưới tiêu đề [mysqld]:

[mysqld] …. slow-query-log = 1 slow-query-log-file = /var/log/mysql/localhost-slow.log long_query_time = 1 log-queries-not-using-indexes

Một lần nữa, những thay đổi sẽ không có hiệu lực cho đến sau khi khởi động lại máy chủ. Do đó nếu bạn cần áp dụng các thay đổi ngay lập tức thì hãy đặt các biến môi trường một cách tự động:

mysql> SET GLOBAL slow_query_log = 'ON'; mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log'; mysql> SET GLOBAL log_queries_not_using_indexes = 'ON'; mysql> SET SESSION long_query_time = 1; mysql> SET SESSION min_examined_row_limit = 100;

Để kiểm tra giá trị của các biến môi trường:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log'; mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

Một hạn chế khi đặt các biến môi trường MySQL một cách tự động là các biến này sẽ bị mất sau khi máy chủ khởi động lại. Để đảm bảo giữ lại các biến quan trọng mà bạn cần lưu trữ nên thêm chúng vào tệp cấu hình MySQL.

Lưu ý: Cú pháp để đặt các biến môi trường một cách tự động thông qua SET và đặt chúng vào tệp cấu hình có chút khác biệt, ví dụ: slow_query_log so với slow-query-log. Xem trang dynamic system variables của MySQL để biết về các cú pháp khác nhau. Định dạng Option-File là định dạng cho tệp cấu hình và System Variable Name là tên biến để đặt các biến môi trường một tự cách động.

Sử dụng MySQL query profiling: tạo dữ liệu hồ sơ truy vấn (query profile data)

Bây giờ sau khi các cấu hình nhật ký truy vấn chậm của MySQL đã được trình bày, đến lúc tạo dữ liệu truy vấn để tạo hồ sơ. Ví dụ này được viết trên một MySQL đang chạy không có cấu hình nhật ký chậm trước đó. 

Các truy vấn trong ví dụ có thể chạy thông qua giao diện MySQL hoặc thông qua dấu nhắc lệnh MySQL. Khi theo dõi nhật ký truy vấn chậm bạn nên mở hai cửa sổ kết nối đến máy chủ: một kết nối để viết các câu lệnh MySQL và một kết nối để xem nhật ký truy vấn.

Trong tab console MySQL, đăng nhập vào máy chủ MySQL với người dùng có đặc quyền SIÊU QUẢN TRỊ VIÊN. Để bắt đầu, hãy tạo một cơ sở dữ liệu và bảng thử nghiệm, thêm một số dữ liệu giả và bật nhật ký truy vấn chậm. Ví dụ này nên được chạy trong môi trường phát triển, lý tưởng là không có ứng dụng khác sử dụng MySQL để tránh làm rác nhật ký truy vấn trong quá trình theo dõi.

$> mysql -u -p mysql> CREATE DATABASE profile_sampling; mysql> USE profile_sampling; mysql> CREATE TABLE users ( id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) ); mysql> INSERT INTO users (name) VALUES ('Walter'),('Skyler'),('Jesse'),('Hank'),('Walter Jr.'),('Marie'),('Saul'),('Gustavo'),('Hector'),('Mike'); mysql> SET GLOBAL slow_query_log = 1; mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log'; mysql> SET GLOBAL log_queries_not_using_indexes = 1; mysql> SET long_query_time = 10; mysql> SET min_examined_row_limit = 0;

Hiện đã có một cơ sở dữ liệu và một bảng thử nghiệm với lượng dữ liệu thử nghiệm nhỏ. Nhật ký truy vấn chậm đã được bật nhưng thời gian truy vấn được cố ý đặt ở mức cao và cờ kiểm tra hàng tối thiểu vẫn bị tắt. Vào trong tab console để xem nhật ký:

cd /var/log/mysql ls -l

Nếu thư mục đã có nhật ký truy vấn chậm, điều đó có nghĩa là nhật ký truy vấn chậm đã được bật và cấu hình trong quá khứ. Điều này có thể làm sai lệch một số kết quả trong ví dụ này. Trong tab MySQL, chạy SQL sau đây:

mysql> USE profile_sampling; mysql> SELECT * FROM users WHERE id = 1;

Câu truy vấn được thực thi là một SELECT đơn giản sử dụng chỉ mục Primary Key từ bảng. Truy vấn này nhanh chóng và sử dụng một chỉ mục, do đó không có bất kỳ mục nhập nào trong nhật ký truy vấn chậm cho truy vấn này. Hãy xem lại trong thư mục nhật ký truy vấn và xác minh rằng không có bản ghi nào được tạo ra. Tiếp theo, chạy:

mysql> SELECT * FROM users WHERE name = 'Jesse';

Câu truy vấn này được thực thi trên một cột không có chỉ mục - "name". Tại thời điểm này, trong nhật ký truy vấn chậm sẽ có một truy vấn với thông tin sau đây (có thể không giống hệt nhau): 

/var/log/mysql/localhost-slow.log

# Time: 140322 13:54:58 # User@Host: root[root] @ localhost [] # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp=1395521698; SELECT * FROM users WHERE name = 'Jesse';

Truy vấn đã được ghi lại thành công.Thêm một ví dụ nữa. Tăng giới hạn hàng được kiểm tra tối thiểu và chạy một truy vấn tương tự:

mysql> SET min_examined_row_limit = 100; mysql> SELECT * FROM users WHERE name = 'Walter';

Sẽ không có dữ liệu nào được thêm vào nhật ký vì tối thiểu 100 hàng không được phân tích.

Lưu ý: Nếu không có dữ liệu được ghi vào nhật ký chậm, một số điều có thể được kiểm tra. Đầu tiên, kiểm tra quyền của thư mục mà nhật ký được tạo ra. Chủ sở hữu/nhóm phải giống như người dùng mysqld (xem phần trên cho ví dụ) và có quyền đúng, ví dụ như chmod 755. 

Thứ hai, có thể đã có cấu hình biến truy vấn chậm hiện tại đang gây nhiễu. Đặt lại giá trị mặc định bằng cách loại bỏ bất kỳ biến truy vấn chậm nào khỏi tệp cấu hình và khởi động lại máy chủ, hoặc tự động đặt các biến toàn cục về giá trị mặc định của chúng. 

Nếu các thay đổi được thực hiện tự động, hãy đăng xuất và đăng nhập lại vào MySQL để đảm bảo các bản cập nhật chung có hiệu lực.

Sử dụng MySQL query profiling: Phân tích thông tin hồ sơ truy vấn 

Nhìn vào dữ liệu hồ sơ truy vấn từ ví dụ trên:

# Time: 140322 13:54:58 # User@Host: root[root] @ localhost [] # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp=1395521698; SELECT * FROM users WHERE name = 'Jesse';

Nhật ký truy vấn chậm hiển thị các thông tin như:

• Thời điểm mà truy vấn được thực thi.
• Người thực thi truy vấn.
• Thời gian thực thi truy vấn.
• Thời gian chờ khóa.
• Số hàng trả về.
• Số hàng được kiểm tra.

Điều này rất hữu ích vì bất kỳ truy vấn nào vi phạm các yêu cầu hiệu suất đã được xác định trong các biến máy chủ sẽ xuất hiện trong nhật ký. Việc này cho phép các nhà phát triển hoặc quản trị viên nhận được cảnh báo từ MySQL khi truy vấn không thực hiện tốt như mong đợi (thay vì phải đọc mã nguồn và tìm kiếm các truy vấn viết kém chất lượng). Ngoài ra, dữ liệu phân tích truy vấn có thể hữu ích khi được phân tích trong một khoảng thời gian dài, giúp xác định các tình huống đóng góp vào hiệu suất ứng dụng kém.

MySQL query profiling: Sử dụng mysqldumpslow

Trong một ví dụ thực tế hơn, việc phân tích sẽ được kích hoạt trên một ứng dụng được điều hành bởi cơ sở dữ liệu, cung cấp một luồng dữ liệu vừa phải để phân tích. 

Nhật ký sẽ tiếp tục được ghi vào, có thể thường xuyên hơn bất kỳ ai đang theo dõi. Khi kích thước nhật ký ngày càng lớn, việc duyệt qua tất cả dữ liệu trở nên khó khăn và các truy vấn gây vấn đề có thể dễ dàng bị mất trong nhật ký. 

MySQL cung cấp một công cụ khác, là mysqldumpslow, giúp giải quyết vấn đề này bằng cách tách riêng các truy vấn chậm trong nhật ký. Tệp nhị phân này được đóng gói cùng với MySQL (trên Linux), để sử dụng chỉ cần chạy lệnh sau và truyền đường dẫn của nhật ký vào:

mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log

Có nhiều tham số khác nhau có thể được sử dụng với lệnh để tùy chỉnh đầu ra. Trong ví dụ trên, 5 truy vấn hàng đầu được sắp xếp theo thời gian trung bình của truy vấn sẽ được hiển thị. Các hàng kết quả dễ đọc hơn cũng như được nhóm theo truy vấn (đầu ra này khác với ví dụ để thể hiện các giá trị cao):

Count: 2 Time=68.34s (136s) Lock=0.00s (0s) Rows=39892974.5 (79785949), root[root]@localhost SELECT PL.pl_title, P.page_title FROM page P INNER JOIN pagelinks PL ON PL.pl_namespace = P.page_namespace WHERE P.page_namespace = N …

Dữ liệu được hiển thị bao gồm:

• Count - Số lần mà truy vấn đã được ghi log
• Time - Cả thời gian trung bình và tổng thời gian (nếu có) của truy vấn
• Lock - Thời gian khóa bảng
• Rows - Số hàng được trả về

Lệnh mysqldumpslow trừu tượng hóa các số và chuỗi, do đó các truy vấn giống nhau với các điều kiện WHERE khác nhau sẽ được đếm như là cùng một truy vấn (chú ý page_namespace = N). 

Có một công cụ như mysqldumpslow giúp bạn tránh việc phải liên tục theo dõi log truy vấn chậm, thay vào đó cho phép kiểm tra định kỳ hoặc tự động. Các tham số của lệnh mysqldumpslow cho phép so khớp biểu thức phức tạp giúp phân tích chi tiết các truy vấn trong log.

Ngoài ra, còn có các công cụ phân tích log bên thứ ba cung cấp các dạng dữ liệu khác nhau, trong đó có một công cụ phổ biến là pt-query-digest.

Phân tích truy vấn

Một công cụ định hình cuối cùng cần lưu ý là công cụ cho phép chia nhỏ truy vấn phức tạp. Một trường hợp sử dụng tốt cho công cụ này là khi bạn lấy một truy vấn gây vấn đề từ log truy vấn chậm và chạy nó trực tiếp trong MySQL. Trước tiên, bạn cần bật chế độ phân tích, sau đó thực thi truy vấn:

mysql> SET SESSION profiling = 1; mysql> USE profile_sampling; mysql> SELECT * FROM users WHERE name = 'Jesse'; mysql> SHOW PROFILES;

Sau khi chế độ phân tích đã được bật, lệnh SHOW PROFILES sẽ hiển thị một bảng liên kết giữa Query_ID và câu lệnh SQL. Tìm Query_ID tương ứng với truy vấn bạn đã thực thi và chạy truy vấn sau (thay # bằng Query_ID của bạn):

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Đầu ra sẽ giống như sau:

Cột STATE biểu thị "bước" trong quá trình thực thi truy vấn và DURATION là thời gian mà bước đó mất để hoàn thành được tính bằng giây. Đây không phải là một công cụ cực kỳ hữu ích nhưng nó thú vị và có thể giúp xác định phần nào trong việc thực thi truy vấn gây ra sự trì trệ nhất định.

Để có mô tả chi tiết về các cột khác nhau, bạn có thể xem trang sau: http://dev.mysql.com/doc/refman/5.5/en/profiling-table.html

Đối với cái nhìn tổng quan về các "bước" khác nhau, bạn có thể tham khảo trang sau: http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html

Lưu ý: Công cụ này KHÔNG NÊN được sử dụng trong môi trường sản xuất mà chỉ dùng để phân tích các truy vấn cụ thể.

Hiệu suất của nhật ký truy vấn chậm (slow query log)

Một câu hỏi cuối cùng cần giải quyết là làm cách nào nhật ký truy vấn chậm (slow query log) sẽ ảnh hưởng đến hiệu suất. Nói chung, việc chạy nhật ký truy vấn chậm trong môi trường sản xuất là an toàn; cả CPU lẫn tải I/O không nên là vấn đề. 

Tuy nhiên, cần có một chiến lược để giám sát kích thước nhật ký để đảm bảo kích thước tập tin nhật ký không trở nên quá lớn cho hệ thống tệp. Ngoài ra, một nguyên tắc đơn giản khi chạy nhật ký truy vấn chậm trong môi trường sản xuất là để giữ long_query_time ở mức 1 giây hoặc cao hơn.

Điều quan trọng: Không nên sử dụng công cụ phân tích (profiling tool) như SET profiling=1 hoặc ghi tất cả các truy vấn (general_log variable) trong môi trường sản xuất có khối lượng công việc cao.

Kỹ thuật MySQL query profiling sử dụng nhật ký truy vấn chậm để phân tích và đánh giá từ đó cải thiện hiệu suất trang. Nhật ký truy vấn chậm là một công cụ vô cùng hữu ích trong việc xác định các truy vấn gây vấn đề và phân tích hiệu suất truy vấn tổng thể. 

Khi sử dụng nhật ký truy vấn chậm để phân tích truy vấn, một nhà phát triển có thể có cái nhìn sâu sắc về cách thức các truy vấn MySQL trong hoạt động ứng dụng. 

Sử dụng các công cụ như mysqldumpslow giúp theo dõi và đánh giá nhật ký truy vấn chậm trở nên dễ dàng và có thể tích hợp vào quá trình phát triển một cách thuận lợi. Bây giờ khi đã xác định được các truy vấn gây vấn đề, bước tiếp theo là điều chỉnh các truy vấn để đạt hiệu suất tối đa.

Mọi người cũng tìm kiếm: mysql profiling, trong một truy vấn (query) có chứa tối thiểu mấy tham số:, query là gì, mysql profiler, sql query mà chậm thì check gì đầu tiên, queries là gì.