Hướng dẫn why does mysql query take so long to execute? - tại sao truy vấn mysql mất quá nhiều thời gian để thực thi?

Các truy vấn/báo cáo/giao dịch chạy dài đôi khi không thể tránh khỏi trong môi trường MySQL. Trong một số trường hợp, một truy vấn dài có thể là chất xúc tác cho một sự kiện thảm khốc. Nếu bạn quan tâm đến cơ sở dữ liệu của mình, tối ưu hóa hiệu suất truy vấn và phát hiện các truy vấn chạy dài phải được thực hiện thường xuyên. Mọi thứ trở nên khó khăn hơn mặc dù khi nhiều trường hợp trong một nhóm hoặc cụm có liên quan.

Khi xử lý nhiều nút, các tác vụ lặp đi lặp lại để kiểm tra mọi nút duy nhất là thứ mà chúng ta phải tránh. ClusterControl giám sát nhiều khía cạnh của máy chủ cơ sở dữ liệu của bạn, bao gồm cả truy vấn. ClusterControl tổng hợp tất cả các thông tin liên quan đến truy vấn từ tất cả các nút trong nhóm hoặc cụm để cung cấp một cái nhìn tập trung về khối lượng công việc. Ngay có một cách tuyệt vời để hiểu toàn bộ cụm của bạn với nỗ lực tối thiểu.

Trong bài đăng trên blog này, chúng tôi chỉ cho bạn cách phát hiện các truy vấn chạy dài MySQL bằng cách sử dụng ClusterControl.

Tại sao một truy vấn mất nhiều thời gian hơn?

Trước hết, chúng ta phải biết bản chất của truy vấn, cho dù đó là một truy vấn dài hay chạy ngắn. Một số hoạt động phân tích và hàng loạt được cho là các truy vấn dài, vì vậy chúng tôi có thể bỏ qua chúng ngay bây giờ. Ngoài ra, tùy thuộc vào kích thước bảng, sửa đổi cấu trúc bảng với lệnh thay đổi có thể là một hoạt động chạy dài.

Đối với một giao dịch ngắn, nó nên được thực hiện nhanh nhất có thể, thường là trong một vấn đề dưới mức. Càng ngắn càng tốt. Điều này đi kèm với một bộ các quy tắc thực hành tốt nhất truy vấn mà người dùng phải tuân theo, như sử dụng chỉ mục thích hợp trong câu lệnh hoặc tham gia câu lệnh, sử dụng công cụ lưu trữ phù hợp, chọn các loại dữ liệu phù hợp, lên lịch hoạt động hàng loạt trong giờ cao điểm, giảm tải phân tích /Báo cáo lưu lượng truy cập cho các bản sao chuyên dụng, và như vậy.

Có một số điều có thể khiến một truy vấn mất nhiều thời gian hơn để thực hiện:

• Truy vấn không hiệu quả-Sử dụng các cột không chỉ số trong khi tra cứu hoặc nối, do đó MySQL mất nhiều thời gian hơn để phù hợp với điều kiện.
• Khóa bảng - Bảng bị khóa, bằng khóa toàn cầu hoặc khóa bảng rõ ràng khi truy vấn đang cố gắng truy cập nó.
• BEDLOCK - Một truy vấn đang chờ để truy cập cùng các hàng bị khóa bởi một truy vấn khác.
• Bộ dữ liệu không phù hợp với RAM - nếu dữ liệu tập hợp làm việc của bạn phù hợp với bộ đệm đó, thì chọn các truy vấn thường sẽ tương đối nhanh.
• Tài nguyên phần cứng dưới mức tối ưu - Đây có thể là đĩa chậm, xây dựng lại RAID, mạng bão hòa, v.v.
• Hoạt động bảo trì - Chạy MySQLDump có thể mang một lượng lớn dữ liệu không được sử dụng vào nhóm bộ đệm, đồng thời dữ liệu (có khả năng hữu ích) đã bị đuổi và xả vào đĩa.

Danh sách trên nhấn mạnh nó không chỉ là chính truy vấn gây ra tất cả các loại vấn đề. Có rất nhiều lý do đòi hỏi phải xem xét các khía cạnh khác nhau của máy chủ MySQL. Trong một số trường hợp xấu hơn, một truy vấn chạy dài có thể gây ra sự gián đoạn toàn bộ dịch vụ như máy chủ xuống, sự cố máy chủ và kết nối tối đa. Nếu bạn thấy một truy vấn mất nhiều thời gian hơn bình thường để thực hiện, hãy điều tra nó.

Làm thế nào để kiểm tra?

Danh sách xử lý

