Mysql hiển thị các truy vấn chậm

MariaDB [(none)]> show global variables like 'slow%log%';

+---------------------+-------------------------------+

| Variable_name       | Value           |

+---------------------+-------------------------------+

| slow_query_log      | ON           |

| slow_query_log_file | /var/log/mysql/mysql-slow.log |

+---------------------+-------------------------------+

2 rows in set (0.001 sec)

Bạn phải đảm bảo rằng biến được đặt thành BẬT, trong khi slow_query_log_file xác định đường dẫn đến đó. . Nếu biến này không được đặt, nó sẽ sử dụng DATA_DIR của thư mục dữ liệu MySQL của bạn.

Đi kèm với biến slow_query_log là và tác động đến cách hoạt động của tính năng ghi nhật ký truy vấn chậm. Về cơ bản, nhật ký truy vấn chậm hoạt động như các câu lệnh SQL mất hơn long_query_time giây để thực thi và cũng yêu cầu ít nhất . Nó có thể được sử dụng để tìm các truy vấn mất nhiều thời gian để thực hiện và do đó là ứng cử viên để tối ưu hóa và sau đó bạn có thể sử dụng các công cụ bên ngoài để mang lại báo cáo cho mình, điều này sẽ nói sau. rows to be examined. It can be used to find queries that take a long time to execute and are therefore candidates for optimization and then you can use external tools to bring the report for you, which will talk later.

Theo mặc định, các câu lệnh quản trị (ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE và REPAIR TABLE) không rơi vào nhật ký truy vấn chậm. Để làm được điều này, bạn cần kích hoạt biến .

Danh sách quy trình truy vấn và Trình giám sát trạng thái InnoDB

Trong một thói quen DBA bình thường, bước này là cách phổ biến nhất để xác định các truy vấn đang chạy dài hoặc các truy vấn đang hoạt động gây ra suy giảm hiệu suất. Nó thậm chí có thể khiến máy chủ của bạn bị kẹt, theo sau là hàng đợi chất đống đang tăng dần do truy vấn đang chạy bị khóa. Bạn chỉ có thể đơn giản là chạy,

SHOW [FULL] PROCESSLIST;

hoặc

SHOW ENGINE INNODB STATUS G

Nếu bạn đang sử dụng ClusterControl, bạn có thể tìm thấy nó bằng cách sử dụng → Hiệu suất → Trạng thái InnoDB như bên dưới,

hoặc sử dụng → Trình giám sát truy vấn → Truy vấn đang chạy (sẽ thảo luận sau) để xem các quy trình đang hoạt động, giống như cách SHOW PROCESSLIST hoạt động .

Phân tích truy vấn MySQL

Nhật ký truy vấn chậm sẽ hiển thị cho bạn danh sách các truy vấn được xác định là chậm, dựa trên các giá trị đã cho trong các biến hệ thống như đã đề cập trước đó. Định nghĩa truy vấn chậm có thể khác nhau trong các trường hợp khác nhau vì có những trường hợp nhất định mà ngay cả truy vấn 10 giây cũng được chấp nhận và vẫn không chậm. Tuy nhiên, nếu ứng dụng của bạn là một OLTP, thì rất phổ biến khi truy vấn 10 giây hoặc thậm chí 5 giây là một vấn đề hoặc gây ra sự suy giảm hiệu suất cho cơ sở dữ liệu của bạn. Nhật ký truy vấn MySQL giúp bạn điều này nhưng nó không đủ để mở tệp nhật ký vì nó không cung cấp cho bạn tổng quan về các truy vấn đó là gì, chúng hoạt động như thế nào và tần suất xuất hiện của chúng là gì. Do đó, các công cụ của bên thứ ba có thể giúp bạn với những

pt-truy vấn-tiêu hóa

Sử dụng Bộ công cụ Percona, mà tôi có thể nói là công cụ DBA phổ biến nhất, là sử dụng pt-query-digest. pt-query-digest cung cấp cho bạn tổng quan rõ ràng về một báo cáo cụ thể đến từ nhật ký truy vấn chậm của bạn. Ví dụ: báo cáo cụ thể này cho thấy góc nhìn rõ ràng về việc hiểu các báo cáo truy vấn chậm trong một nút cụ thể.

