Cách bật Nhật ký truy vấn chậm của MySQL

Các truy vấn chậm bất ngờ là một trong những vấn đề về hiệu suất phổ biến nhất của MySQL. Một truy vấn hoạt động ở mức chấp nhận được trong quá trình phát triển có thể chùn bước khi bị căng thẳng bởi khối lượng công việc sản xuất

Các ứng dụng lớn có thể chạy hàng trăm truy vấn cơ sở dữ liệu duy nhất mỗi khi điểm cuối bị tấn công. Điều này gây khó khăn cho việc tìm ra các truy vấn gây ra sự chậm trễ trong phản hồi của máy chủ. Nhật ký truy vấn chậm của MySQL là một tùy chọn gỡ lỗi có thể giúp bạn xác định các câu lệnh SQL đáng ngờ, cung cấp điểm khởi đầu cho các cuộc điều tra của bạn

Kích hoạt Nhật ký truy vấn chậm

Nhật ký là một cơ chế tích hợp sẵn để ghi lại các truy vấn SQL chạy dài. Các truy vấn không hoàn thành trong thời gian đã định cấu hình sẽ được ghi vào nhật ký. Đọc nội dung của nhật ký cho bạn biết SQL đã được thực thi và thời gian thực hiện

Ghi nhật ký truy vấn chậm bị tắt theo mặc định. Bạn có thể kích hoạt nó trên máy chủ của mình bằng cách chạy lệnh sau từ trình bao MySQL quản trị

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Thay đổi áp dụng ngay lập tức. Các truy vấn chậm bây giờ sẽ được ghi vào

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

MySQL tính một truy vấn là “chậm” nếu mất hơn 10 giây để hoàn thành. Giới hạn này thường quá thoải mái đối với các ứng dụng web hướng tới người dùng nơi mong đợi các phản hồi gần như ngay lập tức. Bạn có thể thay đổi giới hạn bằng cách đặt biến

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

SET GLOBAL long_query_time=1;

Giá trị đặt thời lượng tối thiểu cho các truy vấn chậm. Điều quan trọng là tìm sự cân bằng phù hợp với ứng dụng của riêng bạn. Ngưỡng quá cao sẽ loại trừ các truy vấn đang thực sự ảnh hưởng đến hiệu suất. Ngược lại, các giá trị rất thấp có thể khiến quá nhiều truy vấn được ghi lại, tạo ra nhật ký quá nhiễu

Sử dụng tệp cấu hình của MySQL

Bạn nên kích hoạt nhật ký truy vấn chậm trong tệp cấu hình MySQL của mình nếu bạn định sử dụng nó lâu dài. Điều này sẽ đảm bảo quá trình ghi nhật ký được tiếp tục tự động sau khi máy chủ MySQL khởi động lại

Vị trí của tệp cấu hình có thể khác nhau tùy theo phân phối nền tảng. Nó thường ở

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Khởi động lại MySQL để áp dụng các thay đổi của bạn

$ sudo service mysql restart

Nhật ký truy vấn chậm giờ đây sẽ hoạt động mỗi khi máy chủ MySQL khởi động

Tùy chỉnh nội dung nhật ký

Nhật ký thường chỉ bao gồm các truy vấn SQL loại trừ ngưỡng “chậm” và đã được gửi bởi các ứng dụng khách. Điều này loại trừ mọi hoạt động quản trị chậm có thể xảy ra, chẳng hạn như tạo chỉ mục và tối ưu hóa bảng, cũng như các truy vấn có khả năng bị chậm trong tương lai

Bạn có thể mở rộng nhật ký để bao gồm thông tin này bằng cách thực hiện các thay đổi sau đối với tệp cấu hình của mình

• SET GLOBAL long_query_time=1;

  0 – Bao gồm các câu lệnh SQL quản trị như

  SET GLOBAL long_query_time=1;

  1,

  SET GLOBAL long_query_time=1;

  2,

  SET GLOBAL long_query_time=1;

  3 và

  SET GLOBAL long_query_time=1;

  4. Điều này hiếm khi được mong muốn vì các hoạt động này thường được chạy trong các tập lệnh bảo trì và di chuyển. Tuy nhiên, cài đặt này có thể hữu ích nếu ứng dụng của bạn cũng tự động thực hiện các tác vụ này

• SET GLOBAL long_query_time=1;

  5 – Cài đặt này cho phép ghi nhật ký truy vấn chậm đối với các truy vấn được sao chép trên máy chủ bản sao. Điều này bị tắt theo mặc định. Sử dụng

  SET GLOBAL long_query_time=1;

  6 thay thế cho MySQL phiên bản 8. 0. 26 tuổi trở lên

