Matt’s Blog of Truth

This is a 4 step guide that will show you how to highly optimize your LAMP server by using features already built into the software — in most cases — but simply requires a little configuring to turn them on. Afterwards, you will have a server that will be able to handle incredible web traffic loads that could save you thousands of dollars in computer hardware upgrades, that you otherwise may had purchased, to gain the same effects.

1. Apache mod_expires
2. PHP file compression
3. APC for PHP
4. MySQL persistent connections, Indexes and Helper Tools

Part 1 - Apache mod_expires

Configuring your Apache to cache as many files as possible is done by simply configuring mod_expires module. In most cases you will place the configuration lines in httpd.conf located somewhere in /etc directory on your Linux Server. Add the following lines to your Apache global configuration.

ExpiresActive On
ExpiresDefault "access plus 30 days"
ExpiresByType image/gif "access plus 30 days"
ExpiresByType image/ico "access plus 30 days"
ExpiresByType image/png "access plus 30 days"
ExpiresByType image/jpeg "access plus 30 days"
ExpiresByType image/x-icon "access plus 30 days"
ExpiresByType text/css "Access plus 30 days"
ExpiresByType text/html "Access plus 30 days"
ExpiresByType application/x-shockwave-flash "Access plus 30 days"
ExpiresByType application/x-javascript "Access plus 30 days"

What you are doing is first telling Apache to turn On mod_expires module and then adding all the file mime types you want to instruct Web Browsers, that visit your Apache Server, to cache and for how long. In this case, we are caching for 30 days. You can set this to something shorter or longer. Either way, you want to cache as many files for at least a few weeks. Doing this will result in a domino effect of many optimization benefits. First, once most of your visitors have cached your web site into their Browser’s cache, those files will no longer need to be downloaded until the Browser cache is flushed or after the 30 days expire time. Secondly, visitors will notice your web site loads faster, which means your Web Server does not have to work as hard. Finally, you will also save upwards to Gigabytes in bandwidth usage, depending on what content you have cached and how many visitors you have coming to your awesomely configured web site!

Part 2 - PHP file compression

By simply adding or setting one line in your php.ini file, you can instruct the PHP your Apache Server is using to compress all files that PHP interprets starting at 40% and in some cases up to 90% file size reduction!

zlib.output_compression = On

The configuration line above will activate the zlib PHP module and auto magically state saving you tons of bandwidth, which means faster Web page load times, which in turn leads to more capacity for your Apache Web Server to handle greater loads. It is that simple! Well maybe. PHP does not have zlib compiled in by default, so you may need to first install the zlib libraries and then the PHP zlib module. If you are using a Debian Linux Distro the following command should install it all for you.

sudo apt-get install php5-zlib

Or, if you are on an RPM based Linux Distro, like Red hat, SUSE or Fedora you can run a similar command to install the module.

yum install php5-zlib

There are a few ways to check which PHP modules you have installed. I will show you the two ways, as one may not work while the other one does. Especially, if you are not granted access to your Linux Server’s shell prompt. Upload a simple, one line, PHP file and run it in your Web Browser and it will tell you everything you need to know about your PHP install, and more. Simply put the following inside a file called phpinfo.php and upload/save it to your Web Site.

<?php phpinfo(); ?>

A faster way to check which PHP modules you have installed is to simply issue the following command while logged in and at your Linux terminal.

php -m

This will list all the modules available and loaded. What you are looking for, whether you do it using phpinfo() or php -m, is the module called, zlib.

Part 3 - APC for PHP

APC stands for Alternative PHP cache. It is yet another PHP module, which does some amazing things. As you may or may not know, PHP is an interpreted programming language. This means that each time your server runs a PHP file, it first must be compiled into opcode. Opcode is binary code similar to a C compiled program. Although, PHP is able to compile on the fly and very fast, there is still a CPU that needs to be used to compile each and every time you run any PHP file. APC not only caches all PHP opcode, so your Server will only need to compile each file once, but APC also caches the compiled opcode into memory or RAM! The result is skipping the compile cycle and immediately executing the PHP, with the added benefit of fetching directly from memory and not the hard drive. This results in faster Web page load times, less load on your Server and more CPU cycles left over to do something more important with! To activate APC, add the following to your php.ini file.

extension = "apc.so"
apc.enabled=1
apc.shm_segments=1
apc.shm_size=32
apc.num_files_hint=1024
apc.gc_ttl=3600
apc.ttl=0
apc.mmap_file_mask=/tmp/apc.XXXXXX
apc.filters=
apc.stat=1
apc.enable_cli=0

This will activate APC and allow up to 32 megabytes of memory to cache your PHP opcode. However, APC is not built into PHP by default, so like any PHP module we need to check to see if we need to install it or not. Follow the same steps as before when checking for zlib PHP module using phpinfo() or php -m and look for the characters apc. To install APC, start by following the instructions at the php website, regarding APC. You will need access to your shell terminal, if APC is not installed. If you are on a shared hosting plan, and they do not have APC loaded and available, you can tip them off to it’s benefits. The quick low down to install APC is by issuing the following commands at the command prompt.

pecl install apc

If you are running Debian Etch or Ubuntu you may also need the following package, by running the following command.

sudo apt-get install apache2-prefork-dev

Finally, once APC is installed and your php.ini file is configured, locate the file called apc.php and place it in your Web Server doc root and run it from within a Web Browser. To locate apc.php on most all Linux Distributions run the following commands.

