User already has more than 'max_user_connections
ZC-1.5.5e, PHP 5.6.32, Database patch level 1.5.5
I get this log error once daily(User already has more than 'max_user_connections)
I spoke with my Server Host, My max db user connections is set at 50 per User. My understanding is that it is based on a concurrent basis. So there would have to be >= 50 db connections within milliseconds.
I read the previous posts regarding 'Rotating db users' .
*********************************************************
Code:
// Enter your 4 database usernames here.
// If you have only two, then repeat them: first, second, first, second
$dbuser[] = 'username1';
$db_pwd[] = 'password1';
$dbuser[] = 'username2';
$db_pwd[] = 'password1';
$dbuser[] = 'username3';
$db_pwd[] = 'password1';
$dbuser[] = 'username4';
$db_pwd[] = 'password1';
// NO NEED TO DO ANYTHING IN THIS SECTION:
// calculate the current "minute"
$current_minute = date('i');
// calculate which "quarter of the hour" we're in
$db_user_active = (int)($current_minute/15);
define('DB_SERVER_USERNAME', $dbuser[$db_user_active]);
define('DB_SERVER_PASSWORD', $db_pwd[$db_user_active]);
//echo $db_user_active;
*************************************************************
Based on the above code it seems that this, 50 or greater simultaneous db connections log error, would still occur. since each different db user would still
be the designated user for 15 minutes. if the concurrency occurred at the second the switch to the next user the max could be increased to 100 theoretically.
After doing some searching on this, I came up with the following code :
************************************************************
Code:
$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
$config['MasterServer']['username'] = $mysql_user['user'];
$config['MasterServer']['password'] = $mysql_user['password'];
****************************************************************
The No. of db users could be increased beyond 3
Everytime a page is opened, 1 of the defined username/password combinations will be choosen at random, and by this reducing the number of connections for each user.
My questions are:
1) Will this code work?
If it does work this could eliminate those max user connections.
Re: User already has more than 'max_user_connections
To rotate randomly, I'm trying the following:
Code:
$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
define('DB_SERVER_USERNAME', $mysql_user['user'];
define('DB_SERVER_PASSWORD', $mysql_user['password'];
Re: User already has more than 'max_user_connections
That sort of concept can work.
But the real root issue is that you've outgrown your server's (or the hosting plan's) capabilities.
Now is the time to investigate a server without such limits.
Re: User already has more than 'max_user_connections
Further, while practicing this will reduce the clashing of multiple user connections (though certainly 50 does seem to be a small number), one should try to look at what is causing that to occur from the onset...
There could be a poorly written script that could be causing the issue of using all of the connections. This could just be helping it run a little longer or by using more server resources.
Re: User already has more than 'max_user_connections
Thanks for the input.
As far as figuring out what the cause is;
i've been recently inundated with this Romanian bot (100's of sessions daily): IP Address 185.206.81.10Host: 185.206.81.10 every day.
The following are a few sessions from the User Tracker Mod:
Code:
Session ID User Shopping Cart
Guest, hsa8l0af3rficsevqvbu4mqr35DeleteView
Empty Cart
Click Count: 1
Start Time: 19:12:41 Idle Time: 00:10:23
End Time: 19:12:41 Total Time: 00:00:00
Country: Romania Romania
IP Address: 185.206.81.10
Host: 185.206.81.10
Originating URL: /mysite/toys/tv_movie_and_character_toys?zenid=6i3ouklnofd5ogsbcg7cpgcbn4&pid=04
Session ID User Shopping Cart
Guest, gad0nrause6vcpe9572nnl0gm0DeleteView
Empty Cart
Click Count: 1
Start Time: 19:08:10 Idle Time: 00:14:54
End Time: 19:08:10 Total Time: 00:00:00
Country: Romania Romania
IP Address: 185.206.81.10
Host: 185.206.81.10
Originating URL: /mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&review=362
Session ID User Shopping Cart
Guest, e55qdtfv75t141snvnd9c8kcn7DeleteView
Empty Cart
Click Count: 1
Start Time: 18:51:02 Idle Time: 00:32:02
End Time: 18:51:02 Total Time: 00:00:00
Country: Romania Romania
IP Address: 185.206.81.10
Host: 185.206.81.10
Originating URL: /mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&pid=108
Session ID User Shopping Cart
Guest, nsifem6betp9r9kopua5si8e52DeleteView
Empty Cart
Click Count: 1
Start Time: 18:45:51 Idle Time: 00:37:13
End Time: 18:45:51 Total Time: 00:00:00
Country: Romania Romania
IP Address: 185.206.81.10
Host: 185.206.81.10
Originating URL: /mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&con=74
Could this Bot be responsible for the DB hits over 50?
How do you know if this is a Good bot or Bad one?
Could the 'Bad Bot Block' mod help in this instance?
Any help would be appreciated
Re: User already has more than 'max_user_connections
Even if it's a good bot, if you're not expecting customers from the audience that that bot's search-index-results will normally serve, then it's prudent to block it.
You can google several things:
- lists of good and bad bots
- using robots.txt rules to block (friendly) bots that you wish to not index your site
And you can use a firewall or a plugin to do the blocking.
ie: if the undesired bot is always visiting from a single (or group of) IP address, the most efficient way is to use the server firewall to block it, cuz then it never even hits your site. One can also use some hosting control panels to block undesired IP addresses (in the background it drops those IPs into a .htaccess file).
Using a plugin won't reduce db connections because the hits will actually get to your store and the store will have to do the ignoring after the db lookup.
Re: User already has more than 'max_user_connections
Many thanks
The Hosting Control Panel 'IP Address Deny Manager'
is a great way denying a range of IP addresses.
Re: User already has more than 'max_user_connections
Just moved hosts and have discovered the new one has max users set to 25 (old hosting was 0/unlimited).
I assume this is very low indeed...indicating a server squeezed to the max.
Testing locally all is fine for 2 and above so I assume scripts are ok.
Re: User already has more than 'max_user_connections
I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).
This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".
Re: User already has more than 'max_user_connections
Quote:
Originally Posted by
torvista
I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).
This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".
I've seen 100 on a shared server.