MySQL cung cấp một số công cụ tích hợp để kiểm tra giao dịch chạy dài. Trước hết, hiển thị danh sách xử lý hoặc hiển thị các lệnh danh sách xử lý đầy đủ có thể hiển thị các truy vấn đang chạy trong thời gian thực. Dưới đây là ảnh chụp màn hình của tính năng RusterControl Run Rusing Truy vấn, tương tự như hiển thị lệnh Danh sách xử lý đầy đủ (nhưng ClusterControl tổng hợp tất cả quá trình thành một chế độ xem cho tất cả các nút trong cụm):

Như bạn có thể thấy, chúng ta có thể ngay lập tức thấy truy vấn tấn công ngay từ đầu ra. Nhưng chúng ta thường nhìn chằm chằm vào các quá trình đó như thế nào? Điều này chỉ hữu ích nếu bạn biết về giao dịch chạy dài. Mặt khác, bạn sẽ không biết cho đến khi có điều gì đó xảy ra - như các kết nối đang chồng chất hoặc máy chủ ngày càng chậm hơn bình thường.

Nhật ký truy vấn chậm

Nhật ký truy vấn chậm nắm bắt các truy vấn chậm (các câu lệnh SQL mất nhiều hơn long_query_time giây để thực thi) hoặc các truy vấn không sử dụng các chỉ mục để tra cứu (log_queries_not_using_indexes). Tính năng này không được bật theo mặc định và để bật nó chỉ cần đặt các dòng sau và khởi động lại máy chủ MySQL:

[mysqld]
slow_query_log=1
long_query_time=0.1
log_queries_not_using_indexes=1

Nhật ký truy vấn chậm có thể được sử dụng để tìm các truy vấn mất nhiều thời gian để thực hiện và do đó là ứng cử viên để tối ưu hóa. Tuy nhiên, việc kiểm tra nhật ký truy vấn chậm dài có thể là một nhiệm vụ tốn thời gian. Có các công cụ để phân tích các tệp nhật ký truy vấn chậm MySQL và tóm tắt nội dung của chúng như mysqldumpslow, pt-query-digest hoặc clusterControl truy vấn hàng đầu.

Các truy vấn hàng đầu của ClusterControl tóm tắt truy vấn chậm bằng hai phương thức - log truy vấn chậm MySQL hoặc lược đồ hiệu suất:

Bạn có thể dễ dàng thấy một bản tóm tắt về các bản ghi câu lệnh được chuẩn hóa, được sắp xếp dựa trên một số tiêu chí:

• Chủ nhà
• Xảy ra
• Tổng thời gian thực hiện
• Thời gian thực hiện tối đa
• Thời gian thực hiện trung bình
• Thời gian sai lệch tiêu chuẩn

Chúng tôi đã đề cập đến tính năng này rất chi tiết trong bài đăng trên blog này, cách sử dụng màn hình truy vấn ClusterControl cho máy chủ MySQL, MariaDB và Percona.

Lược đồ hiệu suất

Lược đồ hiệu suất là một công cụ tuyệt vời có sẵn để giám sát nội bộ máy chủ MySQL và chi tiết thực thi ở cấp độ thấp hơn. Các bảng sau trong lược đồ hiệu suất có thể được sử dụng để tìm các truy vấn chậm:

• events_statements_current
• events_statements_history
• events_statements_history_long
• events_statements_summary_by_digest
• events_statements_summary_by_user_by_event_name
• events_statements_summary_by_host_by_event_name

MySQL 5.7.7 trở lên bao gồm lược đồ SYS, một tập hợp các đối tượng giúp DBA và các nhà phát triển giải thích dữ liệu được thu thập bởi lược đồ hiệu suất thành dạng dễ hiểu hơn. Các đối tượng lược đồ SYS có thể được sử dụng để điều chỉnh và chẩn đoán điển hình.

ClusterControl cung cấp các cố vấn, là những chương trình nhỏ mà bạn có thể viết bằng DSL ClusterControl (tương tự như JavaScript) để mở rộng khả năng giám sát ClusterControl tùy chỉnh theo nhu cầu của bạn. Có một số tập lệnh được bao gồm dựa trên lược đồ hiệu suất mà bạn có thể sử dụng để theo dõi hiệu suất truy vấn như I/O Wait, LOCK WAID WORTER, v.v. Ví dụ: Quản lý -> Nhà phát triển Studio, truy cập S9S -> MySQL -> P_S -> TOP_TABLES_BY_IOWAIT.JS và nhấp vào nút Biên dịch và chạy. Bạn sẽ thấy tab đầu ra trong tin nhắn cho 10 bảng hàng đầu được sắp xếp theo I/O chờ cho mỗi máy chủ:

