Sleeping Processes Timeout?
Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sleeping Processes Timeout?
Jason Williard wrote: Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard Jason, Your web application is probably using persistent connections when it does not need them. The best thing to do, if that's the case, would be replace all the mysql_pconnect calls in your application with mysql_connect (or what ever the language-specific function name is). You could also set the wait_timeout value in your my.cnf to something shorter, but this would affect all your applications and does not address the source of your problem. Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sleeping Processes Timeout?
I had a similar problem while connecting to mysql 4.0.21 throuhg MyODBC 3.51.11. If so you must upgrade mysql to 4.1 or downgrade MyOdbc to 3.51.06 Jason Williard wrote: Hello, Today, I started experiencing some issues on our website with max connections exceeded errors. In looking into this, I found that we had too many sleeping processes. I was not even able to login to the mysql server from a command line. Is there a way to set a timeout for sleeping processes? This is a small example of what I am seeing when I run a 'show processlist' query: +--+--+---+---+-+--+---+ --+ | Id | User | Host | db| Command | Time | State | Info | +--+--+---+---+-+--+---+ --+ | 584 | root | localhost | blogs | Sleep | 915 | | NULL | | 595 | root | localhost | blogs | Sleep | 900 | | NULL | +--+--+---+---+-+--+---+ --+ --- Thank You Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sleeping processes
Does this weird behavior remain if you're connecting trough UNIX socket
to local instance of MySQL?
Ronny Melz [EMAIL PROTECTED] wrote:
Thanks so far for your advice,
Is it possible that your application doesn't close connection properly?
that is exactly what also I think is the problem's cause, but I am unable to
locate the place where it actually does happen. My code seems straightforward
and I had looked over it some other more experienced people which were unable
to find the bug as well... weird
Check with netstat the states of connections between your application
and server.
'$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql
show full processlist' up to the point where max_connections are reached:
then the mysql processlist reports max_connections+1 pids (including the
terminal I use to get the processlist) whereas a '$ netstat | grep mysql | wc
-l' does never return due to an ever increasing number of open connections.
Each of them is in state TIME_WAIT.
Do you see some sleeping processes
with ps utility or 'mysqladmin processlist' command?
AFAIK, 'mysqladmin processlist' prints the same as a 'mysql show full
processlist', right? It's max_connections sleeping processes plus the
processlist query.
Your hint to watch out for sleeping processes with ps was interesting.
Actually, I have some 14 processes ('ps lax | grep mysql') without running my
program but max_connections+14 if it is running. Each of the processes is
sleeping. I still don't have any idea, do you?
Any suggestions appreciated.
Ronny
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sleeping processes
On Monday 06 June 2005 10:58, Gleb Paharenko wrote: Does this weird behavior remain if you're connecting trough UNIX socket to local instance of MySQL? it does. At the moment, I try to approach the problem Carl proposed and starting from scratch. It works fine - hence the problem is not mysql, but most probably hidden somewhere in the source of the rest of the program I am improving. It bothers me a bit that I didn't try this earlier, since probably it was the wrong community I was posting to (nevertheless, the problem remains to locate the exact position of the error...). Thank you very much, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: sleeping processes
Hi Carl, thank you for your reply. did you have a look at my original posting where I included the code? your code (omitting the error routines) is essentially like this: sock=mysql_init(0)) mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0) mysql_select_db(sock,gvDatabase) // possibly looped mysql_real_query(sock, query, strlen(query)) mysql_free_result(tableRes); // eoloop // At the end of the program, I close the socket. 1. I don't open a connection for each query, using the already open socket instead (good for some things, not good for other ones.) where in your code are you closing the connection? I tried both: to embrace the query/free_result into mysql_init/mysql_close commands within the loop as well as doing the mysql_init/mysql_close at the beginning and at the end of the program. (just as you do and as I posted in my first message) 2. You have to free the result set after every select. I free the result set after every select until NULL. hmmm... anyway many thanks, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: sleeping processes
Ronny, Oops, no I didn't. Odd that my code (which looks very similar to your code) has no problems but your code does. Has to be something in your code (always work from something that works to something that doesn't.) Is there any way you can subset your code to just a few lines to get it to work and then expand it to what you really want to accomplish? Can you run a debugger on it to make certain what you think is happening is really what is happening? Thanks, Carl - Original Message - From: Ronny Melz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 06, 2005 3:58 AM Subject: Re: Re: sleeping processes Hi Carl, thank you for your reply. did you have a look at my original posting where I included the code? your code (omitting the error routines) is essentially like this: sock=mysql_init(0)) mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0) mysql_select_db(sock,gvDatabase) // possibly looped mysql_real_query(sock, query, strlen(query)) mysql_free_result(tableRes); // eoloop // At the end of the program, I close the socket. 1. I don't open a connection for each query, using the already open socket instead (good for some things, not good for other ones.) where in your code are you closing the connection? I tried both: to embrace the query/free_result into mysql_init/mysql_close commands within the loop as well as doing the mysql_init/mysql_close at the beginning and at the end of the program. (just as you do and as I posted in my first message) 2. You have to free the result set after every select. I free the result set after every select until NULL. hmmm... anyway many thanks, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sleeping processes
Dear all,
I have a problem with the mysql interface for c, which after a couple of
hours thinking about bad or faulty programming are eliminated with
probability almost 100%.
within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the Too many connections error (or under
certain conditions even a Can't create TCP/IP socket (24)).
Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.
Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.
Best,
Ronny
p.s.: code
#define GET_W_NR \
select wort_nr from wortliste where wort_bin='%s' limit 1
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;
init...
{
mysql_init(mysql);
if ( !mysql_real_connect( mysql, 127.0.0.1, username, passwd,
dbase,
0, /var/lib/mysql/mysql.sock, 0 ) )
{
fprintf(stderr, Failed: %s\n,mysql_error(mysql));
}
if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, no
pointer);
}
this code of a function is called in a loop and returns with a mysql_error
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( mysql, query ) )
{
fprintf(stderr, query failed: %s\n, mysql_error(mysql));
return 0;
}
if ( !( res = mysql_store_result( mysql ) ) )
{fprintf(stderr, store failed: %s\n, mysql_error(mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, fetch failed: %s\n, mysql_error(mysql)); return
0;}
mysql_free_result(res);
}
exit...
free(query);
mysql_close(mysql);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sleeping processes
Hello.
Is it possible that your application doesn't close connection properly?
Check with netstat the states of connections between your application
and server. Run netstat on the server. Do you see some sleeping processes
with ps utility or 'mysqladmin processlist' command?
Ronny Melz wrote:
Dear all,
I have a problem with the mysql interface for c, which after a couple of
hours thinking about bad or faulty programming are eliminated with
probability almost 100%.
within a loop, I do a mysql_query(), which is executed and I poll the result
and everything is fine. Up to when max_connections of the mysqld is reached:
each of the queries leaves after executing a sleeping mysql process behind on
the server, which hence throws the Too many connections error (or under
certain conditions even a Can't create TCP/IP socket (24)).
Just because of this problem I recently upgraded mysql (including the
libraries) from 4.0.21 to 4.1.12, but the problem still persists.
Does anybody have an idea of how to solve this problem?
Any suggestions are appreciated.
Best,
Ronny
p.s.: code
#define GET_W_NR \
select wort_nr from wortliste where wort_bin='%s' limit 1
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;
init...
{
mysql_init(mysql);
if ( !mysql_real_connect( mysql, 127.0.0.1, username, passwd,
dbase,
0, /var/lib/mysql/mysql.sock, 0 ) )
{
fprintf(stderr, Failed: %s\n,mysql_error(mysql));
}
if (!(query = (char*) malloc(256*sizeof(char fprintf(stderr, no
pointer);
}
this code of a function is called in a loop and returns with a mysql_error
after `max_connections` cycles:
{
sprintf(query, GET_W_NR, refWort);
if ( mysql_query( mysql, query ) )
{
fprintf(stderr, query failed: %s\n, mysql_error(mysql));
return 0;
}
if ( !( res = mysql_store_result( mysql ) ) )
{fprintf(stderr, store failed: %s\n, mysql_error(mysql)); return
0;}
if ( row = mysql_fetch_row( res ) )
ref_word_nr = atoi(row[0]);
else
{fprintf(stderr, fetch failed: %s\n, mysql_error(mysql)); return
0;}
mysql_free_result(res);
}
exit...
free(query);
mysql_close(mysql);
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sleeping processes
Thanks so far for your advice,
Is it possible that your application doesn't close connection properly?
that is exactly what also I think is the problem's cause, but I am unable to
locate the place where it actually does happen. My code seems straightforward
and I had looked over it some other more experienced people which were unable
to find the bug as well... weird
Check with netstat the states of connections between your application
and server.
'$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql
show full processlist' up to the point where max_connections are reached:
then the mysql processlist reports max_connections+1 pids (including the
terminal I use to get the processlist) whereas a '$ netstat | grep mysql | wc
-l' does never return due to an ever increasing number of open connections.
Each of them is in state TIME_WAIT.
Do you see some sleeping processes
with ps utility or 'mysqladmin processlist' command?
AFAIK, 'mysqladmin processlist' prints the same as a 'mysql show full
processlist', right? It's max_connections sleeping processes plus the
processlist query.
Your hint to watch out for sleeping processes with ps was interesting.
Actually, I have some 14 processes ('ps lax | grep mysql') without running my
program but max_connections+14 if it is running. Each of the processes is
sleeping. I still don't have any idea, do you?
Any suggestions appreciated.
Ronny
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sleeping processes
Ronny,
I think this is actually quite simple. All of your statistic/information
says you are not closing the connection... so where in your code are you
closing the connection?
In one of my projects, I open the data manager as:
// open a MySql database
if (!(sock=mysql_init(0)))
{
_lclose(hfile);
MessageBox(hDlg,Couldn't initialize mysql struct,Convert SportsLog
Data,MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}
// mysql_options(sock,MYSQL_READ_DEFAULT_GROUP,connect);
if
(!mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0)
)
{
_lclose(hfile);
sprintf(tstuff,Couldn't connect to engine!\n%s\n,mysql_error(sock));
MessageBox(hDlg,tstuff,Convert SportsLog Data,MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}
if (mysql_select_db(sock,gvDatabase))
{
_lclose(hfile);
sprintf(tstuff,Couldn't select database test: Error: %s\n,
mysql_error(sock));
MessageBox(hDlg,tstuff,Convert SportsLog Data,MB_OK);
EndDialog(hDlg, LOWORD(wParam));
return TRUE;
}
Then, I use the following code to select, etc.:
if (mysql_real_query(sock, query, strlen(query))) {
queryError(query);
}
After I pull the information I want out of the result set, I close the
result set with:
mysql_free_result(tableRes);
At the end of the program, I close the socket.
Note two things:
1. I don't open a connection for each query, using the already open socket
instead (good for some things, not good for other ones.) But, for the
appropriate applications, it is very fast.
2. You have to free the result set after every select.
By the way, this code came right out of Googling.
Thanks and good luck,
Carl
- Original Message -
From: Ronny Melz [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, June 05, 2005 4:57 PM
Subject: Re: sleeping processes
Thanks so far for your advice,
Is it possible that your application doesn't close connection properly?
that is exactly what also I think is the problem's cause, but I am unable
to
locate the place where it actually does happen. My code seems
straightforward
and I had looked over it some other more experienced people which were
unable
to find the bug as well... weird
Check with netstat the states of connections between your application
and server.
'$ netstat | grep mysql' dumps a list increasing proportionally to 'mysql
show full processlist' up to the point where max_connections are reached:
then the mysql processlist reports max_connections+1 pids (including the
terminal I use to get the processlist) whereas a '$ netstat | grep mysql |
wc
-l' does never return due to an ever increasing number of open
connections.
Each of them is in state TIME_WAIT.
Do you see some sleeping processes
with ps utility or 'mysqladmin processlist' command?
AFAIK, 'mysqladmin processlist' prints the same as a 'mysql show full
processlist', right? It's max_connections sleeping processes plus the
processlist query.
Your hint to watch out for sleeping processes with ps was interesting.
Actually, I have some 14 processes ('ps lax | grep mysql') without running
my
program but max_connections+14 if it is running. Each of the processes is
sleeping. I still don't have any idea, do you?
Any suggestions appreciated.
Ronny
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sleeping Processes
I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+--+ --+ | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+--+ --+ | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sleeping Processes
Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sleeping Processes
Dan: Thanks for the suggest. I will check it out. The strange thing is that as I mentioned, the PHP code that I am using is straightforward, and I have never had any problems with the database not disconnecting properly with PHP, but that being said, the possibility that it is a db connector problem I guess would be OS specific rather than PHP or MYSQL specific necessarily? We will try upgrading PHP to 4.3.4 to see if that helps, and it may be also that a MYSQL 4 upgrade is also on the way. Talking to the host provider, they did mention that the 3 disk array is served only by 1 controller, and they will be adding more, perhaps the bottleneck is in the disk writes, but I cannot see that being a big issue on the relativly low traffic (only around 2GB/month web traffic...) Any other ideas? On 1/6/04 2:29 PM, Dan Greene [EMAIL PROTECTED] wrote: Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade
Re: Sleeping Processes
Mike Morton said: Dan: Thanks for the suggest. I will check it out. The strange thing is that as I mentioned, the PHP code that I am using is straightforward, and I have never had any problems with the database not disconnecting properly with PHP, but that being said, the possibility that it is a db connector problem I guess would be OS specific rather than PHP or MYSQL specific necessarily? We will try upgrading PHP to 4.3.4 to see if that helps, and it may be also that a MYSQL 4 upgrade is also on the way. Talking to the host provider, they did mention that the 3 disk array is served only by 1 controller, and they will be adding more, perhaps the bottleneck is in the disk writes, but I cannot see that being a big issue on the relativly low traffic (only around 2GB/month web traffic...) Any other ideas? On 1/6/04 2:29 PM, Dan Greene [EMAIL PROTECTED] wrote: Is it possible that some portions of your code do not disconnect thier db connections when they are done? I've seen issues like this with platforms that do not use db pool managers to ensure release of db connections when they are idle for a while. Particularly if there are error scenerios where the normal connection closure may be skipped... My $0.02... Dan Greene -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:15 PM To: [EMAIL PROTECTED] Subject: Sleeping Processes I have a mysql database that is running in conjunction with a PHP website, and I am experiencing some significant slowdowns in times of moderate traffic. Now, by moderate, I mean moderate for them, but fairly light traffic in terms of actual visitors. The library the is causing the slowdowns simple updates a 'last_active' variable in a sessions table in mysql which normally has no affect on loading speed at all, but what I am noticing during this high traffic period, is anywhere from 50 - 60 processes that are 'sleeping' in the database when I do a show processlist. E.g. +++---++-+--+- -+ -- + | Id | User | Host | db | Command | Time | State| Info | +++---++-+--+- -+ -- + | 680849 | tienda | localhost | tienda | Sleep | 22 | | NULL | | 680859 | tienda | localhost | tienda | Sleep | 154 | | NULL | | 680878 | tienda | localhost | tienda | Sleep | 101 | | NULL | | 680942 | tienda | localhost | tienda | Sleep | 96 | | NULL | But upwards of as I said, 50 - 60 of them at any given time. I do not have a slow day comparison to offer, but I have done many a sight and never seen sleeping processes like this. The question is, could these be affecting the slowness of the database, as I have determined that it is the database that is slowing down the site. The largest table holds only 42K rows, and the most accessed table is indexed with the most accessed fields and holds only 16K rows, so there should be no problem there. The max connections is set to 300, and the version is 3.23.47 The PHP version is 4.2.3 and I am NOT using mysql_pconnect but rather mysql_connect: [EMAIL PROTECTED](localhost,blocked,blocked); @mysql_select_db(tienda); Because it is a shared box, I do not have access to make a lot of changes, but the company is very co-operative and I am sure would be helpful for making small setting changes, as long as we are not talking major version updates, as they do have other clients using the server. I cannot think of anything else that I can pass on, if anyone has any suggestions or ideas I would appreciate it as I am plumb out of them! :) TIA -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple
Re: (sleeping) processes of 90% CPU
To solve SMP problems with Linux, you should use latest kernel 2.4.* and our binary. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (sleeping) processes of 90% CPU
On Tue, Jun 12, 2001 at 02:16:27PM +0300, Sinisa Milivojevic wrote: To solve SMP problems with Linux, you should use latest kernel 2.4.* and our binary. We are running 2.4.5-ac4 currently on the server. Could you tell me why your binary should run better ? Cause I tried it, and it dind't make a lot of difference. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com -- Met vriendelijke groet/With kind regards, Cable Wirelesshttp://www.widexs.nl Wouter de Jong System-Administrator Tel +31 23 5698070 Fax +31 23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (sleeping) processes of 90% CPU
Wouter de Jong writes: On Tue, Jun 12, 2001 at 02:16:27PM +0300, Sinisa Milivojevic wrote: We are running 2.4.5-ac4 currently on the server. Could you tell me why your binary should run better ? Cause I tried it, and it dind't make a lot of difference. -- Met vriendelijke groet/With kind regards, Cable Wirelesshttp://www.widexs.nl Wouter de Jong System-Administrator Tel +31 23 5698070 Fax +31 23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL Because, we build our binaries in the best possible way, plus we link them statically with latest most stable and fully patched LInuxThreads and other libs. -- Regards, For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (sleeping) processes of 90% CPU
On Sun, Jun 10, 2001 at 01:15:38PM +0300, Sinisa Milivojevic wrote: Hello, I'm a database administrator for a hosting provider, and we have currently 2 SMP MySQL-database servers. The one that is causing me problems, has at least 900 databases running on it. That's an dual PIII-550, with 1024MB memory, running RedHat 6.2, with Linux 2.4.5-ac4. During working hours, a load of 5 6 is pretty default, with about 600 mysqld processes. This is already reduced from 900 to 600, by setting the wait-timeout to 600 seconds, to 120, to 30 at the moment. The problem is that the server is in an unstable situation lately. I see a lot of MySQL-processes with top (sorted by MEM-usage), that are taking over 90% CPU. If I strace -p PID the process, it returns me nothing. I assume this is a sleeping process. RSS sometimes is 120M, most of the time between 20 and 40M. No change in running 3.22.32, 3.23.31 - 3.23.38 (.38 currently). Anyone has a hint on what happens ? SHOW PROCESSLIST gives me for 90% Sleeping as output. Here is (partially) my /etc/my.cnf : skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= max_connections=1250 set-variable= max_connect_errors=9 set-variable= wait_timeout=30 Hi! This is most probably mutex contention problem. Please use our binary, or patch your LinuxThreads with a patch on our site. Hmm, I patched glibc 2.1.3 with the patch on your site, then recompiled MySQL. No change, still huge processes. : I then tried the binary version, that didn't help too. I really don't know what to look for anymore : I can replace the hardware, but I don't think that will change anything... *hoping for hints :* -- Regards, For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com -- Met vriendelijke groet/With kind regards, Cable Wirelesshttp://www.widexs.nl Wouter de Jong System-Administrator Tel +31 23 5698070 Fax +31 23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: (sleeping) processes of 90% CPU
Wouter de Jong writes: Hello, I'm a database administrator for a hosting provider, and we have currently 2 SMP MySQL-database servers. The one that is causing me problems, has at least 900 databases running on it. That's an dual PIII-550, with 1024MB memory, running RedHat 6.2, with Linux 2.4.5-ac4. During working hours, a load of 5 6 is pretty default, with about 600 mysqld processes. This is already reduced from 900 to 600, by setting the wait-timeout to 600 seconds, to 120, to 30 at the moment. The problem is that the server is in an unstable situation lately. I see a lot of MySQL-processes with top (sorted by MEM-usage), that are taking over 90% CPU. If I strace -p PID the process, it returns me nothing. I assume this is a sleeping process. RSS sometimes is 120M, most of the time between 20 and 40M. No change in running 3.22.32, 3.23.31 - 3.23.38 (.38 currently). Anyone has a hint on what happens ? SHOW PROCESSLIST gives me for 90% Sleeping as output. Here is (partially) my /etc/my.cnf : skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= max_connections=1250 set-variable= max_connect_errors=9 set-variable= wait_timeout=30 -- Met vriendelijke groet/With kind regards, Cable Wirelesshttp://www.widexs.nl Wouter de Jong System-Administrator Tel +31 23 5698070 Fax +31 23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL Hi! This is most probably mutex contention problem. Please use our binary, or patch your LinuxThreads with a patch on our site. -- Regards, For technical support contracts, go to https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
(sleeping) processes of 90% CPU
Hello, I'm a database administrator for a hosting provider, and we have currently 2 SMP MySQL-database servers. The one that is causing me problems, has at least 900 databases running on it. That's an dual PIII-550, with 1024MB memory, running RedHat 6.2, with Linux 2.4.5-ac4. During working hours, a load of 5 6 is pretty default, with about 600 mysqld processes. This is already reduced from 900 to 600, by setting the wait-timeout to 600 seconds, to 120, to 30 at the moment. The problem is that the server is in an unstable situation lately. I see a lot of MySQL-processes with top (sorted by MEM-usage), that are taking over 90% CPU. If I strace -p PID the process, it returns me nothing. I assume this is a sleeping process. RSS sometimes is 120M, most of the time between 20 and 40M. No change in running 3.22.32, 3.23.31 - 3.23.38 (.38 currently). Anyone has a hint on what happens ? SHOW PROCESSLIST gives me for 90% Sleeping as output. Here is (partially) my /etc/my.cnf : skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M set-variable= max_connections=1250 set-variable= max_connect_errors=9 set-variable= wait_timeout=30 -- Met vriendelijke groet/With kind regards, Cable Wirelesshttp://www.widexs.nl Wouter de Jong System-Administrator Tel +31 23 5698070 Fax +31 23 5698099 Bijlmermeerstraat 62, 2131 HG HOOFDDORP, NL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
