finding exact query being run

------=_NextPart_000_004B_01CB21E9.3F179DC0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_004C_01CB21E9.3F179DC0"


------=_NextPart_001_004C_01CB21E9.3F179DC0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi All



I am trying to find out how to see the exact query being
run.



When running show processlit, I get a lot of processes that
have been running for a VERY long time.



I a trying to find out exactly what query it is that is
being run , any ideas?



I tried the slow query log but it is not showing up in
there.



All the queries are showing "sleep"



Not sure if this means anything....



Machiel Richards

MySQL DBA

Relational Database Consulting



RDC_Logo




------=_NextPart_001_004C_01CB21E9.3F179DC0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

Re: finding exact query being run

--0016361e8094d206e2048b329c4f
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

SHOW FULL RPOCESSLIST

will show you the full text of the running queries.

- md

On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards wrot=
e:

> Hi All
>
>
>
> I am trying to find out how to see the exact query being
> run.
>
>
>
> When running show processlit, I get a lot of processes th=
at
> have been running for a VERY long time.
>
>
>
> I a trying to find out exactly what query it is that is
> being run , any ideas?
>
>
>
> I tried the slow query log but it is not showing up in
> there.
>
>
>
> All the queries are showing =93sleep=94
>
>
>
> Not sure if this means anything....
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> [image: RDC_Logo]
>
>
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--0016361e8094d206e2048b329c4f--

RE: finding exact query being run

------=_NextPart_000_0063_01CB21E9.D555D5E0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_0064_01CB21E9.D555D5E0"


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

I had a look and all of these just say sleep as the command.



Machiel Richards

MySQL DBA

Relational Database Consulting



RDC_Logo



From: Michael Dykman [mailto:mdykman@gmail.com]
Sent: 12 July 2010 5:43 PM
To: Machiel Richards
Cc: mysql@lists.mysql.com
Subject: Re: finding exact query being run



SHOW FULL RPOCESSLIST



will show you the full text of the running queries.


- md



On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards
wrote:

Hi All



I am trying to find out how to see the exact query being
run.



When running show processlit, I get a lot of processes that
have been running for a VERY long time.



I a trying to find out exactly what query it is that is
being run , any ideas?



I tried the slow query log but it is not showing up in
there.



All the queries are showing "sleep"



Not sure if this means anything....



Machiel Richards

MySQL DBA

Relational Database Consulting



Error! Filename not specified.






--
- michael dykman
- mdykman@gmail.com

May the Source be with you.


------=_NextPart_001_0064_01CB21E9.D555D5E0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

Re: finding exact query being run

--00c09f8992747ea26a048b32a845
Content-Type: multipart/alternative; boundary=00c09f8992747ea263048b32a844

--00c09f8992747ea263048b32a844
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Am I correct in assuming that your application does connection pooling? If
so, then these long lasting, idle threads are normal.

If there is no connection pooling, then this might be a symptom of a proble=
m
with your application server not letting go of processes properly.

- md

On Mon, Jul 12, 2010 at 11:44 AM, Machiel Richards wrot=
e:

> I had a look and all of these just say sleep as the command.
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> [image: RDC_Logo]
>
>
>
> *From:* Michael Dykman [mailto:mdykman@gmail.com]
> *Sent:* 12 July 2010 5:43 PM
> *To:* Machiel Richards
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: finding exact query being run
>
>
>
> SHOW FULL RPOCESSLIST
>
>
>
> will show you the full text of the running queries.
>
>
> - md
>
>
>
> On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards
> wrote:
>
> Hi All
>
>
>
> I am trying to find out how to see the exact query being
> run.
>
>
>
> When running show processlit, I get a lot of processes th=
at
> have been running for a VERY long time.
>
>
>
> I a trying to find out exactly what query it is that is
> being run , any ideas?
>
>
>
> I tried the slow query log but it is not showing up in
> there.
>
>
>
> All the queries are showing =93sleep=94
>
>
>
> Not sure if this means anything....
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> *Error! Filename not specified.*
>
>
>
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--00c09f8992747ea263048b32a844
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Am I correct in assuming that your=A0application=A0does connection pooling?=
=A0If so, then these long lasting, idle threads are normal.

RE: finding exact query being run

------=_NextPart_000_0074_01CB21EA.E8CE01F0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_0075_01CB21EA.E8CE01F0"


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

