slow queries not being logged

------=_NextPart_000_0044_01CAB46E.DC5B65A0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good day all



I hope you can assist me with this one...



We have a client where the slow query log was disabled.



We noticed that the slow query amount (when viewing global
status) has skyrocketed during the last 2 weeks going up to over 2 million
(from 160 million queries).



We wanted to look at these queries to see if it can be
optimised to reduce the amount and went through the whole database restart
routine to enable the slow query log again (they are running version 5.0 so
had to restart).





However, even though the slow query log is enabled, it is
not logging the queries to the file specified.



Can someone please assist in why this is not being done? I
thought that it might be logging to a default filename but there is only one
slow queries log file in the directory and it is empty.



Checking the global status again, it showed 29 000 slow
queries since this morning (3 hours ago) but nothing in the logs.





Your help will be appreciated.





Regards




------=_NextPart_000_0044_01CAB46E.DC5B65A0--

RE: slow queries not being logged

--_1d8729bf-68df-4aa6-a244-d27a3730e8c7_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable




> From: machielr@rdc.co.za
> To: mysql@lists.mysql.com
> Subject: slow queries not being logged
> Date: Tue=2C 23 Feb 2010 09:59:13 +0200
>=20
> Good day all
>=20
> =20
>=20
> I hope you can assist me with this one...
>=20
> =20
>=20
> We have a client where the slow query log was disabled.
>=20

Slow query log is on the server only. If you are saying you have enabled t=
he slow query log and the servers query log is empty can you post your my.c=
nf/my.ini file. Also make sure --long-query-time is set appropriately.


> =20
>=20
> We noticed that the slow query amount (when viewing globa=
l
> status) has skyrocketed during the last 2 weeks going up to over 2 millio=
n
> (from 160 million queries).
>=20
> =20
>=20
> We wanted to look at these queries to see if it can be
> optimised to reduce the amount and went through the whole database restar=
t
> routine to enable the slow query log again (they are running version 5.0 =
so
> had to restart).
>=20
> =20
>=20
> =20
>=20
> However=2C even though the slow query log is enabled=2C i=
t is
> not logging the queries to the file specified.
>=20
> =20
>=20
> Can someone please assist in why this is not being done? =
I
> thought that it might be logging to a default filename but there is only =
one
> slow queries log file in the directory and it is empty.
>=20
> =20
>=20
> Checking the global status again=2C it showed 29 000 slow
> queries since this morning (3 hours ago) but nothing in the logs.
>=20
> =20
>=20
> =20
>=20
> Your help will be appreciated.
>=20
> =20
>=20
> =20
>=20
> Regards
>=20
> =20
>=20
=20
____________________________________________________________ _____
Do you have a story that started on Hotmail? Tell us now
http://clk.atdmt.com/UKM/go/195013117/direct/01/=

--_1d8729bf-68df-4aa6-a244-d27a3730e8c7_--

RE: slow queries not being logged

------=_NextPart_000_006C_01CAB473.95322290
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi All



I found my problem and this was kind of a blonde moment for
me...



When configuring the log_slow_queries parameter, it was
configured as follows: log_slow_queries=1



This the file being created is called 1 and the 1 does not
mean it is enabled.



I have fixed this now but need to wait for a gap to reboot
again to have it set properly. (have to live with the filename 1 for the
time being.)



I did however find something interesting though, while
looking at the queries being logged.



The slow_query_time is set to 2 (2 seconds i am assuming)
however all the queries being logged states that it ran for 0 seconds.



I am busy doing explain plans on some of them now but not
really sure what to look for yet (Rather new to MySQL and hope google will
have some answers J )





Thank you









From: John Daisley [mailto:mg_sv_r@hotmail.com]
Sent: 23 February 2010 10:24 AM
To: machielr@rdc.co.za; mysql@lists.mysql.com
Subject: RE: slow queries not being logged





> From: machielr@rdc.co.za
> To: mysql@lists.mysql.com
> Subject: slow queries not being logged
> Date: Tue, 23 Feb 2010 09:59:13 +0200
>
> Good day all
>
>
>
> I hope you can assist me with this one...
>
>
>
> We have a client where the slow query log was disabled.
>

