Very strange MySQL problem !
Hi, I am having a very weird problem with one of my database servers. Let's take the following setup: 10 load-balanced servers (I'll call them ServerA) 1 admin server (ServerB) 1 database server (DB1) Now ServerA has a function to insert a row into a table on DB1 whenever someone hits certain pages (for stats collection). There are probably about 1000 inserts being done per second. ServerB has a script that runs every 20 minutes, which does a very quick/simple select from DB1, then loops though the results and does updates/deletes on a different database server. Everytime the script on ServerB runs, all attempts to connect to DB1 from ServerA fail. This causes major problems like causing server load on ServerA to rise dramatically. I have checked when the script runs, everything on DB1 is normal. Server load, mysql processes, no locks, etc... I also have it logging the connection failures with mysql_error but mysql_error turns up blank. Can anyone try and help me figure out what's going on here!? Why won't it connect during the period the script runs and why is mysql_error blank? Webservers are FreeBSD 4.8-Stable, DB1 is Redhat 7.3 running MySQL-standard 4.0.13 Thanks!! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very strange MySQL problem !
Possible? Are you sure all connection attempts fail? not just insert attempts? Server B does some updates/deletes... Chances are this causes some table locks, which makes Server A unable to perform it's inserts until Server B releases the lock. On Wed, 13 Aug 2003, Keith Bussey wrote: Hi, I am having a very weird problem with one of my database servers. Let's take the following setup: 10 load-balanced servers (I'll call them ServerA) 1 admin server (ServerB) 1 database server (DB1) Now ServerA has a function to insert a row into a table on DB1 whenever someone hits certain pages (for stats collection). There are probably about 1000 inserts being done per second. ServerB has a script that runs every 20 minutes, which does a very quick/simple select from DB1, then loops though the results and does updates/deletes on a different database server. Everytime the script on ServerB runs, all attempts to connect to DB1 from ServerA fail. This causes major problems like causing server load on ServerA to rise dramatically. I have checked when the script runs, everything on DB1 is normal. Server load, mysql processes, no locks, etc... I also have it logging the connection failures with mysql_error but mysql_error turns up blank. Can anyone try and help me figure out what's going on here!? Why won't it connect during the period the script runs and why is mysql_error blank? Webservers are FreeBSD 4.8-Stable, DB1 is Redhat 7.3 running MySQL-standard 4.0.13 Thanks!! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- 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: Very strange MySQL problem !
Quoting [EMAIL PROTECTED]: Are you sure all connection attempts fail? not just insert attempts? Yes, i have it write to my log if the sock is empty (mysql_connect fails)...before it gets to the insert Server B does some updates/deletes... Chances are this causes some table locks, which makes Server A unable to perform it's inserts until Server B releases the lock. ServerB's updates/deletes are on a different database server though (as stated below ;p)...so it shouldn't factor in here at all. On Wed, 13 Aug 2003, Keith Bussey wrote: Hi, I am having a very weird problem with one of my database servers. Let's take the following setup: 10 load-balanced servers (I'll call them ServerA) 1 admin server (ServerB) 1 database server (DB1) Now ServerA has a function to insert a row into a table on DB1 whenever someone hits certain pages (for stats collection). There are probably about 1000 inserts being done per second. ServerB has a script that runs every 20 minutes, which does a very quick/simple select from DB1, then loops though the results and does updates/deletes on a different database server. Everytime the script on ServerB runs, all attempts to connect to DB1 from ServerA fail. This causes major problems like causing server load on ServerA to rise dramatically. I have checked when the script runs, everything on DB1 is normal. Server load, mysql processes, no locks, etc... I also have it logging the connection failures with mysql_error but mysql_error turns up blank. Can anyone try and help me figure out what's going on here!? Why won't it connect during the period the script runs and why is mysql_error blank? Webservers are FreeBSD 4.8-Stable, DB1 is Redhat 7.3 running MySQL-standard 4.0.13 Thanks!! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- 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: Very strange MySQL problem !
That's the problem, when the script runs, I can connect from serverA fine!...but seems the webpages can't ;/ -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting [EMAIL PROTECTED]: On Wed, 13 Aug 2003, Keith Bussey wrote: Are you sure all connection attempts fail? not just insert attempts? Yes, i have it write to my log if the sock is empty (mysql_connect fails)...before it gets to the insert But you mention mysql reports no connection errors... perhaps wait for an update, and from ServerA, use mysql client: mysql --user=asdf --password=asdf --host=DB1 see if you can get online.. -- 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: Very strange MySQL problem !
On Wed, 13 Aug 2003, Keith Bussey wrote: Are you sure all connection attempts fail? not just insert attempts? Yes, i have it write to my log if the sock is empty (mysql_connect fails)...before it gets to the insert But you mention mysql reports no connection errors... perhaps wait for an update, and from ServerA, use mysql client: mysql --user=asdf --password=asdf --host=DB1 see if you can get online.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very strange MySQL problem !
I'd double check this cronjob script... possible scenario.. kaibash idea if you can prove it's invalid. ServerB has a script that runs every 20 minutes, which does a very quick/simple select from DB1, then loops though the results and does updates/deletes on a different database server. running a select on a server with 1000 inserts a second will take alot longer than if the query had a table lock. Also if the query has anything to do with stats, etc, not having a read lock and inserts going on could affect results and result speed. Perhaps it aquires a read lock before running the query? I'd dig thru the code/sql / use show processlist to disprove this theory. On Wed, 13 Aug 2003, Keith Bussey wrote: That's the problem, when the script runs, I can connect from serverA fine!...but seems the webpages can't ;/ -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting [EMAIL PROTECTED]: On Wed, 13 Aug 2003, Keith Bussey wrote: Are you sure all connection attempts fail? not just insert attempts? Yes, i have it write to my log if the sock is empty (mysql_connect fails)...before it gets to the insert But you mention mysql reports no connection errors... perhaps wait for an update, and from ServerA, use mysql client: mysql --user=asdf --password=asdf --host=DB1 see if you can get online.. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
