RE: Desperate - failed: Lost connection to MySQL server during query
Thanks for the help John, I really don't know all the specifics of the hardware setup as it is all out at our ISP, I know its a rack mounted P4 and its going into a Cisco switch but that's about it. We have made some progress though and as strange as it sounds its really looking like its something with RH 8, in short we downgraded perl 5.8 to 5.6.1 with no results, we then went from Apache 2 down to 1.3 with no results, then that night backup exec came through and the machine spiked to 100% - noticed it by luck of being on the machine at the same time, this pretty much took Apache, Perl and my code out of the question. Did up a new machine (same specs), put RH 7.3 on it, copied everything over and made it live.. Right before we switched over to the new machine we stopped throttling the server (it was the only way to stop those errors) and instantly those connection failures started happening like crazy. As soon as we made the switch, they all went away, load was cut in half and CPU idle went up around 30 to 40% and things were just screaming, 110% improvement in all areas. So you'd think everything was wonderful now, wouldn't you?.. Well check this out.. As soon as the new web server was brought up I started getting (and still get) tons of Aborted connection ... (Got an error reading communication packets) - and mySql has run without a flaw since day one, also the scripts and DBI and everything was exactly the same on the new web server from the old web server (mysql would have no knowledge of the switch). I can say I'm about 98% sure that everything mySql shows for this problem at: http://www.mysql.com/doc/en/Communication_errors.html Isn't a issue. As RH 8 is also running on the dedicated mySql machine, I'm starting to believe its the root of all my problems. Its been a wild week!! Thanks -Chris -Original Message- From: John P [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 28, 2002 1:45 PM To: Chris Faust; [EMAIL PROTECTED] Subject: Re: Desperate - failed: Lost connection to MySQL server during query If you are running close to the same setup with no problems then I feel a little better about it not being a issue of pure traffic. Yes, we operate in a very similar way to you. (BTW, we have had very good results with a PHP based cache facility that simply stores the db driven pages over a selectable time period; good if your pages don't change that much! Capacity increased 4000% or so; would imagine something similar exists for perl) I don't think the link between the machines is a issue, both machines are dedicated and at the same location, I've tried using both the external and internal (10.0.x.x) IP to connect to the DB with the same results and in all cases both machines have had entries in their hosts file for one another. As for simultaneous connections, that is something that I still need to go through the logs and come up with a real number for those time frames.. I don't know anyway to see a real number of actual users using Apache via the command line (is there such a thing?). I've always done ps -ef | grep httpd | wc -l a few times which shows the processes, you can get a good idea of whether the site is busy or not. I don't know how to actually find the exact number of users, I suppose netstat ? One thing I wonder that I forgot to ask - does using IP address as the host for a user in mySql matter? Meaning before DNS switched over I wanted to get everything up and running so I created all the mysql user accounts based on the IP address of the web server (and not name). I would think if this were a issue at all I would be getting denied messages and it wouldn't work 100% of the time, but I thought I would throw it out there. I'd do everything by IP, especially for the internal stuff. It might be worth trying a dedicated cable+two separate network cards in each server to handle MySQL traffic. Just a thought, but could packets be getting lost between the two servers (network overloaded or out of ports on eth0 or ..?) Post your exact network setup (machines, switches/hubs etc) and that might give some more clues? Good luck John - 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: Desperate - failed: Lost connection to MySQL server during query
Hi, You right.RH8 have a problem with glibc.All people from the list recommende to make a downgrade to version 2.x or download binary distribution from MySQL site which is compiled with the right version of glibc. Are and other ways to solve your problems but i think you need to a stable solution. Anyway if you start MySQL daemon with option --skip-name-resolve and the problem it's solved then you have only this 2 options. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Chris Faust [EMAIL PROTECTED] To: John P [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, January 02, 2003 6:54 AM Subject: RE: Desperate - failed: Lost connection to MySQL server during query Thanks for the help John, I really don't know all the specifics of the hardware setup as it is all out at our ISP, I know its a rack mounted P4 and its going into a Cisco switch but that's about it. We have made some progress though and as strange as it sounds its really looking like its something with RH 8, in short we downgraded perl 5.8 to 5.6.1 with no results, we then went from Apache 2 down to 1.3 with no results, then that night backup exec came through and the machine spiked to 100% - noticed it by luck of being on the machine at the same time, this pretty much took Apache, Perl and my code out of the question. Did up a new machine (same specs), put RH 7.3 on it, copied everything over and made it live.. Right before we switched over to the new machine we stopped throttling the server (it was the only way to stop those errors) and instantly those connection failures started happening like crazy. As soon as we made the switch, they all went away, load was cut in half and CPU idle went up around 30 to 40% and things were just screaming, 110% improvement in all areas. So you'd think everything was wonderful now, wouldn't you?.. Well check this out.. As soon as the new web server was brought up I started getting (and still get) tons of Aborted connection ... (Got an error reading communication packets) - and mySql has run without a flaw since day one, also the scripts and DBI and everything was exactly the same on the new web server from the old web server (mysql would have no knowledge of the switch). I can say I'm about 98% sure that everything mySql shows for this problem at: http://www.mysql.com/doc/en/Communication_errors.html Isn't a issue. As RH 8 is also running on the dedicated mySql machine, I'm starting to believe its the root of all my problems. Its been a wild week!! Thanks -Chris -Original Message- From: John P [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 28, 2002 1:45 PM To: Chris Faust; [EMAIL PROTECTED] Subject: Re: Desperate - failed: Lost connection to MySQL server during query If you are running close to the same setup with no problems then I feel a little better about it not being a issue of pure traffic. Yes, we operate in a very similar way to you. (BTW, we have had very good results with a PHP based cache facility that simply stores the db driven pages over a selectable time period; good if your pages don't change that much! Capacity increased 4000% or so; would imagine something similar exists for perl) I don't think the link between the machines is a issue, both machines are dedicated and at the same location, I've tried using both the external and internal (10.0.x.x) IP to connect to the DB with the same results and in all cases both machines have had entries in their hosts file for one another. As for simultaneous connections, that is something that I still need to go through the logs and come up with a real number for those time frames.. I don't know anyway to see a real number of actual users using Apache via the command line (is there such a thing?). I've always done ps -ef | grep httpd | wc -l a few times which shows the processes, you can get a good idea of whether the site is busy or not. I don't know how to actually find the exact number of users, I suppose netstat ? One thing I wonder that I forgot to ask - does using IP address as the host for a user in mySql matter? Meaning before DNS switched over I wanted to get everything up and running so I created all the mysql user accounts based on the IP address of the web server (and not name). I would think if this were a issue at all I would be getting denied messages and it wouldn't work 100% of the time, but I thought I would throw it out there. I'd do everything by IP, especially for the internal stuff. It might be worth trying a dedicated cable+two separate network cards in each server to handle MySQL traffic. Just a thought, but could packets be getting lost between the two
Re: Desperate - failed: Lost connection to MySQL server during query
- Original Message - From: Chris Faust [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 27, 2002 10:22 PM Subject: Desperate - failed: Lost connection to MySQL server during query The problem is this, everytime the web server starts to work a little (load via a uptime showing about 6), 1 out of 5 scripts bomb out with the message of: failed: Lost connection to MySQL server during query As load gets higher then the rate of failure gets higher. I lack the necessary info on your scripts, of course; but you might be looking at it from the wrong angle. I would ere expect a time-out issue with the Apache server (that is, with its spawned children). Try a netstat -a; are many http connection in a TIME_WAIT state? The Timeout value in httpd.conf also leaps to mind. A CPU load of 6, though not itself excessively high, may be an indication that the Apache server gets hits with many, many requests, forcing many to remain in a TIME_WAIT state for a while -- long enough to cause a potential problem for the MySQL connection, at least. A solid test, I would say, is to try and run your Perl scripts directly, and not via a web-page. I have a nagging suspicion the errors will be gone. - Mark - 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: Desperate - failed: Lost connection to MySQL server during query
Hello Mark, Thanks for the reply.. I would need to wait until load gets up there to see the state of apache but even now with almost no load on the machine I'm seeing at least a dozen connections in the TIME_WAIT state. On the flip site of things I have not been seeing any other connection related issues from apache (no connection reset by peer messages or anything like that) I really don't think its any of the scripts either, everything was running fine at another location (none of these errors ever came up) and the scripts do run fine via the command line. To give you a little more on the scripts.. I create a connection to the DB as the first thing in all scripts. That connection is used anyplace a call to the DB is needed, (in other words I don't fork anything or create any children, establish a second connection etc.) there is never anymore then 1 connection made to the DB for any 1 script that is compiled at run-time. For timeout, Apache has: Timeout 300 KeepAlive On MaxKeepAliveRequests 0 KeepAliveTimeout 15 One other test I did was put part of the site that was getting as many lost connection errors as anything else, on another machine (the administration side of it) and once there it ran perfectly using the same mySql server (and its even IIS). I'm leaning towards either Redhat 8, or Apache 2 or the combination of the two of them - I just can't figure out what else to try or what to look for. I hate the thought all the work involved in bring up a new machine and bringing everything over when I at least have a error message to work with :). Thanks -Chris The problem is this, everytime the web server starts to work a little (load via a uptime showing about 6), 1 out of 5 scripts bomb out with the message of: failed: Lost connection to MySQL server during query As load gets higher then the rate of failure gets higher. I lack the necessary info on your scripts, of course; but you might be looking at it from the wrong angle. I would ere expect a time-out issue with the Apache server (that is, with its spawned children). Try a netstat -a; are many http connection in a TIME_WAIT state? The Timeout value in httpd.conf also leaps to mind. A CPU load of 6, though not itself excessively high, may be an indication that the Apache server gets hits with many, many requests, forcing many to remain in a TIME_WAIT state for a while -- long enough to cause a potential problem for the MySQL connection, at least. A solid test, I would say, is to try and run your Perl scripts directly, and not via a web-page. I have a nagging suspicion the errors will be gone. - Mark - 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: Desperate - failed: Lost connection to MySQL server during query
Chris: I bet a dollar to a donut that you have a hardware problem. The essential proof is in the first quoted paragraph below. Believe your data, save your hair. Regards, Doug Filter That! SQL MySQL Query On Sat, 28 Dec 2002 07:18:07 -0500, Chris Faust wrote: One other test I did was put part of the site that was getting as many lost connection errors as anything else, on another machine (the administration side of it) and once there it ran perfectly using the same mySql server (and its even IIS). I'm leaning towards either Redhat 8, or Apache 2 or the combination of the two of them - I just can't figure out what else to try or what to look for. I hate the thought all the work involved in bring up a new machine and bringing everything over when I at least have a error message to work with :). - 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: Desperate - failed: Lost connection to MySQL server during query
Wow, I don't know if that's a good bet as doesn't a donut cost around a dollar these days :). Seriously, It very well could be something hardware related but everything is brand-new and I'm not seeing any issues logged anyplace with anything on the machine (except this specific problem at high load times and there is the error being reported). Something else that may be an issue is its just traffic and how Redhat 8 or Apache 2 deals with it, the site isn't over the top but the high daily average is 10,000 unique users, 250,000 page views and about 6 gig of bandwidth a day.. Pipe is no issue at all and I would think that the P4 could handle that amount of traffic - I only see CPU go off the chart when all the disconnects start happening so its hard to tell if one is the result of the other.. Hardware is my last resort, but its moving up the list fast!! Thanks -Chris -Original Message- From: Doug Thompson [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 28, 2002 7:33 AM To: Chris Faust; Mark; [EMAIL PROTECTED] Subject: RE: Desperate - failed: Lost connection to MySQL server during query Chris: I bet a dollar to a donut that you have a hardware problem. The essential proof is in the first quoted paragraph below. Believe your data, save your hair. Regards, Doug Filter That! SQL MySQL Query On Sat, 28 Dec 2002 07:18:07 -0500, Chris Faust wrote: One other test I did was put part of the site that was getting as many lost connection errors as anything else, on another machine (the administration side of it) and once there it ran perfectly using the same mySql server (and its even IIS). I'm leaning towards either Redhat 8, or Apache 2 or the combination of the two of them - I just can't figure out what else to try or what to look for. I hate the thought all the work involved in bring up a new machine and bringing everything over when I at least have a error message to work with :). - 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: Desperate - failed: Lost connection to MySQL server during query
If you are running close to the same setup with no problems then I feel a little better about it not being a issue of pure traffic. As for the scripts, its a heavy reliance on the database for information - pretty much the whole site is like a classifieds ads system. Other then the advance searches (which are not used all that much) 90% of everything is just a single select to the DB at which point the results are normally parsed out in a single loop and displayed to the user (I also use HTML::Template to deal with serving results). If it means anything, when things are running at a normal load I can sit and watch people hitting the scripts (just using top or ps -eaf) and see that they use up a little CPU and then they are gone (meaning everything did its thing and it was over), I almost never see a perl process that is running one of the webscipts last any more then a second or sometimes two.. I don't think the link between the machines is a issue, both machines are dedicated and at the same location, I've tried using both the external and internal (10.0.x.x) IP to connect to the DB with the same results and in all cases both machines have had entries in their hosts file for one another. As for simultaneous connections, that is something that I still need to go through the logs and come up with a real number for those time frames.. I don't know anyway to see a real number of actual users using Apache via the command line (is there such a thing?). One thing I wonder that I forgot to ask - does using IP address as the host for a user in mySql matter? Meaning before DNS switched over I wanted to get everything up and running so I created all the mysql user accounts based on the IP address of the web server (and not name). I would think if this were a issue at all I would be getting denied messages and it wouldn't work 100% of the time, but I thought I would throw it out there. Thanks -Chris Chris, we are running a similar www/db box setup to you, with similar traffic and data transfer figures. I use Apache 1.3/PHP though. I never see the uptime over 1, let alone 6. Do all your scripts have quite extensive reliance on the DB and calculations etc? I would have thought that the WWW box has very little to do! I know each situation is different, but have you tested the link between the two computers (is it dedicated or over the same net as the WWW?) to check that there isn't a problem there? What sort of simultaneous connection figures does this problem start at? I wonder about a hardware / network problem too.. Cheers, John - 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: Desperate - failed: Lost connection to MySQL server during query
- Original Message - From: Chris Faust [EMAIL PROTECTED] To: Mark [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, December 28, 2002 1:18 PM Subject: RE: Desperate - failed: Lost connection to MySQL server during query I really don't think its any of the scripts either, everything was running fine at another location (none of these errors ever came up) and the scripts do run fine via the command line. Hmm, if stuff runs consistently well on the command line, then that seems to be ruling out a hardware error (unless you connect our DBI over TCP/IP, in which case you could have a faulty Ethernet card; but I assumed you are using a local UNIX domain socket, like /tmp/mysql.sock). One other test I did was put part of the site that was getting as many lost connection errors as anything else, on another machine (the administration side of it) and once there it ran perfectly using the same mySql server (and its even IIS). This DOES seem to indicate you are connecting using TCP/IP. Are you? If so, a whole new sleuth of options appears. I'm leaning towards either Redhat 8, or Apache 2 or the combination of the two of them - I just can't figure out what else to try or what to look for. Try connecting over the domain socket; or / and add something like this to the [mysql] section of my.cnf, set-variable = connect_timeout=4 Just to see whether it is a timing problem. - Mark - 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: Desperate - failed: Lost connection to MySQL server during query
Yes, I do connect to the DB server over IP - in short this is what I do
within MAIN of all my scripts (and $db is what is used anyplace a DB
connection is needed)
##
# Database Setup
# -
#
my $dbpassfile = '/websites/web_files/dbadmin.pass';
my ($db_username,$db_password,$db_location,$db_name,$db);
open (DBPASSFILE, $dbpassfile) or die Couldn't open $dbpassfile: $!\n;
while (DBPASSFILE) {
($db_username,$db_password,$db_location,$db_name) = split;
}
my $database = DBI:mysql:database=$db_name:host=$db_location;
$db = DBI-connect($database,$db_username,$db_password) or die Cannot
Connect: . $db-errstr();
close DBPASSFILE;
I use a text file to store the username,password,table name and IP to add
just a little more security.
I have check to make sure that open files on the linux machine wasn't a
issue (and it wasn't) when opening up that file each time.
As far as the connection_timeout, I don't have anything in my.cnf but if I
do a mysqladmin variables it shows me its set to 5.
I think I'm going back to the drawing board :)
Thanks
-Chris
This DOES seem to indicate you are connecting using TCP/IP. Are
you? If so,
a whole new sleuth of options appears.
I'm leaning towards either Redhat 8, or Apache 2 or the combination of
the two of them - I just can't figure out what else to try or what to
look for.
Try connecting over the domain socket; or / and add something
like this to
the [mysql] section of my.cnf,
set-variable = connect_timeout=4
Just to see whether it is a timing problem.
- Mark
-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Desperate - failed: Lost connection to MySQL server during query
If you are running close to the same setup with no problems then I feel a little better about it not being a issue of pure traffic. Yes, we operate in a very similar way to you. (BTW, we have had very good results with a PHP based cache facility that simply stores the db driven pages over a selectable time period; good if your pages don't change that much! Capacity increased 4000% or so; would imagine something similar exists for perl) I don't think the link between the machines is a issue, both machines are dedicated and at the same location, I've tried using both the external and internal (10.0.x.x) IP to connect to the DB with the same results and in all cases both machines have had entries in their hosts file for one another. As for simultaneous connections, that is something that I still need to go through the logs and come up with a real number for those time frames.. I don't know anyway to see a real number of actual users using Apache via the command line (is there such a thing?). I've always done ps -ef | grep httpd | wc -l a few times which shows the processes, you can get a good idea of whether the site is busy or not. I don't know how to actually find the exact number of users, I suppose netstat ? One thing I wonder that I forgot to ask - does using IP address as the host for a user in mySql matter? Meaning before DNS switched over I wanted to get everything up and running so I created all the mysql user accounts based on the IP address of the web server (and not name). I would think if this were a issue at all I would be getting denied messages and it wouldn't work 100% of the time, but I thought I would throw it out there. I'd do everything by IP, especially for the internal stuff. It might be worth trying a dedicated cable+two separate network cards in each server to handle MySQL traffic. Just a thought, but could packets be getting lost between the two servers (network overloaded or out of ports on eth0 or ..?) Post your exact network setup (machines, switches/hubs etc) and that might give some more clues? Good luck John - 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