• SET GLOBAL long_query_time=1;

  7 – Khi cài đặt này được bật, các truy vấn dự kiến ​​sẽ truy xuất tất cả các bản ghi từ bảng hoặc dạng xem đích sẽ được ghi lại, ngay cả khi chúng không loại trừ ngưỡng truy vấn chậm. Điều này có thể giúp xác định khi truy vấn thiếu chỉ mục hoặc không thể sử dụng chỉ mục đó. Các truy vấn có sẵn chỉ mục sẽ vẫn được ghi nếu chúng thiếu các ràng buộc giới hạn số lượng hàng được tìm nạp

Truy vấn ghi nhật ký không sử dụng chỉ mục có thể làm tăng đáng kể mức độ chi tiết. Có thể có những tình huống trong đó dự kiến ​​hoặc cần thiết phải quét toàn bộ chỉ mục. Các truy vấn này sẽ tiếp tục hiển thị trong nhật ký mặc dù chúng không thể được giải quyết

Bạn có thể xếp hạng các truy vấn giới hạn mà không có chỉ mục bằng cách đặt biến

SET GLOBAL long_query_time=1;

SET GLOBAL long_query_time=1;

Giải thích Nhật ký truy vấn chậm

Mỗi truy vấn đưa nó vào nhật ký truy vấn chậm sẽ hiển thị một tập hợp các dòng giống như sau

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Các dòng nhận xét phía trên truy vấn chứa thời gian thực thi truy vấn, người dùng MySQL mà máy khách đã kết nối và thống kê cung cấp thời lượng cũng như số lượng hàng được gửi. Ví dụ trên lấy 3. 5 giây để hoàn thành và xem xét hơn 320.000 hàng, trước khi chỉ gửi 5.143 cho khách hàng. Đây có thể là một dấu hiệu cho thấy các chỉ mục bị thiếu đang khiến MySQL kiểm tra quá nhiều bản ghi

Bạn có thể tùy ý đưa thêm thông tin vào nhật ký bằng cách đặt biến hệ thống

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Tệp nhật ký phải được xử lý cẩn thận vì nội dung của nó sẽ nhạy cảm. Các truy vấn được hiển thị đầy đủ mà không có bất kỳ giá trị tham số nào bị che khuất. Điều này có nghĩa là dữ liệu người dùng sẽ xuất hiện nếu bạn đang sử dụng nhật ký truy vấn chậm trên máy chủ sản xuất. Quyền truy cập phải được giới hạn đối với các nhà phát triển và quản trị viên cơ sở dữ liệu đang điều chỉnh các câu lệnh SQL

Ghi nhật ký và sao lưu truy vấn chậm

Một sự thất vọng phổ biến với nhật ký truy vấn chậm phát sinh khi bạn cũng đang sử dụng MySQLDump để tạo bản sao lưu cơ sở dữ liệu. Các truy vấn

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Bạn có thể giải quyết vấn đề này bằng cách tạm thời tắt nhật ký truy vấn chậm trước khi chạy

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Điều này sẽ loại bỏ hoạt động MySQLDump khỏi nhật ký truy vấn chậm, giúp bạn dễ dàng tập trung vào SQL được ứng dụng của bạn thực thi hơn

Bản tóm tắt

Nhật ký truy vấn chậm của MySQL là một trong những cách hiệu quả nhất để xác định nguyên nhân của các vấn đề về hiệu suất. Bắt đầu bằng cách ước tính độ trễ bạn đang gặp phải và sử dụng giá trị này làm

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Nhật ký truy vấn chậm sẽ không cho bạn biết chính xác cách khắc phục tình trạng chậm. Tuy nhiên, khả năng xem SQL chính xác mà máy chủ nhận được cho phép bạn lặp lại các câu lệnh hoạt động kém và sau đó đánh giá hiệu quả của việc tối ưu hóa. Thêm một chỉ mục hoặc thiếu ràng buộc có thể là sự khác biệt giữa một truy vấn chạm vào hàng nghìn hàng và một truy vấn hoạt động với một số ít

Nhật ký truy vấn chậm của MySQL ở đâu?

Nhật ký truy vấn chậm của MySQL là gì?

Làm cách nào để kích hoạt nhật ký MySQL?

Làm cách nào để tìm nhật ký truy vấn chậm trong máy chủ SQL?