Slow query log is on the server only. If you are saying you have enabled
the slow query log and the servers query log is empty can you post your
my.cnf/my.ini file. Also make sure --long-query-time is set appropriately.


>
>
> We noticed that the slow query amount (when viewing global
> status) has skyrocketed during the last 2 weeks going up to over 2 million
> (from 160 million queries).
>
>
>
> We wanted to look at these queries to see if it can be
> optimised to reduce the amount and went through the whole database restart
> routine to enable the slow query log again (they are running version 5.0
so
> had to restart).
>
>
>
>
>
> However, even though the slow query log is enabled, it is
> not logging the queries to the file specified.
>
>
>
> Can someone please assist in why this is not being done? I
> thought that it might be logging to a default filename but there is only
one
> slow queries log file in the directory and it is empty.
>
>
>
> Checking the global status again, it showed 29 000 slow
> queries since this morning (3 hours ago) but nothing in the logs.
>
>
>
>
>
> Your help will be appreciated.
>
>
>
>
>
> Regards
>
>
>

_____

Do you want a Hotmail account? Sign-up
now - Free


------=_NextPart_000_006C_01CAB473.95322290--

Re: slow queries not being logged

--001636e0a7b203e8b90480407da4
Content-Type: text/plain; charset=ISO-8859-1

slow query log will also have sql's which are not using indexes(doing full
table scan).
May be those queries with "ZERO SECOND" run on small table without using
indexes.

regards
anandkl

On Tue, Feb 23, 2010 at 2:02 PM, Machiel Richards wrote:

> Hi All
>
>
>
> I found my problem and this was kind of a blonde moment for
> me...
>
>
>
> When configuring the log_slow_queries parameter, it was
> configured as follows: log_slow_queries=1
>
>
>
> This the file being created is called 1 and the 1 does not
> mean it is enabled.
>
>
>
> I have fixed this now but need to wait for a gap to reboot
> again to have it set properly. (have to live with the filename 1 for the
> time being.)
>
>
>
> I did however find something interesting though, while
> looking at the queries being logged.
>
>
>
> The slow_query_time is set to 2 (2 seconds i am assuming)
> however all the queries being logged states that it ran for 0 seconds.
>
>
>
> I am busy doing explain plans on some of them now but not
> really sure what to look for yet (Rather new to MySQL and hope google will
> have some answers J )
>
>
>
>
>
> Thank you
>
>
>
>
>
>
>
>
>
> From: John Daisley [mailto:mg_sv_r@hotmail.com]
> Sent: 23 February 2010 10:24 AM
> To: machielr@rdc.co.za; mysql@lists.mysql.com
> Subject: RE: slow queries not being logged
>
>
>
>
>
> > From: machielr@rdc.co.za
> > To: mysql@lists.mysql.com
> > Subject: slow queries not being logged
> > Date: Tue, 23 Feb 2010 09:59:13 +0200
> >
> > Good day all
> >
> >
> >
> > I hope you can assist me with this one...
> >
> >
> >
> > We have a client where the slow query log was disabled.
> >
>
> Slow query log is on the server only. If you are saying you have enabled
> the slow query log and the servers query log is empty can you post your
> my.cnf/my.ini file. Also make sure --long-query-time is set appropriately.
>
>
> >
> >
> > We noticed that the slow query amount (when viewing global
> > status) has skyrocketed during the last 2 weeks going up to over 2
> million
> > (from 160 million queries).
> >
> >
> >
> > We wanted to look at these queries to see if it can be
> > optimised to reduce the amount and went through the whole database
> restart
> > routine to enable the slow query log again (they are running version 5.0
> so
> > had to restart).
> >
> >
> >
> >
> >
> > However, even though the slow query log is enabled, it is
> > not logging the queries to the file specified.
> >
> >
> >
> > Can someone please assist in why this is not being done? I
> > thought that it might be logging to a default filename but there is only
> one
> > slow queries log file in the directory and it is empty.
> >
> >
> >
> > Checking the global status again, it showed 29 000 slow
> > queries since this morning (3 hours ago) but nothing in the logs.
> >
> >
> >
> >
> >
> > Your help will be appreciated.
> >
> >
> >
> >
> >
> > Regards
> >
> >
> >
>
> _____
>
> Do you want a Hotmail account? Sign-up
> now - Free
>
>

