XF 2.1 MySQL DB User ID round robin

[PatMa]

Hi all,

My forum is hitting the default limit of max_user_connections in mysql - currently set at 30.

I could not convince the service provider to up the connection limit for just one of my DB user ids.
The shared environment allows me to create up to 15 ids, each having a 30 connection limit.
My global max_connections is 150.

So you can see where i'm going. I'd like to create two (or N) ids

$config['db']['username'] = array ( 'dbXXXXXX', 'dbYYYYY' [,....]);

alternately to not break things

$config['db']['usernames'] = array ( 'dbXXXXXX', 'dbYYYYY' [,....]);
$config['db']['username'] = $config['db']['usernames'][0];

and rotate the incoming requests between them, either randomly or systematically based on the existence of the array.
would start the coding by requiring identical passwords - but that should probably be updated too.

I haven't looked into the code yet, so i'm not sure if this is the best data structure - but it is a descriptive enough (i hope).

Looks like this would live in Adapter.php

Has anybody tried this?
Worries? Comments? Gotchas?
Jokes are good too - since it is my first time asking a Q in here.
I'm also very old school unix/c/perl guy - so just learning php in case i typo'd something.

As an aside:
Yes i've tried hard to have them change the value.
Yes only XF is associated with that user - i peek/poke with another user id, another for wordpress, another for wiki.
Yes, i've asked the robots to slow down
- what have i missed?

Many Thanks
Pat

PS - the behavior when max_user_connections is triggered is a usually a broken picture embed - we like our pictures.

mtbNJ.com

Mountain Biking in New Jersey

mtbnj.com

 

[Kirby]

Hmm, certainly doable but that does sound like a really ugly workaround.

First of all, I'd try to find out why you are using more than 30 connections. 30 doesn't sound much, but usually this is just fine for any small to medium forum - and according to your online stats you do not seem to have massive amounts of traffic.

If the amount of concurrent connections i really the bottleneck, I'd try to find another host with higher limits.

But if you really want to go down that road, a simple approach would be

$allcredentials = [
    ['username1', 'password1'],
    ['username2', 'password2'],
];

$index = rand(0, count($allcredentials)-1);

$credentials = $allcredentials[$index];

$config['db']['username'] = $credentials[0];
$config['db']['password'] = $credentials[1];

in config.php

 

[PatMa]

First of all, I'd try to find out why you are using more than 30 connections.

We like our pictures!
Probably should dig into the logs and find out if some of the queries are taking a bit long,

Thanks for simplifying! This makes way more sense (put in config, and use a simple structure.)

I can take control of the DB if i run it in a container, but i'd rather not start breaking things right out of the gate!
TY Again.

 

[PatMa]

$index = rand(0, count($allcredentials)-1);

quickly implemented and working - thanks.
----
I updated count(..)-1 to array_key_last() - an old perl ($#) throwback..new in php 7.3 ?

 

[Kirby]

I updated count(..)-1 to array_key_last() - an old perl ($#) throwback..new in php 7.3 ?

Yeah, that's essentially the same thing. Guess I am oldschool here, didn't know that function was available

 

[PatMa]

Quick update: No errors since implementing! Thanks.

We can lock this one up!