Scaling website (mysql) tips?

[CaanaCast]

I have a website that runs on 2 boxes, 1 for mysql and 1 for html. My problem is during peak hours, my mysql load is too high and the site takes forever to load. We are no experts with NIX/Server Management and its a small operation we are running here (revenue wise) so we can't afford to buy expensive hardware nor are we really technical when it comes to scaling, we are student-hackers who are decent at coding and I guess that is about it. The current system was set up by our webhost, but we could have set it up if we really needed to.

My question is, how do I add more mysql boxes to scale this thing out, I have already applied the simple tweaks of memcache, splitting up tables, etc.

Any simple ways or tutorials anyone can point us out to for scaling without spending too much would be great or tips on what to do next or who to approach would be helpful.

[jacquesm]

I would start off by seeing if you can't optimise that db server that you're running, that would be the low hanging fruit.

A tuned mysql server versus an out-of-the-box configured one will make a world of a difference.

It would be nice if you provided some more information about what you consider to be 'peak hours', how many queries per second you are processing, how the division between static and dynamic content on your site is and whether you have done things like enabling and analysing the 'slow query log'.

Have you used 'explain' on all your queries to make sure that you are not accidentally hitting all or a large number of rows in some of your queries ?

[CaanaCast → jacquesm]

Peak hours would be 100 queries a second minimum. Static content is served by 2 different servers (1 img, 1 html). I did not know of the slow query log parameters (but will enable it now).

When starting we were pulling too many rows, but we quickly realized that and lowered scripts that display too much content that is not needed, we are also using proper indexes.

[jacquesm → CaanaCast]

Other nasties to look out for: follow on queries that could have been done at the time of the first one and joins.

[gojomo]

If your content is amenable, have you set up a front-end HTTP cache (aka 'reverse proxy') yet? The fastest database operation is one that doesn't happen at all.

Make sure the machine(s) are really CPU/IO loaded, and that you're not just witnessing exhaustion of some artificially-capped pool of connections (at any tier).

Make absolutely certain no swapping is occurring, and that the things you've tried so far (such as memcache'ing some things) haven't backfired by introducing swapping or more process contention.

Making sure you have relevant indexes and key bottleneck queries are being handled in an efficient manner should come before anything that might be called 'splitting tables'.

[HNer]

are you adding indexes?

you can skype me for some tips @agentbleu