Drupal and too many connections problem

[Apathy]

We just got our site going a couple of days ago, now we often getting the max_questions(72000) error.

For a start this seems strange, the max page loads per hour has been ~1400, and about ~300 visitors at any one time. I looked at the code and it would seem the maximum queries per page should be no more than 10ish, and at that rate the most we'd get would be 14000 queries per hour!

I optimised some of my code that I thought might be causing the proble (droping it from 8 queries per load to 3 for a perticular page)

The strange thing the error will occur (once every 20 pages or so) and then reloading the page will usually solve the problem?
Has anyone had similar experience with Drupal
* am I underestimating the number of queries it makes to create a page?
* do I need to use a rotaing DB user, like I've seen on this forum for mambo user?
* has anyone implimented this in Drupal?
* is there something else going on here I don't relise?

FYI The site in question is http://collectiveapathy.com

[ticoroman]

I visited your website and I did see the following error message:

User 'Apathy' has exceeded the 'max_connections' resource (current value: 7200)

I'm a little confused. Why is the current value 7200 and not 72000?

* the error message was shown on http://www.collectiveapathy.com/About

====
Wait a minute, it says 7200 connections and not queries. I don't know anything about the max connections limitation....

[Caligatio]

As I mentioned in a thread I started, try using ADoDB (http://adodb.sourceforge.net/)

It let's you cache queries for any amount of time. So, if you are having limit issues, this could help ya out.

[Apathy]

I just got that one too, looks like we are hitting a max_connections limit too?
The question is, should all this be happening with drupal and only about 1400 page loads per hour?

And has anybody been able to reduce drupals load with a random mysql users patch?

[Apathy]

Caching requests would definately hellp (though drupal alread does this to some extent and I get the same errors with cache on or off :( ).

From the web page you gave ADoDB doesn't seem to support drupal directly, has anybody used it with drupal?

[RTH10260]

Wait a minute, it says 7200 connections and not queries. I don't know anything about the max connections limitation....That's a fairly new restriction imposed about a month ago. Check the current version of TOS ...

Workaround: spread load among the possible three database usernames.

[RTH10260]

We just got our site going a couple of days ago, now we often getting the max_questions(72000) error.

For a start this seems strange, the max page loads per hour has been ~1400, and about ~300 visitors at any one time. I looked at the code and it would seem the maximum queries per page should be no more than 10ish, and at that rate the most we'd get would be 14000 queries per hour!

I optimised some of my code that I thought might be causing the proble (droping it from 8 queries per load to 3 for a perticular page)

The strange thing the error will occur (once every 20 pages or so) and then reloading the page will usually solve the problem?
Has anyone had similar experience with Drupal
* am I underestimating the number of queries it makes to create a page?
* do I need to use a rotaing DB user, like I've seen on this forum for mambo user?
* has anyone implimented this in Drupal?
* is there something else going on here I don't relise?

FYI The site in question is http://collectiveapathy.comAs I don't know anything about the CMS you use, I can only assume that there is some inefficient code. The cause may be that several modules are generating part of the final page, and each is making its own connection locally in its own code.

The preferred way to operate against a database is to only open a connection once at the beginning of a transaction in the main script and closing it at that level at the end, and any independant module should just be using that one connection.

I don't have the time to study the detail of mysql just now, but I seem to remember, that when a connection already exists, and a request for another connection with identical parameters is made, that the existing connection would be reused.

For a workaround this would indicate, that one could add a connection request at some higher level script and it would be reused at lower level script. The connection would not be closed as long as the top level keeps its connection open.

Someone must experiment with this.

[ticoroman]

That's a fairly new restriction imposed about a month ago. Check the current version of TOS ...There is nothing about it in the TOS. Everything I can found about mySQL is:
MySQL: 5 MySQL databases are included per package. Additional databases can be purchase for a one time setup fee of $10/db. 3 user limit per database, 72,000 queries/user per hour limit.
http://powweb.com/PowWeb/Company/Policy/TOS

[Pig]

The 72,000 limit has been in place for many months. CMS and BBS often use a ton of queries. phpBB for example, can use 60+ queries on one page. Most CMS aren't any better. You can rotate your database users to spread your queries out. Check out the sticky thread in the PHP forums for more info on that, if you are interested.

[coldhead]

FYI.
'show variables;'
.....
'connect_timeout | 5'
'max_connect_errors | 10'
'max_connections | 500'
'max_delayed_threads | 20'
'max_error_count | 64'
net_read_timeout | 30'
'net_retry_count | 100000
'max_user_connections | 0'
...
last one would be overwritten at the user level to 3 i guess.
mysql10 only allows 500 connections at any one time.
Have a look at your process list in phpAdmin.

PHP people can get this error if scripts open persistent connections that aren't closed when the script terminates. Use mysql_connect() instead of mysql_pconnect() unless you have a good reason. In particular, check this setting in third-party scripts (such as osCommerce).

Server administrators can disable persistent connections for PHP scripts in php.ini file:
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent=Off
Scripts won't fail, they'll just use non-persistent connections silently.

[RTH10260]

The 72,000 limit has been in place for many months. CMS and BBS often use a ton of queries. phpBB for example, can use 60+ queries on one page. Most CMS aren't any better. You can rotate your database users to spread your queries out. Check out the sticky thread in the PHP forums for more info on that, if you are interested.But the 7200 connections limit is new, only a couple of weeks or so.
ref http://forum.powweb.com/showthread.php?t=52115&highlight=7200#post307749

The other information given at the time, was that this is a rolling 60 minutes counter/limit.

[Apathy]

I implemented a random 3 user scheme for Drupal as suggested - see below if your interested in my Drupal Solution.

The funny thing was I was still getting the error (7200 connections) but only for my original user not the others. This suggested to me that there may be some persistant connections as mentioned above, checking the code it only uses msql_connect() not mysql_pconnect(). I deletet the user and replaced it with another, it seems to be working now!

Solution for Drupal
================
in settings.php where you set $db_url make it an array (drupal already accepts this):

//'default' index is only to account for case where database.inc is not modified!
$db_url['default'] = 'mysql://user1:passwd@mysqlXX.powweb.com/Databasename';
$db_url[1] = 'mysql://user1:passwd@mysqlXX.powweb.com/Databasename';
$db_url[2] = 'mysql://user2:passwd@mysqlXX.powweb.com/Databasename';
$db_url[3] = 'mysql://user3:passwd@mysqlXX.powweb.com/Databasename';

Then in database.inc in function db_set_active () at the top of the function place the following code:

if ($name == 'default') $name = rand(1,3);

and thats it

[ticoroman]

But the 7200 connections limit is new, only a couple of weeks or so.
ref http://forum.powweb.com/showthread.php?t=52115&highlight=7200#post307749I see it now. However it's not included in the TOS (as it should be).