# A software update is available:



# 100ms user time, 100ms system time, 29.12M rss, 242.41M vsz

# Current date: Mon Feb  3 20:26:11 2020

# Hostname: testnode7

# Files: /var/log/mysql/mysql-slow.log

# Overall: 24 total, 14 unique, 0.00 QPS, 0.02x concurrency ______________

# Time range: 2019-12-12T10:01:16 to 2019-12-12T15:31:46

# Attribute          total min max     avg 95% stddev median

# ============     ======= ======= ======= ======= ======= ======= =======

# Exec time           345s 1s 98s   14s 30s 19s 7s

# Lock time             1s 0 1s 58ms    24ms 252ms 786us

# Rows sent          5.72M 0 1.91M 244.14k   1.86M 629.44k 0

# Rows examine      15.26M 0 1.91M 651.23k   1.86M 710.58k 961.27k

# Rows affecte       9.54M 0 1.91M 406.90k 961.27k 546.96k       0

# Bytes sent       305.81M 11 124.83M  12.74M 87.73M 33.48M 56.92

# Query size         1.20k 25 244   51.17 59.77 40.60 38.53



# Profile

# Rank Query ID                         Response time Calls R/Call V/M   

# ==== ================================ ============= ===== ======= ===== 

#    1 0x00C8412332B2795DADF0E55C163.. 98.0337 28.4%     1 98.0337 0.00 UPDATE sbtest?

#    2 0xDEF289292EA9B2602DC12F70C7A.. 74.1314 21.5%     3 24.7105 6.34 ALTER TABLE sbtest? sbtest3

#    3 0x148D575F62575A20AB9E67E41C3.. 37.3039 10.8%     6 6.2173 0.23 INSERT SELECT sbtest? sbtest

#    4 0xD76A930681F1B4CC9F748B4398B.. 32.8019  9.5% 3 10.9340 4.24 SELECT sbtest?

#    5 0x7B9A47FF6967FD905289042DD3B.. 20.6685  6.0% 1 20.6685 0.00 ALTER TABLE sbtest? sbtest3

#    6 0xD1834E96EEFF8AC871D51192D8F.. 19.0787  5.5% 1 19.0787 0.00 CREATE

#    7 0x2112E77F825903ED18028C7EA76.. 18.7133  5.4% 1 18.7133 0.00 ALTER TABLE sbtest? sbtest3

#    8 0xC37F2569578627487D948026820.. 15.0177  4.3% 2 7.5088 0.00 INSERT SELECT sbtest? sbtest

#    9 0xDE43B2066A66AFA881D6D45C188.. 13.7180  4.0% 1 13.7180 0.00 ALTER TABLE sbtest? sbtest3

# MISC 0xMISC                           15.8605 4.6% 5 3.1721 0.0 <5 ITEMS>



# Query 1: 0 QPS, 0x concurrency, ID 0x00C8412332B2795DADF0E55C1631626D at byte 5319

# Scores: V/M = 0.00

# Time range: all events occurred at 2019-12-12T13:23:15

# Attribute    pct total min     max avg 95% stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count          4 1

# Exec time     28 98s 98s     98s 98s 98s   0 98s

# Lock time      1 25ms 25ms    25ms 25ms 25ms       0 25ms

# Rows sent      0 0 0       0 0 0 0       0

# Rows examine  12 1.91M 1.91M   1.91M 1.91M 1.91M       0 1.91M

# Rows affecte  20 1.91M 1.91M   1.91M 1.91M 1.91M       0 1.91M

# Bytes sent     0 67 67      67 67 67   0 67

# Query size     7 89 89      89 89 89   0 89

# String:

# Databases    test

# Hosts        localhost

# Last errno   0

# Users        root

# Query_time distribution

#   1us

#  10us

# 100us

#   1ms

#  10ms

# 100ms

#    1s

#  10s+  ################################################################

# Tables

#    SHOW TABLE STATUS FROM `test` LIKE 'sbtest3'G

#    SHOW CREATE TABLE `test`.`sbtest3`G