updatedb
locate apc.php

This file PHP file will tell you if APC is running properly and also gives you a wealth of information as to the status, health and activity of your APC usage.

Step 4 - MySQL persistent connections, Indexes and Helper Tools

This final optimization tip can be the easiest of them all to complete, or the hardest. This all depends on how you coded your PHP, how many PHP scripts you have and any 3rd party PHP programs that use MySQL you have installed. All you need to do to activate persistent MySQL connections is use the mysql_pconnect() PHP function instead of mysql_connection().

Persistent MySQL connection usage is the same as before, only difference this time is, once your PHP scripts finish executing, they do not close the database connection it was using. Also, mysql_pconnect() will look for a connection that is open and not being used and uses it, resulting in faster web page load times, because establishing a new connection to any database is very CPU intensive. Now can you see what I meant by, this tip will be cake to implement or a complete rewrite and organization of your PHP code that manages your database connections.

Hopefully, you are using a PHP class and instantiating the class, so all you have to do is update your database class file. Or, you have dozens of mysql_connect() functions you need to track down and change to use mysql_pconnect() instead. Once you do implement persistent MySQL connections you may need to increase the number of max connections allowed within my.cnf or MySQL’s configuration file. This is because instead of connections being created and dropped as needed, they are left open and alive as a running daemon socket, just waiting to be used. Expect the number of daemons needed to be 3-4 times more.If you have plenty of RAM in your Server, there is little worry about how many are needed.

Indexes on your MySQL Table columns

Indexing your MySQL tables properly can make the difference between a fast query site and one that takes ages to load from the database. In fact, if I were to remove even just a few important Indexes in just one or two of some heavily used MySQL tables, would render my server useless and completely over loaded.

The only drawback, to using Indexes, is that it requires more Disk space. Luckily, this really isn’t much of an issue, considering hard drive space is cheap, these days. Also, MySQL backup tools, do not back up the Index data. It only records that an Index should be created, when you do a restore. So, it is my advise to Index just about every single table column that is used in SQL WHERE clauses, excluding the ones that are only used at the rarest of occasions, within your program code.

What you do not want to do, is Index every single table column. Instead, you need to Index just the columns that are used in your WHERE clauses of your SQL statements. Do not mind the SELECT part, it is the WHERE clause contents that will tell you which columns you need to build Indexes for.

Example:
SELECT username, pass, name FROM users WHERE username=’billybob’ AND pass=’hispassword01′

From the example above, we are asking MySQL to return any records that have matches for the table columns username and pass. MySQL will be able to look up (query) records faster, if an Index for username and pass are created

The speed is due to ordering the column data in binary and calling it Indexed. This will lower the search times the bigger your tables get. Because, instead of searching row by row by row, using a non indexed column, that is randomly ordered and scattered, as your Database grows, MySQL will instead, automatically use an Index, if one exists for the column being searched. MySQL will also properly maintain your Indexes every time a record is inserted or deleted. How awesome is that?

After you have reviewed all the SQL statements in your code and noted which columns need to be Indexed, now comes the part of actually creating the Indexes.

A few things to note about Index creating. You do not need to Index any columns you use as Primary or Unique Keys. Why? Because, MySQL automatically creates Indexes for all your primary keys for you! Secondly, depending on how large your database is, determines how much time it will take to generate each Index. On tables over 50-100 Megabytes in size, expect it to take a few minutes. For even larger Databases, this could take even longer. It is possible to create the Index on a Live Server, but it is best to do it as a planned downtime, if your database is in the Gigabytes.

This is why it is always good to set up your Indexes, before your Database gets too large. However, things change, schemes get updated and whatever.

How to create MySQL column Indexes. The easiest way is to use phpMyAdmin. Navigate to your Database and table and click on the ‘Index’ Icon for the column you want to Index. Be careful, not to create more than one Index for the same column and, like mentioned earlier, and do not create Indexes for columns that are Primary and UNIQUE Keys, as they are already Indexed by MySQL.

To deal with Indexes from the mysql command prompt, log in using mysql -u root -p, and issue the following commands:

CREATE INDEX index_name ON table_name (column_name);

To drop or erase an Index:

ALTER TABLE table_name DROP INDEX name_of_Index;

Two simple yet useful tools, mytop and mysqltuner

A highly useful, yet very simple utility for monitoring your MySQL Server, which I highly recommend you start using called mytop. This is a simple perl script that is much like the command top, but instead it only concerns itself with mysql information, in real time.

sudo apt-get install mytop

Finally, I want to recommend the following tool, which scans your MySQL server can offers up some very useful tips on how to optimize your MySQL Server. It is extremely easy to read, and in fact, tells you the exact option entires to add to your my.cnf file as well as many other things. It is a great tool because MySQL’s configuration options are many and complex. This gem of a script tells you in plain english what to do as well as the good things you have already done to optimize your MySQL Server. Get this perl script by following the commands below.

wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

To Conclude

After those 4 optimization tips presented to you, you will have turned your default install of your LAMP server into, quiet literally, a speed machine. MySQL is also somewhat of a memory hog, but you will find that after you start using persistent MySQL connections the amount of memory drain will become less, even if the number of processes are 2-3 times more! An important step is to write good MySQL and PHP code. But, that is another article, that I may add later.

Thanks for reading,
Matt k.