Enabling Slow query log in Mysql 5.0

------=_NextPart_000_0001_01CAAEE9.74757C30
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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)?



Help is much appreciated.



Regards


------=_NextPart_000_0001_01CAAEE9.74757C30--

Re: Enabling Slow query log in Mysql 5.0

--0016e68dd284c802bc047fb3673f
Content-Type: text/plain; charset=ISO-8859-1

Hi Machiel,

The below link will help you.
mk-query-digesthttp://www.xaprb.com/blog/category/maatkit/

Regards,
Krishna



On Tue, Feb 16, 2010 at 12:51 PM, 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)?
>
>
>
> Help is much appreciated.
>
>
>
> Regards
>
>

--0016e68dd284c802bc047fb3673f--

Re: Enabling Slow query log in Mysql 5.0

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#o ption_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-variabl es.html#sysvar_long_query_time

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org