we have a high-traffic dynamic web site that generates lots of database queries on each request and two or three linux servers for that , now I want to decreas the servers load I know squid reverse cache could help , but will it be enough ? what other solutions do I have ?
Step 1) Fix the fucking code.
Why is it making so many queries per request? Can the code be optimized before you throw more moving parts into the mix? Install something like NewRelic to get an idea of where the application bottlenecks are.
Step 2) Sort out the database.
Are you using indexes? Are you using them correctly?
Are you using the right DB Engine (MyISAM vs INNODB)?
Could stuff be stored more efficiently in a key-value store?
Cache All The Things.
Starting at the lowest level: Do your servers have a flash-backed cache on the disks?
Do your servers have enough RAM to avoid swapping?
Increase the amount of RAM available on the servers, so that you can:
- Use memcached to store database query results (this requires a codebase change, too, usually).
- Use memcached to store pre-generated page fragments.
- Increase the database in-memory limits so that more query results are stored in memory.
- Install Varnish reverse proxy which will cache lots of page data, taking the load off the backend.
Split up the servers into role-based clusters. Have a master/slave DB pair. Edit the codebase so that you get read/write splitting (reads go to the slave(s), writes to the master).
A frontend cache/load balancer might help. Both Nginx and Varnish are excellent choices (personally i find NginX easier to setup and Varnish higher performance, but for just three backend servers either would be enough).
But to really make a difference you need to attack the source of the problem.
Since you're not the programmer, you can start by analyzing the database queries. Both MySQL and PostgreSQL have a "slow queries" log option. Turn it on and try to get some hours (or days) of real traffic. You'll likely find a few (2-10) very similar queries that consume most of the time. Open a DB administator console and run
EXPLAIN on them. In most cases, just identifying the right index to add can result in 100x performance. And that's without touching code!
I recommend using http://en.wikipedia.org/wiki/Nginx
That would take care of the caching among other thing.
Deploy an integrated cache like memcached to take some of the load off the database.