詳解MySQL慢日誌（下）選項引數篇

神諭丶發表於2017-11-16
原文網址 : http://blog.itpub.net/29773961/viewspace-2147352/
MySql

詳解MySQL慢日誌（上）query_time\start_time\lock_time 的坑 
http://blog.itpub.net/29773961/viewspace-2147315/


〇 log_output
列舉型，動態引數。
用於設定slow log和general log的輸出物件。
可以設定為none，table，file，分別代表：不輸出，存於表，存於檔案。

並且也可以組合設定：
比如SET GLOBAL log_output='table,file';
則代表同時輸出到表和檔案中。

如果設定SET GLOBAL log_output='none,file' 或 'none,table' 或 'table,file,none' 均代表'none'



〇 slow_query_log與slow_query_log_file
slow_query_log 布林型，動態引數，預設為OFF。
用於控制是否開啟slow log。

slow_query_log_file 動態引數，指定slow log檔案的名稱和路徑。
若未設定，則slow log的檔名取預設值$host_name-slow.log，存放於$datadir下。



〇 long_query_time
動態引數，預設值為10。
記錄執行時間（real time）超過該值以上的SQL。



〇 log_queries_not_using_indexes 
布林型，動態引數，預設為OFF。
若開啟，則表示記錄所有未使用索引的SQL，無論是否超過long_query_time所設定的值。
不遵循long_query_time。

  1. mysql> SET SESSION long_query_time=10000;
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> CREATE TABLE test.slow(id int);
  5. Query OK, 0 rows affected (0.03 sec)

  7. mysql> INSERT INTO test.slow SELECT 1;
  8. Query OK, 1 row affected (0.01 sec)
  9. Records: 1 Duplicates: 0 Warnings: 0

  11. mysql> SELECT * FROM test.slow WHERE id=1;
  12. +------+
  13. | id   |
  14. +------+
  15. | 1    |
  16. +------+
  17. 1 row in set (0.00 sec)

  19. mysql> SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log;
  20. +----------------------------+-----------------+-----------------+------------------------------------+
  21. | start_time                 | query_time      | lock_time       | sql_text                           |
  22. +----------------------------+-----------------+-----------------+------------------------------------+
  23. | 2017-11-16 15:09:32.114488 | 00:00:00.000346 | 00:00:00.000166 | SELECT * FROM test.slow WHERE id=1 |
  24. +----------------------------+-----------------+-----------------+------------------------------------+
  25. 1 row in set (0.00 sec)
此時上面這一條SQL被記錄到slow log，並且忽略了long_query_time的設定。

