Monday, May 5, 2008

Php / MySQL Performance Tuning

by Steve Exley

Linux, Apache, PHP and MySQL has fast become the defacto standard in small to medium website development. Ease of use, low start up costs wide availablity of support are all reasons for it's adoption. Almost all Linux distributions come ready to run LAMP based websites with minimum effort out of the box. However these out of the box approaches are designed to deliver working systems regardless of the hardware underneath. These include ancient processors, low memory systems and rely heavily on resouces like the hard disk to guarantee they work.

With a few easy tweaks, it is possible to turbo-boost the PHP and MySQL components of your web server often generating page delivery increases up to 20% and reducing that all important disk load on your server. Whether you run one disk or more, failing hard disks still cause downtime in all but high end systems. If you are on one disk - then I strongly recommend asking your hosting provider about RAID upgrades. Having 2 or more disks not only provides security in the event of disk failure, but can also be another source of performance boost, It's normal to notice a 50% increase in speed reading from disks in servers running RAID compared to single disk systems.

Look at PHP first. Every time a page requests a PHP file, your server will read the file from disk, compile the code and run it. Those first two steps are wasteful, wouldn't it be easier to read the disk and store the compiled code. Then the server would only need to run it for each page request. Most people know that RAM, the memory on your server is faster than the memory on the hard disk, so why not use the RAM! This is exactly how PHP accelerators work. The compiled code can be stored in RAM and used instead of the uncompiled PHP code residing on your hard disk.

There are a number of free PHP accelerators available. APC, eAccelerator and xCache are a few. Zend also produce a paid up version for the over cautious types. I'm not going to discuss the merits/drawbacks of them each, there are other articles that do that. Try a search for 'PHP accelerator comparison' and you should see good results. The amount of RAM used by these tools is small, less than 32Mb on servers with only a few sites. More is required on servers with many websites and this is where a configurable accelerator which lets yous specify which files to cache can be useful. Choose the most common / largest PHP files and cache those.

It's worth noting the Linux systems will use any free memory available to store files too. This works by copying commonly used files unused memory and checking that before reading from the hard disk. It all happens behind the scenes and transparently to the user. Use the command 'free -m' in Linux to see what is going on. If you're using you're 'Swap:' then you need to reduce the services running on your server or ask your hosting provide for a RAM upgrade.

MySQL comes with built in acceleration features. By default these are all tuned right down so MySQL uses the minimum amount of RAM, however if 'free -m' on the Linux command line shows you have free 'Mem:' then some setting changes will offer big performance gains. Before starting out, check your SQL queries though. An efficient MySQL database structure and well designed queries will offer far better performance gains than just increasing the RAM. Learn how to use Indexes, roughly speaking these should be the fields used to JOIN tables and the fields queried with the WHERE statement.

If you think your queries and databases are hot, but you still want more then you need to tune MySQL itself. I'll assume that like most people, you have phpMyAdmin installed. If not then you can find how to find this information manually - but you probably won't be needing to read this article. Go to phpMyAdmin and click the home icon, normal in the top right of the page. Then 'Show MySQL runtime information'. There is a lot of information here, lets go for the low hanging fruit.

Under 'Query Cache', if there are only zeros then your query cache is not even running. Otherwise 'Qcache_free_memory' will give you an idea of your current usage. Next is 'Temporary data' - what is the ratio of 'Created_tmp_disk_tables' to 'Created_tmp_tables'. You want as many temporary tables as possible to exist in 'Created_tmp_tables'. Finally check your 'Key cache'. The ratio of 'Key_read_requests' to 'Key_reads' should be as high as possible, greater than 100:1 on most systems.

These values all tell you how often MySQL is using RAM compared to the hard disk. Remember, RAM is way faster than the hard disk, so you want to use it as much as possible without affecting other systems. The defaults in MySQL are very low and MySQL needs your permission to use more. This is frequently done using the file '/etc/my.cnf'. Some systems may use other locations, but then they would not be using the defaults either. If the file does not exist, then it is safe to create it. You may find some examples in '/usr/share/doc/mysql-server-****'. Backup any files before starting. Add the lines 'key_buffer = 8M','query_cache_size=16M','tmp_table_size=16M'. Issue a /etc/init.d/mysqld restart and see what happens. The server should be working fine. Monitor it for 24 hours and then look at your values in phpMyAdmin again to see if they show and improvement. Use 'free -m' on the Linux command line to ensure you are not using 'Swap:' and tweak the values in /etc/my.cnf until you have a good compromise between performance and memory usage. I've seen much higher values for all the above on modern systems.

About the Author

Steve Exley is Technical Manager at studio-40.com and is tasked with the server maintenance of many commercial business websites. Studio-40 is an established created web design agency in Leicestershire, East Midlands.