debugging Error establishing mySQL database connection under extreme load

views:

475

answers:

+1 Q:

debugging Error establishing mySQL database connection under extreme load

Under high traffic my mysql 5.0.45 server /Apache2/ CentOS 5 is getting "Error establishing mySQL database connection". I need to find the root cause.

I would very much appreciate any pointer to information about the procedure I should take to find the cause (memory limit, thread limits, CPU load, slow queries etc, large dataset, wrong keys ...) I would assume it involves looking at relevant log files etc....

Thank you.

+2 A:

That particular error message sounds like it's being generated by your application, and not by a system library. MySQL has functionality to report the specific errors that are occurring, so your best bet would be to utilize that in some way.

For instance, if you were using PHP, there is a function called mysql_error() that returns specifics about the last error encountered (too many connections, etc). You would put in some error handling near your connection call, and log the mysql_error() results if it failed.

You didn't mention what language you were using, but the MySQL libraries would provide the same functionality to whichever you are using. I'd suggest modifying your application code to take advantage of it.

zombat 2009-06-18 16:44:03

Thanks !I'm using PHP5 and a library called ezsql http://www.woyano.com/jv/ezsql I'll try to output the mysql errors per your advice (the error happens occasionally). Isn't there a log that mysql keeps of such errors?

Nir 2009-06-18 17:08:48

I get "Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug" about 70% of the errors and "Can't create UNIX socket (12)" about 25%, and occasional "Lost connection to MySQL server at 'reading initial communication packet'".It does not seem to be a memory problem since the 'Top' command shows 600M Free (which is similar to low traffic situations)

Nir 2009-06-18 17:57:39

Hmm, interesting. You'll probably have to do some googling to get some help with those specific errors. MySQL has a page on tuning server parameters as well, at http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html, it might be worth a look.

zombat 2009-06-18 18:05:13

+1 A:

I'm willing to bet this is because you're hitting the max user limit allowed by the mysql server but in general, do print the mysql errors, if not to the screen but at least to the log, or email.

Artem Russakovskii 2009-06-18 16:46:14

I get "Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug" about 70% of the errors and "Can't create UNIX socket (12)" about 25%, and occasional "Lost connection to MySQL server at 'reading initial communication packet'". It does not seem to be a memory problem since the 'Top' command shows 600M Free (which is similar to low traffic situations)

Nir 2009-06-19 20:05:12

Nir, the first error seems more common while the latter is almost impossible to find any info on. I would recommend adjusting some settings, for ex mentioned at http://bugs.mysql.com/bug.php?id=5656. It is most likely related to OS running out of resources - perhaps your ulimit is set too low for something. Run ulimit -a. Also try vmstat -a 1 and see what your RAM and swap are doing. Do you have free swap?

Artem Russakovskii 2009-06-20 00:48:08

ansaurus

tags:

views:

answers:

debugging Error establishing mySQL database connection under extreme load

related questions