Có một số tập lệnh mà bạn có thể sử dụng để hiểu thông tin cấp thấp ở đâu và tại sao sự chậm chạp xảy ra như top_tables_by_lockwait.js, TOP_ACCEDY_DB_FILES.JS, v.v.

ClusterControl - Phát hiện và cảnh báo cho các truy vấn chạy dài

Với ClusterControl, bạn sẽ nhận được thêm các tính năng mạnh mẽ mà bạn đã giành được trong bản cài đặt MySQL tiêu chuẩn. ClusterControl có thể được cấu hình để chủ động theo dõi các quy trình đang chạy và đưa ra báo động và gửi thông báo cho người dùng nếu vượt quá ngưỡng truy vấn dài. Điều này có thể được cấu hình bằng cách sử dụng cấu hình thời gian chạy trong Cài đặt:

Đối với pre1.7.1, giá trị mặc định cho query_monitor_alert_long_rasty_query là sai. Chúng tôi khuyến khích người dùng bật điều này bằng cách đặt nó thành 1 (Đúng). Để làm cho nó kiên trì, hãy thêm dòng sau vào /etc/cmon.d/cmon_x.cnf:/etc/cmon.d/cmon_X.cnf:

query_monitor_alert_long_running_query=1
query_monitor_long_running_query_ms=30000

Bất kỳ thay đổi nào được thực hiện trong cấu hình thời gian chạy được áp dụng ngay lập tức và không cần khởi động lại. Bạn sẽ thấy một cái gì đó như thế này trong phần báo thức nếu một truy vấn vượt quá các ngưỡng 30000ms (30 giây):

Nếu bạn định cấu hình các cài đặt người nhận thư dưới dạng phân phối của DBComponent cộng với danh mục mức độ nghiêm trọng nghiêm trọng (như được hiển thị trong ảnh chụp màn hình sau):

Bạn sẽ nhận được một bản sao của báo thức này trong email của bạn. Nếu không, nó có thể được chuyển tiếp thủ công bằng cách nhấp vào nút Gửi email.

Hơn nữa, bạn có thể lọc ra bất kỳ loại tài nguyên nào phù hợp với các tiêu chí nhất định với biểu thức thông thường (Regex). Ví dụ: nếu bạn muốn ClusterControl phát hiện truy vấn chạy dài cho ba người dùng MySQL có tên là ‘SBTest,‘ MyShop, và ‘DB_USER1, thì điều này nên làm:

Bất kỳ thay đổi nào được thực hiện trong cấu hình thời gian chạy được áp dụng ngay lập tức và không cần khởi động lại.

Ngoài ra, ClusterControl sẽ liệt kê tất cả các giao dịch bế tắc cùng với trạng thái InnoDB khi nó xảy ra theo hiệu suất -> Nhật ký giao dịch:

Tính năng này không được bật theo mặc định, do phát hiện bế tắc sẽ ảnh hưởng đến việc sử dụng CPU trên các nút cơ sở dữ liệu. Để kích hoạt nó, chỉ cần đánh dấu vào hộp kiểm Viết nhật ký giao dịch cho phép và chỉ định khoảng thời gian mà bạn muốn. Để làm cho nó tồn tại, hãy thêm biến có giá trị tính bằng giây bên trong /etc/cmon.d/cmon_x.cnf:/etc/cmon.d/cmon_X.cnf:

db_deadlock_check_interval=30

Tương tự, nếu bạn muốn kiểm tra trạng thái Innodb, chỉ cần chuyển đến hiệu suất -> trạng thái Innodb và chọn máy chủ MySQL từ thả xuống. Ví dụ:

Ở đó chúng tôi đi - tất cả các thông tin cần thiết đều có thể dễ dàng lấy lại trong một vài lần nhấp.

Bản tóm tắt

Các giao dịch chạy dài có thể dẫn đến suy thoái hiệu suất, máy chủ xuống, kết nối tối đa và bế tắc. Với ClusterControl, bạn có thể phát hiện các truy vấn chạy dài trực tiếp từ UI, mà không cần kiểm tra từng nút MySQL trong cụm.

Đăng ký để có được nội dung tốt nhất và mới nhất của chúng tôi

Làm thế nào tôi có thể tăng tốc độ thực thi truy vấn mysql?

Tại sao truy vấn SQL mất quá nhiều thời gian để thực hiện?

Truy vấn MySQL nên mất bao lâu?

Làm cách nào để dừng một truy vấn dài trong MySQL?