How do I enable slow query logs in mysql 5?[solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

How do I enable slow query logs in mysql 5?[solved]

Post by raghavan20 »

I found from the system variables that slow query log is turned off and I want to turn it on...

Code: Select all

mysql> show variables like  'log_slow_queries';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set @@log_slow_queries=on;
ERROR 1193 (HY000): Unknown system variable 'log_slow_queries'
mysql> set @log_slow_queries=on;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'on' at line 1
mysql> set log_slow_queries=on;
ERROR 1193 (HY000): Unknown system variable 'log_slow_queries'
Last edited by raghavan20 on Fri Jan 20, 2006 6:38 am, edited 1 time in total.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I have read it already. I just want to set the variable to value ON, how do I do that?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Look at the long_query_time is set to 1 now, but after the query ran for 14.9 sec, there is no log entry in the log file...

Code: Select all

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

mysql> set long_query_time=3;
Query OK, 0 rows affected (0.02 sec)

mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set long_query_time=.1;
ERROR 1232 (42000): Incorrect argument type to variable 'long_query_time'
mysql> select benchmark(1000000000, "select * from mysql.user");
+---------------------------------------------------+
| benchmark(1000000000, "select * from mysql.user") |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (14.97 sec)
2. Is it not possible to set values less than 1 for long_query_time?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

raghavan20 wrote:I have read it already. I just want to set the variable to value ON, how do I do that?
via the config file - do you have access to that?

see section 3 here: http://hackmysql.com/nontech

Mac
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

It worked after I added this line to my.conf file

Code: Select all

log-slow-queries

mysql> show variables like '%log_slo%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
+------------------+-------+
1 row in set (0.01 sec)
EDIT: query used

Code: Select all

mysql> select benchmark(10000000000000000000000, "select * from forum_db.forums_tbl");
+-------------------------------------------------------------------------+
| benchmark(10000000000000000000000, "select * from forum_db.forums_tbl") |
+-------------------------------------------------------------------------+
|                                                                       0 |
+-------------------------------------------------------------------------+
1 row in set (1 min 5.41 sec)
log file:

Code: Select all

mysqld, Version: 5.0.18-log. started with:
Tcp port: 3306  Unix socket: (null)
Time                 Id Command    Argument
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.18-nt-log. started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 060120 12:03:56
# User@Host: [ODBC] @ localhost [127.0.0.1]
# Query_time: 65  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
select benchmark(10000000000000000000000, "select * from forum_db.forums_tbl");
Post Reply