Làm cách nào để kiểm tra thời gian truy vấn trong MySQL?

Mọi ứng dụng được hỗ trợ bởi MySQL đều có thể hưởng lợi từ máy chủ cơ sở dữ liệu được tinh chỉnh. Nhóm hỗ trợ anh hùng của Liquid Web đã gặp phải nhiều tình huống trong nhiều năm khi một số điều chỉnh nhỏ đã tạo ra một thế giới khác biệt về hiệu suất của trang web và ứng dụng. Trong loạt bài viết này, chúng tôi đã phác thảo một số đề xuất phổ biến hơn có tác động lớn nhất đến hiệu suất

Bài viết này áp dụng cho hầu hết các máy chủ MySQL VPS dựa trên Linux. Điều này bao gồm, nhưng không giới hạn, cả máy chủ VPS đám mây và chuyên dụng truyền thống chạy nhiều bản phân phối Linux phổ biến. Bài viết có thể được sử dụng với các loại hệ thống Liquid Web sau

• CentOS 6x/7x được quản lý lõi
• Ubuntu 14 được quản lý lõi. 16/04. 04
• CentOS 6/7 cPanel được quản lý hoàn toàn
• CentOS 7 Plesk Onyx 17 được quản lý hoàn toàn
• Máy chủ Linux tự quản lý

Các hệ thống tự quản lý đã từ chối hỗ trợ trực tiếp có thể tận dụng các kỹ thuật được thảo luận ở đây, tuy nhiên, Nhóm hỗ trợ anh hùng Web lỏng không thể cung cấp hỗ trợ trực tiếp trên các loại máy chủ này

Loạt bài viết này giả định rằng bạn đã quen thuộc với các khái niệm quản trị hệ thống cơ bản sau đây

Tối ưu hóa MySQL là gì?

Không có định nghĩa rõ ràng cho thuật ngữ MySQL Optimization. Nó có thể có ý nghĩa khác nhau tùy thuộc vào từng người, quản trị viên, nhóm hoặc công ty. Đối với loạt bài viết về Tối ưu hóa MySQL này, chúng tôi sẽ định nghĩa Tối ưu hóa MySQL là. Cấu hình của máy chủ MySQL hoặc MariaDB đã được cấu hình để tránh tắc nghẽn thường gặp được thảo luận trong loạt bài viết này

Rất giống với cổ chai nước ngọt, cổ chai theo thuật ngữ kỹ thuật là một điểm trong ứng dụng hoặc cấu hình máy chủ nơi một lượng nhỏ lưu lượng truy cập hoặc dữ liệu có thể đi qua mà không gặp sự cố. Tuy nhiên, một khối lượng lớn hơn của cùng loại lưu lượng truy cập hoặc dữ liệu bị cản trở hoặc bị chặn và không thể hoạt động thành công như vốn có. Xem ví dụ sau về nút cổ chai cấu hình

Trong ví dụ này, máy chủ có khả năng xử lý đồng thời 10 kết nối. Tuy nhiên cấu hình chỉ chấp nhận 5 kết nối. Sự cố này sẽ không xuất hiện miễn là có từ 5 kết nối trở xuống cùng một lúc. Tuy nhiên, khi lưu lượng truy cập tăng lên tới 10 kết nối, một nửa trong số chúng bắt đầu bị lỗi do không sử dụng tài nguyên trong cấu hình máy chủ. Các ví dụ trên minh họa hình dạng nút cổ chai nơi nó lấy tên của nó so với cấu hình được tối ưu hóa để khắc phục nút cổ chai

Khi nào tôi nên tối ưu hóa cơ sở dữ liệu MySQL của mình?

Lý tưởng nhất là việc điều chỉnh hiệu suất cơ sở dữ liệu nên diễn ra thường xuyên và trước khi năng suất bị ảnh hưởng. Cách tốt nhất là tiến hành kiểm tra hiệu suất cơ sở dữ liệu hàng tuần hoặc hàng tháng để ngăn các sự cố ảnh hưởng xấu đến các ứng dụng. Các triệu chứng rõ ràng nhất của các vấn đề về hiệu suất là

• Truy vấn xếp chồng lên nhau và không bao giờ hoàn thành trong bảng quy trình MySQL
• Các ứng dụng hoặc trang web sử dụng cơ sở dữ liệu trở nên chậm chạp
• Lỗi hết thời gian kết nối, đặc biệt là trong giờ cao điểm

