WeBWorK Main Forum

MySQL error - too many connections

MySQL error - too many connections

by Fuzzy Rogers -
Number of replies: 3
Just started getting these today- stopped apache, stopped and restarted mysqld, started apache... things go ok for a bit, errors come for a bit, things go ok for a bit...
Error from error_logs in Apache...

[Mon Oct 1 14:56:43 2007] [warn] [client 24.254.82.147] [/webwork2/Math34A-01-F07-Wei/hw2/6/] DBI connect('webwork','webworkWrite',...) failed: Too many connections at /opt/pg/lib/WeBWorK/PG/ImageGenerator.pm line 427\n
[Mon Oct 1 14:56:43 2007] [error] [client 24.254.82.147] [/webwork2/Math34A-01-F07-Wei/hw2/6/] Can't call method "prepare" on an undefined value at /opt/pg/lib/WeBWorK/PG/ImageGenerator.pm line 429.\n\n * in WeBWorK::PG::Local::new called at line 52 of /opt/webwork2//lib/WeBWorK/PG.pm\n * in WeBWorK::PG::new called at line 616 of /opt/webwork2//lib/WeBWorK/ContentGenerator/Problem.pm\n * in WeBWorK::ContentGenerator::Problem::pre_header_initialize called at line 169 of /opt/webwork2//lib/WeBWorK/ContentGenerator.pm\n * in WeBWorK::ContentGenerator::go called at line 338 of /opt/webwork2//lib/WeBWorK.pm
[Mon Oct 1 14:56:48 2007] [warn] [client 24.254.82.147] [/webwork2/Math34A-01-F07-Wei/hw2/4/] DBI connect('webwork','webworkWrite',...) failed: Too many connections at /opt/pg/lib/WeBWorK/PG/ImageGenerator.pm line 427\n
[Mon Oct 1 14:56:48 2007] [error] [client 24.254.82.147] [/webwork2/Math34A-01-F07-Wei/hw2/4/] Can't call method "prepare" on an undefined value at /opt/pg/lib/WeBWorK/PG/ImageGenerator.pm line 429.\n\n * in WeBWorK::PG::Local::new called at line 52 of /opt/webwork2//lib/WeBWorK/PG.pm\n * in WeBWorK::PG::new called at line 616 of /opt/webwork2//lib/WeBWorK/ContentGenerator/Problem.pm\n * in WeBWorK::ContentGenerator::Problem::pre_header_initialize called at line 169 of /opt/webwork2//lib/WeBWorK/ContentGenerator.pm\n * in WeBWorK::ContentGenerator::go called at line 338 of /opt/webwork2//lib/WeBWorK.pm
[Mon Oct 1 14:56:54 2007] [warn] [client 24.254.82.147] [/webwork2/Math34A-01-F07-Wei/hw2/] DBI connect('webwork','webworkWrite',...) failed: Too many connections at /opt/pg/lib/WeBWorK/PG/ImageGenerator.pm line 427
[Mon Oct 1 14:56:54 2007] [error] [client 24.254.82.147] [/webwork2/Math34A-01-F07-Wei/hw2/] Can't call method "prepare" on an undefined value at /opt/pg/lib/WeBWorK/PG/ImageGenerator.pm line 429.\n * in WeBWorK::PG::Local::new called at line 52 of /opt/webwork2//lib/WeBWorK/PG.pm\n * in WeBWorK::PG::new called at line 242 of /opt/webwork2//lib/WeBWorK/ContentGenerator/ProblemSet.pm\n * in WeBWorK::ContentGenerator::ProblemSet::info called at line 152 of /opt/webwork2//lib/WeBWorK/Template.pm\n * in WeBWorK::Template::template called at line 480 of /opt/webwork2//lib/WeBWorK/ContentGenerator.pm\n * in WeBWorK::ContentGenerator::content called at line 188 of /opt/webwork2//lib/WeBWorK/ContentGenerator.pm\n * in WeBWorK::ContentGenerator::go called at line 338 of /opt/webwork2//lib/WeBWorK.pm

I'm looking in to mysql settings at the moment, but anyone ever see this? We're running WW 2.3.1 on a DELL 2850 (dual 1.8GHz) with 3 GB RAM, and basically no other services, for 1500 students. The quarter just started, and I'm starting to get email, so any quick thoughts would be very much appreciated.

Thanks-
Fuzzy
In reply to Fuzzy Rogers

Re: MySQL error - too many connections

by Fuzzy Rogers -
So, edited /etc/my.cnf and raised max_connections under [myslqd] to 300 - so far so good.

However when I issue a show processlist; in mysql I noticed that the majority of processes are in Sleep Command. Perhaps I should also shorten the timeout period?

Googling info on that right now.
Fuzzy
In reply to Fuzzy Rogers

Re: MySQL error - too many connections

by Fuzzy Rogers -
So now the question is how much RAM will the maximum mysql connections take up? I'm referencing this site...
http://forums.mysql.com/read.php?24,15350,32027#msg-32027
and it gives some ominous results. I imagine mysql performance tuning is, well, a time consuming job unto itself. Regardless, following that calc, and assuming that the
show global variables;
command yields values in bytes, for a max_connections of 300 I get a RAM usage of...
3.1 GB (if I'm calculating it correctly). Yikes. That would explain my system crash yesterday when I only had a total of 3GB in the system. I presume there are certain factors that can be adjusted to performance tune mysql better for webwork- but that sort of db administration will take some careful studying before changing values I have no idea what they do.

Checking last night, my processor load never exceeded 2, which is great (for 2500 students), and I think my connections in mysqld show processlist; never exceeded 200. I might lower my max_connections if the server starts paging out to swap and the load increases.

another good reference page...
http://www.mysqltalk.org/per-thread-memory-use-question-vt158608.html
Take care,
Fuzzy
In reply to Fuzzy Rogers

Re: MySQL error - too many connections

by Fuzzy Rogers -
And from the WW archives...

http://webhost.math.rochester.edu/webworkdocs/discuss/msgReader$2273