Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

jump.cgi - too many connections?

Quote Reply
jump.cgi - too many connections?
I've seen some other posts from people with this problem, but no solution as such.. The error my users have been getting is:

In Reply To:
Software error:
DBSQL (10161): Fatal Error: Unable to connect to the SQL server. Reason: Too
many connections at jump.cgi line 65
My traffic has picked up over the last couple of days (which is when these errors have been reported), but from what I found at the mysql site, the default max connections limit is 100, and I wouldn't have thought there would be more than 100 connections happening at once. I restarted MySQL but didn't make a difference.

Is it just a matter of upping the max_connections in mysql (and where exactly do you change that value?)?

Thanks,
Ethan

Quote Reply
Re: jump.cgi - too many connections? In reply to
are you running on a dedicated machine? It sounds like it.

When you hit the problem try the mysql show status command to see if anything looks out of whack. You can also use mysql show variables for additional info.

I'm not sure what the solution is, but I've been running several sites off the same mysql server for over a year now, using 90% GT software, and it's never had a problem like that. Current uptime is almost 200 days. My limit is set at 100 as well. I have apache set at 150, and I've hit problems with that a couple of times though.

Very often this has been fixed by the ISP making changes.

Often, a bad script -- or modified script -- will cause that. If you don't explicitly disconnect, there may be a lag in the reclamation of resources. But, a system reboot should have fixed that, and you shouldn't have started to hit problems unless you are really having 100+ simultaneous connects.

I would think you'd notice a load/performance problem before that.

I wonder if it's a problem with apache-keep-alive and persistent connections? If you have more apache sessions running than you have MySQL connections, you could run out even with lower loads. Just speculation.

I haven't hit this, so I haven't had a chance/cause to try to solve it.


PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: jump.cgi - too many connections? In reply to
Hi, thanks for replying so quickly. I have since changed the link template so that users are sent directly to the detailed pages, rather than going through jump.cgi . This means that its working for pretty much everybody except AOL users who (presumably) are seeing cached pages. And I have received a few e-mails from AOL users who have run into the problem again (which is odd, since most people would be bypassing jump.cgi, so there shouldn't be too many connections).

As for links mods, I haven't changed anything recently, and anything I have changed is pretty minor and only effects static 'built' pages. WWWThreads is also un-modded (excpet for aesthetics). Perhaps the problem with MySQL load is because of my PHP comments script (which is a very modified version of michael bray's review.php). I don't think I ever close the connection to the database in those scripts, but PHP is meant to close off the connection at the end of the script's execution.

(btw, they are the only public programs that use MySQL on the server)

I ran the show status and show variables commands and did come up with some numbers, although I wouldn't know whether they values are out of the ordinary or not.

I just checked the apache keepalive value and it's also set to 100 (same as MySQL). hmmm

Any ideas?

Thanks,
Ethan

Quote Reply
Re: jump.cgi - too many connections? In reply to
Ok,

If it's just AOL users... ignore it :) You'll be chasing your tail.

It's quite possible the PHP is making the connection load too high. I don't know enough about it to comment.

But if it's just AOL users complaining, ignore it. Seriously.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: jump.cgi - too many connections? In reply to
Hi Ethan,

When you see this run:

mysqladmin proc

and you will see a list of all connections to mysql. The only reason I could see Links SQL keeping the connections is if you are using a misconfigured mod_perl or mod_fcgi server. Under regular cgi, connections are always released.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: jump.cgi - too many connections? In reply to
I ran this before when people were getting all the errors, but would never see any great number of processes (and nowhere near 100). I just ran it then, and only had one user other than me.

I'm not running mod_perl or mod_fcgi.

Any ideas?
ethan

Quote Reply
Re: jump.cgi - too many connections? In reply to
Alex,

PHP tries to do some funny things as well, to gain speed. It's internals are somewhat similar to mod_perl in that respect, so that may be the problem.

I don't know enough about it to know for sure. But the reason PHP seems faster than Perl, is that it's embedded into the server, like mod_perl. So, it would (theoretically) suffer from the same problems. It really should only be compared to mod_perl for speed, but everyone says it's faster than PERL.

The reason jump.cgi would report errors is 1) links error reporting is better than most programs, and 2) it's probably the single most called script on a site.


PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: jump.cgi - too many connections? In reply to
Hi,

Are you sure the server you are running 'mysqladmin proc' is the one that is running Links SQL? If so, then try a test to see how many connections it can really hold:

Code:
use DBI;
while (1) {
push (@dbhs, DBI->connect('DBI:mysql:YOURDB:localhost', 'YOURUSER', 'YOURPASS', { RaiseError => 1 }));
$count++;
}
END {
print "Got: $count connections ok\n";
foreach my $dbh (@dbhs) { $dbh and $dbh->disconnect; }
}
and see if it really can hold 100 connections (note: connections get immediately released so this won't harm the server).

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: jump.cgi - too many connections? In reply to
Yes, definately. PHP can be compiled to keep persistent connections, so if you have a poorly done php script it can keep requesting a new connection and never releasing it. Then when Links SQL goes to get one connection, it will fail.

However, you should still see those in `mysqladmin proc`.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: jump.cgi - too many connections? In reply to
Alex, I ran that script and it handled 100 connections fine

In Reply To:
DBI->connect failed: Too many connections at ./test.pl line 4
Got: 101 connections ok
As for what pugdog was saying... what would users see if they ran other scripts that used MySQL (such as WWWThreads)? I've got no reports of errors with other programs that use mysql, but that is probably because they don't come accross a page saying to e-mail me.

Ethan

Quote Reply
Re: jump.cgi - too many connections? In reply to
Hi,

Hmm, I don't know what to suggest. The error 'Too many connections' is from mysql, not Links SQL and it means exactly what it says.

As for what you would see, it really depends on how the other programs catch the error. Hard to say, it may be a 500 server error which might disappear next time the user hits it so they don't worry about it, it may seem to work but data is missing, it may seem to work but information doesn't get stored properly. Lots of possibilities.

Hope that helps,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: jump.cgi - too many connections? In reply to
What version of MySQl are you using??

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: jump.cgi - too many connections? In reply to
mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686)

I just had a look at the mysql site, and the latest is 3.23.28. Do you think upgrading will help?

Btw, thank you so much for all your help.. i really appreciate it :)

ethan