Okay here is the dealio....
I have three SMF forums, none of which see very much use at all. About 2, 3, or 4 weeks ago I started to get timeout errors in my internet browsers, both firefox and internet explorer.
And the sites became very unresponsive. Often times I need to click on a link a couple times before it "registers" with the server. (All non interactive sites are responsive however).
I contacted DH support who finally got back to me and told me that the problem might be caused becuase all the MySQL databases that we have have the same one user associated with them (again, our sites don't see a lot of traffic at all, one site has 4500 users but is lucky to see 20 users sign in on a daily basis, and the other one has 938 users and might get 15 people signing in a couple times a day), so they said to make one user for each database which we did.
Specifically they told us the following:
Handler_read_rnd 13 M The number of requests to read a row
based on a fixed position. This is high if you are doing a lot of queries
that require sorting of the result. You probably have a lot of queries
that require MySQL to scan whole tables or you have joins that don't use
Handler_read_rnd_next 392 M The number of requests to read the next
row in the data file. This is high if you are doing a lot of table scans.
Generally this suggests that your tables are not properly indexed or that
your queries are not written to take advantage of the indexes you have.
Both numbers should be zero. You are using the same database user for all
your databases. Its possible this is causing your table scans to be so
high. I would recommend creating a new database user for each database
and having a single user for each database as well. If this does not
improve your mysql performance then you will need to do thru an optimize
each table or look for the queries that are not every efficient.
Okay so we created a single new user for each database, but the sites still remain slow and unresponsive and those two numbers they quoted me are now up to like 50M and 1500M. So they keep rising and the sites stay slow.
They seem to indicate that we need to look for queries that are not very efficient.
Im sorry but SMF is a good forum package and used by tens of thousands if not hundreds of thousands of websites, and there is nobody else that has complained on the SMF site about this issue.
Now the only thing that I can recall that changed about the same time the sites started to get slow is that we did actually create one SMF full domain forum (at our domain name) and we created 12 subdomain forums, and all of them were using the same database which had about 650 tables in it. This only lasted about 3 to 5 days, and we only had 15 people sign up in that time and so most of any queries run would have been me setting up the sites.
In any event I took those 12 subdomain forums down nearly 2 weeks ago and am now just running one larger forum in it's place (database is only about 55 tables and about 3 or 4 MB in size. So it's small. Maybe 150 posts on that forum.
Everytime that I test the site through the DH web panel it tells me the server was under a high load (I guess I can't really test the MySQL server through the web panel which is what I suspect is causing the slow downs.
The authors of the SMF software have told me that it's not their software causing this issue and I need to take it up with my host which I have done on a couple occasions now. What it's starting to feel like is that they don't want to respond anymore and would like to sell me on a VS or dedi server which is out of the question, I just don't get that kind of traffic for either of those.
So, what the hell am I supposed to do at this point to fix this. Theres nothing I can do to the forums software to optimize the queries, and nobody else is having this kind of trouble. It seems to me theres a problem with the Indianna server and nobody at DH knows or cares to find out about it.
The first couple times that I contacted them they told me that the server was found to be under a high load and that they found a user who's scripts were sucking up the resources and had disabled his scripts until he can fix them or replace them. The next time I contacted them they told me they found another user also sucking down server resources with his scripts and had disabled them.
The next time I contacted them they tell me the stuff about making a new user for each database which I've done.
Im really rather pissed off at this point as people are complaining to me that they are getting browser network timeouts and other people that register on the sites are not getting the activation emails sent to them for 12 to 24 hours. This has gotten ridiculous already and I need some suggestions on how to deal with this.
Please give me some suggestions if any of you have any or if any of you are also on Indianna MySQL server tell me if you are having any difficulties or slowdowns with your forums if you are running them.
Even the wiki that we installed through the web panel runs like mollasses in winter. And that's the wiki that gets installed in the one click install, so if theres optimizing to queries that need to be done, why is the wiki running so damn slow too?
Sorry, Im really frustrated and need some assistance and not feeling like Im getting it from Dreamhost at this point.