Optimize apache/php/mysql running on VPS for heavy load

Question

Question about optimizing an apache/mysql server on a VPS with 512m of RAM. Under normal load everything runs fast, no connection lag. However when we get our heavy traffic days (50k+ visits) the site crawls and it takes 30 seconds+ to get content back from apache.

The site is running on Expression Engine (CMS) (in PHP) and I've followed their heavy-load optimization guide. I've googled and followed quite a few out there for apache with some luck, getting it to where it is now, but I need to get constant response times.

I assume this is different from the 'optimize for low memory' question on here as I have enough RAM (for what I'm trying to do), I just need to get the server to not choke under heavy load.

Any recommondations?

Answer 1

For PHP there are 2 important things that will increase capacity:

1. 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.
2. 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:

1. Use worker MPM
2. 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:

1. 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.
2. 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!)
3. 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.
4. 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.

Answer 2

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>

Answer 3

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.

Answer 4

• Use a opcode cache for php like apc.
• Use a http accelerator like squid or varnish.

Answer 5

Beyond the great suggestions here, it should be noted all VPS' are not created equal. In my experience PHP turned out to be CPU heavy.

A Wordpress AB Benchmark (ab -n 500 -c 25 http://domain.com/index.php) of nginx/apc/phpfpm/mysql(local) on EC2 resulted in ~2 requests/second on their entry level "2GB RAM/1 Compute Unit Server".

The same Benchmark run against the same exact stack (deployed by script to identical OS) on a 512MB Rackspace Cloudserver returns ~80 req/second. So 4x Less ram, 40x performance in this rudimentary experiment.

Viewing top during the AB you see that EC2 simply could not handle the concurrency, and would immediately hit 100% CPU Load and lock up. Viewing top on the 512MB Server (virtualized quad core CPU) durring the same benchmark, The cores would hit ~60% Load and smoothly handle the benchmark.

VPS's are extremely easy to spin up and turn off with no commitment, it doesn't hurt to put the infrastructure your VM/VPS resides in to the test!

EDIT 1: Also, EC2's "High CPU" Small Instance was only able to yield ~10/req second, with the CPU still being the bottleneck. My conclusion was that you sacrifice performance for stability/robustness with EC2, and there are of course many use-cases that call for such an environment.

Answer 6

In a low memory situation (512Mb is low, for a high-traffic server) it is worth considering your choice of web server and DB engine.

Lighttp is lighter out of the box than Apache can usually be made after much tweaking, and there are lighter options than that even. This is of course no possible if there are Apache features you depend on that are not support in other servers.

sqlite is much tighter than mySQL, and faster under many conditions too. Check if the engine you are using supports this as well as and if it does give it a try.

The other option, the easy option, is to get more RAM in the VM if you can afford it.