List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:February 16 2010 2:31pm
Subject:Re: Enabling Slow query log in Mysql 5.0
View as plain text  
Machiel Richards wrote:
> Hi All
> 
>  
> 
>                 I hope that someone can assist me with this.
> 
>  
> 
>                 We have a client with a production MySQL database running
> MySQL 5.0.
> 
>                 
> 
>                 Their slow query counts have skyrocketed over the last week
> and I found that their slow query logs are not enabled.
> 
>  
> 
>                 However when trying to configure this I get the following
> message:
> 
>  
> 
> mysql> set global log_slow_queries=ON;
> 
> ERROR 1238 (HY000): Variable 'log_slow_queries' is a read only variable
> 
> mysql>
> 
>  
> 
>                 I did the same thing many times before on other databases
> but this specific one gives me this message.
> 
>  
> 
>                 Can anyone perhaps give me some insight as to why i'm
> getting this and how to enable it (preferably without having to restart the
> database seeing it is a high availability production system)?
> 
>  

You can do what you described in version 5.1 but not in 5.0 . For 5.0 
that variable is not dynamic. That means you cannot change it while the 
system is running:

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_log-slow-queries

One way around this is to setup the machine with the Slow Query Log 
enabled but to use a very large value of --long-query-time to 
essentially ignore every query. Then, when you want to capture slow 
queries, you reset --long-query-time to a reasonable value. 
Unfortunately, this requires a restart to initialize. After that you can 
adjust the --long-query-time to throttle the contents of the log.

http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_long_query_time

-- 
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN


Thread
Enabling Slow query log in Mysql 5.0Machiel Richards16 Feb
  • Re: Enabling Slow query log in Mysql 5.0Krishna Chandra Prajapati16 Feb
  • Re: Enabling Slow query log in Mysql 5.0Shawn Green16 Feb