Site Is So Slow!

[eluser]dmorin[/eluser]
@Hockeychap Great script I hadn't seen that before!

@J-Slim To use, do the following from putty:

1. Download the script

wget http://day32.com/MySQL/tuning-primer.sh

2. Change the permissions for the file

chmod 755 tuning-primer.sh

3. Run it

./tuning-primer.sh

4. Apply the suggestions

From http://www.cpanelconfig.com/optimize-a-c...er-script/

You can certainly benefit from the recommendations of this script, but I'm guessing your biggest problem are inefficient queries and lack of indexes in the correct places. All of those "Copying to tmp table" are not good and look at the Time column. Those are seconds! So the top query has been running for almost 24 minutes. So your mysql server is simply getting overwhelmed and starts lagging which makes future queries execute even more slowly so it's a degrading cycle.

As a test, find the full query that is listed in the info column above. It starts with:

SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total, COUNT(Coach.id) AS Coach FROM meet_ev...

Run the full query in MySQLAdmin. After you run it, there will be an Explain link. Click that and paste the results here. That will tell you how your query is executing. Since it's listed so many times, optimizing this single query is likely to give you a noticeable improvement (but won't fix everything).

EDIT:
Just reading back through and saw you say you think it's your hardware. Throwing hardware at it might help, but it's not the cause. I wouldn't worry about hardware yet.

Also, your max connections is small, but it's not the root cause.