———————————————————————-
log_output=TABLEを設定するとCSVストレージエンジンのテーブルに格納
出来るので調査が簡単に出来る。5.1からは動的に設定変更可能なので
調べたい時のみONに設定して普段は負荷を考えてOFFにしておくのも
良いかと思います。


slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/slow_query.log
log_output=TABLE
long_query_time=2

———————————————————————-


mysql> show variables like '%slow%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow_query.log |
+---------------------+--------------------------------------+
4 rows in set (0.00 sec)

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'log_%';
+---------------------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | /usr/local/mysql/data/study01.local.err |
| log_output | FILE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
+---------------------------------+----------------------------------------------+
9 rows in set (0.00 sec)

mysql>

----------------------------------------------------------------------
MYSQL5.1 slow_query_logの動的設定確認
----------------------------------------------------------------------

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow_query.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> set @@global.slow_query_log = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/slow_query.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> set @@global.slow_query_log = ON;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow_query.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

mysql> select version();
+------------+
| version() |
+------------+
| 5.1.34-log |
+------------+
1 row in set (0.00 sec)

mysql>

slow_query

———————————————————————-
information_schema.processlistでも現在実行中のコマンド時間を調査出来る。
———————————————————————-


mysql> select * from information_schema.processlist;
+------+------+-----------+--------------------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+------+------+-----------+--------------------+---------+------+-----------+----------------------------------------------+
| 2476 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.processlist |
+------+------+-----------+--------------------+---------+------+-----------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

———————————————————————-
サーバで実行時間がかかった SQL 文
上位 5 個を見つける場合、次の SQL を発行。
———————————————————————-

select substr(sql_text,1,30), query_time, rows_examined
from slow_log
order by 2 desc limit 5;

wp_table

Comments are closed.

Post Navigation