Làm cách nào bạn có thể cho biết các vấn đề về hiệu suất của MySQL với các truy vấn chậm?
|
Bất chấp những nỗ lực hết sức của bạn, khi các bảng tăng kích thước và ngày càng có nhiều người dùng truy cập trực tuyến, hiệu suất chậm chạp của MySQL cuối cùng cũng gây ra cái đầu xấu xí của nó. Khi điều đó xảy ra, bạn có thể muốn bỏ qua nó như một phần của công việc kinh doanh. Đừng. Bằng cách hiểu các cơ chế đằng sau sự chậm trễ của cơ sở dữ liệu và bằng cách giải quyết vấn đề một cách có phương pháp, bạn có thể xác định (các) thủ phạm và khắc phục sự cố kịp thời, trước khi doanh nghiệp của bạn chịu tổn thất đáng kể
Mục đích của blog này là để hỗ trợ bạn xác định (các) nguyên nhân của các vấn đề về hiệu suất cơ sở dữ liệu MySQL bằng một số công cụ tích hợp Show
Tại sao hiệu suất cơ sở dữ liệu chậm lạiKhông có gì bí mật khi hiệu suất cơ sở dữ liệu có xu hướng giảm theo thời gian. Mặc dù thật dễ dàng để chỉ ra số lượng người dùng đồng thời, quét bảng và phát triển bảng, nhưng thực tế phức tạp hơn thế. Lý do phổ biến nhất khiến hiệu suất cơ sở dữ liệu chậm dựa trên “phương trình” này (số lượng người dùng) x (kích thước cơ sở dữ liệu) x (số lượng bảng/lượt xem) x (số hàng trong mỗi bảng/lượt xem) x (tần suất cập nhật) x (bộ xử lý) x (độ trễ mạng) Mặc dù phương trình trên không bao gồm mọi yếu tố có thể xảy ra, nhưng nó đề cập đến những yếu tố phổ biến nhất Tầm quan trọng của việc tuân theo kế hoạch giám sátMặc dù bài báo này là về khắc phục sự cố MySQL, nhưng tất cả các phương pháp hay nhất về cơ sở dữ liệu phòng ngừa cũng áp dụng cho MySQL. Do đó, phòng thủ tốt nhất vẫn là một hành vi phạm tội tốt. Về quản lý cơ sở dữ liệu MySQL, điều đó có nghĩa là thường xuyên theo dõi hiệu suất của cơ sở dữ liệu của bạn thông qua các số liệu chính. Một kế hoạch giám sát cơ sở dữ liệu hợp lý có thể giúp bạn luôn cập nhật
Mặc dù làm theo lời khuyên ở trên sẽ không loại bỏ được các vấn đề về hiệu suất, nhưng nó có thể giúp giải quyết các vấn đề tốt hơn khi chúng phát triển Một số nguyên nhân gốc rễ của các vấn đề về hiệu suấtTìm hiểu gốc rễ của các vấn đề về hiệu suất cơ sở dữ liệu sẽ dễ dàng hơn rất nhiều khi bạn biết cần tìm gì. Trong những năm qua, nhiều vấn đề phổ biến nhất mà các hệ thống cơ sở dữ liệu quan hệ gây ra đã được ghi lại đầy đủ cho hậu thế. Hóa ra những lý do phổ biến nhất khiến hiệu suất cơ sở dữ liệu chậm có liên quan đến một hoặc nhiều vấn đề trong bốn lĩnh vực rộng lớn
Các vấn đề về hiệu suất liên quan đến cơ sở dữ liệuCó thể ngạc nhiên khi có nhiều vấn đề về cơ sở dữ liệu xuất phát từ chính cơ sở dữ liệu. Từ việc chuẩn hóa quá mức các bảng đến lập chỉ mục không đúng cách, đến các truy vấn được xây dựng kém, có rất nhiều lỗi chỉ bị lộ ra khi đã đạt đến một ngưỡng sử dụng nhất định May mắn thay, MySQL được trang bị một vài công cụ tuyệt vời để giúp tìm ra nguyên nhân gốc rễ của hiệu suất MySQL chậm, cụ thể là Nhật ký chậm và Lược đồ hiệu suất Nhật ký truy vấn chậm của MySQLNguyên nhân nội bộ phổ biến nhất gây chậm cơ sở dữ liệu là các truy vấn độc quyền tài nguyên hệ thống. Các yếu tố góp phần vào hiệu suất truy vấn kém bao gồm lập chỉ mục không đầy đủ, tìm nạp tập dữ liệu rất lớn, liên kết phức tạp và khớp văn bản Nhật ký truy vấn chậm nhằm xác định các truy vấn có thể trở thành ứng cử viên tốt để tối ưu hóa. Nó bao gồm các câu lệnh SQL mất hơn một số giây nhất định để thực thi Giá trị mặc định của mười giây được lưu trữ trong biến hệ thống long_query_time. Bạn có thể kiểm tra giá trị của nó bằng cách chọn nó (lưu ý hai ký tự '@'). mysql> select @@long_query_time +-------------------+ | @@long_query_time | +-------------------+ | 10.000000 | +-------------------+ Mười giây có lẽ là quá dài vì là giới hạn đối với hầu hết các cài đặt sản xuất. Để thay đổi nó, hãy sử dụng câu lệnh SET mysql> SET @@long_query_time = 5 Bạn cũng có thể ghi các truy vấn yêu cầu MySQL kiểm tra nhiều hơn một số hàng nhất định vào nhật ký truy vấn chậm. Giá trị đó được xác định trong biến hệ thống min_examined_row_limit. Nó chỉ bao gồm thời gian để giải quyết truy vấn chứ không phải thời gian để lấy khóa. Nếu bạn chưa thay đổi nó, nó sẽ chứa giá trị bằng 0. mysql> select @@ min_examined_row_limit; +--------------------------+ | @@ min_examined_row_limit | +--------------------------+ | 0 | +--------------------------+ Để sửa đổi nó, hãy sử dụng câu lệnh SET. Nó có thể chứa bất kỳ Số nguyên nào trong khoảng từ 0 đến 4294967295 trên nền tảng 32 bit và 18446744073709551615 trên nền tảng 64 bit mysql> SET @@min_examined_row_limit = 500000; Điều quan trọng cần lưu ý là nhật ký truy vấn chậm bị tắt theo mặc định. Điều này là do việc ghi nhật ký có thể gây ra một chút cản trở đối với hiệu suất. Do đó, tốt nhất là bật nhật ký Truy vấn chậm (tốt nhất là từ tệp cấu hình MySQL) trong một khoảng thời gian ngắn, e. g. 2-3 ngày, khi hiệu suất ứng dụng của bạn bị giảm vì một lý do nào đó và bạn muốn phát hiện các truy vấn chậm Bạn có thể kiểm tra xem nhật ký truy vấn chậm của MySQL có được bật hay không bằng cách sử dụng câu lệnh sau SHOW VARIABLES Like 'slow_query_log%'; Variable_name Value --------------------------------- slow_query_log OFF slow_query_log_file C:\Program Files\MySQL\MySQL Server 5.6\data\LH12CU19884-slow.log Thực hiện theo các bước sau để bật Nhật ký truy vấn chậm
# What's the threshold for a slow query to be logged? long_query_time=0.5 # Where should the queries be logged to? slow_query_log_file=/path/to/logfile # Enable slow query logging - note the dashes rather than underscores: slow-query-log=1
Một công cụ giám sát MySQL chuyên nghiệp cũng sẽ cung cấp quyền truy cập vào Nhật ký truy vấn chậm và các tham số của nó Xem Nhật ký truy vấn chậmMySQL cung cấp công cụ có tên là mysqldumpslow có thể dùng để phân tích tệp nhật ký. Cú pháp sau đây sẽ hiển thị cho bạn 10 truy vấn hàng đầu được sắp xếp theo thời gian truy vấn trung bình (Hãy nhớ cập nhật tên tệp và đường dẫn cho phù hợp với môi trường của bạn) mysqldumpslow -t 10 mysql-slow-query.log > mysqldumpslow.out Đây là một số đầu ra mẫu Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log Count: 90 Time=56.73s (6183s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost select * from t1 Các giá trị quan trọng cần xem ở đây là Đếm và Thời gian. Đếm là số lần truy vấn này chạy trong tập hợp nhật ký của bạn. Thời gian là lượng thời gian trung bình cho mỗi lần chạy truy vấn đó để hoàn thành. Với số trong ngoặc đơn, trong trường hợp này là 6183s, là tổng lượng thời gian (Đếm x Thời gian) dành cho việc chạy truy vấn này Các công cụ GUI như Monyog có thể đơn giản hóa rất nhiều việc phân tích Nhật ký truy vấn chậm của MySQL bằng cách hiển thị các báo cáo chi tiết Lược đồ hiệu suấtLược đồ hiệu suất MySQL là một tính năng để theo dõi việc thực thi Máy chủ MySQL ở mức thấp. Nó cung cấp chi tiết về việc thực thi máy chủ theo cách có cấu trúc có thể truy cập thông qua SQL thông thường. Ngoài ra, PERFORMANCE_SCHEMA là một Công cụ lưu trữ chỉ được sử dụng cho các bảng đặc biệt trong cơ sở dữ liệu performance_schema. Cơ sở dữ liệu đó chứa 52 bảng và không có dạng xem, bao gồm một tập hợp các bảng bao gồm các danh mục sau
PERFORMANCE_SCHEMA có thể được sử dụng để khắc phục sự cố
Cấu hìnhBắt đầu từ MySQL 5. 6. 6 Lược đồ hiệu suất được bật theo mặc định. Nó chứa các công cụ, là những thứ bạn có thể đo lường và người tiêu dùng là những người sử dụng các phép đo. Không phải tất cả các công cụ và người tiêu dùng đều được kích hoạt ngay lập tức, vì vậy để đảm bảo chúng tôi đã kích hoạt mọi thứ, một số tùy chọn đã được thêm vào tệp cấu hình MySQL. Xem Tài liệu MySQL để biết thêm thông tin về cách định cấu hình Lược đồ hiệu suất. Quy trình chạy dàiLược đồ hiệu suất theo dõi các sự kiện mất thời gian khi các công cụ chờ đợi. Các bảng này lưu trữ các sự kiện chờ đợi
Sau khi lược đồ hiệu suất được bật, lược đồ này sẽ thu thập số liệu trên tất cả các câu lệnh do máy chủ thực thi. Nhiều số liệu trong số đó được tóm tắt trong bảng events_statements_summary_by_digest, có sẵn trong MySQL 5. 6 trở lên Các số liệu về khối lượng truy vấn, độ trễ, lỗi, thời gian chờ khóa, mức sử dụng chỉ mục, v.v. đều có sẵn cho mỗi câu lệnh SQL chuẩn hóa được thực thi. (Chuẩn hóa ở đây có nghĩa là loại bỏ các giá trị dữ liệu khỏi câu lệnh SQL và chuẩn hóa khoảng trắng. ) Truy vấn này tìm thấy 10 câu lệnh hàng đầu theo thời gian chạy trung bình dài nhất ________số 8_______Đơn vị hẹn giờ sự kiệnLược đồ hiệu suất hiển thị thông tin bộ đếm thời gian sự kiện tính bằng pico giây (phần nghìn tỷ giây) để chuẩn hóa dữ liệu thời gian thành đơn vị tiêu chuẩn. Trong ví dụ sau, các giá trị TIMER_WAIT được chia cho 1000000000000 để hiển thị dữ liệu theo đơn vị giây. Các giá trị cũng được cắt ngắn đến 6 chữ số thập phân digest_text_start count_star avg_timer_wait
----------------------------------------------------------------------------------------
SHOW FULL TABLES FROM `sakila` 1 1.110825
SHOW GLOBAL STATUS LIKE ? 1 1.038069
SELECT `digest_text`, `count_star`, `avg_timer_w 1 0.945742
etc.
Chỉ mục không sử dụngBảng table_io_waits_summary_by_index_usage có thể được sử dụng, không chỉ để tổng hợp các thao tác trên mỗi chỉ mục mà còn để tổng hợp có bao nhiêu thao tác không sử dụng chỉ mục khi truy cập các bảng. Điều này có thể được thực hiện bằng cách bao gồm tiêu chí “Cột INDEX_NAME là NULL” trong mệnh đề WHERE. Đây là một truy vấn tạo ra một tập hợp kết quả gồm hai hàng trong đó chỉ mục của một bảng đang được sử dụng và một bảng khác thì không mysql> SET @@long_query_time = 50 Truy vấn chạy dàiLà một trong những nguyên nhân phổ biến nhất gây ra hiệu suất cơ sở dữ liệu chậm, các truy vấn chạy dài phải nằm ở đầu danh sách nghi ngờ của bạn mỗi khi xảy ra hiện tượng chậm Bảng event_statements_history_long chứa rất nhiều trường biểu thị về chủ đề thời gian chờ đợi và các câu lệnh tương ứng của chúng. Truy vấn sau đây tổng hợp một số phép tính hữu ích về thời gian chờ để cung cấp một bức tranh rõ ràng về câu lệnh nào mất nhiều thời gian nhất để thực thi mysql> SET @@long_query_time = 51 Đây là một cách để tìm một truy vấn chạy dài bằng cách sử dụng bảng chủ đề mysql> SET @@long_query_time = 52 Sử dụng bộ nhớBạn muốn nhanh chóng xác định máy chủ có bao nhiêu RAM? . 7, các bảng hệ thống cung cấp thông tin chi tiết về việc sử dụng bộ nhớ của bộ nhớ trong được phân bổ của mỗi máy chủ mysql> SET @@long_query_time = 53 Ví dụ cuối cùng này tìm nạp mức sử dụng bộ nhớ theo luồng mysql> SET @@long_query_time = 54 Phần kết luậnKhắc phục sự cố cơ sở dữ liệu MySQL có thể giúp giảm tốc độ và thời gian chết. Ở khía cạnh này, Nhật ký truy vấn chậm và Lược đồ hiệu suất có thể đóng góp chính trong việc xác định các vấn đề hiệu suất chính Trong blog này, chúng tôi đã trình bày ý nghĩa của nhật ký truy vấn chậm nhằm xác định các truy vấn có thể trở thành ứng cử viên tốt để tối ưu hóa và cách Lược đồ hiệu suất theo dõi các quy trình chạy lâu, các chỉ mục không sử dụng, mức sử dụng bộ nhớ và các số liệu khác có thể hữu ích trong việc khắc phục sự cố MySQL chậm chạp Khi các bảng tăng kích thước và ngày càng có nhiều người dùng trực tuyến, hiệu suất cơ sở dữ liệu chậm chạp chắc chắn sẽ xảy ra sớm hay muộn. Bằng cách hiểu các cơ chế đằng sau sự chậm lại của MySQL và bằng cách sử dụng các công cụ tích hợp sẵn của MySQL, bạn có thể xác định (các) thủ phạm và khắc phục sự cố một cách kịp thời Phụ lục 1. Tệp cấu hình MySQL nằm ở đâu?Vị trí của tệp cấu hình MySQL (hoặc của tôi. ini hoặc của tôi. cnf) tùy thuộc vào hệ điều hành của bạn MySQL sẽ xem xét từng vị trí theo thứ tự và sử dụng tệp đầu tiên mà nó tìm thấy. Thông thường, nếu tệp không tồn tại, nó phải được tạo trước Các hệ thống MySQL dựa trên Linux sẽ sử dụng các tệp cấu hình theo thứ tự ưu tiên sau
SYSCONFDIR đề cập đến thư mục được chỉ định khi MySQL được xây dựng; . etc directory located under the compiled-in installation directory. MYSQL_HOME là một biến môi trường đề cập đến đường dẫn mà tôi. cnf có thể được tìm thấy Các hệ thống MySQL dựa trên Windows sẽ sử dụng các tệp cấu hình theo thứ tự ưu tiên sau
Monyog là một công cụ giám sát MySQL giúp cải thiện hiệu suất cơ sở dữ liệu của các hệ thống hỗ trợ MySQL của bạn. Tải xuống bản dùng thử miễn phí của bạn Làm cách nào để phân tích hiệu suất truy vấn MySQL?Cải thiện hiệu suất MySQL. Tìm và khắc phục truy vấn sự cố
. Lọc các vấn đề truy vấn cụ thể như quét toàn bộ bảng và lập chỉ mục xấu bằng các tùy chọn tìm kiếm toàn cầu nâng cao. Khắc phục nguyên nhân gốc rễ của hiệu suất kém trực tiếp trong SQL.
MySQL xử lý các truy vấn chậm như thế nào?Để bật nhật ký truy vấn chậm, hãy nhập lệnh sau tại dấu nhắc mysql>. Sao chép SET GLOBAL slow_query_log = 'ON'; Có các tùy chọn bổ sung mà bạn có thể đặt cho nhật ký truy vấn chậm. Theo mặc định, khi nhật ký truy vấn chậm được bật, nó sẽ ghi nhật ký bất kỳ truy vấn nào mất hơn 10 giây để chạy.
Tại sao truy vấn MySQL của tôi chạy chậm?Các truy vấn có thể trở nên chậm vì nhiều lý do khác nhau, từ việc sử dụng chỉ mục không đúng cách cho đến các lỗi trong chính công cụ lưu trữ . Tuy nhiên, trong hầu hết các trường hợp, các truy vấn trở nên chậm chạp vì các nhà phát triển hoặc quản trị viên cơ sở dữ liệu MySQL bỏ qua việc theo dõi và theo dõi hiệu suất của chúng.
Làm cách nào để kiểm tra truy vấn nào mất nhiều thời gian hơn trong MySQL?Chạy 'hiển thị danh sách quy trình;' . (Thêm công cụ sửa đổi 'đầy đủ' vào lệnh sẽ vô hiệu hóa việc cắt bớt cột Thông tin. Điều này là cần thiết khi xem các truy vấn dài. ) chuyên nghiệp. Sử dụng công cụ sửa đổi đầy đủ cho phép xem toàn bộ truy vấn trên các truy vấn dài hơn. . (Adding the 'full' modifier to the command disables truncation of the Info column. This is necessary when viewing long queries.) Pro: Using the full modifier allows for seeing the full query on longer queries. |
