Optimize apache/php/mysql running on VPS for heavy load
For PHP there are 2 important things that will increase capacity:
- Advanced PHP Caching (APC) as was mentioned. This is what we use at Yahoo!. There are other similar projects, but this one is Rasmus' baby.
- FastCGI instead of mod_php. There is debate on this issue as mod_php is usually the fastest. However, I would assume that you have a single Apache server delivering both dynamic PHP content and static assets (JS, CSS, flash, images, PDFs, etc.). The requests for those static assets do not need to consume as much memory, but because PHP is a module it is in every Apache thread.
For Apache:
- Use worker MPM
- Enable KeepAlive
You may also go so far as to consider switching from Apache to Lighttpd, or Nginx. I love Apache. I use the fool out of many of it's advanced features. I accept its overhead because I need what it offers. For the common LAMP stack, it is more than is needed and a waste of resources.
For MySQL:
- Your optimization efforts will payoff 10 fold when spent analyzing and correcting queries, instead of tweaking your my.cnf values. I'm not saying that it's not important to get your caching, connections, etc. correct... but for most people it is only 9% of the problem.
- During your QA, turn on the general query log on your staging/dev mysqld to capture all queries sent. (Do NOT do that on your production mysql server!)
- Use EXPLAIN to analyze the queries. Especially if you are using a framework with an ORM (abstracts away the DB and keeps you from writing your own SQL) you will need to clean out extraneous JOINs, SELECTs with no WHERE clause, ORDER BYs that induce 'using filesort', and queries that use no indexes.
- If you are using MySQL 5.1 take advantage of the query profiler.
Other tools worth considering are mk-visual-explain
I've cited 10 great references. These things ought to get you humming. Please lets us know how it turns out.
Move your PHP session files to a tmpfs, use APC ( or other ) and remove all PHP modules that you do not need. Remove all Apache modules you do not need/use.
To create a tmpfs (a directory in RAM! )
mkdir /tmpfs; chmod 777 /tmpfs
mount -t tmpfs -o size=256M tmpfs /tmpfs
In /etc/fstab add the line below to create it on reboot!
tmpfs /tmpfs tmpfs size=256m,mode=0777 0 0
In /etc/apache2/php.ini adjust to store your sessions in RAM (tmpfs)!
session.save_handler = files
session.save_path = "/tmpfs"
Note: With your PHP files AND session files in RAM you barely touch disk!
Use expires_module in apache so browsers will cache most things.
ExpiresActive On
ExpiresDefault "access plus 90 days"
ExpiresByType image/gif "access plus 90 days"
ExpiresByType image/ico "access plus 90 days"
ExpiresByType image/png "access plus 90 days"
ExpiresByType image/jpeg "access plus 90 days"
ExpiresByType image/x-icon "access plus 90 days"
ExpiresByType text/css "Access plus 90 days"
ExpiresByType text/html "Access plus 90 days"
ExpiresByType application/x-shockwave-flash "Access plus 90 days"
ExpiresByType application/x-javascript "Access plus 90 days"
Do not use .htaccess files! Instead, hard code them in vhost config file! Will drastically eliminate/reduce disk checks per all http requests ... it really adds up.
Options FollowSymLinks
AllowOverride None
Example of .htaccess used in your vhost.conf file...
<Directory /home/user/www/site.com/secure>
Order Allow,Deny
Deny from All
</Directory>
Couple of things come to mind.
Opcode cache is always a good idea. I prefer http://eaccelerator.net/ over APC. If you have not been developing with APC along the way trying to add it in is almost always painful. Eaccelerator while not as fancy just seems to work.
A reverse proxy is also a good idea, but you need to watch RAM usage. I find Apache 2.2 with mpm-worker to take up a fair amount of RAM on it's own. In your case I'd recommend something lighter like Nginx and run Apache with PHP as FASTCGI or just leave it as per process. The idea with using Varnish, Squid, Nginx, etc is to have them serve static content, deal with user connections, and only pass PHP requests to Apache which you treat as an application server.
If you're running a fairly recent version of Mysql 5.1, like at least 5.1.24 you now have access to sub second slow logs. I would start long_query_time at 1 or 2 and then bring it down to 0.5 as you get a handle on the really long ones. There is also lots of general tuning info on the net for Mysql, but you don't have the RAM to do much. Have you increased any of the setting from default? Most default my.cnf files are configured to use about 64MB of RAM. It the very least I'd raise the key_buffer from 16MB to 64MB.
Additionally are you using Myisam or Innodb tables? If you're keeping session in the DB you'll want to change the session table to Innodb (or make it cookie instead) rather than leave it a Mysiam table which does table level locking rather than row level locking. Basically any table that is more more than 20% write to 80% reads is a candidate for moving to Innodb. Remember you'll need to balance the amount of RAM between Myisam tables and Innodb tables because the buffers for each are configured separately.
And lastly another 512MB of RAM would go a long way in your setup or even another 512MB VPS to run Mysql in if that's cheaper or roughly the same price. I'd actually lean towards a second instance because that'll double the available disk IO. One of the problems with VPS servers is your IO is not protected from other people on the same physical server.
Hmmm my post all sorta scatterbrained, but gives you a lot of places to look. Good luck.