update sbtest3 set c=substring(MD5(RAND()), -16), pad=substring(MD5(RAND()), -16) where 1G

# Converted for EXPLAIN

# EXPLAIN /*!50100 PARTITIONS*/

select  c=substring(MD5(RAND()), -16), pad=substring(MD5(RAND()), -16) from sbtest3 where  1G



# Query 2: 0.00 QPS, 0.01x concurrency, ID 0xDEF289292EA9B2602DC12F70C7A041A9 at byte 3775

# Scores: V/M = 6.34

# Time range: 2019-12-12T12:41:47 to 2019-12-12T15:25:14

# Attribute    pct total min     max avg 95% stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count         12 3

# Exec time     21 74s 6s     36s 25s 35s 13s     30s

# Lock time      0 13ms 1ms     8ms 4ms 8ms   3ms 3ms

# Rows sent      0 0 0       0 0 0 0       0

# Rows examine   0 0 0       0 0 0 0       0

# Rows affecte   0 0 0       0 0 0 0       0

# Bytes sent     0 144 44      50 48 49.17   3 49.17

# Query size     8 99 33      33 33 33   0 33

# String:

# Databases    test

# Hosts        localhost

# Last errno   0 (2/66%), 1317 (1/33%)

# Users        root

# Query_time distribution

#   1us

#  10us

# 100us

#   1ms

#  10ms

# 100ms

#    1s ################################

#  10s+  ################################################################

# Tables

#    SHOW TABLE STATUS FROM `test` LIKE 'sbtest3'G

#    SHOW CREATE TABLE `test`.`sbtest3`G

ALTER TABLE sbtest3 ENGINE=INNODBG

Sử dụng performance_schema

Nhật ký truy vấn chậm có thể là một vấn đề nếu bạn không có quyền truy cập trực tiếp vào tệp, chẳng hạn như sử dụng RDS hoặc sử dụng các dịch vụ cơ sở dữ liệu được quản lý hoàn toàn, chẳng hạn như Google Cloud SQL hoặc Azure SQL. Mặc dù bạn có thể cần một số biến để kích hoạt các tính năng này, nhưng tính năng này rất hữu ích khi truy vấn các truy vấn được đăng nhập vào hệ thống của bạn. Bạn có thể đặt hàng nó bằng cách sử dụng câu lệnh SQL tiêu chuẩn để truy xuất một phần kết quả. Ví dụ,

mysql> SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 SUM_TIMER_WAIT_SEC, MIN_TIMER_WAIT/1000000000000 MIN_TIMER_WAIT_SEC, AVG_TIMER_WAIT/1000000000000 AVG_TIMER_WAIT_SEC, MAX_TIMER_WAIT/1000000000000 MAX_TIMER_WAIT_SEC, SUM_LOCK_TIME/1000000000000 SUM_LOCK_TIME_SEC, FIRST_SEEN, LAST_SEEN FROM events_statements_summary_by_digest;

+--------------------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------------------+--------------------+--------------------+--------------------+-------------------+---------------------+---------------------+

| SCHEMA_NAME        | DIGEST               | DIGEST_TEXT                                                                                                                                                                                                                                                                                                                               | COUNT_STAR | SUM_TIMER_WAIT_SEC | MIN_TIMER_WAIT_SEC | AVG_TIMER_WAIT_SEC | MAX_TIMER_WAIT_SEC | SUM_LOCK_TIME_SEC | FIRST_SEEN | LAST_SEEN |

+--------------------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------------------+--------------------+--------------------+--------------------+-------------------+---------------------+---------------------+

| NULL               | 390669f3d1f72317dab6deb40322d119 | SELECT @@`skip_networking` , @@`skip_name_resolve` , @@`have_ssl` = ? , @@`ssl_key` , @@`ssl_ca` , @@`ssl_capath` , @@`ssl_cert` , @@`ssl_cipher` , @@`ssl_crl` , @@`ssl_crlpath` , @@`tls_version`                                                                                                                                                             | 1 | 0.0373 | 0.0373 | 0.0373 | 0.0373 | 0.0000 | 2020-02-03 20:22:54 | 2020-02-03 20:22:54 |

