The Arcive of Official vBulletin Modifications Site.

MrEyes · #1 01-24-2008, 06:30 PM

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?

Eikinskjaldi · #2 01-24-2008, 08:28 PM

Quote:

Originally Posted by MrEyes

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?

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.

Quote:

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.

Move to a new host.

Quote:

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:

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.

Quote:

and does VB use a single class/php function for DB access?

single class.

MrEyes · #3 01-24-2008, 09:03 PM

Quote:

Originally Posted by Eikinskjaldi

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.

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

Eikinskjaldi · #4 01-25-2008, 03:06 AM

Quote:

Originally Posted by MrEyes

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.

Like I said, depends on your host.

Quote:

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

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.

Marco van Herwaarden · #5 01-25-2008, 07:10 AM

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.

Marco van Herwaarden · #6 01-25-2008, 07:23 AM

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

PHP Code:


			
// Define array of username/password combo's
$my_sql_configs = array(
  0 => array('user' => 'user1', 'pass' => 'pass1')
 , 1 => array('user' => 'user2', 'pass' => 'pass2')
 );
// Randomly select 1
$selecteduser = array_rand($my_sql_configs);
// Assign selected user to config
$config['MasterServer']['username'] = $my_sql_configs[$selecteduser]['user'];
$config['MasterServer']['password'] = $my_sql_configs[$selecteduser]['pass'];

MrEyes · #7 01-25-2008, 10:33 AM

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.

cheesegrits · #8 01-25-2008, 04:08 PM

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

MrEyes · #9 01-27-2008, 08:30 PM

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.

cheesegrits · #10 01-27-2008, 08:41 PM

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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04199 seconds Memory Usage 2,267KB Queries Executed 13 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (1)bbcode_php (7)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_postinfo_query fetch_postinfo fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!