mmm... I am not sure about the connection pooling... Fairly new to the site.



What I do know is that they run a couple of web servers that connect to the
MySQL database and are currently experiencing some performance and
connection problems.



There are a lot of processes that have been running for more than 25000
seconds and all of them seem to be coming from the web server / host.



I am trying to find out whether this can be the cause of the problem and if
o if we can simply kill these processes.







Machiel Richards

herbalife_logo



From: Michael Dykman [mailto:mdykman@gmail.com]
Sent: 12 July 2010 5:47 PM
To: Machiel Richards
Cc: mysql@lists.mysql.com
Subject: Re: finding exact query being run



Am I correct in assuming that your application does connection pooling? If
so, then these long lasting, idle threads are normal.



If there is no connection pooling, then this might be a symptom of a problem
with your application server not letting go of processes properly.



- md

On Mon, Jul 12, 2010 at 11:44 AM, Machiel Richards
wrote:

I had a look and all of these just say sleep as the command.



Machiel Richards

MySQL DBA

Relational Database Consulting



RDC_Logo



From: Michael Dykman [mailto:mdykman@gmail.com]
Sent: 12 July 2010 5:43 PM
To: Machiel Richards
Cc: mysql@lists.mysql.com
Subject: Re: finding exact query being run



SHOW FULL RPOCESSLIST



will show you the full text of the running queries.


- md



On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards
wrote:

Hi All



I am trying to find out how to see the exact query being
run.



When running show processlit, I get a lot of processes that
have been running for a VERY long time.



I a trying to find out exactly what query it is that is
being run , any ideas?



I tried the slow query log but it is not showing up in
there.



All the queries are showing "sleep"



Not sure if this means anything....



Machiel Richards

MySQL DBA

Relational Database Consulting



Error! Filename not specified.






--
- michael dykman
- mdykman@gmail.com

May the Source be with you.




--
- michael dykman
- mdykman@gmail.com

May the Source be with you.


------=_NextPart_001_0075_01CB21EA.E8CE01F0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

Re: finding exact query being run

--00c09f899274611783048b32e718
Content-Type: multipart/alternative; boundary=00c09f899274611772048b32e717

--00c09f899274611772048b32e717
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

What are they running on those web servers? PHP? if so, which is of the
several PHP interfaces to MySQL are they using? (Zend Enterprise does some
smart connection pooling). Java-based applications tend to use connection
polling more often than not.

You should find out to determine if those sleeping connections are a good
sign or a bad one.

On Mon, Jul 12, 2010 at 11:51 AM, machiel.richards <
machiel.richards@gmail.com> wrote:

> mmm... I am not sure about the connection pooling... Fairly new to the
> site.
>
>
>
> What I do know is that they run a couple of web servers that connect to t=
he
> MySQL database and are currently experiencing some performance and
> connection problems.
>
>
>
> There are a lot of processes that have been running for more than 25000
> seconds and all of them seem to be coming from the web server / host.
>
>
>
> I am trying to find out whether this can be the cause of the problem and =
if
> o if we can simply kill these processes.
>
>
>
>
>
>
>
> *Machiel Richards*
>
> [image: herbalife_logo]
>
>
>
> *From:* Michael Dykman [mailto:mdykman@gmail.com]
> *Sent:* 12 July 2010 5:47 PM
>
> *To:* Machiel Richards
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: finding exact query being run
>
>
>
> Am I correct in assuming that your application does connection pooling? =
If
> so, then these long lasting, idle threads are normal.
>
>
>
> If there is no connection pooling, then this might be a symptom of a
> problem with your application server not letting go of processes properly=
..
>
>
>
> - md
>
> On Mon, Jul 12, 2010 at 11:44 AM, Machiel Richards
> wrote:
>
> I had a look and all of these just say sleep as the command.
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> [image: RDC_Logo]
>
>
>
> *From:* Michael Dykman [mailto:mdykman@gmail.com]
> *Sent:* 12 July 2010 5:43 PM
> *To:* Machiel Richards
> *Cc:* mysql@lists.mysql.com
> *Subject:* Re: finding exact query being run
>
>
>
> SHOW FULL RPOCESSLIST
>
>
>
> will show you the full text of the running queries.
>
>
> - md
>
>
>
> On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards
> wrote:
>
> Hi All
>
>
>
> I am trying to find out how to see the exact query being
> run.
>
>
>
> When running show processlit, I get a lot of processes th=
at
> have been running for a VERY long time.
>
>
>
> I a trying to find out exactly what query it is that is
> being run , any ideas?
>
>
>
> I tried the slow query log but it is not showing up in
> there.
>
>
>
> All the queries are showing =93sleep=94
>
>
>
> Not sure if this means anything....
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> *Error! Filename not specified.*
>
>
>
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--00c09f899274611772048b32e717
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