Mặc dù việc có nhiều truy vấn đồng thời chạy cùng lúc trên một hệ thống bận rộn là điều bình thường, nhưng sẽ có vấn đề khi các truy vấn này mất quá nhiều thời gian để hoàn thành thường xuyên. Mặc dù ngưỡng cụ thể thay đổi theo từng hệ thống và từng ứng dụng, nhưng thời gian truy vấn trung bình vượt quá vài giây sẽ biểu hiện là sự chậm lại trong các trang web và ứng dụng được đính kèm. Những sự chậm lại này đôi khi có thể bắt đầu nhỏ và không được chú ý cho đến khi lưu lượng truy cập tăng vọt chạm vào một nút cổ chai cụ thể

Xác định các vấn đề về hiệu suất

Biết cách kiểm tra bảng quy trình MySQL là rất quan trọng để chẩn đoán nút cổ chai cụ thể đang gặp phải. Có một số cách để xem bảng quy trình tùy thuộc vào máy chủ và tùy chọn cụ thể của bạn. Để cho ngắn gọn, loạt bài này sẽ tập trung vào các phương pháp phổ biến nhất được sử dụng thông qua truy cập Secure Shell (SSH)

Phương pháp 1. Sử dụng Bảng quy trình MySQL

Sử dụng công cụ dòng lệnh 'mysqladmin' với cờ 'danh sách quy trình' hoặc viết tắt là 'proc'. (Thêm cờ 'thống kê' hoặc viết tắt là 'stat' sẽ hiển thị số liệu thống kê đang chạy cho các truy vấn kể từ lần khởi động lại cuối cùng của MySQL. )

 +-------+------+-----------+-----------+---------+------+-------+
 | Id    | User | Host      | db        | Command | Time | State | Info               | Progress |
 +-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
 | 77255 | root | localhost | employees | Query   | 150  |       | call While_Loop2() | 0.000    |
 | 77285 | root | localhost |           | Query   | 0    | init  | show processlist   | 0.000    |
 +-------+------+-----------+-----------+---------+------+-------+--------------------+----------+
 Uptime: 861755  Threads: 2  Questions: 20961045  Slow queries: 0  Opens: 2976  Flush tables: 1  Open tables: 1011  Queries per second avg: 24.323

chuyên nghiệp. Được sử dụng trên giao diện trình bao, điều này giúp dễ dàng chuyển đầu ra cho các tập lệnh và công cụ khác. Côn. Cột thông tin của bảng quy trình luôn bị cắt ngắn nên không cung cấp truy vấn đầy đủ đối với các truy vấn dài hơn

Phương pháp 2. Sử dụng Bảng quy trình MySQL

Chạy truy vấn 'show processlist;' từ trong lời nhắc chế độ tương tác của MySQL. (Việc 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. )

MariaDB [(none)]> show full processlist;
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
 | Id    | User | Host      | db        | Command | Time | State | Info                  | Progress |
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+
 | 77006 | root | localhost | employees | Query   |  151 | NULL  | call While_Loop2()    |    0.000 |
 | 77021 | root | localhost | NULL      | Query   |    0 | init  | show full processlist |    0.000 |
 +-------+------+-----------+-----------+---------+------+-------+-----------------------+----------+

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. Côn. Chế độ tương tác MySQL không thể truy cập các tập lệnh và công cụ có sẵn trong giao diện trình bao

Một công cụ có giá trị khác trong MySQL là tính năng ghi nhật ký truy vấn chậm đi kèm. Tính năng này là phương pháp ưa thích để thường xuyên tìm các truy vấn chạy dài. Có một số chỉ thị có sẵn để điều chỉnh tính năng này. Tuy nhiên, các cài đặt cần thiết phổ biến nhất là

slow_query_logenable/vô hiệu hóa nhật ký truy vấn chậmslow_query_log_filename và đường dẫn của nhật ký truy vấn chậm filelong_query_timetime tính bằng giây/micro giây xác định truy vấn chậm

Các lệnh này được đặt trong phần [mysqld] của tệp cấu hình MySQL có tại /etc/my. cnf và sẽ yêu cầu khởi động lại dịch vụ MySQL trước khi chúng có hiệu lực. Xem ví dụ bên dưới để định dạng

Có vấn đề về dung lượng ổ đĩa lớn với tệp nhật ký truy vấn chậm, tệp này cần được xử lý liên tục cho đến khi tính năng nhật ký truy vấn chậm bị tắt. Hãy ghi nhớ, chỉ thị long_query_time của bạn càng thấp thì nhật ký truy vấn chậm sẽ lấp đầy phân vùng đĩa càng nhanh