| NULL               | fba95d44e3d0a9802dd534c782314352 | SELECT `UNIX_TIMESTAMP` ( )                                                                                                                                                                                                                                                                                                                                     | 2 | 0.0002 | 0.0001 | 0.0001 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | 18c649da485456d6cdf12e4e6b0350e9 | SELECT @@GLOBAL . `SERVER_ID`                                                                                                                                                                                                                                                                                                                                   | 2 | 0.0001 | 0.0001 | 0.0001 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | dd356b8a5a6ed0d7aee2abd939cdb6c9 | SET @? = ?                                                                                                                                                                                                                                                                                                                                                      | 6 | 0.0003 | 0.0000 | 0.0001 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | 1c5ae643e930af6d069845d74729760d | SET @? = @@GLOBAL . `binlog_checksum`                                                                                                                                                                                                                                                                                                                           | 2 | 0.0001 | 0.0001 | 0.0001 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | ad5208ffa004a6ad7e26011b73cbfb4c | SELECT @?                                                                                                                                                                                                                                                                                                                                                       | 2 | 0.0001 | 0.0000 | 0.0000 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | ed0d1eb982c106d4231b816539652907 | SELECT @@GLOBAL . `GTID_MODE`                                                                                                                                                                                                                                                                                                                                   | 2 | 0.0001 | 0.0000 | 0.0000 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | cb47e22372fdd4441486b02c133fb94f | SELECT @@GLOBAL . `SERVER_UUID`                                                                                                                                                                                                                                                                                                                                 | 2 | 0.0001 | 0.0000 | 0.0000 | 0.0001 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | 73301368c301db5d2e3db5626a21b647 | SELECT @@GLOBAL . `rpl_semi_sync_master_enabled`                                                                                                                                                                                                                                                                                                                | 2 | 0.0001 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 2020-02-03 20:22:57 | 2020-02-03 20:23:00 |

| NULL               | 0ff7375c5f076ba5c040e78a9250a659 | SELECT @@`version_comment` LIMIT ?                                                                                                                                                                                                                                                                                                                              | 1 | 0.0001 | 0.0001 | 0.0001 | 0.0001 | 0.0000 | 2020-02-03 20:45:59 | 2020-02-03 20:45:59 |

| NULL               | 5820f411e67a393f987c6be5d81a011d | SHOW TABLES FROM `performance_schema`                                                                                                                                                                                                                                                                                                                           | 1 | 0.0008 | 0.0008 | 0.0008 | 0.0008 | 0.0002 | 2020-02-03 20:46:11 | 2020-02-03 20:46:11 |

| NULL               | a022a0ab966c51eb820da1521349c7ef | SELECT SCHEMA ( )                                                                                                                                                                                                                                                                                                                                               | 1 | 0.0005 | 0.0005 | 0.0005 | 0.0005 | 0.0000 | 2020-02-03 20:46:29 | 2020-02-03 20:46:29 |

| performance_schema | e4833a7c1365b0b4492e9a514f7b3bd4 | SHOW SCHEMAS                                                                                                                                                                                                                                                                                                                                                    | 1 | 0.1167 | 0.1167 | 0.1167 | 0.1167 | 0.0001 | 2020-02-03 20:46:29 | 2020-02-03 20:46:29 |

| performance_schema | 1107f048fe6d970cb6a553bd4727a1b4 | SHOW TABLES                                                                                                                                                                                                                                                                                                                                                     | 1 | 0.0002 | 0.0002 | 0.0002 | 0.0002 | 0.0000 | 2020-02-03 20:46:29 | 2020-02-03 20:46:29 |

...

Bạn có thể sử dụng bảng performance_schema. event_statements_summary_by_digest . Mặc dù có khả năng các mục trên bảng từ performance_schema sẽ bị xóa, bạn có thể quyết định lưu mục này vào một bảng cụ thể. Hãy xem bài đăng bên ngoài này từ Percona Thông báo truy vấn MySQL với Lược đồ hiệu suất.

Trong trường hợp bạn thắc mắc tại sao chúng ta cần chia các cột thời gian chờ (SUM_TIMER_WAIT, MIN_TIMER_WAIT_SEC, AVG_TIMER_WAIT_SEC ), những cột này đang sử dụng .