What are they running on those web servers? =A0PHP? =A0if so, which is of t=
he several PHP interfaces to MySQL are they using? (Zend Enterprise does so=
me smart connection pooling). =A0Java-based applications tend to use connec=
tion polling more often than not.

Re: finding exact query being run

--00221597511e4836bf048b34d2c0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

If you have lots of sleeping processes the chances are you have a poorly
written app that is not closing its database connections. Those sleeping
processes are just connections waiting for another command from the
application or whatever else initiated them.

The sleeping processes will not show up in the slow query log because they
are not slow queries and you cannot see the sql they are running because
they aren't running any.

If you need to prevent these building up (and you do because they will eat
away at system resources) you can add a wait_timeout option to your my.cnf =
/
my.ini file but the best way to control these is ensure your app closes its
connections when it is finished with them and do not use persistent
connections unless absolutely necessary

Regards

John

--------------------------------
John Daisley

SQL Server 2005/2008 Database Administrator
Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

---------------------------------

On 12 July 2010 16:39, Machiel Richards wrote:

> Hi All
>
>
>
> I am trying to find out how to see the exact query being
> run.
>
>
>
> When running show processlit, I get a lot of processes th=
at
> have been running for a VERY long time.
>
>
>
> I a trying to find out exactly what query it is that is
> being run , any ideas?
>
>
>
> I tried the slow query log but it is not showing up in
> there.
>
>
>
> All the queries are showing =93sleep=94
>
>
>
> Not sure if this means anything....
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> [image: RDC_Logo]
>
>
>



--=20
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--00221597511e4836bf048b34d2c0--

Re: finding exact query being run

--0016363b801ea8e0f2048b35eb60
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

In my.cnf, check wait_timeout value , default is 4800. Make it to
wait_timeout =3D 60
Thus, the connections will automatically be closed after waiting for 60
seconds.

On Mon, Jul 12, 2010 at 11:51 PM, John Daisley
wrote:

> If you have lots of sleeping processes the chances are you have a poorly
> written app that is not closing its database connections. Those sleeping
> processes are just connections waiting for another command from the
> application or whatever else initiated them.
>
> The sleeping processes will not show up in the slow query log because the=
y
> are not slow queries and you cannot see the sql they are running because
> they aren't running any.
>
> If you need to prevent these building up (and you do because they will ea=
t
> away at system resources) you can add a wait_timeout option to your my.cn=
f
> /
> my.ini file but the best way to control these is ensure your app closes i=
ts
> connections when it is finished with them and do not use persistent
> connections unless absolutely necessary
>
> Regards
>
> John
>
> --------------------------------
> John Daisley
>
> SQL Server 2005/2008 Database Administrator
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.daisley@butterflysystems.co.uk
>
> ---------------------------------
>
> On 12 July 2010 16:39, Machiel Richards wrote:
>
> > Hi All
> >
> >
> >
> > I am trying to find out how to see the exact query bein=
g
> > run.
> >
> >
> >
> > When running show processlit, I get a lot of processes
> that
> > have been running for a VERY long time.
> >
> >
> >
> > I a trying to find out exactly what query it is that is
> > being run , any ideas?
> >
> >
> >
> > I tried the slow query log but it is not showing up in
> > there.
> >
> >
> >
> > All the queries are showing =93sleep=94
> >
> >
> >
> > Not sure if this means anything....
> >
> >
> >
> > Machiel Richards
> >
> > MySQL DBA
> >
> > Relational Database Consulting
> >
> >
> >
> > [image: RDC_Logo]
> >
> >
> >
>
>
>
> --
> John Daisley
>
> Certified MySQL 5 Database Administrator
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> Telephone: +44 (0)7918 621621
> Email: john.daisley@butterflysystems.co.uk
>



--=20
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--0016363b801ea8e0f2048b35eb60--

