Bạn có thể tự hỏi liệu người dùng của bạn có thể làm chậm hoặc thậm chí DOS cơ sở dữ liệu MySQL của bạn với một yêu cầu không may mắn hay không. Nếu nó chỉ chứa sai số lượng điều kiện trong biểu mẫu tìm kiếm nâng cao của bạn, kích hoạt quá trình quét toàn bộ bảng trên tập dữ liệu lớn hơn nhiều so với bạn dự đoán thì có thể họ có thể
Nếu bạn cung cấp chức năng tìm kiếm nâng cao, thống kê tổng hợp, báo cáo hoặc xuất dữ liệu trong ứng dụng của mình, thì truy vấn chạy dài không thường xuyên là một phần của thiết kế. Bạn có thể chưa bị quá tải và chậm cơ sở dữ liệu, chỉ vì người dùng của bạn không thường xuyên sử dụng các tính năng này
Cho đến MySQL 5. 7, bạn không có cách nào dễ dàng để tự bảo vệ mình khỏi một lượng lớn người dùng DDoS cơ sở dữ liệu của bạn bằng các truy vấn chạy dài. Ngoài ra, PHP max_execution_time
không áp dụng cho việc chờ các cuộc gọi mạng như được hiển thị trong bài đăng trên blog trước đây trên max_execution_time. Điều này có nghĩa là các tập lệnh PHP và kết nối MySQL của bạn có thể bị chặn miễn là truy vấn chạy, trường hợp xấu nhất là vài phút hoặc vài giờ
Khi bạn Google vấn đề này, giải pháp được đề xuất là chạy một daemon hoặc cronjob theo dõi danh sách quy trình và chỉ loại bỏ các truy vấn chạy dài. Điều này chắc chắn hoạt động, nhưng bạn không biết ai đang chạy truy vấn và tại sao, điều này ngăn bạn áp dụng thời gian chờ chi tiết hơn dựa trên ngữ cảnh [ví dụ: web so với quy trình nền]
Với MySQL5. 7, giờ đây bạn có thể sử dụng gợi ý truy vấn trình tối ưu hóa mới cho các truy vấn SELECT
trong Mili giây
SELECT /*+ MAX_EXECUTION_TIME[1000] */ status, count[*] FROM articles GROUP BY status ORDER BY status;
Hoặc bạn có thể thiết lập một
SET SESSION MAX_EXECUTION_TIME=2000; SET GLOBAL MAX_EXECUTION_TIME=2000;
Thời gian chờ chỉ áp dụng cho các truy vấn CHỌN chỉ đọc
Nếu truy vấn của bạn mất nhiều thời gian, nó sẽ không thành công với lỗi sau
ERROR 3024 [HY000]: Query execution was interrupted, maximum statement execution time exceeded
Bạn có thể xử lý lỗi này một cách tập trung trong ứng dụng của mình và hiển thị thông báo cho người dùng rằng phản hồi mất quá nhiều thời gian và đã bị hủy bỏ
Sử dụng phần mềm Theo dõi ngoại lệ [chẳng hạn như Tideways], bạn có thể ghi nhật ký người dùng, giao dịch hoặc trang nào đang khiến các truy vấn này bị hủy bỏ, tần suất chúng xảy ra và liệu hiệu suất truy vấn có thể được cải thiện hay không
Trong bài đăng này, chúng ta sẽ tìm hiểu về những tùy chọn khả dụng nếu bạn cần tự động loại bỏ các truy vấn chạy dài
mysql 5. 7 trở lên Đối với câu lệnh SELECT
Bắt đầu từ MySQL 5. 7. 4 có hỗ trợ cho thời gian chờ tự động của các câu lệnh CHỌN chỉ đọc [thảo luận sau đây giả định 5. 7. 8 trở lên vì tính năng này đã thay đổi tên và cú pháp tùy chọn/gợi ý]
Có hai cách để đặt giới hạn trên về thời gian cho phép thực thi câu lệnh CHỌN chỉ đọc
- tùy chọn max_execution_time
- gợi ý trình tối ưu hóa MAX_EXECUTION_TIME[] cho các câu lệnh CHỌN
Những điều sau đây áp dụng
- Chỉ các câu lệnh SELECT chỉ đọc mới bị ảnh hưởng
- Chỉ cấp cao nhất [i. e. không phụ truy vấn] bị ảnh hưởng
- Các câu lệnh CHỌN bên trong các chương trình được lưu trữ không bị ảnh hưởng [gợi ý MAX_EXECUTION_TIME[] không được hỗ trợ trong các chương trình được lưu trữ]
Hai tùy chọn này sẽ được thảo luận trong phần sau
Tùy chọn max_statement_time
Tùy chọn max_execution_time chỉ định thời gian chờ cho các câu lệnh CHỌN chỉ đọc nếu gợi ý trình tối ưu hóa MAX_EXECUTION_TIME[] không được thêm vào. Giá trị tính bằng mili giây
Tùy chọn tồn tại ở cả cấp độ TOÀN CẦU và PHIÊN và có thể thay đổi linh hoạt. Giá trị TOÀN CẦU được sử dụng làm giá trị mặc định cho giá trị SESSION. Giá trị bằng 0 có nghĩa là không có thời gian chờ nào có hiệu lực
mysql> connect Connection id: 5 Current database: *** NONE *** mysql> SELECT @@global.max_statement_time, @@session.max_statement_time; +-----------------------------+------------------------------+ | @@global.max_statement_time | @@session.max_statement_time | +-----------------------------+------------------------------+ | 1000 | 1000 | +-----------------------------+------------------------------+ 1 row in set [0.00 sec]
mysql> SET SESSION max_statement_time = 2000; -- 2 seconds Query OK, 0 rows affected [0.00 sec] mysql> SELECT @@global.max_statement_time, @@session.max_statement_time; +-----------------------------+------------------------------+ | @@global.max_statement_time | @@session.max_statement_time | +-----------------------------+------------------------------+ | 1000 | 2000 | +-----------------------------+------------------------------+ 1 row in set [0.00 sec]
Một truy vấn lấy 1. 5 giây thành công
mysql> SELECT SLEEP[1.5]; +------------+ | SLEEP[1.5] | +------------+ | 0 | +------------+ 1 row in set [1.51 sec]
Truy vấn mất hơn 2 giây không thành công
mysql> SELECT SLEEP[5]; +----------+ | SLEEP[5] | +----------+ | 1 | +----------+ 1 row in set [2.02 sec]
Chỉ các câu lệnh SELECT bị ảnh hưởng
mysql> DO SLEEP[5]; Query OK, 0 rows affected [5.00 sec]
Mệnh đề MAX_STATEMENT_TIME cho câu lệnh SELECT
Có thể sử dụng gợi ý tối ưu hóa MAX_EXECUTION_TIME[] để thay đổi thời gian chờ cho một truy vấn cụ thể. Ví dụ: nếu max_execution_time được đặt thành 1 giây, nhưng bạn biết một truy vấn nhất định mất 5 giây, thì bạn có thể thay đổi thời gian chờ
________số 8_______
mysql> SELECT /*+ MAX_EXECUTION_TIME[6000] */ /* may take up to 5+ seconds - don't kill */ SLEEP[5]; +----------+ | SLEEP[5] | +----------+ | 0 | +----------+ 1 row in set [5.00 sec]
hoặc bạn có thể sử dụng nó để giảm thời gian chờ
mysql> SELECT /*+ MAX_EXECUTION_TIME[500] */ /* may take up to 5+ seconds - don't kill */ SLEEP[5]; +----------+ | SLEEP[5] | +----------+ | 1 | +----------+ 1 row in set [0.50 sec
Tất cả các phiên bản và tất cả các loại tuyên bố
Quan trọng. Nếu bạn đang thay đổi dữ liệu trong các bảng không phải giao dịch, chẳng hạn như bảng MyISAM, thì việc tắt truy vấn sẽ không khôi phục giao dịch. Điều này có nghĩa là dữ liệu của bạn sẽ ở trạng thái không xác định khi tác động của câu lệnh được áp dụng một phần. Nếu bạn sử dụng bản sao, bạn sẽ cần xây dựng lại các bản sao. Nói chung, đối với các công cụ phi giao dịch, tốt hơn là để câu lệnh chạy
Trước MySQL 5. 7. 4, không có cách tích hợp nào để tự động loại bỏ các truy vấn chạy dài. Thay vào đó, một trong hai giải pháp sau đây có thể được sử dụng
- Viết một tập lệnh kiểm tra định kỳ xem có bất kỳ truy vấn nào đang chạy dài hay không và loại bỏ chúng nếu cần
- Viết một thủ tục được lưu trữ có thể được thực thi bằng bộ lập lịch sự kiện. Tùy chọn này chỉ khả dụng trong MySQL 5. 1 trở lên vì các phiên bản trước đó không hỗ trợ cho các sự kiện
Cả hai hoạt động theo những cách rất giống nhau. Đầu tiên có lợi thế là bạn có thể chọn ngôn ngữ lập trình mà bạn chọn trong khi giải pháp thủ tục/sự kiện được lưu trữ giữ mọi thứ bên trong cơ sở dữ liệu
Ghi chú. Các giao dịch InnoDB đã sửa đổi một số lượng lớn hàng sẽ mất một lúc để khôi phục. Theo nguyên tắc thông thường, bạn nên mong đợi quá trình khôi phục sẽ mất gấp 10 lần miễn là thực hiện các sửa đổi. Vì vậy, bạn muốn xem xét số lượng mục hoàn tác cho một giao dịch trước khi giết nó
Để thực hiện kiểm tra, bạn sẽ cần sử dụng danh sách quy trình. Trong MySQL5. 1 trở lên, bạn có thể sử dụng information_schema. Bảng PROCESSLIST cho phép lấy danh sách quy trình từ truy vấn CHỌN tiêu chuẩn cần thiết nếu giải pháp được triển khai bên trong một thủ tục được lưu trữ. Trong MySQL5. 6 trở lên, performance_schema. bảng chủ đề có thể được sử dụng [và nói chung được ưa thích hơn vì nó yêu cầu ít khóa hơn SHOW PROCESSLIST hoặc information_schema. DANH SÁCH QUY TRÌNH]
Đối với InnoDB, bạn cũng có thể quan tâm đến các giao dịch dài hạn. Chúng có thể được theo dõi thông qua INNODB_TRX trong Lược đồ thông tin. Bảng INNODB_TRX bao gồm thời điểm bắt đầu giao dịch
Bạn cũng có thể muốn xem xét liệu có bất kỳ truy vấn nào khác đang chờ khóa do truy vấn chạy dài hay không. Đối với các bảng InnoDB, có thể dễ dàng kiểm tra điều này bằng cách sử dụng các bảng INNODB_LOCK_WAITS và INNODB_TRX trong Lược đồ thông tin. Các bảng lược đồ thông tin InnoDB có sẵn bắt đầu từ MySQL 5. 1 với Plugin InnoDB và MySQL 5. 5 trở lên
Mặc dù các truy vấn và giao dịch chạy dài nói chung là không tốt vì chúng có thể chặn các kết nối khác và tăng mức sử dụng tài nguyên, một số truy vấn chắc chắn sẽ mất nhiều thời gian, ví dụ: tạo lại bảng của các bảng lớn, nhập hàng loạt, v.v. Vì vậy, bạn có thể muốn xem xét một cách để gắn cờ các truy vấn mà bạn không muốn chấm dứt;
Ghi chú. Hãy chắc chắn rằng bạn kiểm tra giải pháp của bạn. Các tài liệu tham khảo ở trên chỉ là ví dụ và có nghĩa là đề xuất cách bắt đầu và không nên được coi là giải pháp sẵn sàng sản xuất