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]
Too many connections
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]
Too many connections
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]
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
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]
