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
Tại sao hiệu suất cơ sở dữ liệu chậm lại
Khô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át
Mặ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
- Hiệu suất. Bằng cách theo dõi các số liệu liên quan đến hiệu suất như thời gian hoàn thành truy vấn, bạn có thể phát hiện ra bất kỳ hoạt động kém hiệu quả nào và đưa ra các giải pháp khả thi
- Sự phát triển. Quan sát các thay đổi về người dùng và lưu lượng truy cập. Nhu cầu sử dụng cơ sở dữ liệu có thể phát triển rất nhanh và dẫn đến tắc nghẽn
- Bảo vệ. Đảm bảo rằng các biện pháp an ninh đầy đủ được áp dụng
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ất
Tì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
Nguyên nhân liên quan đến phần cứng/hệ điều hành/mạng
- Dung lượng ổ đĩa và/hoặc bộ nhớ có sẵn trên máy chủ hoặc máy khách bị hạn chế
- Thẻ giao diện mạng [NIC] lỗi thời/lỗi thời hoặc phần cứng máy chủ/máy khách khác
- Hệ điều hành [hệ điều hành] không phù hợp để chạy các ứng dụng máy chủ hoặc ứng dụng khách
- Tất cả các loại phần mềm độc hại
- Sự cố mạng, lưu lượng truy cập mạng quá mức, tắc nghẽn mạng hoặc lưu lượng truy cập cơ sở dữ liệu
Can thiệp ứng dụng gốc và bên thứ 3
- Công việc theo lịch trình – sao lưu, bảo trì, quét AV, cập nhật ứng dụng và hệ thống
- Các ứng dụng và phần mềm [bên thứ 3] khác được cài đặt trên máy. Hãy nhớ rằng tất cả các chương trình được khởi chạy khi khởi động luôn chạy ở chế độ nền
Thiết kế cơ sở dữ liệu
- Số lượng lớn các dạng xem trong cơ sở dữ liệu với các truy vấn sử dụng các truy vấn con đắt tiền và việc lập chỉ mục các bảng kém có thể sử dụng nhiều bộ nhớ và gây ra hoán đổi
- Một số thuộc tính cơ sở dữ liệu có thể làm chậm hiệu suất trong khi những thuộc tính khác cải thiện hiệu suất. Hãy cẩn thận khi chọn bộ ký tự và kiểu dữ liệu nói riêng
- Cấu hình của máy chủ và khung ứng dụng không cho phép truy cập vào tất cả các tài nguyên hệ thống hoặc yêu cầu nhiều tài nguyên hơn khả dụng. Cả hai sẽ làm giảm hiệu suất
- Đảm bảo rằng không có tiện ích quản trị cơ sở dữ liệu nào như sao lưu, đồng bộ hóa hoặc nén đang chạy trong giờ cao điểm
- Tệp đính kèm lớn [BLOBS] trong cơ sở dữ liệu
- Mã hóa/lập trình ứng dụng tùy chỉnh không hiệu quả. Thông thường, có thể có logic ứng dụng trong ứng dụng hoặc trong Thủ tục được lưu trữ, Trình kích hoạt, v.v. Chọn cách tiếp cận phù hợp với nhiệm vụ. Cơ sở dữ liệu bị hỏng và phân mảnh [cũng có thể được kết hợp với các triệu chứng khác bao gồm kết quả không chính xác từ một số truy vấn]. Cân nhắc chạy một số quy trình kiểm tra/sửa chữa/chống phân mảnh định kỳ
Các vấn đề về hiệu suất liên quan đến cơ sở dữ liệu
Có 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 MySQL
Nguyê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
- Tắt mọi ứng dụng đang sử dụng MySQL
- Tự tắt MySQL
- Thêm các tùy chọn cấu hình sau vào của tôi. ini hoặc của tôi. cnf [xem “Phụ lục 1. Tệp Cấu hình MySQL nằm ở đâu?”
# 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
- Lưu tệp và khởi động lại MySQL
- Khởi động lại các ứng dụng được kết nối của bạn
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ậm
MySQL 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ất
Lượ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
- Bảng cấu hình
- Bảng đối tượng
- Bảng hiện tại
- Bảng lịch sử
- Bảng tóm tắt
- Bàn khác
PERFORMANCE_SCHEMA có thể được sử dụng để khắc phục sự cố
- Tắc nghẽn máy chủ do khóa, mutexes, IO
- Báo cáo ít hơn tối ưu
- Hoạt động tốn kém nhất
- Các vấn đề kết nối
- Sử dụng bộ nhớ
- sao chép thất bại
- Hơn…
Cấu hình
Bắ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ài
Lượ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
- sự kiện_chờ_cản. Sự kiện chờ hiện tại
- sự kiện_chờ_history. Các sự kiện chờ gần đây nhất trên mỗi luồng
- sự kiện_chờ_history_long. Các sự kiện chờ gần đây nhất trên toàn cầu [trên tất cả các luồng]
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ện
Lượ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ụng
Bả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ài
Là 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ận
Khắ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
- /etc/của tôi. cnf
- /etc/mysql/my. cnf
- SYSCONFDIR/của tôi. cnf
- $MYSQL_HOME/của tôi. cnf
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
- %PROGRAMDATA%\MySQL\\my.ini
- %PROGRAMDATA%\MySQL\\my.cnf
- %WINDIR%\của tôi. ban đầu
- %WINDIR%\của tôi. cnf
- C. \của tôi. ban đầu
- C. \của tôi. cnf
- CÀI ĐẶT\của tôi. ban đầu
- CÀI ĐẶT\của tôi. cnf
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