Simultaneous Connections to mySQL

[apextreme]

I was wondering how many simultaneous connections would be needed for a site with either 1,000, 5,000 or 10,000 users.

I'm thinking of hosting (virtual) with this company which allows 100 simultaneous connections to mySQL and wanted to know what a good number would be.

Perhaps the better question is, what percentage of your users are logged into your vbulletin board at your busiest times? I'm hoping that you experienced administrators can shed some light on this.

Thanks.

[Martin]

it really depends on the number that are online at once.

if you havev 5000 users online at once, you better look for a sturdier database than mySQL. It won't handle that.

I have about 6k members, the most i have had on at once is about 160, but my forum seems to be a little more active than most with that few members.

if you're going to have more than 100 people on at once, 100 connections will not be enough.

[George L]

well i have had up to 140 users on my forum and mysql shows that only mysql max connections used was 85

[shri]

If the mysql server is local there is no need for more than 3 or 4 connections on my server (100-150 vB users during peak hours).

Change the pconnect to connect.

pconnects are useful when there is network latency between two servers (web and sql host). Atleast this is what I gathered when I read the docs.

The good part is that I don't have any mysql server process that are "sleeping" yet consuming some memory.

Shri

[wert]

pconnect

I'm not guru, but I've heard that using pconnect can quickly use up a shared evironments mySQL connections. I've always wondered exactly how many SQL queries vBulletin makes to generate its index and view forum pages. Probably a lot less in 2.0 since it's obviously much faster than 1.1.5...

[Martin]

Originally posted by shri
...Change the pconnect to connect...

...The good part is that I don't have any mysql server process that are "sleeping" yet consuming some memory.

I did this after reading this post just to try it out and I have noticed change in load, no change in running/sleeping/zombie processes, no change in speed.

The only thing I noticed is that now, after almost 24 hours, the max used connections is 57 vs 186.

[George L]

if you do use connects rather than pconnects, there will be alot more new connections - from mysql.com docs it's recommended to increase your thread_cache_size variable in that case

thread_cache_size
How many threads we should keep in a cache for reuse. When a client disconnects, the client's threads are put in the cache if there aren't more than thread_cache_size threads from before. All new threads are first taken from the cache, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.) By examing the difference between the Connections and Threads_created you can see how efficient the current thread cache is for you.

[apextreme]

That's the dillema I am in right now. For someone who has not run a bulletin board system before, it is difficult to predict how many connections I would need to support it. This will be one of the factors I will be considering whether to go virtual or dedicated. If I can get away with the cheaper alternative for now, that's great.

So my question is this: What percentage of your users are logged on during your peak times?

So, if you have 5,000 users and get 500 on Thursday nights, your peak, it would be 10%.

This is the only problem a low-end user like me may have with VB because of the mySQL connection limit that most hosts enforce. Otherwise, I'd have to use the other board out there which does not require mySQL. It may not have as many features as VB, but at least I could use the cheaper virtual hosting during the infancy of my site.

[Overgrow]

4000 uniques per day, 5000 members, 130 peak usage. Seems that your % online is always going to be around 2.5%

At around 120 to 150 users peak concurrent is the point where you need to switch from a virtual host to your own dedicated box. (Yes I'm late on my deadline) This is the only way you can make the necessary mySQL tweaks to keep a forum of that size running smoothly.

If it's a new forum on a new site, it will be a little while before you have to worry about reaching your mySQL limit. But good for you if you're thinking ahead-- it is a pain to switch providers. Does that provider offer a good upsize policy? IE, do they offer the dedicated box that you'll need at that point so you won't have to switch hosts?

[George L]

Originally posted by apextreme

So my question is this: What percentage of your users are logged on during your peak times?

So, if you have 5,000 users and get 500 on Thursday nights, your peak, it would be 10%.

This is the only problem a low-end user like me may have with VB because of the mySQL connection limit that most hosts enforce. Otherwise, I'd have to use the other board out there which does not require mySQL. It may not have as many features as VB, but at least I could use the cheaper virtual hosting during the infancy of my site.

Well i went dedicated before i even purchased vBulletin - i was confident i would need a dedicated solution but didn't know how to admin a server - jumped in the deep end and well was lucky to stay a float.

From June 24, 2000 to present - i have 1300 - 2800 visitors/day, 1860 members, only 900+ have ever posted and of those 40 - 140 are active at the same time. I found concurrent users do not equal mysql connections used. When i had 140 on my forum for 6 hours mysql was only using 85 max_connections from MySQL.

I know for sure that my current dedicated host's virtual accounts are good for 40 - 50 max connections for a virtual site.... most other hosts i'd think it was around 20 - 40.

It helps if your web host ain't running off mysql default settings as well.

[apextreme]

I was thinking of going with hostway which hosts all my other websites although none of them are currently using mysql. One site that I have is running a test version of the OTHER bb. However, I'd rather have a system as flexible as VB.