Tại sao truy vấn của tôi chạy chậm trong Amazon RDS cho MySQL?
Cập nhật lần cuối: 2021-10-27
Tôi đang cố gắng khắc phục sự cố các truy vấn chạy chậm trong Dịch vụ cơ sở dữ liệu quan hệ Amazon [Amazon RDS] cho MySQL. Tại sao điều này xảy ra và làm thế nào tôi có thể cải thiện hiệu suất truy vấn?
Mô tả ngắn
Để cải thiện hiệu suất truy vấn, hãy xem xét các yếu tố sau:
- Sử dụng tài nguyên [như CPU, bộ nhớ và lưu trữ]
- Phân tích khối lượng công việc
- Truy vấn điều chỉnh và giám sát
Nghị quyết
Sử dụng tài nguyên [như CPU, bộ nhớ và lưu trữ]
Để hiểu nguyên nhân gốc rễ của bất kỳ vấn đề hiệu suất cơ sở dữ liệu nào, hãy kiểm tra tất cả các tài nguyên trên toàn máy chủ mà phiên bản của bạn đang sử dụng. Bạn có thể theo dõi khối lượng công việc và khảo sát của mình khi hiệu suất truy vấn là bình thường so với khi truy vấn bắt đầu mất quá nhiều thời gian để chạy.
Sử dụng các số liệu Amazon CloudWatch để theo dõi các tài nguyên này trong một khoảng thời gian bao gồm nhiều ngày khi hiệu suất được coi là bình thường. Bạn cũng có thể xem các số liệu hiệu suất trong bảng điều khiển Amazon RDS để giám sát hiệu suất cơ sở dữ liệu.
Bạn cũng có thể kiểm tra trạng thái thể hiện của mình để xác định bất kỳ quy trình hoạt động hoặc theo lịch trình nào khác có thể ảnh hưởng đến hiệu suất cơ sở dữ liệu. Trong bảng điều khiển Amazon RDS, hãy kiểm tra các sự kiện xảy ra trong khi cơ sở dữ liệu của bạn hoạt động kém.
Phân tích khối lượng công việc
Để phân tích khối lượng công việc góp phần tiêu thụ tài nguyên, hãy sử dụng những hiểu biết hiệu suất. Những hiểu biết về hiệu suất sẽ cung cấp một phân tích đồ họa về tất cả các truy vấn của bạn và bất kỳ sự chờ đợi nào đang góp phần tăng mức tiêu thụ tài nguyên.
Hiệu suất Insights sử dụng khối lượng công việc làm số liệu chính của nó thay vì sử dụng số lượng VCPU cho một thể hiện. Nếu khối lượng công việc hiện tại của bạn vượt quá giới hạn VCPU, thì máy chủ của bạn bị quá tải. Nếu máy chủ của bạn bị quá tải, hãy kiểm tra các truy vấn đóng góp vào khối lượng công việc của bạn và xác định các cách để tối ưu hóa các truy vấn của bạn. Sau đó, xem xét sửa đổi lớp thể hiện của bạn.
Khối lượng công việc hiểu biết hiệu suất của bạn cũng có thể được chia thành các sự kiện chờ đợi. Điều tra sự chờ đợi tiêu thụ tài nguyên hàng đầu bằng cách cắt tải DB theo số lượng sự kiện chờ đợi. Các dải màu dày hơn trong biểu đồ tải cho thấy các loại chờ đóng góp nhiều nhất vào khối lượng công việc. Để biết thêm thông tin, hãy xem tải DB theo dõi với những hiểu biết hiệu suất trên Amazon RDS.
Bạn cũng có thể sử dụng nhật ký truy vấn chậm [được bật trong nhóm tham số tùy chỉnh của bạn] để xác định các truy vấn chạy chậm.
Sau đó, bạn có thể sử dụng các số liệu Amazon CloudWatch để kiểm tra xem lượng công việc được thực hiện trên trường hợp của bạn có tăng không. Ví dụ:
- Kết nối cơ sở dữ liệu: Số lượng phiên khách được kết nối với thể hiện DB.
- Mạng nhận được thông lượng [MB/giây]: Tốc độ lưu lượng mạng đến và từ thể hiện DB.
- Viết và đọc thông lượng: Số lượng megabyte trung bình đọc từ hoặc viết sang đĩa mỗi giây.
- Viết và đọc độ trễ: Thời gian trung bình cho hoạt động đọc hoặc viết tính bằng mili giây.
- IOPS [Đọc và Viết]: Số lượng hoạt động đọc hoặc ghi trung bình mỗi giây.
- Không gian lưu trữ miễn phí [MB]: Lượng không gian đĩa hiện không được sử dụng bởi ví dụ DB.
Các số liệu độ trễ cho biết lượng thời gian thực hiện để hoàn thành hoạt động I/O Disk Read Read hoặc ghi. Mối tương quan của các số liệu độ trễ với các kết nối cơ sở dữ liệu tăng hoặc các số liệu thông lượng có thể chỉ ra khối lượng công việc là lý do để thực hiện truy vấn chậm. Để biết thêm thông tin về việc xác định các yếu tố sử dụng, hãy xem làm thế nào để tôi xem những gì đang sử dụng lưu trữ trong phiên bản DB của Amazon RDS đang chạy MySQL?
Bạn cũng có thể sử dụng giám sát nâng cao để truy xuất danh sách các hệ điều hành liên quan đến khối lượng công việc của bạn và các số liệu hệ thống cơ bản. Theo mặc định, khoảng thời gian giám sát để tăng cường giám sát là 60 giây. Đó là một thực tế tốt nhất để đặt số này thành 1-5 giây cho các điểm dữ liệu chi tiết hơn.
Tối ưu hóa truy vấn
Sau khi truy vấn chạy dài đã được xác định từ nhật ký truy vấn chậm hoặc hiểu biết hiệu suất, hãy xem xét các cách để cải thiện hiệu suất truy vấn của bạn. Để điều chỉnh một truy vấn, hãy xem xét các phương pháp sau:
- Để tìm các trạng thái nơi dành nhiều thời gian nhất, hãy hồ sơ các truy vấn chậm hơn của bạn. Để biết thêm thông tin, hãy xem Hiển thị tuyên bố hồ sơ trên trang web MySQL.
- Chạy lệnh Hiển thị toàn bộ quy trình cùng với giám sát nâng cao. Khi được sử dụng cùng nhau, bạn có thể xem lại danh sách các hoạt động hiện đang được thực hiện trên máy chủ cơ sở dữ liệu.SHOW FULL PROCESSLIST command along with Enhanced Monitoring. When used together, you can review the list of operations that are currently being performed on the database server.
- Sử dụng lệnh hiển thị lệnh trạng thái InnoDB để có được thông tin về xử lý giao dịch, chờ đợi và bế tắc.SHOW ENGINE INNODB STATUS command to obtain nformation about transaction processing, waits, and deadlocks.
- Tìm bất kỳ truy vấn chặn và giải quyết việc chặn. Để biết thêm thông tin, hãy xem tại sao một truy vấn cho Amazon RDS của tôi cho phiên bản DB MySQL bị chặn khi không có phiên hoạt động nào khác?
- Xuất bản nhật ký MySQL lên Amazon CloudWatch. Nhật ký được xoay mỗi giờ để duy trì 2% ngưỡng không gian lưu trữ được phân bổ. Sau đó, chúng bị thanh trừng nếu chúng hơn hai tuần tuổi hoặc nếu kích thước kết hợp của chúng vượt quá ngưỡng 2%.
- Đặt báo thức Amazon CloudWatch để bạn có thể theo dõi việc sử dụng tài nguyên của mình và được cảnh báo bất cứ khi nào vượt quá ngưỡng.
- Tìm kế hoạch thực thi cho truy vấn và kiểm tra xem truy vấn có sử dụng các chỉ mục phù hợp hay không. Bạn có thể tối ưu hóa truy vấn của mình bằng cách sử dụng kế hoạch giải thích và xem xét chi tiết về cách MySQL chạy truy vấn.
- Giữ cho số liệu thống kê truy vấn của bạn được cập nhật với câu lệnh bảng phân tích. Tối ưu hóa truy vấn đôi khi có thể chọn các kế hoạch thực hiện kém vì số liệu thống kê lỗi thời. Điều này có thể dẫn đến hiệu suất kém của một truy vấn vì ước tính cardinality không chính xác của cả bảng và chỉ mục.
- MySQL 8.0 hiện sử dụng một câu lệnh phân tích giải thích. Tuyên bố phân tích giải thích là một công cụ định hình cho các truy vấn của bạn cho bạn thấy MySQL dành thời gian cho truy vấn của bạn và tại sao. Với giải thích Phân tích, các kế hoạch, công cụ của MySQL và chạy truy vấn trong khi đếm các hàng và đo thời gian dành cho các điểm khác nhau của kế hoạch thực hiện. Khi truy vấn hoàn thành, giải thích Phân tích sẽ in kế hoạch và các phép đo của nó thay vì kết quả truy vấn.EXPLAIN ANALYZE statement is a profiling tool for your queries that shows you where MySQL spends time on your query and why. With EXPLAIN ANALYZE, MySQL plans, instruments, and runs the query while counting rows and measuring the time spent at various points of the execution plan. When the query completes, EXPLAIN ANALYZE will print the plan and its measurements instead of the query result.
- Trong phiên bản MySQL 8, hãy lưu ý rằng các chờ khóa được liệt kê trong lược đồ hiệu suất của bảng data_lock_waits. Ví dụ:data_lock_waits table. For example:
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id;Bài viết này có giúp ích không?
Bạn có cần thanh toán hoặc hỗ trợ kỹ thuật?
Hỗ trợ AWS cho Internet Explorer kết thúc vào ngày 31/07/2022. Các trình duyệt được hỗ trợ là Chrome, Firefox, Edge và Safari. Tìm hiểu thêm »