MySQL hoặc tuyên bố chậm

Tôi có cụm cơ sở dữ liệu Phiên bản tương thích với MySQL của Amazon Aurora và tôi muốn sử dụng truy vấn CHỌN để chọn dữ liệu từ cơ sở dữ liệu của mình. Khi tôi chạy truy vấn CHỌN trên cụm DB của mình, truy vấn đang chạy chậm. Làm cách nào tôi có thể xác định và khắc phục nguyên nhân của các truy vấn CHỌN chậm?

Mô tả ngắn

Có một số lý do khiến các truy vấn CHỌN có thể chạy chậm trên Cụm CSDL tương thích với Aurora MySQL của bạn

• Tài nguyên hệ thống Amazon Relational Database Service (Amazon RDS) của bạn đã được sử dụng quá mức. Điều này có thể xảy ra do CPU cao, bộ nhớ thấp hoặc khối lượng công việc vượt quá khả năng xử lý của loại phiên bản CSDL của bạn
• Cơ sở dữ liệu đang khóa và kết quả là các sự kiện chờ đang khiến các truy vấn CHỌN hoạt động kém
• Truy vấn CHỌN đang thực hiện quét toàn bộ bảng trên các bảng lớn hoặc truy vấn thiếu các chỉ mục cần thiết
• Độ dài danh sách lịch sử InnoDB của bạn (HLL) đã tăng lên đáng kể do các giao dịch kéo dài

Nghị quyết

Giám sát tài nguyên hệ thống Amazon RDS của bạn bằng cách sử dụng số liệu

Cách tốt nhất là luôn theo dõi mức sử dụng CPU và bộ nhớ trống trên cụm Amazon Aurora của bạn. Mặc dù CPU thỉnh thoảng tăng đột biến là bình thường, nhưng CPU cao liên tục trong thời gian dài có thể khiến các truy vấn CHỌN của bạn chạy chậm. Sử dụng các công cụ sau để xác định cách thức và vị trí bạn đang sử dụng CPU của mình

1. Các chỉ số của Amazon CloudWatch là cách dễ dàng nhất để giám sát việc sử dụng CPU của bạn. Để biết thêm thông tin về những chỉ số nào có sẵn cho Aurora, hãy xem chỉ số CloudWatch cho Aurora

2. Giám sát nâng cao cung cấp cái nhìn chi tiết về các chỉ số cấp hệ điều hành ở mức độ chi tiết thấp hơn. Bảng phân tích chi tiết cho thấy các quy trình đang sử dụng CPU của bạn như thế nào

3. Thông tin chi tiết về hiệu suất xác định chính xác tải DB của bạn. Bật Thông tin chi tiết về hiệu suất cho phiên bản CSDL của bạn, sau đó kiểm tra xem tải của bạn có vượt quá vCPU tối đa không. Bạn cũng có thể theo dõi các truy vấn và SQL chịu tải của mình theo thời gian chờ và xác định người dùng gây ra thời gian chờ tối đa

Các truy vấn CHỌN cũng có thể chạy chậm do tìm đĩa. Để giảm thiểu I/O của đĩa, công cụ cơ sở dữ liệu cố gắng lưu trữ khối đã đọc từ đĩa. Điều này có nghĩa là lần tới khi cơ sở dữ liệu cần cùng một khối dữ liệu, khối đó sẽ được tìm nạp từ bộ nhớ chứ không phải từ đĩa

Sử dụng các số liệu này để kiểm tra xem bạn đang phục vụ một truy vấn cụ thể từ đĩa hay từ bộ nhớ

• Khối LượngĐọcIOPS. Số liệu này là số lượng hoạt động đọc [đĩa] mức âm lượng được lập hóa đơn. Đó là cách thực hành tốt nhất để đảm bảo rằng giá trị này càng thấp càng tốt
• Bộ nhớ đệmBộ nhớ đệmHitRatio. Số liệu này là tỷ lệ phần trăm yêu cầu mà bộ nhớ đệm phục vụ. Đó là cách tốt nhất để đảm bảo rằng giá trị này càng cao càng tốt. Nếu BufferCacheHitRatio giảm xuống và câu lệnh CHỌN của bạn chậm, thì bạn đang xử lý truy vấn từ các ổ đĩa cơ bản

