MySQL Need Optimizing? Or Need more RAM? Server Load is High.
I'm in need of some advice here.
I'm running a P4 2.4ghz with 512mb Ram.
I have a very database intensive website that receives about 15,000 unique visits daily and can have as many as 300 users online at one time.
For the past 48 hours or so the system load has been floating between 1.0 - 4.0 during peak periods. I have some cron jobs that must run in order for the site to remain updated, they run every 20 minutes in a staggered fashion so that they don't all run at the same time and bring the system to a crawl. (All of the cron jobs are using the database to do table updates and are dealing with hundreds of thousands of rows of data).
This morning I had a scare when I saw my system load hanging around 12 - 14 for about 15 minutes..
So my question.
Would optimizing MySQL be my best bet? (if so, any mysql optimization wizards care to help?)
Would adding an additional 512mb ram help me out a lot?
Should I just switch servers to a multi-processor server?
Thanks, any sound advice would be much appreciated.
RAM is usually a good idea on a database server, SQL servers eat RAM.
Database performance is generally either disk or memory related. A multi-processor system would not necesserally help, although if there's a lot of processing of the database results, then it would most probably help with that, again if this is not a bottleneck, then it'd not solve the issue.
As for optimisation, are most of your main database reads running against an index ? If not, a bit of index tuning may help you. Specific issues would need knowledge of your schema.
What do the cron jobs do ? Could these be re-written to be more friendly ?
The queries on the site are running against indexes, and if you were to browse the site (www.tutorialized.com) you'll see that the actual time it takes to load a page is generally under 0.5 seconds, so the queries are relatively fast.
The cron jobs are my biggest pain, I know they could be more friendly, however, I would need to change my database structure to accomodate that. They are basically doing counts and then updates.
You can almost always solve an issue by throwing more hardware at it, but I'd rather get to the bottom of the design issues otherwise you're always going to be moving to the next level of hardware when you get a problem.
If the site was operating slowly, then I'd suggest perhaps a quick fix would be move servers, but this responds at a decent speed, it's only the routine update jobs at fault (it seems).
What's the cost difference between a P4 & a dual Opteron ? I'd guess it adds up to quite a bit over the year. Looking at Managed.com (cos it's cheap !), you're looking at about $110 per month difference between their P4 2.4 and their dual Opteron.