Phân tích các truy vấn chậm bằng cách sử dụng ClusterControl

Nếu bạn đang sử dụng ClusterControl, có nhiều cách khác nhau để giải quyết vấn đề này. Ví dụ: trong Cụm MariaDB mà tôi có bên dưới, nó hiển thị cho bạn tab sau (Trình giám sát truy vấn) và các mục thả xuống của nó (Truy vấn hàng đầu, Truy vấn đang chạy, Ngoại lệ truy vấn).

• –   danh sách tổng hợp tất cả các truy vấn hàng đầu đang chạy trên tất cả các nút của cụm cơ sở dữ liệu của bạn
• – Xem các truy vấn đang chạy hiện tại trên cụm cơ sở dữ liệu của bạn tương tự như lệnh SHOW FULL PROCESSLIST trong MySQL
• – Hiển thị các truy vấn ngoại lệ. Ngoại lệ là truy vấn mất nhiều thời gian hơn truy vấn thông thường thuộc loại đó

Ngoài ra, ClusterControl cũng ghi lại hiệu suất truy vấn bằng cách sử dụng các biểu đồ giúp bạn có cái nhìn nhanh về cách hệ thống cơ sở dữ liệu của bạn hoạt động liên quan đến hiệu suất truy vấn. Xem bên dưới,

Đợi đã, nó vẫn chưa kết thúc. ClusterControl cũng cung cấp số liệu có độ phân giải cao bằng cách sử dụng Prometheus và hiển thị các số liệu rất chi tiết cũng như thu thập số liệu thống kê theo thời gian thực từ máy chủ. Chúng tôi đã thảo luận về điều này trong các blog trước của chúng tôi, được chia thành hai phần của loạt blog. Kiểm tra phần 1 và sau đó là blog phần 2. Nó cung cấp cho bạn cách giám sát hiệu quả không chỉ các truy vấn chậm mà cả hiệu suất tổng thể của các máy chủ cơ sở dữ liệu MySQL, MariaDB hoặc Percona của bạn.

Ngoài ra còn có các công cụ khác trong ClusterControl cung cấp các con trỏ và gợi ý có thể gây ra hiệu suất truy vấn chậm ngay cả khi nó chưa xảy ra hoặc được nhật ký truy vấn chậm ghi lại. Kiểm tra Tab Hiệu suất như bên dưới,

những mục này cung cấp cho bạn những điều sau đây

• Tổng quan – Bạn có thể xem biểu đồ của các bộ đếm cơ sở dữ liệu khác nhau trên trang này
• Advisors – Danh sách kết quả của các cố vấn đã lên lịch được tạo trong ClusterControl > Manage > Developer Studio bằng cách sử dụng .
• Trạng thái DB – Trạng thái DB cung cấp tổng quan nhanh về trạng thái MySQL trên tất cả các nút cơ sở dữ liệu của bạn, tương tự như câu lệnh SHOW STATUS
• Biến DB – Biến DB cung cấp tổng quan nhanh về các biến MySQL được đặt trên tất cả các nút cơ sở dữ liệu của bạn, tương tự như câu lệnh SHOW GLOBAL VARIABLES
• Tăng trưởng DB – Cung cấp bản tóm tắt về cơ sở dữ liệu và tăng trưởng bảng của bạn hàng ngày trong 30 ngày qua.  
• Trạng thái InnoDB – Tìm nạp đầu ra màn hình InnoDB hiện tại cho máy chủ đã chọn, tương tự như lệnh SHOW ENGINE INNODB STATUS
• Trình phân tích lược đồ – Phân tích các lược đồ cơ sở dữ liệu của bạn để tìm các khóa chính bị thiếu, các chỉ mục và bảng dư thừa bằng cách sử dụng công cụ lưu trữ MyISAM.  
• Nhật ký giao dịch – Liệt kê các giao dịch và bế tắc đang diễn ra trong thời gian dài trên cụm cơ sở dữ liệu nơi bạn có thể dễ dàng xem giao dịch nào đang gây ra bế tắc. Ngưỡng thời gian truy vấn mặc định là 30 giây