Một tài nguyên quan trọng khác để xác định các câu lệnh CHỌN chậm là nhật ký truy vấn chậm. Kích hoạt ghi nhật ký truy vấn chậm cho cụm DB của bạn để ghi lại các truy vấn này và thực hiện hành động sau. Đối với MySQL5. 6 tương thích, hãy sử dụng Lược đồ hiệu suất SQL của tôi để theo dõi hiệu suất truy vấn liên tục

Xác định bế tắc và chờ sự kiện

Amazon RDS khóa dữ liệu trong cơ sở dữ liệu của bạn để chỉ một phiên người dùng có thể ghi hoặc cập nhật một hàng vào bất kỳ thời điểm nào. Bất kỳ giao dịch nào khác yêu cầu hàng này đều được giữ lại. Trong khóa dùng chung, các giao dịch ghi/cập nhật được giữ trong khi các giao dịch đọc đọc dữ liệu. Nếu một truy vấn đang chờ truy cập vào một hàng bị khóa bởi một truy vấn khác thì điều này có thể gây ra bế tắc

Để xác định bế tắc trên cơ sở dữ liệu của bạn, hãy bật tham số innodb_print_all_deadlocks trong nhóm tham số của bạn. Sau đó, theo dõi lỗi mysql. log từ bảng điều khiển RDS/CLI/API

Hoặc, đăng nhập vào MySQL bằng tài khoản quản trị viên, sau đó chạy lệnh này để xác định bế tắc từ đầu ra lệnh trong phần Bế tắc được phát hiện mới nhất

mysql> SHOW ENGINE INNODB STATUS\G;

Kiểm tra xem truy vấn của bạn có đang sử dụng chỉ mục không

Nếu truy vấn không có chỉ mục hoặc quét toàn bộ bảng thì truy vấn sẽ chạy chậm hơn. Các chỉ mục giúp tăng tốc các truy vấn CHỌN

Để kiểm tra xem truy vấn của bạn có đang sử dụng chỉ mục hay không, hãy sử dụng truy vấn EXPLAIN. Đây là một công cụ hữu ích để khắc phục sự cố truy vấn chậm. Trong đầu ra GIẢI THÍCH, hãy kiểm tra tên bảng, khóa được sử dụng và số lượng hàng được quét trong khi truy vấn. Nếu đầu ra không hiển thị bất kỳ khóa nào đang được sử dụng, thì hãy tạo một chỉ mục trên các cột được sử dụng trong mệnh đề WHERE

Nếu bảng có yêu cầu lập chỉ mục, hãy kiểm tra xem số liệu thống kê của bảng có được cập nhật không. Đảm bảo rằng số liệu thống kê chính xác có nghĩa là trình tối ưu hóa truy vấn sử dụng các chỉ mục chọn lọc nhất với số lượng chính xác. Điều này cải thiện hiệu suất truy vấn

Kiểm tra độ dài danh sách lịch sử (HLL)

InnoDB sử dụng một khái niệm gọi là Kiểm soát đồng thời nhiều phiên bản (MVCC). MVCC duy trì nhiều bản sao của cùng một bản ghi để đảm bảo tính nhất quán khi đọc. Điều này có nghĩa là khi bạn thực hiện một giao dịch, thì InnoDB sẽ xóa các bản sao cũ hơn. Nhưng khi một giao dịch không được cam kết trong một thời gian dài do sự phát triển của các phân đoạn hoàn tác, thì độ dài danh sách lịch sử (HLL) sẽ tăng lên. Độ dài danh sách lịch sử InnoDB biểu thị số lượng thay đổi chưa được xử lý

Nếu khối lượng công việc của bạn yêu cầu nhiều giao dịch mở hoặc dài hạn, thì bạn có thể thấy HLL cao trên cơ sở dữ liệu

Ghi chú. Các giao dịch dài hạn không phải là nguyên nhân duy nhất khiến HLL tăng đột biến. Ngay cả khi các luồng thanh lọc không thể theo kịp các thay đổi trên DB, HLL vẫn có thể ở mức cao