RE: finding exact query being run

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

get "mytop"


_____

From: Machiel Richards [mailto:machielr@rdc.co.za]
Sent: Monday, July 12, 2010 8:40 AM
To: mysql@lists.mysql.com
Subject: finding exact query being run



Hi All



I am trying to find out how to see the exact query being
run.



When running show processlit, I get a lot of processes that
have been running for a VERY long time.



I a trying to find out exactly what query it is that is
being run , any ideas?



I tried the slow query log but it is not showing up in
there.



All the queries are showing "sleep"



Not sure if this means anything....



Machiel Richards

MySQL DBA

Relational Database Consulting



RDC_Logo




------=_NextPart_000_0120_01CB21D3.B0432E80--

Re: finding exact query being run

--000e0cd29fc4d9903f048b407e5f
Content-Type: text/plain; charset=ISO-8859-1

Which will still not be particularly helpful for subsecond queries :-)

Either turn on the full query log (beware of I/O implications, use separate
spindles if possible) or use mk-query-analyzer with tcpdump to get a running
analysis of what passes the line.

The addition of tcpdump support is my all-time favourite feature of maatkit
:-D


On Tue, Jul 13, 2010 at 12:05 AM, Daevid Vincent wrote:

> get "mytop"
>
>
> _____
>
> From: Machiel Richards [mailto:machielr@rdc.co.za]
> Sent: Monday, July 12, 2010 8:40 AM
> To: mysql@lists.mysql.com
> Subject: finding exact query being run
>
>
>
> Hi All
>
>
>
> I am trying to find out how to see the exact query being
> run.
>
>
>
> When running show processlit, I get a lot of processes that
> have been running for a VERY long time.
>
>
>
> I a trying to find out exactly what query it is that is
> being run , any ideas?
>
>
>
> I tried the slow query log but it is not showing up in
> there.
>
>
>
> All the queries are showing "sleep"
>
>
>
> Not sure if this means anything....
>
>
>
> Machiel Richards
>
> MySQL DBA
>
> Relational Database Consulting
>
>
>
> RDC_Logo
>
>
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000e0cd29fc4d9903f048b407e5f--

RE: finding exact query being run

Well, you should be using a wrapper around your DB library class already
right? Like you don't use PEAR:DB directly, you have a wrapper class to
handle making a connection, doing the query, handling the error, etc.
Otherwise you're wasting a LOT of code/time. Not to mention it makes it
trivial to swap out the calls to another database library without chaning
all of YOUR code.

So all I did is wedge a logger/profiler into my sql_query() routine. This
has various levels of logging and saving the start/end times, etc.

> -----Original Message-----
> From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On
> Behalf Of Johan De Meersman
> Sent: Tuesday, July 13, 2010 1:17 AM
> To: Daevid Vincent
> Cc: Machiel Richards; mysql@lists.mysql.com
> Subject: Re: finding exact query being run
>
> Which will still not be particularly helpful for subsecond queries :-)
>
> Either turn on the full query log (beware of I/O
> implications, use separate
> spindles if possible) or use mk-query-analyzer with tcpdump
> to get a running
> analysis of what passes the line.
>
> The addition of tcpdump support is my all-time favourite
> feature of maatkit
> :-D
>
>
> On Tue, Jul 13, 2010 at 12:05 AM, Daevid Vincent
> wrote:
>
> > get "mytop"
> >
> >
> > _____
> >
> > From: Machiel Richards [mailto:machielr@rdc.co.za]
> > Sent: Monday, July 12, 2010 8:40 AM
> > To: mysql@lists.mysql.com
> > Subject: finding exact query being run
> >
> >
> >
> > Hi All
> >
> >
> >
> > I am trying to find out how to see the exact
> query being
> > run.
> >
> >
> >
> > When running show processlit, I get a lot of
> processes that
> > have been running for a VERY long time.
> >
> >
> >
> > I a trying to find out exactly what query it
> is that is
> > being run , any ideas?
> >
> >
> >
> > I tried the slow query log but it is not
> showing up in
> > there.
> >
> >
> >
> > All the queries are showing "sleep"
> >
> >
> >
> > Not sure if this means anything....
> >
> >
> >
> > Machiel Richards
> >
> > MySQL DBA
> >
> > Relational Database Consulting
> >
> >
> >
> > RDC_Logo
> >
> >
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>


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