Server high load/high mysql usage. Need new server. [HELP]
I would like to thank you in advance for your time and for help on this issue of mine.
I have a site that is eating up my server resources and need to know what the best solution for this is. I'm thinking of getting another server just for mysql but do not know what specs the server should be to handle the current traffic/database load and have the site run smoothly without slowing down to a snail's pace.
An alternative is to get another server just for the videos being served and leave the database and html on the current server. This is where I'm stuck and don't know what route to take with this.
I've attached screenshots of top and bandwidth usage per day. Hopefully with this information you could tell me if I need another server or if there are any things I can do to the current server to help things move faster.
How can I see exactly what are generally causing the high load?
It's generally best to start with logs, to find out exactly what is happening on the system.
You will already have web server logs; so check them to verify there is nothing unusual happening.
There will also be an SQL error log; check that too, to verify there is nothing unexpected in there.
Then you need to see what SQL queries are being executed.
By default the MySQL query log is not activated in the MySQL configuration file (my.cnf or my.ini) because it generates very large amounts of data.
Don't simply activate the log on a busy system; first, set up a test database server, activate query logging, and execute some queries. Examine the query log, to confirm you can make sense of the data collected (if you can't, there's no point in proceeding to log queries on your production server.)
have you tried tweaking your system, from the kernel level up to the services level?
moveing the SQL to an offsite machine will create more traffic on your NIC.
do you have iostat readings to share?
cant you ask your host to upgrade your machine ?
add more RAM and switch to 64BIT based distro.
Thanks everyone for their input on identifying what should be optimized. My host has helped me with the optimization and now there is a significant increase in page load times and a decrease in server loads.