--001636e0a7b203e8b90480407da4--

Re: slow queries not being logged

You might want to read the comments to this posting:

http://www.bitbybit.dk/carsten/blog/?p=116



Several tools/methods for controlling and analyzing the slow query log are

suggested there.



Best,



/ Carsten



On Tue, 23 Feb 2010 14:09:30 +0530, Ananda Kumar

wrote:

> slow query log will also have sql's which are not using indexes(doing

full

> table scan).

> May be those queries with "ZERO SECOND" run on small table without using

> indexes.

>

> regards

> anandkl

>

> On Tue, Feb 23, 2010 at 2:02 PM, Machiel Richards

> wrote:

>

>> Hi All

>>

>>

>>

>> I found my problem and this was kind of a blonde moment

>> for

>> me...

>>

>>

>>

>> When configuring the log_slow_queries parameter, it was

>> configured as follows: log_slow_queries=1

>>

>>

>>

>> This the file being created is called 1 and the 1 does

not

>> mean it is enabled.

>>

>>

>>

>> I have fixed this now but need to wait for a gap to

reboot

>> again to have it set properly. (have to live with the filename 1 for

the

>> time being.)

>>

>>

>>

>> I did however find something interesting though, while

>> looking at the queries being logged.

>>

>>

>>

>> The slow_query_time is set to 2 (2 seconds i am

assuming)

>> however all the queries being logged states that it ran for 0 seconds.

>>

>>

>>

>> I am busy doing explain plans on some of them now but

not

>> really sure what to look for yet (Rather new to MySQL and hope google

>> will

>> have some answers J )

>>

>>

>>

>>

>>

>> Thank you

>>

>>

>>

>>

>>

>>

>>

>>

>>

>> From: John Daisley [mailto:mg_sv_r@hotmail.com]

>> Sent: 23 February 2010 10:24 AM

>> To: machielr@rdc.co.za; mysql@lists.mysql.com

>> Subject: RE: slow queries not being logged

>>

>>

>>

>>

>>

>> > From: machielr@rdc.co.za

>> > To: mysql@lists.mysql.com

>> > Subject: slow queries not being logged

>> > Date: Tue, 23 Feb 2010 09:59:13 +0200

>> >

>> > Good day all

>> >

>> >

>> >

>> > I hope you can assist me with this one...

>> >

>> >

>> >

>> > We have a client where the slow query log was disabled.

>> >

>>

>> Slow query log is on the server only. If you are saying you have

enabled

>> the slow query log and the servers query log is empty can you post your

>> my.cnf/my.ini file. Also make sure --long-query-time is set

>> appropriately.

>>

>>

>> >

>> >

>> > We noticed that the slow query amount (when viewing global

>> > status) has skyrocketed during the last 2 weeks going up to over 2

>> million

>> > (from 160 million queries).

>> >

>> >

>> >

>> > We wanted to look at these queries to see if it can be

>> > optimised to reduce the amount and went through the whole database

>> restart

>> > routine to enable the slow query log again (they are running version

>> > 5.0

>> so

>> > had to restart).

>> >

>> >

>> >

>> >

>> >

>> > However, even though the slow query log is enabled, it is

>> > not logging the queries to the file specified.

>> >

>> >

>> >

>> > Can someone please assist in why this is not being done? I

>> > thought that it might be logging to a default filename but there is

>> > only

>> one

>> > slow queries log file in the directory and it is empty.

>> >

>> >

>> >

>> > Checking the global status again, it showed 29 000 slow

>> > queries since this morning (3 hours ago) but nothing in the logs.

>> >

>> >

>> >

>> >

>> >

>> > Your help will be appreciated.

>> >

>> >

>> >

>> >

>> >

>> > Regards

>> >

>> >

>> >

>>

>> _____

>>

>> Do you want a Hotmail account? Sign-up

>> now - Free

>>

>>

>

>

> !DSPAM:451,4b839535858212076517642!

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