———————————————————————-
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


動的にSLOWログを有効/無効出来るか確認

MySQL 5.1.12 から、ランタイムで一般クエリ ログとスロー クエリ ログを
無効化できるようになりました。 」

4.11.6. ログ ファイルの保守に書かれてました。


[root@colinux ~]# mysqladmin version -u root -p
Enter password:
mysqladmin  Ver 8.42 Distrib 5.1.30, for pc-linux-gnu on i686
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.30-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 52 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 17  Flush tables: 1  Open tabl
es: 4  Queries per second avg: 0.19
[root@colinux ~]#

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


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

mysql>

slow

mysql>SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.01 sec)

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

mysql>

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

mysql>

mysql>SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

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

mysql>

slow_query_log

===========================================================

MySQL 5.1.12 から、ランタイムで一般クエリ ログとスロー クエリ ログを無効化できるようになりました。

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

ログを無効化した状態で、コマンドラインなどを使用して、ログ ファイルの名前を外部的に変更します。そして、ログを再び有効化します。

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

このやり方は、どのプラットフォームでも使用でき、サーバの再起動は不要です。

===========================================================