Nếu bạn không theo dõi kích thước của HLL, thì hiệu suất sẽ giảm dần theo thời gian. Kích thước ngày càng tăng của HLL cũng có thể gây ra mức tiêu thụ tài nguyên cao hơn, hiệu suất câu lệnh CHỌN chậm hơn và không nhất quán cũng như tăng dung lượng lưu trữ. Trong trường hợp cực đoan, điều này có thể dẫn đến mất cơ sở dữ liệu

Để kiểm tra độ dài danh sách lịch sử, hãy chạy lệnh sau

SHOW ENGINE INNODB STATUS;

------------ TRANSACTIONS ------------
Trx id counter 26368570695
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Đối với Aurora MySQL, do bản chất của dung lượng lưu trữ dùng chung, độ dài danh sách lịch sử ở cấp Cụm chứ không phải ở cấp phiên bản riêng lẻ. Kết nối với nhà văn của bạn và chạy truy vấn sau

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

Truy vấn này giúp bạn hiểu độ trễ của bản sao giữa các nút người đọc và nút người ghi. Nó cũng nêu chi tiết LSN cũ nhất được phiên bản DB sử dụng để đọc từ bộ lưu trữ và ID TRX dạng xem cũ nhất của phiên bản DB. Sử dụng thông tin này để kiểm tra xem một trong những trình đọc có đang giữ chế độ xem đã đọc cũ hay không (so với trạng thái InnoDB của công cụ trên trình ghi)

Ghi chú. Bắt đầu từ Aurora MySQL 1. 19 và 2. 06, bạn có thể theo dõi HLL bằng chỉ số RollbackSegmentHistoryListLength trong CloudWatch. Hoặc, trong các phiên bản cũ hơn, hãy sử dụng trx_rseg_history_len để kiểm tra HLL bằng lệnh sau

select NAME AS RollbackSegmentHistoryListLength, 
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

Nếu Thông tin chi tiết về hiệu suất được kích hoạt cho các phiên bản Aurora MySQL của bạn, thì bạn có thể kiểm tra RollbackSegmentHistoryListLength. Điều hướng đến nhà văn Thông tin chi tiết về hiệu suất và thực hiện các thao tác sau

1. Chọn Quản lý chỉ số rồi chọn Chỉ số cơ sở dữ liệu

2. Chọn chỉ số trx_rseg_history_len rồi chọn Cập nhật biểu đồ

Sử dụng các phương pháp sau để giải quyết các vấn đề với tăng trưởng HLL

• Nếu DML (ghi) gây ra sự tăng trưởng HLL. Việc hủy bỏ hoặc chấm dứt tuyên bố này liên quan đến việc khôi phục giao dịch bị gián đoạn. Điều này mất một lượng thời gian đáng kể vì tất cả các bản cập nhật được thực hiện cho đến thời điểm này đang được khôi phục
• Nếu READ gây ra sự tăng trưởng HLL. Chấm dứt truy vấn bằng mysql. rds_kill_query
• Tùy thuộc vào thời gian chạy truy vấn, hãy làm việc với DBA của bạn để kiểm tra xem bạn có thể chấm dứt truy vấn bằng thủ tục được lưu trữ hay không

Đó là cách tốt nhất để tránh tăng trưởng bằng cách theo dõi HLL bằng các phương pháp này và để tránh các giao dịch mở hoặc chạy dài trên cơ sở dữ liệu. Ngoài ra, cách tốt nhất là cam kết dữ liệu theo các đợt nhỏ hơn

Quan trọng. Không khởi động lại cụm DB hoặc phiên bản. Sẽ hiệu quả hơn khi xóa HLL khi nó có thể truy cập dữ liệu trong bộ nhớ trong vùng đệm. Nếu bạn khởi động lại cơ sở dữ liệu, bộ nhớ cache của trang còn tồn tại có thể bị mất. Khi điều đó xảy ra, các trang dữ liệu từ ổ đĩa cụm phải được đọc để xóa HLL. Điều này chậm hơn so với thực hiện trong bộ nhớ và gây thêm chi phí thanh toán I/O

Tại sao truy vấn MySQL của tôi quá chậm?

Làm cách nào để cải thiện tốc độ truy vấn MySQL?

Truy vấn nào nhanh hơn trong MySQL?

Điều gì làm chậm truy vấn SQL?