Optimizing database structure?

[MsJacquiiC]

This is getting a bit RIDICULOUS - I have a ticket that hasn't been commented on in 3 days or so:

mysql_connect() [function.mysql-connect]: User poetjc has already more than 'max_user_connections' active connections

That's the database error I get many times a day for the past few weeks. Questions:
1. What is the limit of persistent connections I have with the Mocha account?
2. Can this limit be increased so that there is no further database errors?

Also - just curious - how many other people/websites are on the server that currently hosts JPiC Forum?
--------------------------
--------------------------
Now page loads after 45 seconds if I'm lucky at times... Other times are just fine. What is the deal? Something needs to be remedied here, because I haven't paid for the most elite MochaHost hosting package to only have database errors and slow loading pages

OK - That was a recent ticket I filed with my hosting company - I may have exagerated (sp?) just a little with the 45 seconds number LOL - but it's quite irritating to have slow loading pages... JPiC Forum is a small - medium size Community for Poets & Writers - Generally there's only between 100 - 200 simultaneous browsers at one time, including search engine spiders and such...

So MochaHost responds to my support ticket:

Hello,

The limit is set to 20 concurrent connections per user and the max server limit is 500. There are no more than 200 clients on the server on which you are being hosted.

We have advised in previous tickets that you need to look into optimizing your database in order to reduce the overhead associated with your database making persistent connections and executing slow queries. Did you check with your forum provide for more info on how to optimize your database structure?

So my question is two-fold... The stats they mention - is this any good and similar to other shared hosting stats? ALSO - how would I go about optimizing my database as MochaHost has suggested?

Thanx,

Jacquii.

[Mr_Butter]

Numbers I'm not too sure about but to optimize your tables...you can do that through vBulletin.


Admin CP --> Maintenance --> Repair / Optimize Tables

Check all, scroll down, make sure optimize is set to yes and repair you can set to no if you wish.

[Steve Machol]

That is a ridiculously low number. Also they clearly have no idea what they are talking about since 'optimizing' the database has absolutely no effect on that restriction.

The server has maxed out the number of MySQL connections your host allows you to have. You can try turning persistent connections off in your config.php (however it should already be turned off by default):

        //      ****** MASTER DATABASE PERSISTENT CONNECTIONS ******
        //      This option allows you to turn persistent connections to MySQL on or off.
        //      The difference in performance is negligible for all but the largest boards.
        //      If you are unsure what this should be, leave it off. (0 = off; 1 = on)
$config['MasterServer']['usepconnect'] = 0; 

Other than that there is nothing more you can do. All you can do is ask your host to raise the maximum number of connections they allow.

Here is more info on this error:

http://dev.mysql.com/doc/refman/4.1/en/user-resources.html

http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

[MsJacquiiC]

http://www.vbulletin.com/forum/showthread.php?t=279758

OK - so I have asked how to optimize my database for the best outcome. I have now optimized my database.... Still every once in a while - there will be a stream of database error emails...

Interesting commentary one of the vBulletin creators said in the the thread as listed above. You can view the comments STEVE made by browsing to the above thread.

He basically says that the 20 max connections you're allowing is a ridiculously low number - and I can't help but to agree, because sometimes there are moments when there's at least 20 or more yahoo bots scanning the forum LOL - So - per his instruction I ask - Is there a way you can raise the limit of persistent connections for my account so that I will no longer receive the database errors?

ALSO - Because of this issue - I have been considering VPS service. I know MochaHost doesn't offer a VPS service as of yet - Is there perhaps a MochaHost affiliate you can recommend for inexpensive VPS? Mostly though - I'd like the connections adjusted for my current account so that things can be fine.

Thanx,

Jacquii.

OKay - so I have optimized per MR BUTTER's suggestion = Thanx very much. I have also sent the above statement to my webhost. I suppose it's gonna be a bit of interesting to see how they respond LOL

At anyrate - Thanx you both GENTLEMEN for your kind and prompt responses.

I'll ask you Guys as well - I wouldn't think that JPiC would need a VPS - we have a good stream of traffic... Well - perhaps the necessity for additional connections... ARG! Okay.... Deep breath = No stress......

Do YAWL know of a decent hosting that offers reasonably inexpensive VPS hosting?

Thanx,

Jacquii.