Handling any amount of web traffic can be hard if you don’t configure the various aspects of your server properly, and especially so for high traffic websites. LAMP stack (Linux, Apache, MySQL, PHP) is one of the most common server setups today, and many sites are running on it, for a good reason. It works well, it’s reliable, and is fairly easy to set up and configure. Today we’ll focus on tweaking MySQL / MariaDB and PHP so your server can handle as much traffic in the least amount of time.

Hardware / requirement considerations

Before we dive into the actual configuration of those two services, let’s first consider your needs, and what role does hardware play in all of this.

Data – the actual content that gets displayed on your site, along with all the backend magic that happens behind the scenes, sits on your hard drive or SSD. A processor is, of course, the brain of the server, and does all the necessary computations needed to run a site. Lastly, there’s RAM, that keeps the most frequently used data at hand for the processor to use.

If you run a high traffic site, chances are great you have lots of data that needs to be accessed often in a short amount of time. So, in order of speed, and importance, you should consider the following:

• RAM – the more, the better. Every PHP script that runs needs some memory, how much will depend on the specific script. More importantly, database data gets cached in RAM (especially so for InnoDB tables), so as RAM is the fastest of the three, you’ll want to have as much of that data cached
• Processor – this one is pretty self explanatory. Generally, the more cores it has (and the higher the working frequency), the faster it will process information.
• Disk – the slowest part of the trio, thus usually the bottleneck. You should have a disk large enough to store all of your data (obviously), but you might want to invest in an SSD too as they are many times faster than standard HDDs. If you have both, it might be a good idea to have your SQL data on the HDD as the point is to have it all cached in RAM to minimize reading from the actual disk, which will free up the processor to do other things as well.

Now that we got that out of the way, let’s see some of the most ‘important’ configuration variables you should tweak.

MySQL / MariaDB

The two most common storage engines used are InnoDB and MyISAM. In newer versions (MySQL 5.7, MariaDB 10+) InnoDB is the default, so we’ll focus on it a bit more.

You can set up various variables either in real-time, or on run-time. We’ll do the latter here, and for that we’ll have to edit MySQL’s configuration file, which is most commonly /etc/my.cnf , and we’ll set up these variables under the [mysqld]  section.

InnoDB

As our goal here is to keep as much of data + indexes cached in RAM, the most important variable we’d need to set up is innodb_buffer_pool_size . The buffer pool is the name for the storage area that keeps the data in memory.

First we’ll want to check exactly how much data and indexes we have, and we can use the following to do that (this will show you MyISAM usage too):

Now that we have that information, we can act accordingly. For example, if your InnoDB usage is 5GB, and you have 8GB of RAM, it wouldn’t be wise to set the buffer pool size to 5GB as that would leave the rest of the processes short on RAM, especially if you need to have a high memory limit for PHP.

Be wary of setting this variable too high, as that could cause the server to start paging, or using swap memory (which is basically a part of the HDD / SDD that serves as RAM but is much slower).

If we follow the 8GB RAM example, and your site relies heavily on the database, it would be prudent to get more RAM, so you can set the buffer pool size to match the data + index size. If, on the other hand, it doesn’t rely on it that heavily, setting the buffer pool size to 2 or 3GB should work just fine. Keep in mind the actual usage will be about 10% higher than what you specify.

On MySQL 5.5 and higher, and on 64-bit systems, you can divide this buffer pool into multiple parts, to minimize contention for the memory structures among concurrent operations. If the buffer pool size is larger than 1GB, you might encounter bottlenecks from multiple threads trying to access the buffer pool at once. This is done by setting innodb_buffer_pool_instances  variable.

Ideally, each buffer pool instance should be at least 1GB in size, meaning if your innodb_buffer_pool_size  is 5G, innodb_buffer_pool_instances  should be set to 5.

Next thing to consider is how much tables MySQL actually uses, and needs to open. The variables that define the limits on the maximum number of open tables at any point in time are table_open_cache  and max_connections . Of these two, the former is more important than the latter. If these limit are set too low, MySQL can spend an inordinate amount of time closing very active tables in order to remain under the limit.

We can check the number of tables we have with the following command:

You will usually want to set table_open_cache  to a value of about 30% of that number, depending on how many tables are used at each point in time. Another variable, open_files_limit , should usually be set to at least three times the number for table_open_cache .

An example of my.cnf:

MyISAM

Basically, the only variable we’d need to tweak here is key_buffer_size . This variable determines the size of the buffer used for index blocks.

The key buffer only stores keys from MyISAM tables, so you don’t want to make this large enough to fit all data, and rather leave memory free for the OS file cache to handle the non-key columns. A good rule of thumb would be to keep this below 30% of total RAM. If you don’t use much of MyISAM tables, feel free to leave this at default (8MB), or even better, set it to 64MB. MySQL’s internal tables still use MyISAM so you don’t want to have it set to 0!

Monitoring the progress

Obviously, there are many more variables that can be tweaked to fine-tune your MySQL, but the ones we covered make the most difference, so if there’s no need, leave the rest at their defaults.

Now that we’ve set up our my.cnf, we should run MySQL for some time, and then check the status with:

The output of this will include the values for these counters:

The “Opens” value is the number of tables that have been opened since the current MySQL process started. The “Open tables” value is the number open right now. Ideally, you want them to be as close to each other as possible. Under normal conditions, you should be able to reach a situation where Opens is increasing but only slowly, meaning the server doesn’t need to re-open tables often. If Opens is much higher than Open Tables, you should increase table_open_cache  further.

PHP

For PHP, the most important configuration options we should set up are:

Resource Limits options:

• max_execution_time  – Maximum execution time of each script, in seconds
• max_input_time  – Maximum amount of time each script may spend parsing request data
• memory_limit  – Maximum amount of memory a script may consume

Data Handling options:

• post_max_size – Maximum size of POST data that PHP will accept

File Uploads options:

• file_uploads  – Whether to allow HTTP file uploads
• upload_max_filesize  – Maximum allowed size for uploaded files
• max_file_uploads  – Maximum number of files that can be uploaded via a single request

and of course, Module Settings pertaining to MySQL (this will depend on what MySQL connector you use).

We won’t get into the specific values of these options as you should have at least a general idea on what your requirements are.

The important thing to keep in mind here is not to set memory_limit  too high, as if you do, you risk having your server run Out Of Memory.You should take into consideration your buffer pool sizes from MySQL when setting up the memory limit too.

One last thing to consider in regards to PHP is what handler you use. There are more than a few of those, each with their own benefits and drawbacks, but exploring them is beyond the scope of this article.

Conclusion

Running a website can be difficult business, and if improperly configured, your server can have many bottlenecks that don’t allow your site to run optimally. Hopefully, this tutorial will help you in getting closer to those fast load times we all want, while keeping the server stable and the site up without issues. And once again, always keep hardware requirements in mind, as no amount of tweaking can replace the need for hardware resources!