Truy vấn chậm có thể tác động tiêu cực đến hiệu suất cơ sở dữ liệu. Nếu bạn không phân tích, tối ưu hóa và điều chỉnh các truy vấn chậm càng sớm càng tốt, hiệu suất tổng thể của hệ thống của bạn cuối cùng sẽ bị suy giảm. Tối ưu hóa truy vấn là điều cần thiết để đảm bảo hiệu suất cao cho các truy vấn quan trọng nhất
Tính năng nhật ký truy vấn chậm trong MySQL cho phép bạn ghi nhật ký tất cả các truy vấn mất nhiều thời gian hơn một ngưỡng thời gian thực hiện nhất định. Các truy vấn trong nhật ký truy vấn chậm là những ứng cử viên tốt để bắt đầu tối ưu hóa và loại bỏ các tắc nghẽn
Tính năng này không được kích hoạt theo mặc định, vì vậy trong bài viết này, tôi sẽ giải thích cách kích hoạt tính năng này và cách phân tích kết quả để phát hiện ra các nút thắt cổ chai trong đường dẫn dữ liệu. Sau khi đọc bài viết này, bạn sẽ có thể áp dụng các khái niệm và kỹ thuật này khi bạn có một hệ thống hoạt động kém hiệu quả
Thử nghiệm
Theo mặc định, tính năng nhật ký truy vấn chậm bị tắt trong MySQL, vì vậy, để bật tính năng này, chúng ta cần đặt tham số slow_query_log thành BẬT như trong danh sách 01
mysql> SET GLOBAL slow_query_log = 1;
Liệt kê 01
Khi bật tính năng nhật ký truy vấn chậm, theo mặc định, MySQL sẽ ghi nhật ký bất kỳ truy vấn nào mất hơn 10 giây để thực thi. Chúng tôi có thể thay đổi cài đặt này thành 5 giây bằng cách sử dụng tham số long_query_time như trong danh sách 02
mysql> SET GLOBAL long_query_time = 5;
Liệt kê 02
Bạn nên thiết lập long_query_time=0 để ghi nhật ký tất cả các truy vấn, vì bước đầu tiên là phân tích tất cả các truy vấn để tìm ra những truy vấn được sử dụng phổ biến nhất và chậm nhất. Hãy hết sức cẩn thận khi thiết lập long_query_time=0 vì cài đặt này sẽ làm tăng khối lượng công việc I/O trên hệ thống của bạn [do các thao tác ghi] và dung lượng ổ đĩa của bạn có thể bị đầy. Một chiến lược là bật cài đặt này trong một khoảng thời gian và sau đó hoàn nguyên cài đặt đó để chỉ ghi nhật ký các truy vấn chậm
Theo mặc định, tệp nhật ký truy vấn chậm nằm ở /var/lib/mysql/hostname-slow. đăng nhập. Chúng ta cũng có thể thiết lập một vị trí khác như trong danh sách 03 bằng cách sử dụng tham số slow_query_log_file
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
Liệt kê 03
Chúng tôi cũng có thể chỉ ra các truy vấn nhật ký không sử dụng chỉ mục, như trong danh sách 04
mysql> SET GLOBAL log_queries_not_using_indexes = 1;
Liệt kê 04
Mặc dù điều quan trọng là phải ghi nhật ký tất cả các truy vấn không đánh vào chỉ mục, nhưng điều đó không biểu thị truy vấn chậm vì có những trường hợp trình tối ưu hóa truy vấn chọn quét toàn bộ bảng thay vì sử dụng bất kỳ chỉ mục có sẵn nào;
Các thay đổi tham số sẽ bị mất sau khi MySQL khởi động lại vì các tham số được đặt động, vì vậy để các thay đổi có hiệu lực vĩnh viễn, chúng ta cần thiết lập các tham số trong tệp cấu hình MySQL [/etc/mysql/my. cnf] như trong danh sách 05
[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes = 1
Liệt kê 05
Chúng ta cũng có thể kiểm tra các giá trị của biến/tham số như trong danh sách 06
mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
Liệt kê 06
Để xác minh rằng nhật ký truy vấn chậm đang hoạt động chính xác, chúng tôi thực hiện câu lệnh sau với giá trị lớn hơn cài đặt long_query_time , như được hiển thị trong danh sách 07
Liệt kê 07
Truy vấn này được ghi lại do ngưỡng trong thời gian thực hiện, như trong danh sách 08
# Time: 2017-08-04T19:24:50.630232Z
# User@Host: root[root] @ localhost [] Id: 236
# Schema: Last_errno: 0 Killed: 0
# Query_time: 11.003017 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0
# Bytes_sent: 57
SET timestamp=1501874690;
SELECT SLEEP[11];
Liệt kê 08
Hãy xem nhật ký truy vấn chậm trong danh sách 8
Dòng đầu tiên cho biết thời gian hoàn thành truy vấn [thời gian chạy truy vấn]. Dòng tiếp theo chứa dữ liệu người dùng [người đã chạy nó]. Dòng tiếp theo cho biết truy vấn mất bao lâu để chạy [thời gian xử lý], thời gian khóa được yêu cầu [thời gian chờ], kết quả là bao nhiêu hàng được trả về và bao nhiêu hàng được kiểm tra để xác định kết quả. Dòng cuối cùng hiển thị truy vấn SQL thực tế
Phân tích nhật ký truy vấn chậm
Chúng tôi cần phân tích nhật ký truy vấn chậm để xác định các truy vấn ảnh hưởng đến hệ thống. Không phải lúc nào truy vấn chậm nhất cũng thực hiện điều này, bởi vì truy vấn chạy thường xuyên hơn với thời gian thực hiện thấp hơn [ví dụ: truy vấn mất 1 giây để chạy nhưng chạy hàng nghìn lần mỗi phút] làm tăng khối lượng công việc trên hệ thống nhiều hơn . Các truy vấn thực sự chậm ảnh hưởng đến thông lượng hệ thống, nhưng các truy vấn chạy thường xuyên tạo ra phần lớn khối lượng công việc của hệ thống
Cuối cùng, nhật ký truy vấn chậm tăng kích thước và kích thước quá lớn để phân tích bằng cách kiểm tra đơn giản. Để có được bức tranh toàn cảnh về các truy vấn chậm, MySQL cung cấp một công cụ có tên mysqldumpslow để hiển thị kết quả theo cách dễ dàng phân tích. Để sử dụng công cụ này, chúng tôi cần chuyển nhật ký truy vấn chậm làm tham số, như trong danh sách 09
$ mysqldumpslow /var/log/mysql/mysql-slow.log
Liệt kê 08
Đầu ra có các trường sau
- Đế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 của thời gian xử lý và thời gian chờ
- Hàng - Số hàng được trả về
Chúng tôi có thể thực thi công cụ này để khám phá các vấn đề cụ thể, chẳng hạn như "5 truy vấn hàng đầu đã trả về các hàng tối đa" để tìm ra các truy vấn thiếu mệnh đề GIỚI HẠN như được hiển thị trong danh sách 09
________số 8Liệt kê 09
Chúng ta cũng có thể sắp xếp đầu ra theo số lượng [số lần truy vấn được tìm thấy trong nhật ký truy vấn chậm] để tìm ra các truy vấn thường xuyên nhất có thể tạo ra phần lớn khối lượng công việc của máy chủ như trong danh sách 10
$ mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log
Liệt kê 10
Chúng tôi cũng có thể hiển thị 5 truy vấn hàng đầu được sắp xếp theo thời gian truy vấn trung bình, như được hiển thị trong danh sách 11
mysql> SET GLOBAL long_query_time = 5;0
Liệt kê 11
Phần kết luận
Trong bài viết này, tôi đã giải thích các khái niệm và lợi ích liên quan đến tính năng nhật ký truy vấn chậm trong MySQL. Bạn đã học cách kích hoạt tính năng này và phân tích nhật ký
Giờ đây, bạn có thể bắt đầu sử dụng tính năng tuyệt vời này trong môi trường cơ sở dữ liệu của riêng mình để tối ưu hóa các truy vấn và loại bỏ tắc nghẽn nhằm cải thiện hiệu suất và khả năng mở rộng của hệ thống
Cần trợ giúp quản lý dữ liệu?
Quest Software® sẵn sàng giúp bạn đơn giản hóa sự phức tạp, giảm chi phí và rủi ro, đồng thời thúc đẩy hiệu suất. Có lẽ đó là lý do tại sao chúng tôi luôn được độc giả DBTA bầu chọn là số 1 và có tỷ lệ hài lòng của khách hàng là hơn 95%.
Các công cụ quản lý cơ sở dữ liệu Toad® là giải pháp đa nền tảng của Quest® có thể giúp các DBA mới và kỳ cựu, và thậm chí cả các DBA “tình cờ” quản lý dữ liệu trong các môi trường Oracle, SQL Server, DB2, SAP, MySQL và Postgres.
thẻ. mysql
Được viết bởi
Giám đốc điều hành và người sáng lập Chuyên gia kiến trúc phần mềm doanh nghiệp Nubisera. cố vấn. doanh nhân. Oracle ACE. MVP của Microsoft. Tư vấn kỹ thuật bên ngoài cho Microsoft, Oracle, HP và Dell. Blogger phong phú về tất cả các chủ đề liên quan đến công nghệ phần mềm và tinh thần kinh doanh. Bằng tốt nghiệp Khoa học Máy tính Thạc sĩ Khoa học Tin học Kinh doanh [MBA]