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 dae...@daevid.com wrote: get mytop _ From: Machiel Richards [mailto:machi...@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
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: vegiv...@gmail.com [mailto:vegiv...@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 dae...@daevid.com wrote: get mytop _ From: Machiel Richards [mailto:machi...@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=arch...@jab.org
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 machi...@rdc.co.zawrote: 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 [image: RDC_Logo] -- - michael dykman - mdyk...@gmail.com May the Source be with you.
RE: finding exact query being run
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:mdyk...@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 machi...@rdc.co.za 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 - mdyk...@gmail.com May the Source be with you.
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 machi...@rdc.co.zawrote: 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:mdyk...@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 machi...@rdc.co.za 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
RE: finding exact query being run
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:mdyk...@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 machi...@rdc.co.za 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:mdyk...@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 machi...@rdc.co.za 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: finding exact query being run
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.richa...@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 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* [image: herbalife_logo] *From:* Michael Dykman [mailto:mdyk...@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 machi...@rdc.co.za 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:mdyk...@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 machi...@rdc.co.za 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 - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: finding exact query being run
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.dais...@butterflysystems.co.uk - On 12 July 2010 16:39, Machiel Richards machi...@rdc.co.za 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 [image: RDC_Logo] -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: finding exact query being run
In my.cnf, check wait_timeout value , default is 4800. Make it to wait_timeout = 60 Thus, the connections will automatically be closed after waiting for 60 seconds. On Mon, Jul 12, 2010 at 11:51 PM, John Daisley daisleyj...@googlemail.comwrote: 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.dais...@butterflysystems.co.uk - On 12 July 2010 16:39, Machiel Richards machi...@rdc.co.za 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 [image: RDC_Logo] -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- 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
RE: finding exact query being run
get mytop _ From: Machiel Richards [mailto:machi...@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
