MySQL max concurrent connections
 

Hello,

At first this may not seem like a programming question, bear with me it will turn into one

I am trying to get my head around the mx concurrent connection limit in MySQL. Is this as the name suggests the maximum concurrent connections that can be made to the database for all db users? Or is it the maximum concurrent connections that can be made to the database for a single db user?

As an example lets say I have a the MyForum database, there are 3 DB access acounts, acc1, acc2, acc3. Now lets say that the max concurrent connections limit is 10. So if acc1 has 7 connections open and acc2 has 3 connections open can acc3 make a connection?

The reason I ask is that my current hosting is causing me no end of grief with their low connection cap, and I am looking for a way to get around it.

Now this is where it turns into a programming, question...

It the connections are per user and not per server, then I can create a hack in VB that rather than use a single DB account it uses multiple. So rather than have this in the config:

$config['MasterServer']['username'] = 'acc1';

I would have:

$config['MasterServer'][0]['username'] = 'acc1';
$config['MasterServer'][1]['username'] = 'acc2';
$config['MasterServer'][2]['username'] = 'acc3';

Then I would hack the database access component of VB to randomly select one of these accounts.

So the questions are, how exactly does max concurrent connections work in MySQL and does VB use a single class/php function for DB access?

Both options are independently settable. You can find out what the settings are by running the mysql command show variables (you need specific permission to do this, which your host may not have provided)

As an alternative you could look at /etc/my.cnf which might also contain the information.

Move to a new host.

Many shared hosts limit the number of mysql accounts you can have. Having said that, yes what you ask is possible, probably best done as a round-robin scheduler. It would involve alot more that a few lines in config.


single class.

Now this has peak my curiosity, I honestly expected that the answer would be that max connections is for the entire MySQL instance my webserver uses regardless of the user account connecting.

Allow me to do this random user hack, would fix the issue but I have to admit that it still seems rather odd.

Also could you tell me which file the DB access class is located in. After a short hunt I believe it to be db_connect in includes/class_core.php but there is no harm in asking

Like I said, depends on your host.

db_connect is one of the function in the vB_Database class in class_core, but what you really want to look at is the stuff in init.php, where the db class is instantiated on the registry object.

Your host can have either capped the entire database or have the limit on a per user base.

If the limit is on a per user base, then you can create a work around by adding an array of username/passwords in the config.php, and then randomly select 1 of the entries in the array and asign the values to the standard variables for username/password. the only place you need to edit is the config.php, no need to edit any other files.

Not tested, but code like the following in your config.php should do the trick:

Thanks for that code, I have run a test on my closed beta forum and it seems to work, problem is the closed forum doesnt have the traffic to cap out connections so time to bite the bullet and put in on live.

But first...

In the interests of not annoying the host I need to check with them exactly how they have this setup. I have a sneaky feeling that if this "hack" works and they discover it they will probably have a problem with it.

I know the best solution is to move hosts, but in the last 2 weeks or so I have made a lot of updates and changes (all for the good I add), and would prefer to let the dust settle on these before annoying the community once more with a hosting move. If I could do this with 0 down time and 0 data loss I wouldn't hesitate but this just isn't possible.

I'd just keep quiet about it if I was you! Sounds like they've set some arbitrary per-user limit which has no relation to actual load. And if they give you the ability to create multiple MySQL users ... they can't complain if you use them. It would only become an issue if you started to hog the system and cause bottlenecks for other users. At which point they'll let you know it's time to move up the hosting food chain anyway.

BTW, can you keep us updated on how this works out? I'm very interested to see if this works as expected. I don't see any reason it shouldn't.

-- hugh

I am far too honest, actually thats a load of old tosh - I am far to worrried that the host might kill the site.... So I asked them....

You could almost hear their veins popping :)

Mod isnt going on the live DB, time to look for another host. I knew this was coming I was just trying to avoid the inevitable.

LOL! A fairly predictable response. Kind of like being a kid, doing something you know you your folks would say "No" to if you asked,, but technically nobody ever told you not to ... so it's OK as long as you don't get caught. :)

-- hugh