Cách sử dụng profile truy vấn MySQL
Cấu hình truy vấn MySQL là một kỹ thuật hữu ích khi cố gắng phân tích hiệu suất tổng thể của một ứng dụng hướng database . Khi phát triển một ứng dụng có kích thước từ trung bình đến lớn, có xu hướng có hàng trăm truy vấn được phân phối khắp một cơ sở mã lớn và có khả năng nhiều truy vấn chạy ngược lại database mỗi giây.Nếu không có một số loại kỹ thuật lập profile truy vấn, sẽ rất khó xác định vị trí và nguyên nhân gây ra tắc nghẽn và ứng dụng chạy chậm lại. Bài viết này sẽ trình bày một số kỹ thuật lập profile truy vấn hữu ích bằng cách sử dụng các công cụ được tích hợp sẵn trong server MySQL.
Nhật ký truy vấn chậm MySQL là gì?
Nhật ký truy vấn chậm MySQL là log mà MySQL gửi các truy vấn chậm, có khả năng có vấn đề. Chức năng ghi log này đi kèm với MySQL nhưng bị tắt theo mặc định. Những truy vấn nào được ghi lại được xác định bởi các biến server có thể tùy chỉnh cho phép lập profile truy vấn dựa trên các yêu cầu về hiệu suất của ứng dụng. Nói chung các truy vấn được ghi lại là các truy vấn mất nhiều thời gian hơn một khoảng thời gian cụ thể để thực thi hoặc các truy vấn không đánh đúng index .Cài đặt các biến cấu hình
Các biến server chính để cài đặt log truy vấn chậm 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 - Boolean để bật và tắt log truy vấn chậm.
slow_query_log_file - Đường dẫn tuyệt đối cho file log truy vấn. Thư mục của file phải thuộc sở hữu của user mysqld và có quyền chính xác để đọc và ghi vào. Daemon mysql có thể sẽ chạy dưới dạng `mysql` nhưng để xác minh hãy chạy những điều sau trong terminal Linux:
ps -ef | grep bin/mysqld | cut -d' ' -f1
Đầu ra có thể sẽ hiển thị user hiện tại cũng như user mysqld. Ví dụ về đặt đường dẫn folder / var / log / mysql:
cd /var/log mkdir mysql chmod 755 mysql chown mysql:mysql mysql
long_query_time - Thời gian, tính bằng giây, để kiểm tra độ dài truy vấn. Đối với giá trị là 5, mọi truy vấn mất hơn 5 giây để thực thi sẽ được ghi lại.
log_queries_not_using_indexes - Giá trị Boolean có ghi các truy vấn không đạt được index hay không. Khi thực hiện phân tích truy vấn, điều quan trọng là ghi lại các truy vấn không đạt được index .
min_examined_row_limit - Đặt giới hạn thấp hơn về số lượng hàng sẽ được kiểm tra. Giá trị 1000 sẽ bỏ qua bất kỳ truy vấn nào phân tích ít hơn 1000 hàng.
Các biến server MySQL có thể được đặt trong file conf MySQL hoặc động thông qua MySQL GUI hoặc dòng lệnh MySQL. Nếu các biến được đặt trong file conf, chúng sẽ vẫn tồn tại khi server khởi động lại nhưng cũng sẽ yêu cầu khởi động lại server để hoạt động. Tệp conf MySQL thường nằm trong `/ etc hoặc / usr`, thường là` / etc / my.cnf` hoặc `/ etc / mysql / my.cnf`. Để tìm file conf (có thể phải mở rộng tìm kiếm đến nhiều folder root hơn):
find /etc -name my.cnf find /usr -name my.cnf
Khi file conf đã được tìm thấy, chỉ cần nối 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
, các thay đổi sẽ không ảnh hưởng đến sau khi server khởi động lại, vì vậy nếu cần thay đổi ngay lập tức thì hãy đặt các biến độ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 các giá trị biến:
mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log'; mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';
Một nhược điểm khi đặt biến MySQL động là các biến sẽ bị mất khi server khởi động lại. Bạn nên thêm bất kỳ biến quan trọng nào mà bạn cần được duy trì vào file conf MySQL.
LƯU Ý : Cú pháp để cài đặt các biến động thông qua SET và đặt chúng vào file conf hơi khác nhau, ví dụ: `slow_query_log` so với` slow-query-log`. Xem trang biến hệ thống động của MySQL để biết các cú pháp khác nhau. Định dạng file tùy chọn là định dạng cho file conf và Tên biến hệ thống là tên biến để đặt các biến động.
Tạo dữ liệu profile truy vấn
Bây giờ các cấu hình log truy vấn chậm MySQL đã được phác thảo, đã đến lúc tạo một số dữ liệu truy vấn để lập profile . Ví dụ này được viết trên một version MySQL đang chạy mà không có cấu hình log chậm nào được đặt trước đó. Các truy vấn của ví dụ có thể được chạy thông qua MySQL GUI hoặc thông qua dấu nhắc lệnh MySQL. Khi theo dõi log truy vấn chậm, sẽ rất hữu ích khi mở hai cửa sổ kết nối đến server : một kết nối để viết các câu lệnh MySQL và một kết nối để xem log truy vấn.
Trong tab console MySQL, đăng nhập vào server MySQL với user có quyền SUPER ADMIN. Để bắt đầu, hãy tạo database và bảng thử nghiệm, thêm một số dữ liệu giả và bật log 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 nào khác sử dụng MySQL để giúp tránh làm lộn xộn log truy vấn khi nó đang được 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 database thử nghiệm và bảng với một lượng nhỏ dữ liệu thử nghiệm. Nhật ký truy vấn chậm đã được bật nhưng thời gian truy vấn được cố ý đặt cao và cờ kiểm tra hàng tối thiểu vẫn bị tắt. Trong tab console để xem log :
cd /var/log/mysql ls -l
Không nên có log truy vấn chậm trong folder , vì không có truy vấn nào được chạy. Nếu có, điều đó nghĩa là log 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ả của ví dụ này. Quay lại tab MySQL, chạy SQL sau:
mysql> USE profile_sampling; mysql> SELECT * FROM users WHERE id = 1;
Truy vấn được thực hiện là một lựa chọn đơn giản bằng cách sử dụng index Khóa chính từ bảng. Truy vấn này nhanh và sử dụng một index , vì vậy sẽ không có mục nhập nào trong log truy vấn chậm cho truy vấn này. Nhìn lại folder log truy vấn và xác minh không có log nào được tạo. Bây giờ quay lại cửa sổ MySQL của bạn chạy:
mysql> SELECT * FROM users WHERE name = 'Jesse';
Truy vấn này được chạy trên một cột không được lập index - tên. Đến đây, sẽ có một truy vấn trong log với thông tin sau (có thể không hoàn toàn giống 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 thành công. Thêm một ví dụ nữa. Tăng giới hạn hàng đã 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';
Không có dữ liệu nào sẽ được thêm vào log 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 nào được điền vào log , có một số điều có thể được kiểm tra. Trước tiên, các quyền của folder mà log đang được tạo trong đó. Chủ sở hữu / group phải giống với user mysqld (xem ví dụ ở trên) cũng như có quyền chính xác, chmod 755 để chắc chắn. Thứ hai, có thể đã tồn tại các cấu hình biến truy vấn chậm đang can thiệp vào ví dụ. Đặt lại giá trị mặc định bằng cách xóa mọi biến truy vấn chậm khỏi file conf và khởi động lại server hoặc đặt động các biến toàn cục trở về giá trị mặc định của chúng. Nếu các thay đổi được thực hiện độ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 global có hiệu lực.
Phân tích thông tin profile truy vấn
Xem xét dữ liệu profile 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';
Mục nhập hiển thị:
- Thời gian chạy truy vấn
- Ai đã điều hành nó
- Truy vấn mất bao lâu
- Chiều dài của khóa
- Có bao nhiêu hàng đã trả về
- Có bao nhiêu hàng nơi đã kiểm tra
Điều này 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 chỉ định với các biến server sẽ kết thúc trong log . Điều này cho phép nhà phát triển hoặc administrator yêu cầu MySQL cảnh báo họ khi một truy vấn không hoạt động tốt như nó phải [trái ngược với việc đọc qua mã nguồn và cố gắng tìm các truy vấn được viết kém]. Ngoài ra, dữ liệu profile truy vấn có thể hữu ích khi nó được lập profile trong một khoảng thời gian, điều này có thể giúp xác định những trường hợp nào đang góp phần vào hiệu suất ứng dụng kém.
Sử dụng mysqldumpslow
Trong một ví dụ thực tế hơn, việc lập profile sẽ được bật trên ứng dụng hướng database , cung cấp một stream dữ liệu vừa phải để lập profile . Nhật ký sẽ liên tục được ghi vào, có thể thường xuyên hơn bất kỳ ai đang xem. Khi kích thước log tăng lên, việc phân tích tất cả dữ liệu trở nên khó khăn và các truy vấn có vấn đề dễ bị mất trong log . MySQL cung cấp một công cụ khác, mysqldumpslow, giúp tránh vấn đề này bằng cách chia nhỏ log truy vấn chậm. Hệ binary đi kèm với MySQL (trên Linux) vì vậy để sử dụng nó chỉ cần chạy lệnh và chuyển vào đường dẫn log :
mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.logCó nhiều tham số khác nhau được dùng với lệnh để giúp tùy chỉnh kết quả . Trong ví dụ trên, 5 truy vấn hàng đầu được sắp xếp theo thời gian truy vấn trung bình sẽ được hiển thị. Các hàng kết quả dễ đọc hơn cũng như được group theo truy vấn ( kết quả này khác với ví dụ để chứng minh 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 đang được hiển thị:
- Đếm - Số lần truy vấn đã được ghi lại
- Thời gian - Cả thời gian trung bình và tổng thời gian trong ()
- Khóa - Thời gian khóa bảng
- Hàng - Số hàng được trả về
Lệnh này tóm tắt các số và chuỗi, vì vậy các truy vấn giống nhau với các mệnh đề WHERE khác nhau sẽ được tính là cùng một truy vấn ( lưu ý page_namespace = N). Có một công cụ như mysqldumpslow ngăn chặn nhu cầu 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 một số đối sánh biểu thức phức tạp giúp đi sâu vào các truy vấn khác nhau trong log .
Ngoài ra còn có các công cụ phân tích log của bên thứ 3 cung cấp các chế độ xem dữ liệu khác nhau, một công cụ phổ biến là pt-truy vấn-thông báo .
Phân tích truy vấn
Một công cụ cấu hình cuối cùng cần lưu ý là công cụ cho phép chia nhỏ một truy vấn phức tạp. Một trường hợp sử dụng tốt cho công cụ là lấy một truy vấn có vấn đề từ log truy vấn chậm và chạy nó trực tiếp trong MySQL. Cấu hình đầu tiên phải được bật, sau đó truy vấn được chạy:
mysql> SET SESSION profiling = 1; mysql> USE profile_sampling; mysql> SELECT * FROM users WHERE name = 'Jesse'; mysql> SHOW PROFILES;
Sau khi đã bật cấu hình, CẤU HÌNH HIỂN THỊ sẽ hiển thị một bảng liên kết một Query_ID với một câu lệnh SQL. Tìm Query_ID tương ứng với truy vấn đã chạy và chạy truy vấn sau (thay thế # bằng Query_ID của bạn):
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;
Đầu ra mẫu:
| SEQ | TIỂU BANG | DURATION |
|---|---|---|
| 1 | bắt đầu | 0,000046 |
| 2 | kiểm tra quyền | 0,000005 |
| 3 | mở bảng | 0,000036 |
| ... | ... | ... |
STATE là "bước" trong quá trình thực hiện truy vấn và DURATION là thời gian để hoàn thành bước đó, tính bằng giây. Đây không phải là một công cụ quá hữu ích, nhưng nó rất thú vị và có thể giúp xác định phần nào của việc thực thi truy vấn đang gây ra độ trễ nhiều nhất.
Để có phác thảo chi tiết về các cột khác nhau: http://dev.mysql.com/doc/refman/5.5/en/profiling-table.html
Để biết tổng quan chi tiết về các "bước" khác nhau: http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html
LƯU Ý: KHÔNG nên sử dụng công cụ này trong môi trường production để phân tích các truy vấn cụ thể.
Hiệu suất log truy vấn chậm
Một câu hỏi cuối cùng cần giải quyết là log truy vấn chậm sẽ ảnh hưởng đến hiệu suất như thế nào. Nói chung, sẽ an toàn khi chạy log truy vấn chậm trong môi trường production ; CPU và tải I / O đều không phải là mối quan tâm ¹ ² . Tuy nhiên, cần có một số chiến lược để theo dõi kích thước log đảm bảo kích thước file log không quá lớn đối với hệ thống file . Ngoài ra, một nguyên tắc nhỏ khi chạy log truy vấn chậm trong môi trường production là để long_query_time ở mức 1s hoặc cao hơn.
QUAN TRỌNG: Bạn không nên sử dụng công cụ lập profile , SET profiling = 1, cũng như ghi log tất cả các truy vấn, tức là biến general_log, trong môi trường production , dung lượng công việc cao .
Kết luận
Nhật ký truy vấn chậm cực kỳ hữu ích trong việc chọn ra các truy vấn có vấn đề và lập profile hiệu suất truy vấn tổng thể. Khi lập profile truy vấn với log truy vấn chậm, nhà phát triển có thể hiểu sâu về cách các truy vấn MySQL của ứng dụng đang hoạt động. Sử dụng một công cụ như mysqldumpslow, việc giám sát và đánh giá log truy vấn chậm trở nên có thể quản lý được và có thể dễ dàng kết hợp vào quá trình phát triển. Bây giờ các truy vấn có vấn đề đã được xác định, bước tiếp theo là điều chỉnh các truy vấn để có hiệu suất tối đa.
Các tin liên quan
Mở rộng quy mô Ruby on Rails: Thiết lập server MySQL chuyên dụng (phần 2)2014-02-27
Cách sử dụng HAProxy để thiết lập cân bằng tải MySQL
2013-12-02
Cách sử dụng HAProxy để thiết lập cân bằng tải MySQL
2013-12-02
Cách cài đặt MySQL 5.6 từ kho lưu trữ chính thức của Yum
2013-11-13
Cách tối ưu hóa các truy vấn và bảng trong MySQL và MariaDB trên VPS
2013-11-11
Cách bảo mật sao chép MySQL bằng SSH trên VPS
2013-09-18
Cách tạo và quản lý database trong MySQL và MariaDB trên server cloud
2013-07-29
Cách thực hiện các truy vấn cơ bản trong MySQL và MariaDB trên server cloud
2013-07-29
Cách cài đặt Etherpad cho Sản xuất với Node.js và MySQL trên VPS
2013-07-26
Cách bảo mật database MySQL và MariaDB trong VPS Linux
2013-07-23
