Project

General

Profile

Updated 12 months ago by Jessie Lee

MySQL, Apache and PHP-FPM Tuning

Apache

We want to manage apache so it performs as fast as possible both in high and low load conditions and does not melt the server in resource usage. Apache is actually the smallest part of performance so it's not worth giving great care to it.
Seperating out PHP by using php-fpm (see below) and tuning php will have the greatest impact.

Front-end First!!!!

All the tuning in the world will not solve this issue if the front end is not the first part addressed.

The 3 kinds of MPM (Multi-processing Module)

Prefork (oldest)

Worker

Event

The formula under the MaxClients section will be helpful.

http://www.jeffgeerling.com/blog/3-small-tweaks-make-apache-fly

Also possibly a good reference

https://www.linode.com/docs/websites/apache-tips-and-tricks/tuning-your-apache-server

MySQL

It might also be possible to fine tune MySQL to improve the speeds of that, but honestly everything looks mostly fine.

mysql> SHOW STATUS LIKE "key%";
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Key_blocks_not_flushed | 1013     |
| Key_blocks_unused      | 50569    |
| Key_blocks_used        | 5422     |
| Key_read_requests      | 96070105 |
| Key_reads              | 1567     |
| Key_write_requests     | 20794146 |
| Key_writes             | 409372   |
+------------------------+----------+
7 rows in set (0.10 sec)

key_cache_hit_percentage = (1 - Key_reads/Key_read_requests) * 100

(1 - 1567/96070105) * 100 = 99.998%, which the percentage of the time we're reading from the MySQL cache.

key_buffer_size=64M
max_allowed_packet=16M
query_cache_limit=4M
query_cache_size=64M
query_cache_min_res_unit=1K
innodb_buffer_pool_size=6144M
table_cache=4096
open_files_limit=65534

The innodb_buffer_pool_size could be increased to use half the RAM, which is less than the rule of thumb.

mysql> SELECT CEILING(SUM(data_length+index_length)/POWER(1024,2)) RIBPS FROM information_schema.tables WHERE engine='InnoDB';
+-------+
| RIBPS |
+-------+
|  5082 |
+-------+
1 row in set (0.50 sec)

PHP-FPM

Using PHP-FPM is a way to seperate out Apache from PHP. This helps greatly with performance as both can be regulated seperately and it removes some overhead when php isn't needed. It also allows us to tune php as well as apache and provides greater securty when running multiple sites as we can isolate users. No privileges needed

24G - 6G MySQL - 1G Debian = 17G/.25G = 66 processes

I maxed it 50 processes (original value)

pm.max_children = 100 (5)
pm.start_servers = 45 (2)
pm.min_spare_servers = 20 (1)
pm.max_spare_servers = 70 (3)

For reference

https://www.wirehive.net/how-we-help/configuring-apache-white-paper

Should perhaps implemented emergency_restart tweaks

http://www.if-not-true-then-false.com/2011/nginx-and-php-fpm-configuration-and-optimizing-tips-and-tricks#php-fpm-global-configuration-tweaks

Also available in: PDF HTML TXT

Go to top
Add picture from clipboard (Maximum size: 40 MB)