加一個索引，再多一次查詢：

  1. mysql> ALTER TABLE test.slow ADD PRIMARY KEY pk(id);
  2. Query OK, 0 rows affected (0.14 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

  5. mysql> SELECT * FROM test.slow WHERE id=1;
  6. +----+
  7. | id |
  8. +----+
  9. | 1  |
  10. +----+
  11. 1 row in set (0.00 sec)

  13. mysql> SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log;
  14. +----------------------------+-----------------+-----------------+----------------------------------------------------------------------+
  15. | start_time                 | query_time      | lock_time       | sql_text                                                             |
  16. +----------------------------+-----------------+-----------------+----------------------------------------------------------------------+
  17. | 2017-11-16 15:09:32.114488 | 00:00:00.000346 | 00:00:00.000166 | SELECT * FROM test.slow WHERE id=1                                   |
  18. | 2017-11-16 15:10:50.196590 | 00:00:00.000437 | 00:00:00.000148 | SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log |
  19. +----------------------------+-----------------+-----------------+----------------------------------------------------------------------+
  20. 2 rows in set (0.00 sec)
可以發現第二次SELECT的SQL的沒有被記錄到slow log。
至於這條查詢slow log的語句本身被記錄到slow log，也是因為這條查mysql.slow_log的SQL沒有使用到索引。

因為開啟該引數造成slow log暴漲的案例：
http://blog.itpub.net/29773961/viewspace-1811829/



〇 log_throttle_queries_not_using_indexes
整型，動態引數，預設為0。
如果log_queries_not_using_indexes開啟，
那麼log_throttle_queries_not_using_indexes用於限制每分鐘所記錄的slow log數量。
設定為0則表示“不限制”。



〇 log_slow_admin_statements 
布林型，動態引數，預設為OFF。5.7後新增的引數。
可用於控制slow log是否記錄資料庫管理的SQL。
若開啟，則表示記錄這些SQL。
資料庫管理的SQL包括：
ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE。
遵循long_query_time。

第一次執行check table，時間超過2秒，但未被記錄。
第二次執行check table，開啟log_queries_not_using_indexes，超過2秒，被記錄。

  1. mysql> SELECT @@long_query_time, @@log_queries_not_using_indexes;
  2. +-------------------+---------------------------------+
  3. | @@long_query_time | @@log_queries_not_using_indexes |
  4. +-------------------+---------------------------------+
  5. | 2.000000          | 0                               |
  6. +-------------------+---------------------------------+
  7. 1 row in set (0.00 sec)

  9. mysql> CHECK TABLE test.t0;
  10. +---------+-------+----------+----------+
  11. | Table   | Op    | Msg_type | Msg_text |
  12. +---------+-------+----------+----------+
  13. | test.t0 | check | status   | OK       |
  14. +---------+-------+----------+----------+
  15. 1 row in set (4.28 sec)

  17. mysql> SET GLOBAL log_slow_admin_statements = 1;
  18. Query OK, 0 rows affected (0.00 sec)

  20. mysql> CHECK TABLE test.t0;
  21. +---------+-------+----------+----------+
  22. | Table   | Op    | Msg_type | Msg_text |
  23. +---------+-------+----------+----------+
  24. | test.t0 | check | status   | OK       |
  25. +---------+-------+----------+----------+
  26. 1 row in set (4.27 sec)

  28. mysql> SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log;
  29. +----------------------------+-----------------+-----------------+---------------------+
  30. | start_time                 | query_time      | lock_time       | sql_text            |
  31. +----------------------------+-----------------+-----------------+---------------------+
  32. | 2017-11-16 15:31:24.378343 | 00:00:04.271940 | 00:00:00.000134 | CHECK TABLE test.t0 |
  33. +----------------------------+-----------------+-----------------+---------------------+
  34. 1 row in set (0.00 sec)


〇 log_slow_slave_statements
布林型，動態引數，預設為OFF。5.7後新增的引數。
開啟後，在slave上將會記錄超過long_query_time的日誌記錄。
即便開啟了這個選項，也不會立刻生效，新的變更需要再一次START SLAVE後生效。



〇 min_examined_row_limit
整型，動態引數，預設為0。
設定該值，則表示返回行數大於等於該值的sql，將會被記錄到slow log中。

  1. mysql> SET SESSION long_query_time=0, SESSION min_examined_row_limit=5;
  2. Query OK, 0 rows affected (0.00 sec)

  4. mysql> SELECT * FROM test.t0 LIMIT 4;
  5. …………
  6. 4 rows in set (0.00 sec)

  8. mysql> SELECT * FROM test.t0 LIMIT 5;
  9. …………
  10. 5 rows in set (0.00 sec)

  12. mysql> SELECT * FROM test.t0 LIMIT 10;
  13. …………
  14. 10 rows in set (0.00 sec)

  16. mysql> SELECT start_time, query_time,lock_time,sql_text FROM mysql.slow_log;
  17. +----------------------------+-----------------+-----------------+--------------------------------+
  18. | start_time                 | query_time      | lock_time       | sql_text                       |
  19. +----------------------------+-----------------+-----------------+--------------------------------+
  20. | 2017-11-17 16:08:14.851394 | 00:00:00.000286 | 00:00:00.000134 | SELECT * FROM test.t0 LIMIT 5  |
  21. | 2017-11-17 16:08:16.744389 | 00:00:00.000284 | 00:00:00.000135 | SELECT * FROM test.t0 LIMIT 10 |
  22. +----------------------------+-----------------+-----------------+--------------------------------+
  23. 2 rows in set (0.00 sec)
個人認為大多數場景都無需刻意設定該值，取預設為0就好。



〇 log-short-format
預設為FLASE，該選項僅僅為啟動時選項，並不支援系統變數。
如果該選項被啟用，則表示在slow log中記錄更少的資訊。



〇 log_timestamps
列舉型，動態，預設為UTC，5.7.2後出現。
這個引數是用於控制記錄在error log、general log、slow log中，對應日期時區的選項。

更多可參考：
http://blog.itpub.net/29773961/viewspace-2146053/



〇 參考文件：
MySQL 5.7 Reference Manual - 5.1.3 Server Option and Variable Reference



作者微信公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2147352/，如需轉載，請註明出處，否則將追究法律責任。

相關文章