[mysqld]
 log-error=/var/lib/mysql/mysql.err
 innodb_file_per_table=1
 default-storage-engine=innodb
 innodb_buffer_pool_size=128M
 innodb_log_file_size=128M
 max_connections=300
 key_buffer_size = 8M
 slow_query_log=1
 slow_query_log_file=/var/lib/mysql/slowquery.log
 long_query_time=5

Sau khi bật nhật ký truy vấn chậm, bạn sẽ cần theo dõi định kỳ nhật ký truy vấn đó để xem xét các truy vấn không phù hợp cần được điều chỉnh để có hiệu suất tốt hơn. Để phân tích tệp nhật ký truy vấn chậm, bạn có thể phân tích cú pháp trực tiếp để xem lại nội dung của tệp. Ví dụ sau đây hiển thị số liệu thống kê cho truy vấn mẫu chạy lâu hơn 5 giây đã định cấu hình

Có một điểm nhấn về hiệu suất được thực hiện bằng cách bật tính năng nhật ký truy vấn chậm. Điều này là do các quy trình bổ sung cần thiết để phân tích từng truy vấn cũng như I/O cần thiết để ghi các truy vấn cần thiết vào tệp nhật ký. Do đó, việc vô hiệu hóa nhật ký truy vấn chậm được coi là phương pháp hay nhất trên các hệ thống sản xuất. Nhật ký truy vấn chậm chỉ nên được bật trong một khoảng thời gian cụ thể khi tích cực tìm kiếm các truy vấn rắc rối có thể ảnh hưởng đến ứng dụng hoặc trang web

# Time: 180717  0:23:28
 # User@Host: root[root] @ localhost []
 # Thread_id: 32  Schema: employees  QC_hit: No
 # Query_time: 627.163085  Lock_time: 0.000021  Rows_sent: 0  Rows_examined: 0
 # Rows_affected: 0
 use employees;
 SET timestamp=1531801408;
 call While_Loop2();

Theo tùy chọn, bạn có thể sử dụng công cụ dòng lệnh mysqldumpslow, công cụ này phân tích tệp nhật ký truy vấn chậm và nhóm các truy vấn giống nhau ngoại trừ các giá trị của dữ liệu số và chuỗi

~ $ mysqldumpslow -a /var/lib/mysql/slowquery.log
 Reading mysql slow query log from /var/lib/mysql/slowquery.log
 Count: 2  Time=316.67s (633s)  Lock=0.00s (0s)  Rows_sent=0.5 (1), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
 call While_Loop2()

Vì vậy, kết thúc phần đầu tiên của loạt bài Tối ưu hóa cơ sở dữ liệu của chúng tôi và cung cấp cho chúng tôi cơ sở vững chắc để tham khảo cho các mục đích điểm chuẩn. Mặc dù các vấn đề về cơ sở dữ liệu có thể phức tạp, loạt bài của chúng tôi sẽ chia nhỏ các khái niệm này để cung cấp phương tiện tối ưu hóa cơ sở dữ liệu của bạn thông qua chuyển đổi cơ sở dữ liệu, chuyển đổi bảng và lập chỉ mục

Chúng tôi tự hào là Những Con Người Hữu Ích Nhất Trong Hosting™

Nhóm hỗ trợ của chúng tôi bao gồm các kỹ thuật viên Linux có kinh nghiệm và quản trị viên hệ thống tài năng, những người có kiến ​​thức sâu rộng về nhiều công nghệ lưu trữ web, đặc biệt là những công nghệ được thảo luận trong bài viết này

Nếu bạn có bất kỳ câu hỏi nào liên quan đến thông tin này, chúng tôi luôn sẵn sàng giải đáp mọi thắc mắc về các vấn đề liên quan đến bài viết này, 24 giờ một ngày, 7 ngày một tuần 365 ngày một năm

Nếu bạn là máy chủ VPS được quản lý hoàn toàn, Đám mây chuyên dụng, Đám mây riêng VMWare, Máy chủ mẹ riêng, Máy chủ đám mây được quản lý hoặc chủ sở hữu máy chủ chuyên dụng và bạn không thoải mái khi thực hiện bất kỳ bước nào đã nêu, thì có thể liên hệ với chúng tôi qua điện thoại theo số @800. 580. 4985, một phiếu trò chuyện hoặc hỗ trợ để hỗ trợ bạn trong quá trình này

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?

Làm cách nào để kiểm tra thời gian thực hiện truy vấn trong MySQL Workbench?

Làm cách nào để kiểm tra thời gian truy vấn chậm trong MySQL?

Làm cách nào để đặt thời gian thực hiện truy vấn trong MySQL?