How to Replace MySQL with Percona on Plesk CentOS 7

This article outlines the procedure for replacing the native MySQL®️ or MariaDB®️ service that is preinstalled on any typical Plesk Onyx 11 CentOS 7 server. The procedure outlines removal of the existing MySQL related binaries and replaces them with an adequate version of the Percona binaries. Once these Percona binaries are in place, a typical multistage MySQL Incremental version upgrade is processed to bring the existing databases and Percona binaries to the desired Percona 5.7 version.

Maintenance Expectations: Length & Downtime

Suggested Maintenance Duration:  90 minutes
Minimum Expected Downtime: ~30 minutes of service flapping

The expected MySQL service downtime for this procedure is less than 30 minutes of MySQL service availability going up and down repeatedly (flapping). This figure assumes that all goes smoothly and no unforeseen errors are encountered. The suggested maintenance length allows time for troubleshooting should error arise as well as an extra 30 minutes in the unlikely event that all changes need to be reverted and the maintenance plan aborted.

 

Maintenance Stages Overview

The plan is broken up into preparation tasks and stages. Preparation tasks should be completed prior to the start time of your maintenance window. Stages are conducted throughout the maintenance event window and walk through the entire procedure for accomplishing each important stage of the outline.

Preparation – Pre Maintenance Tasks

These two important tasks must be completed prior to the scheduled start time of your maintenance window:

  • Copy existing MySQL config file
  • Stage backup of MySQL database data

We use stage zero in this plan to help expedite the backup process of the MySQL data. Since the amount of data MySQL will hold varies wildly per server, it is a good practice to run an incremental backup solution, like rsync, prior to the maintenance event window. Rsync will backup the majority of static data while MySQL is still running and then will backup the incremental difference once MySQL is shut down for maintenance.

Copy existing MySQL config file:
1) Make a backup copy of the existing myself config file.

cp -pv /etc/my.cnf{,.pre-percona.bak}

Stage backup of MySQL database data:

2) Run the following rsync to clone the MySQL data. [IMPORTANT: trailing slash required]

rsync -vaH /var/lib/mysql{,.pre-percona}/

Note:
Very Large databases will take considerably longer to complete so plan accordingly.

3) Wait for step 1 to complete before starting Stage 1.

 

Stage 1 – Shutdown MySQL & Rerun Rsync Final Sync

The purpose of stage one is to ensure a pristine backup of the MySQL data is in place before proceeding. To accomplish this, it is necessary to completely stop MySQL services so all buffers are written and no additional changes are being written to the database. Once stopped, rsync is executed again to copy the differences between the now stopped pristine data and the target backup data.

1) Shutdown MySQL/MariaDB Service

systemctl stop mysql

2) Rerun the following rsync to finalize the cloned data. [IMPORTANT: trailing slash required]

rsync -vaH /var/lib/mysql{,.pre-percona}/

3) Wait for rsync to complete before moving on to Stage 3.

 

Stage 2 – Replace MariaDB 5.5 Binaries With Percona 5.5

NOTE:
MariaDB 5.5 is the version installed with Onyx 11 CentOS 7, so this is the starting point.

Replacing the existing binaries involves removing the necessary binary packages from the system using the RPM command line tool instead of yum. This allows the packages to be removed without touching any of the dependencies linking to other system packages. Since Percona, like MariaDB, is a drop-in replacement for MySQL, once the new binaries are in place the linked dependencies won’t know the difference and will function normally.

1) Remove MariaDB packages without dependencies:

rpm -e --nodeps mariadb mariadb-libs mariadb-server

2) Remove mariadb-devel package (do this as a separate command from above to avoid removal failure do to missing package)

rpm -e --nodeps mariadb-devel

3) Install Percona repository:

yum install https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm

4) Install Percona DB server:

yum install Percona-Server-client-55 Percona-Server-server-55 Percona-Server-shared-55

5) Execute the following commands (allows InnoDB plugin to load correctly)

mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.bak
cp -a /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
cp -a /var/lib/mysql/ib_logfile0.bak /var/lib/mysql/ib_logfile0
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
cp -a /var/lib/mysql/ib_logfile1.bak /var/lib/mysql/ib_logfile1

6) Make sure that there are no processes listening on port 3306 and kill the process if any.

netstat -tulpn | grep 3306

7) Start MySQL (now Percona 5.5)

service mysql start

8) Run mysql_upgrade script

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

9) [Optional]  Enable Percona XtraDB Cluster UDF from Percona Toolkit:

plesk db -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
plesk db -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
plesk db -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

10) Restart MySQL to complete upgrade procedure

systemctl start mysql

 

Stage 3 – Switch Plesk MySQL Package over to the Plesk MySQL Community package

Running Percona on Plesk is an unsupported configuration. For this reason, to satisfy dependency connections between the Plesk MySQL package and the new Percona binaries, it is necessary to switch the Plesk MySQL release over to using the Plesk MySQL Community release instead. The change is necessary to facilitate upgrading and downgrading Percona versions.

1) Create the necessary repo config file:

printf '[PLESK_17_8_11-dist] %s\nname=PLESK_17_5_3 dist %s\nbaseurl=http://autoinstall.plesk.com/PSA_17.8.11/dist-rpm-CentOS-7-x86_64/ %s\nenabled=1 %s\ngpgcheck=1 %s\n' > /etc/yum.repos.d/plesk178.repo

2) Install “plesk-mysql-server-community” package from the new repository:

yum install plesk-mysql-server-community

3) Disable /etc/yum.repos.d/plesk178.repo file as it’s no longer needed:

mv /etc/yum.repos.d/plesk178.repo{,.disabled}

4) Remove ”plesk-mysql-server” package without touching dependencies.

rpm -e --nodeps plesk-mysql-server

 

Percona should now be installed and working on the base 5.5 version. The next stages are identical to the typical MySQL upgrade process where binaries are upgraded, and then the schema updates are made manually using the mysql_upgrade tool.

Upgrade Percona 5.5 to 5.6

1) Stop MySQL service:

service mysql stop

2) Remove Percona 55 packages without dependencies:

rpm -e --nodeps Percona-Server-shared-55 Percona-Server-client-55 Percona-Server-server-55

3) Install Percona 56 packages

yum install Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56

4) Make sure that there are no processes listening on port 3306 and kill the process if any.

netstat -tulpn | grep 3306

5) Start the MySQL service:

service mysql start

6) Upgrade MySQL tables:

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

 

Stage 4 – Upgrade Percona 5.6 to 5.7

1) Stop MySQL service:

service mysql stop

2) Remove Percona 56 packages without dependencies:

rpm -e --nodeps Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56

3) Install Percona 57 packages

yum install Percona-Server-shared-57 Percona-Server-client-57 Percona-Server-server-57

4)  Make sure that there are no processes listening on port 3306 and kill the process if any.

netstat -tulpn | grep 3306

5) Start the MySQL service:

service mysql start

6) Upgrade MySQL tables:

mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

 

Stage 5 – Finalizing Upgrade

The system should now be running Percona 5.7 and be fully accessible by applications. At this stage, any sites or application using a database should be tested and any errors investigated.

MySQL Performance: MyISAM

MyISAM is a table-locking based engine. Table-locking prevents all other processes from writing data to the target table. This locking behavior encompasses the entire MyISAM table, no matter how minor the data change. This type of locking preserves the integrity of the data but at the cost of performance. The performance penalty for using table-locking based engines like MyISAM become more laborious as the row count in the table grows. Very large tables will see a significant delay in performance when data needs to be written to a MyISAM table.

Unlike InnoDB, there is little that can be adjusted on the server level to optimize MyISAM tables for performance. Instead, MyISAM based performance relies heavily on query construction using best practices. There are a number of session level variables that can be adjusted on the fly to boost the performance of the invoking query. However, optimization on the query level is beyond the purview of this article.

MyISAM optimization on the server level comes down to two major practices.

  1. Determining when to use MyISAM tables versus InnoDB tables
  2. MyISAM Key Cache optimization & maintenance

When to Use MyISAM vs. InnoDB Tables

Although MyISAM is not intended as a performance solution, it does have its niche roles where it outperforms the more robust InnoDB engine. Identifying these niches in your database structure can help squeeze the best performance out of your database and associated applications. To determine whether a table should be MyISAM or InnoDB, it’s best to keep the following rule of thumb in mind.

Rule of Thumb: When to Use MyISAM
MySQL®️ Recommends: MyISAM is most effective on primarily read operations and degrades in performance as concurrency rises. This is due to the impact of table locking limits on simultaneous write operations. The following situations hold true:

– MyISAM performs best on small tables.

– MyISAM performs well on medium or large tables with mostly read operations.

– MyISAM performs poorly on large and very large tables.

MyISAM Key Cache Optimization & Maintenance

The MyISAM Key Cache (also called the Key Buffer), represents the amount of memory pre-allocated for storing Index data of MyISAM tables. Optimizing the Key Cache is done by setting an adequate key_buffer_size, ensuring it is large enough to include as much of the total length of all MyISAM table Indexes as well as a minor portion for administrative overhead.

Key_buffer_size is equal to 3840M for adequate caching.

Important MySQL®️ Warning
MySQL®️ Warns: Setting too large a value here can cause the system to begin thrashing. This is due to MySQL’s reliance on the operating system’s file system cache, which is needed for data read operations. It is imperative that enough free memory is left for file system cache.

The amount of overhead the key_buffer_size requires for administrative tasks is small but grows along with the size of table indexes. Using a generic value of 5% of total index length of all MyISAM tables ensures enough room is available in the Key Cache for overhead. However, on extremely large database setups, the required overhead can be lowered further by increasing  key_cache_block_size. This is due to more rows being accessed at once, reducing the need for administrative overhead to frame those additional rows.

How to Calculate MyISAM Key Cache Size (key_buffer_size)

There are a couple of different methods used for calculating a proper key_buffer_size. These are listed and detailed below.

  1. Calculate key_buffer_size from ALL MyISAM Index Lengths
  2. Dedicated Server: Calculate key_buffer_size from total physical memory (MySQL Recommended)
  3. Shared Server: Calculate key_buffer_size from available system memory
Calculate key_buffer_size from ALL MyISAM Index Lengths

To keep the memory usage of the key_buffer_size as small as possible while having little risk of setting the value too small. This method relies on calculating the total length of the Indexes for all MyISAM tables currently on the server.

Rule of Thumb: Calculate key_buffer_size from all MyISAM Index lengths.
Set key_buffer_size to the total Index Length of all MyISAM table Indexes plus 5% for overhead. The sample script below helps to make this calculation by reading the necessary values from the INFORMATION_SCHEMA database. The percent (PCT) value used for calculating overhead can be adjusted. The highlighted 5 in the first line can be set to the desired percentage needed.

Script: Calculate key_buffer_size from Index Lengths

mysql -e "set @overhead = 5 / 100;
        select count(INDEX_LENGTH) as Indexes,
        sum(INDEX_LENGTH) as Total_Index_Length,
        floor(@overhead * 100) as PCT,
        floor(sum(INDEX_LENGTH)*@overhead) as Overhead,
        floor(sum(INDEX_LENGTH)*(1+@overhead)) as key_buffer_size
        FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';"

Example Output:

+---------+--------------------+------+----------+-----------------+
| Indexes | Total_Index_Length | PCT | Overhead | key_buffer_size |
+---------+--------------------+------+----------+-----------------+
| 744 | 5775360 | 5 | 288768 | 6064128 |
+---------+--------------------+------+----------+-----------------+

  Dedicated Server Calculate key_buffer_size from total physical memory (MySQL Recommended)

MySQLⓇ also has some recommendations for finding an appropriate key_buffer_size value. Their approach only considers total physical system memory. Though this is an acceptable method, it accounts little for the actual size of the server’s MyISAM indexes and often results in over-tuning the buffer size. Below is the rule and a script to help calculate this value for any given server.

Rule of Thumb: key_buffer_size (Dedicated Servers)
When MySQL is the only major service on a server.
MySQL®️ Recommends: Dedicated MySQL servers whose primary role is handling MySQL services can use a value as large as 25% of physical memory in order to get better performance from indexing reads and multiple writes.
The following awk script calculates this setting based on 25% of the MemTotal value available in /proc/meminfo.

Script: Calculate key_buffer_size for Dedicated Servers

awk '/MemTotal/{$3="GB";$2=sprintf("%.0f",$2/1048576); print;$1="  Mem80%:";$2=sprintf("%.0f",$2*.25);print}' /proc/meminfo

Example Output:

MemTotal: 15 GB
Mem80%: 3.75 GB

 Shared Server:  Calculate key_buffer_size from available system memory

This rule is akin to the previous MySQL recommended rule. However, this one is modified to use available system memory instead of total physical memory. This makes the rule much more suitable for shared servers, yet is still rather generic.

Rule of Thumb: key_buffer_size (Shared Servers)

When MySQL runs alongside other major services like Web, Email, etc... (e.g., cPanel, Plesk)

Due to dynamic resource usage, there is not a one-size-fits-all calculation for shared servers. It becomes prudent to calculate the available system memory used after subtracting memory requirements of all other system services.

One way to accomplish this is to use a generic calculation instead. Reserving something between 15 and 30 percent of available system memory is a reasonable approach. However, finding the exact setting requires some guesswork and testing.

The following awk script reads MemAvail from /proc/meminfo and provides a selection of percentage based calculations to choose for the server.

Script: Calculate key_buffer_size  for Shared Servers

awk '/MemAvail/{$3="G";_=$2;$2=sprintf("% 3.0f",_/1048576);print;
for (i=30;i>=15;i-=5) {$1="MemAvail_"i"%:"; $2=sprintf("%3.0f",_*(i/100)/1048576); $4=sprintf("| %.0f M",_*(i/100)/1024);print}}' /proc/meminfo

Example Output:

MemAvailable:  10 G
MemAvail_30%:   3 G | 3087 M
MemAvail_25%:   3 G | 2573 M
MemAvail_20%:   3 G | 2058 M
MemAvail_15%:   3 G | 1544 M

MyISAM Key Cache Maintenance (Performance Tuning)

As with most MySQL optimization, it is important to re-evaluate performance periodically. MySQL makes it possible to examine the performance of the Key Cache by using the SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES statements and reviewing some specific variables. This capability is instrumental in making sure the Key Cache is performing well. There are a few different metrics to consider when evaluating the Key Cache Performance. These metrics are outlined in the following sub-sections.

Calculating Key Cache Usage Rate

One useful metric is the Key Cache Usage Rate. This measures the percentage of the Key Cache that is filled, a helpful detail when determining if the key_buffer_size is large enough or if memory is being wasted on a key_buffer_size that is too large for the server’s MyISAM tablespace. Calculating the Key Cache Usage Rate requires variables from both SHOW STATUS and SHOW VARIABLES statements. Below is the list of needed variable names and the equation used to calculate Key Cache Usage Rate.

To calculate key cache usage rate use this formula.

Script: Calculate Key Cache Usage Rate

mysql -e '
  select VARIABLE_VALUE into @Key_blocks_unused FROM information_schema.global_status
    WHERE VARIABLE_NAME = "Key_blocks_unused";
  select @Key_blocks_unused as Key_blocks_unused,
    @@key_cache_block_size as key_cache_block_size,
    @@key_buffer_size as key_buffer_size\G
  select truncate((1-((@Key_blocks_unused*@@key_cache_block_size)/@@key_buffer_size))*100, 2)
   as "(1-((Key_blocks_unused × key_cache_block_size) ÷ key_buffer_size)) × 100"\G
'

Example Output:

*************************** 1. row ***************************
Key_blocks_unused: 26476
key_cache_block_size: 1024
key_buffer_size: 33554432
*************************** 1. row ***************************
(1-((Key_blocks_unused × key_cache_block_size) ÷ key_buffer_size)) × 100: 19.20

Calculating Key Cache Miss Rate

Another useful Key Cache statistics is the Key Cache Miss Rate. This shows how often items that could be in the Key Cache are being read from a physical disk. The larger the rate, the more often items are read from disk instead of the Key Cache which indicates the current key_buffer_size is too small and should be increased. While a lower rate here indicates the Key Cache is being under-utilized. This indicates the key_buffer_size is too large and consuming excess memory needlessly.

Use this formula to calculate key cache miss rate.

Script: Calculate Key Cache Miss Rate

mysql -e '
  select VARIABLE_VALUE into @Key_reads FROM information_schema.global_status
    WHERE VARIABLE_NAME = "Key_reads";
  select VARIABLE_VALUE into @Key_read_requests FROM information_schema.global_status 
    WHERE VARIABLE_NAME = "Key_read_requests";
  select @Key_reads as"Key_reads (From Disk)", 
         @Key_read_requests as "Key_read_requests (Total)", 
         truncate(@Key_reads/@Key_read_requests*100, 2) as "Key_Cache_Miss_Rate%"\G
'

Example Output:

*************************** 1. row ***************************
Key_reads (From Disk): 1275
Key_read_requests (Total): 1209967
Key_Cache_Miss_Rate%: 0.10

Calculating Key Cache Flush Rate

The Key Cache Flush Rate measures how frequently the Key Cache is written to disk. Frequent flushing can slow down MyISAM operations as writing data to a physical disk is much slower than writing to the Key Cache in memory. The closer the Key Cache Flush Rate is to 100% the more frequently MyISAM flushes to disk. The lower the rate, the less often disk writes are conducted.

Use this formula to calculate key cache flush rate.

Script: Calculate Key Cache Miss Rate

mysql -e '
  select VARIABLE_VALUE into @Key_reads FROM information_schema.global_status
    WHERE VARIABLE_NAME = "Key_reads";
  select VARIABLE_VALUE into @Key_read_requests FROM information_schema.global_status 
    WHERE VARIABLE_NAME = "Key_read_requests";
  select @Key_reads as"Key_reads (From Disk)", 
         @Key_read_requests as "Key_read_requests (Total)", 
         truncate(@Key_reads/@Key_read_requests*100, 2) as "Key_Cache_Miss_Rate%"\G
'

Example Output:

*************************** 1. row ***************************
Key_reads (From Disk): 1275
Key_read_requests (Total): 1209967
Key_Cache_Miss_Rate%: 0.10

Improving Key Cache Flush Rate

The Key Cache Flush Rate can be improved (reduced) in a couple of different ways. One method is on the query level. Making sure that UPDATE queries on MyISAM tables are changing multiple rows in one single query whenever possible. This reduces the amount of flushing needed to conclude writing changed data by addressing them in a group together with a single write operation to disk instead of individually.

The other method for reducing the Key Cache Flush Rate is intended for large MyISAM tables. These tables should take advantage of the DELAY_KEY_WRITE create table attribute. This attribute only functions on MyISAM tables and instructs the engine to delay all Key Cache writing operations until after tables are closed, instead of at the moment data changes. This provides the biggest performance increase when enabled on large or medium tables with large amounts of volatile (changing) data.

Enabling DELAY_KEY_WRITE should be done on a per table basis and should be part of the initial CREATE TABLE query for any MyISAM table expected to be large or volatile. It is also simple to modify an existing MyISAM table so that it will start using the delayed write enhancement. This is done with a simple ALTER TABLE query like so:

Important MySQLⓇ Warning
MySQL®️ Warning: When running with –external-locking, any table using DELAY_KEY_WRITE has absolutely no protection from index corruption.

Example: Enable DELAY_KEY_WRITE on an existing MyISAM table

mysql -e 'ALTER TABLE databaseName.tableName DELAY_KEY_WRITE = 1;'

Example Output:

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

One final method still relates to the DELAY_KEY_WRITE table attribute. It is possible, using the DELAY_KEY_WRITE system variable, to force DELAY_KEY_WRITE behavior on for ALL MyISAM tables. This is done by setting the DELAY_KEY_WRITE system variable within the system configuration file to ALL instead of the default ON.

The delay_key_write directive is suggested to be set to ALL instead of ON, ensuring new tables will inherit this option.

Risk versus Reward

MySQL stresses the potential risk when planning on using the  DELAY_KEY_WRITE property with MyISAM.

Important MySQL®️ Warning

MySQL®️ Warning: When using tables with DELAY_KEY_WRITE enabled, it is suggested to backup indexes using the –myisam-recover-options directive.

Also see:

Section 5.1.7, Server Command Options

Section 16.2.1, MyISAM Startup Options

Example Output:

[mysqld] myisam-recover-options=BACKUP,FORCE

As with most performance enhancement, there is usually a sacrifice to be made for speed. It is important to understand that data integrity, particularly the integrity of your table indexes are more corruptible when running with a the DELAY_KEY_WRITE feature enabled. For this reason, it is important to have alternative means of warding off corruption with other solutions like the –myisam-recover-options feature to create a backup of indexes. Another common practice with high-performance database server is using a secondary power source or battery backup, so services have time to shut down properly during a power loss scenario.

 

Other MyISAM Directives

There are several other techniques for tuning your MyISAM Cache Key and its behavior. These techniques fall outside the scope of the current article. However, more details about these techniques are available on the MySQL website here: 16.2.1 MyISAM Startup Options.

 

MySQL Performance: InnoDB Buffers & Directives

As discussed earlier in our MySQL Performance series, the InnoDB storage engine is designed to be a high-performance database for very large datasets. The row-locking technique it uses allows for many read and write requests to occur on a single table concurrently. This is a vast improvement in speed over traditional table-locking of the MyISAM engine. This part of our MySQL Performance series will focus on configuring InnoDB tables for maximum concurrency with minimal disk input/output (I/O).

Disk I/O & Temporary Tables

Disk I/O & Temporary Tables.

A simple and effective change that can provide a noticeable performance boost immediately is the practice of forcing all temporary table writes into memory instead of writing to a hard disk. Writing temp tables to RAM reduces the impact MySQL has on overall system disk I/O, which greatly improves performance. This change is done by leveraging the /dev/shm tmpfs partition in Linux. This special device gives server processes the ability to write files directly into RAM as if it were a standard disk drive.

 

InnoDB Log File Size

InnoDB Log File Size

The InnoDB log file is not your traditional log file. It’s a special binary redo log, which InnoDB uses to store redo activity for every write request handled by the server. Every write query executed gets a redo entry in the log file so that change can be recovered in the event of a crash. Because of this reason, the InnoDB log file size plays a critical role in MySQL write query performance, particularly on write-heavy databases.

Rule of Thumb: innodb_log_file_size
MySQL®️ Recommends: “The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O.” A log file size of 1GB is sufficient for most situation. This may need to be raised when database growth exceeded several dozen GB in size.
Notice
MySQL®️ Warns: “Larger log files also make crash recovery slower, although improvements to recovery performance make log file size less of a consideration than it was in earlier versions of MySQL. “

Due to the special nature of the InnoDB Redo Logs, this setting cannot be changed without first deleting the existing InnoDB log files. The following procedure for adjusting the innodb_log_file_size directive must be followed before MySQL will start again.

Changing innodb_log_file_size Procedure:

  1. Stop MySQL Serviceservice mysql stop
  1. Update innodb_log_file_size within [mysqld] header in /etc/my.cnf file.innodb_log_file_size=1G
  1. Move the existing log files out of the mysql directorymv /var/lib/mysql/ib_logfile* /backup
  1. Start MySQL Serviceservice mysql start

 

InnoDB Buffer Pool (IBP)

The InnoDB Buffer Pool plays a critical role in MySQL Performance. The IBP is a reserved portion of system memory where InnoDB table and index data are cached. This allows frequently accessed data to be returned quickly, without the need of spinning up a physical hard drive. The more InnoDB tablespace that is cached in memory, the less often MySQL will access physical disks, which manifests as faster query response times and improved overall system performance.

There are multiple directives which control IBP behavior. Tuning these to match the servers existing tablespace data, with some room to grow, will optimize the performance of any queries using InnoDB tables. Since InnoDB is the recommended engine for very large tables, optimizing the IBP gives major performance gains.

 

InnoDB Buffer Pool Size

InnoDB Buffer Pool Size

The larger the IBP, the more InnoDB functions as a high-performance in-memory database. However, setting the size too large can be detrimental to overall system performance. A balance must be struck between the IBP size and needed memory for other system services. In an ideal configuration, the IBP should be as large as possible, without causing excessive swapping (i.e., Thrashing) to occur. There are some simple rules to follow when making these determinations.

Rule of Thumb: innodb_buffer_pool_size (Dedicated Servers)

When MySQL is the only major service on a server.

The recommended configuration by MySQL is for innodb_buffer_pool_size to use as much as 80% of the system’s total physical memory.

Calculate innodb_buffer_pool_size Script: (Dedicated Server)

awk '
/MemTotal/{
$3="GB"
$2=sprintf("%.0f",$2/1048576)
print
$1="  Mem80%:"
$2=sprintf("%.0f",$2*.8)
print
}' /proc/meminfo
Example Output:

MemTotal: 15 GB
Mem80%: 12 GB

Rule of Thumb: innodb_buffer_pool_size (Shared Servers)

When MySQL runs alongside other major services like Web, Email, etc... (e.g. cPanel, Plesk)

Due to their varied resource requirements, there is no one-size-fits-all calculation for shared servers. It becomes necessary to calculate the memory requirement needs of all other critical services on the server and subtract those from total system memory to find a proper amount of available memory which can be assigned to innodb_buffer_pool_size.

A simplified method is to use a generic calculation. A conservative starting point is assigning between 30 and 80 percent of available system memory, instead of total physical memory. However, determining the exact setting may require some guesswork and testing.

The following awk script reads MemAvail from /proc/meminfo and provides a selection of percentage based calculations to choose for the server.

Calculate innodb_buffer_pool_size Script: (Shared Server)

awk '
/MemAvail/{
$3="G";_=$2
$2=sprintf("% 3.0f",_/1048576)
print
for (i=80;i>=25;i-=10) {
$1="MemAvail_"i"%:"
$2=sprintf("% 3.0f",_*(i/100)/1048576)
$4=sprintf("| %.0f M",_*(i/100)/1024)
print
}
}' /proc/meminfo

Example Output:

MemAvailable:  10 G
MemAvail_80%:   8 G | 8405 M
MemAvail_70%:   7 G | 7354 M
MemAvail_60%:   6 G | 6304 M
MemAvail_50%:   5 G | 5253 M
MemAvail_40%:   4 G | 4203 M
MemAvail_30%:   3 G | 3152 M

 

InnoDB Buffer Pool Instances

InnoDB Buffer Pool Instances

The innodb_buffer_pool_instances directive controls the number of memory pages Innodb creates. MySQL ignores this directive unless the innodb_buffer_pool_size is greater than 1G/1024M. When larger than 1G, the buffer pool is divided up into a number of equal sized memory pages specified by this directive.

Rule of Thumb: innodb_buffer_pool_instances
MySQL®️ Recommends: “For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.”

  ‣ Rule Exception: innodb_buffer_pool_instances should not exceed Total_IO_Threads

Total_IO_Threads=(innodb_read_io_threads + innodb_write_io_threads)

Preference a 1:1 Ratio for: Total_Instances:Total_IO_Threads

 

InnoDB I/O Threads

InnoDB I/O ThreadsInnoDB I/O Threads

Input/Output threads are sub-processes of MySQL which directly access the IBP memory pages. There are two types of I/O threads: innodb_read_io_threads and innodb_write_io_threads. These threads read and write, respectively, to the individual memory pages created by innodb_buffer_pool_instances.

The default value for both of these thread types is 4, making a total of 8 threads running concurrently. This pairs up with the default number of memory pages created when innodb_buffer_pool_size is larger than 1G (x8). This synergy is a key factor when improving InnoDB performance on very large database servers with large volumes of RAM and CPU cores. When increasing innodb_buffer_pool_instances past the default of 8, it becomes optimal to increase the number of threads to handle the extra memory pages concurrently. The goal being, to keep the synergy of total I/O threads equal to total memory pages:

( innodb_read_io_threads + innodb_write_io_threads ) = innodb_buffer_pool_instances

When to change InnoDB I/O Threads

Two conditions should be satisfied before adjusting I/O threads for a server.

  1. When innodb_buffer_pool_size is larger than 8 Gigabytes, this means there are more memory pages than I/O threads to handle them concurrently.
  2. When the server has more than 8 CPU cores to devote to the MySQL Service.

It is not enough to merely divide the innodb_buffer_pool_instances in half to get equal numbers of read and write threads. The goal is to increase the number of threads primarily used by the specific server’s query workload. For instance, if your server reads more data than it writes, increasing write threads would be counter-productive. The same holds true in reverse.

How to Calculate MySQL Read:Write Ratio

The global statistics kept by a MySQL server can be leveraged to determine a systems Read:Write Ratio. The following MySQL queries can be used to calculate the Total_Reads and Total_Writes of a server.

Total_Reads = Com_select

SHOW GLOBAL STATUS LIKE 'Com_select';

Total_Writes = Com_delete + Com_insert + Com_replace + Com_update

SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete');

  • If the Total_Reads is greater than Total_Writes, the server is considered Read_Heavy.
  • If the Total_Writes is greater than Total_Reads, the server is considered Write_Heavy.

The following awk script will print the necessary statistics variables from MySQL and automatically calculate a simplified Read:Write Ratio to help determine whether the system in question is Read_Heavy or Write_Heavy.

Calculate Read:Write Ratio Script:

mysql -e 'SHOW GLOBAL STATUS;'|\
awk '
$1~/Com_(delete|insert|update|replace)$/{
w += $2
printf $0 "\t + Total_Writes = " w "\n"
}
$1~/Com_(select)/{
r += $2
printf $0 "\t + Total_Reads = " r "\n"
}
END {
printf "\nRead:Write Ratio:\n\t" r ":" w " "
if (r >= w) {
R=sprintf("%.0f",r/w)
print R ":1"
} else {
W=sprintf("%.0f",w/r)
print "1:" W
}
}'

Example Output:

Com_delete    14916     + Total_Writes = 14916
Com_insert    87413     + Total_Writes = 102329
Com_replace   0         + Total_Writes = 102329
Com_select    675528    + Total_Reads = 675528
Com_update    18976     + Total_Writes = 121305
Read:Write Ratio:
675528:121305 6:1

Note:
Adjust the first line’s mysql statement as needed to connect to the appropriate server. e.g.,

mysql -h localhost -u root -p -e 'SHOW GLOBAL STATUS;' |\

Hardware CPU Core Considerations

When adjusting innodb_read_io_threads or innodb_write_io_threads, keep the total threads between the two equal to the number of CPU cores available to MySQL. This ensures maximum concurrency as each memory page can be accessed simultaneously by each individual CPU core.

Rule of Thumb: InnoDB I/O Threads (High-Performance) ‣innodb_read_io_threads + innodb_write_io_threads
The total number of InnoDB I/O Threads should not surpass the total number of CPU cores available to MySQL.

When increasing Innodb I/O Threads, increase only the read or write threads depending on whether the server is Read_Heavy or Write_Heavy.

Other Directives

There are several more advanced techniques for fine tuning the InnoDB Buffer Pool and its behavior. These are beyond the scope of this article. However, you can find more details about these techniques on the MySQL website here: 15.5.1 Buffer Pool.

 

MySQL Performance: System Configuration File & Routine Maintenance

The majority of work needed when adjusting the MySQL server is editing the applicable directives within a MySQL configuration file. There are multiple, optional configuration files that MySQL looks for when starting up. They are read in the following order:

MySQL Configuration Files:

MySQL Configuration Files:

1./etc/my.cnf

2./etc/mysql/my.cnf

3.SYSCONFDIR/my.cnf

4.$MYSQL_HOME/my.cnf

5.~/.my.cnf

Any of these files can contain MySQL server directives. For the sake of simplicity, this article assumes the default file /etc/my.cnf is being used for all examples. This is the most widely known file path in the majority of cases. However, some Linux distributions favor others than the ones listed from above. Though the file location is changed in these other distributions, the syntax and recommendations in this article series apply equally to all Linux based systems.

 

MySQL Configuration File Syntax

Each entry inside the configuration file applies to the most recent section header. Section headers are made up of a single line with the name of the header encapsulated within square brackets (e.g. [name]). When optimizing the MySQL service daemon,  changes will need to be within in the [mysqld] section header. The example below illustrates how this should look.

Example: Configuration File Syntax

[mysqld] innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2

Note
The trailing d character after mysql is required to apply settings to the MySQL service daemon. The [mysql] header without a trailing d character is incorrect and applies only to MySQL client.

 

Applying MySQL Config Changes

The vast majority of directive changes merely require a restart of the MySQL service. There are a handful of other directives which may require an additional task to be undertaken while the MySQL service is offline. These tasks will be outlined in their specific section later in the article.

Restart MySQL Service:

service mysql restart

Important:
Refrain from making several optimization changes to your existing MySQL config at one time. This is especially true when adjusting production level servers. Making small incremental changes will make it easier to identify problems with individual changes and isolate settings that may not work well for your unique setup.

Routine Maintenance

In the follow-up articles to this part of the MySQL Performance series, we will be outlining several directives and some suggested techniques to use for configuring those directives. It is imperative to understand that MySQL Optimization is an ongoing, ever-evolving configuration. As sites grow, so do data sets and workload behavior. The settings you configure today, will eventually become obsolete and probably sooner than you would like. Because of this eventuality, it is vitally important that routine maintenance is conducted on your configuration.

What are some tasks to perform routinely:
  1. Reevaluate all buffers and directives that have been modified previously. This includes the changes discussed and recommended in the entirety of this article series.
  2. Reassess MySQL Query Statistics to determine workload behaviors.
  3. Reassess tablespace data, rate of growth or other trends in data consumption.
  4. Archive old data from large or heavy trafficked tables to ease the burden of read/write requests to those tables.
  5. Reevaluate indexes and their performance. Create new, better indexes and remove old, unused indexes.

Performance Expectations

Although, the changes recommended in this article series aim to squeeze the best performance out of MySQL, the performance increase you may see is entirely subjective. Following these recommendations should help smooth the edges on any servers hitting the bottlenecks they are designed to target. However, there is no guarantee that these changes will have a positive or noticeable impact on the application level.

The biggest issues facing application performance are often on the coding level and not the server level. The underlying MySQL server configuration only carries application performance so far. Identifying problematic queries and inefficient coding practices that serialize your workload are problems that even a finely tuned MySQL server configuration just cannot correct. If you are in doubt, or your application performance seems to suffer despite all the server level optimization in the world, then you’re probably hitting code level performance problems, and we recommend contacting a qualified Database Administrator (DBA) to evaluate your application performance properly.

MySQL Performance: Converting MySQL to MariaDB

As we explored in our previous article of our MySQL Perfomance Series: MySQL vs. MariaDB there are very few downsides to using MariaDB over standard MySQL. Our high-availbility MariaDBs have proven itself to be a worthy successor with easily migitated drawbacks.  As the last article in our series we will focus on upgrading to various MySQL and MariaDB version on the following servers:

CentOS 6/7

Ubuntu 14.04/16.04

Continue reading “MySQL Performance: Converting MySQL to MariaDB”

MySQL Performance: MySQL vs. MariaDB

MariaDB and MySQL logos.

We continue our series on MySQL Optimization by focusing on the differences and similarities between MariaDB and MySQL. MariaDB is a popular alternative to using traditional MySQL for database hosting. The free market of hosting allows additional actors to compete with the long-standing database solution, MySQL. MariaDB is the most prominent alternative to MySQL and is a favorite among industry giants such as Google, Craigslist, Wikipedia, Arch Linux, RedHat, CentOS, Fedora, and cPanel. In a direct performance comparison, MariaDB leaps to the head of the pack through performance optimizations, easy of conversion and compatibility.

Why Switch to MariaDB?

MariaDB is mostly a clone of MySQL, but it also provides several improvements over running the standard MySQL instance. MariaDB aims for a drop-in replacement to MySQL, which makes the switch painless and straightforward. Combine the simplicity of switching with a long list of improvements, MariaDB brings both performance and cutting-edge features to the table. Below are some of its selling points over standard MySQL.

More Storage Engines Options: There is 12 new storage engine built into MariaDB. Among these are CONNECT, Spider, and SphinxSE. Visit their Storage Engines page for a complete list of these engines, how they function, and ways to leverage them to optimize your database.

Speed improvements: MariaDB sports many new speed improvements compared directly with standard MySQL. This improved performance makes MariaDB stand apart from the baseline performance of traditional MySQL servers. Like MySQL, MariaDB has dozens of features for speed optimization including disk access, JOIN and EXPLAIN improvements, subquery, derived tables/views, execution control, and optimizer control.

Faster Indexes/Cache: When using the MEMORY storage engine, MariaDB can complete INSERT statements up to 24% faster than traditional MySQL servers, along with CHECKSUM TABLE and MyISAM Segment Key Cache being 4x faster.

Speedier and Larger Connection Pool: MariaDB benefits from an improved pool of threads that run faster and support up to 200,000+ connections where standard MySQL falls short.

Improved Replication: MariaDB sports faster and safer replication with updates being up to 2x faster than with traditional MySQL Replication setups. Now possible, parallel replication allows the existence of Active/Active or Master/Master configurations. MariaDB replication is backward compatible with MySQL servers, so migrating your cluster to MariaDB is possible by utilizing one node at a time.

New Extensions/Features: There are several new extension and features, to name a few, the WITH, JSON and KILL statements. DECIMAL sees an increased from 30 to 38 decimals while KILL ALL queries for a specified user.

List of Features: Available MariaDB’s website is a comprehensive list of improvements and features, located here: MariaDB versus MySQL-Features.

 

What is the Downside to Using MariaDB?

The MariaDB project is entirely open-source and free, unlike MySQL which uses dual licensing to keep its Enterprise edition specific features proprietary. The MariaDB developers actively update and enhance with cutting-edge features that standard MySQL does not carry.

Missing Features: MySQL uses some proprietary code in its Enterprise Edition. MariaDB does not have access to this propriety content and is a closed source, meaning the features are only available to MySQL Enterprise users.

Mitigation: MariaDB has addressed this concern through allowing viable open-source alternative plugins for MariaDB, providing the same functionality that the MySQL Enterprise Edition offers. This allows the vast majority of standard MySQL and MySQL Enterprise Edition users the option to switch to MariaDB, taking advantage of its robust performance enhancing features.

Delayed MySQL Compatibility: MariaDB originally forked from MySQL 5.5, so this is the base starting point for the MariaDB source code. Meaning that newer features and bug fixes developed for standard MySQL after version 5.5 are not part of the existing MariaDB source code.

Mitigation: MariaDB conducts monthly merges of the standard MySQL source code to ensure both compatibility and feature/bug-fix adoption. This kind of regular code merging, allows MariaDB to maintain its allure as a drop-in replacement for practically any existing versions of standard MySQL. However, there will always be a delay causing MariaDB to lag behind MySQL’s newer features and bug patches.

MySQL Performance: How To Leverage MySQL Database Indexing

A Mysql Indexing Logo

Throughout this tutorial, we will cover some of the fundamentals of indexing. As part of the MySQL series, we will introduce capabilities of MySQL indexing and the role it plays in optimizing database performance. Liquid Web recommends consulting with a DBA before making any changes to your production level application.

What is Indexing?

Indexing is a powerful structure in MySQL which can be leveraged to get the fastest response times from common queries. MySQL queries achieve efficiency by generating a smaller table, called an index, from a specified column or set of columns. These columns, called a key, can be used to enforce uniqueness. Below is a simple visualization of an example index using two columns as a key.

+------+----------+----------+
| ROW | COLUMN_1 | COLUMN_2 |
+------+----------+----------+
| 1 | data1 | data2 |
+------+----------+----------+
| 2 | data1 | data1 |
+------+----------+----------+
| 3 | data1 | data1 |
+------+----------+----------+
| 4 | data1 | data1 |
+------+----------+----------+
| 5 | data1 | data1 |
+------+----------+----------+

Queries utilize indexes to identify and retrieve the targeted data, even if they are a combination of keys. Without an index, running that same query results in an inspection of every row for the needed data. Indexing produces a shortcut, with much faster query times on expansive tables. A textbooks analogy may provide another common way to visualize how indexes function.
This analogy compares MySQL indexing to indexing in the back of a book.

When to Enable Indexing?

Indexing is only advantageous for huge tables with regularly accessed information. For instance, to continue with our textbook analogy, it makes little sense to index a children’s storybook with only a dozen pages. It’s more efficient to simply read the book to find each occurrence of the word “turtle” than it would be to set up and maintain indexes, query for those indexes, and then review each page provided. In the computing world, those extra tasks surrounding indexing represent wasted resources which would be better purposed by not indexing.

Without indexes, when tables grow to enormous proportions, response times suffer from queries targeting those obtuse tables. Inefficient queries manifest into latency within application or website performance. We commonly identify this latency by using the MySQL slow query log feature. You can find more details about using the slow query log feature in the first article in this series: MySQL Performance: Identifying Long Queries.
Once a colossal table hits its tipping point, it reaches the potential for downtime for applications and websites. Conducting routine evaluations for growing database establishes optimal database performance and sidesteps long queries’ inherent interruptions.

MySQL Indexing Pros vs. Cons

There are benefits and downsides to using MySQL indexing, and we’ll discuss the significant pros and cons for your consideration. These aspects will guide you to decide whether indexing is an appropriate choice for your situation.

quick data transmissions and ideal for OLAP.

What Information Does One Index?

Selecting what to index is probably the most challenging part to indexing your databases. Determining what is important enough to index and what is benign enough to not index. Generally speaking, indexing works best on those columns that are the subject of the WHERE clauses in your commonly executed queries. Consider the following simplified table:

ID, TITLE, LAST_NAME, FIRST_NAME, MAIDEN_NAME, DOB, GENDER, AGE, DESCRIPTION, HISTORY, ETC...

If your queries rely on testing the WHERE clause using LAST_NAME and FIRST_NAME then indexing by these two columns would significantly increase query response times. Alternately, if your queries rely on a simple ID lookup, indexing by ID would be the better choice.

These examples are merely a rudimentary example, and there are several types of indexing structures built-in to MySQL. The following MySQL page discusses these types of indexes in greater detail, and a recommended read for anyone considering indexing: How MySQL Uses Indexes

What is a Unique Index?

Another point for consideration when evaluating which columns to serve as the key in your index is whether to use the UNIQUE constraint. Setting the UNIQUE constraint will enforce uniqueness based on the configured indexing key. As with any key, this can be a single column or a concatenation of multiple columns. The function of this constraint ensures that there are no duplicate entries in the table based on the configured key.

UNIQUE constraints increase write speeds, a taxation of implementation.

What is a Primary Key Index?

As commonly invoked as the UNIQUE constraint the PRIMARY KEY is used to optimize indexes. This constraint ensures that the designated PRIMARY KEY cannot be of a null value. As a result, a performance boost occurs when running on an InnoDB storage engine for the table in question. This boost is due to how InnoDB physically stores data, placing null valued rows in the key out of contiguous sequence with rows that have values. Enabling this constraint ensures the rows of the table are kept in contiguous order for quicker responses.

Primary Key Index is absolutely necessary for large tables.

Managing Indexes

Now we will cover some of the basics of manipulating indexes using MySQL syntax. In examples, we will include the creation, deletion, and listing of indexes.Keywords for Managing Indexes: dbName, tableName, indexName Keep in mind, these examples have placeholder entries for the specific keywords. These keywords are self-explanatory by nature for easy reading, and below is an outline of them.

Instead of tableName you can use dbName.tableName.

Listing/Showing Indexes

Tables can have multiple indexes. Managing indexes will inevitably require being able to list the existing indexes on a table. The syntax for viewing an index is below.

SHOW INDEX FROM tableName;

SHOW INDEX FROM tableName; shows all indexes.

Indexing are present on 3 different columns.

Creating Indexes

Index creation has a simple syntax. The difficulty is in determining what columns need indexing and whether enforcing uniqueness is necessary. Below we will illustrate how to create indexes with and without a PRIMARY KEY and UNIQUE constraints.

As previously mentioned, tables can have multiple indexes. Multiple indexing is useful for creating indexes attuned to the queries required by your application or website. The default settings allow for up to 16 indexes per table, increase this number but is generally more than is necessary. Indexes can be created during a table’s creation or added on to the table as additional indexes later on. We will go over both methods below.

Creating too many indexes can add latency, but if you must then increase buffers in MySQL config.

Example: Create a Table with a Standard Index

CREATE TABLE tableName (
ID int,
LName varchar(255),
FName varchar(255),
DOB varchar(255),
LOC varchar(255),
INDEX ( ID )
);
You can create an index for several columns, using ID as the index.

Example: Create a Table with Unique Index & Primary Key

CREATE TABLE tableName (
ID int,
LName varchar(255),
FName varchar(255),
DOB varchar(255),
LOC varchar(255),
PRIMARY KEY (ID),
UNIQUE INDEX ( ID )
);
You can create an Primary Key and UNIQUE constraint over several columns.

Example: Add an Index to Existing Table

CREATE INDEX indexName ON tableName (ID, LName, FName, LOC);CREATE INDEX statement creates an index and names it.

Example: Add an Index to Existing Table with Primary Key

CREATE UNIQUE INDEX indexName ON tableName (ID, LName, FName, LOC);the CREATE UNIQUE command can add an index to a table ensuring no duplicate data.

Deleting Indexes

While managing indexes, you may find it necessary to remove some. Deleting indexes is also a very simple process, see the example below:

DROP INDEX indexName ON tableName;The DROP INDEX command lets us drop indexes on particular column.

There are many ways to optimize your database for true efficiency. If you would like to learn more or convert the search engines types available in MySQL read through our MyISAM vs. InnoDB tutorial.  Or if you are need of high functioning databases check out our MySQL product page to view different options.

Apache Performance Tuning: Configuring MPM Directives

 

Our previous article in this series focused on defining and fitting MPM to match your environment.  Building off of our last tutorial we will be discussing specific details on how to adjust the previously mentioned Apache configuration directives on the various types of Liquid Web servers.  

Continue reading “Apache Performance Tuning: Configuring MPM Directives”

Apache Performance Tuning: MPM Directives

How directives behave and which directives are mainly available hinges on the loaded MPM. As discussed in our previous series, MPM is short for MultiProcess Modules, and they determine the basis for how Apache addresses multiprocessing. Using our last article on Apache MPM Modules as a springboard, we will use this section to cover the following subsections:

Each part will focus on how the directives affect performance for their respective MPM and some common considerations that should be assessed when optimizing Apache with those specific MPMs.

Note:
Be sure to review this article in its entirety as the universal directives operate in the same manner regardless of the MPM chosen.

 

General Optimization

IfModule

An important directive to learn when working with Apache servers is the IfModule conditional statement. There are two parts to the IfModule statement. A beginning, which also accepts a module name or module source file name, as well as a closing statement. When the provided module is loaded into Apache, then all directives between the beginning IfModule statement and the closing IfModule statement are also read into the Apache running configuration. Please review the provided example below for further clarification:

 

<ifModule mpm_prefork_module>
MaxSpareServers 16
</ifModule>
Timeout 60
The above example defines the MaxSpareServers directive only when loaded by mpm_prefork_module. The Timeout directive is applied every time due to it being outside of the IfModule closing statement.

 

IfModule statements are used to maintain compatibility within Apache configuration between module changes. Maintaining compatibility is done by grouping directives into IfModule statements, so they are only used when the required module is loaded. Ensuring a syntactically correct configuration file even when swapping modules.

Rule of Thumb:
Appropriately wrapping everything in an IfModule statement is a best practice standard with Apache and should be adhered to for superior compatibility in config files.

Timeout

The numerical value of seconds Apache waits for all common I/O events. Apache will abandon requests fail to complete before the provided Timeout value.

Determining the right Timeout depends on both traffic habits and hosted applications. Ideally, Timeout should be as low as possible while still allowing the vast majority of regular traffic to operate without issue. Large timeouts, those above 1 minute, open the server to SlowLoris style DOS attacks and foster a long wait in the browser when it encounters a problem. Lower timeouts allow Apache to recover from errant stuck connections quickly. It becomes necessary to strike a balance between the two extremes.

Tip:
Avoid increasing the global Timeout when addressing issues with a single script, or user, that requires a long Timeout. Problems can usually be resolved by a .htaccess file or include file to increase the Timeout directive for that specific script.

KeepAlive

KeepAliveA simple on|off toggle enables the KeepAlive protocols with supported browsers. The KeepAlive feature can provide as much as a 50% reductions in latency, significantly boosting the performance of Apache. KeepAlive accomplishes this by reusing the same initial connections a browser creates when connecting to Apache for all follow-up requests which occur within a short period.

KeepAlive is a powerful feature and in general, should be enabled in most situations. It works great for reducing some of the CPU and Network overhead with modern element heavy websites. For example, an easy way to visualize KeepAlive is with the “hold the door” phrase. Imagine a queue of people entering a building through a single doorway. Each person is required to open the door, walk through it, then close the door before the next person does the same process. Mostly, that’s how Apache works without KeepAlive. When enabled, the door stays open until all the people in line are through the door before it closes again.

Two additional related directives also govern KeepAlive. MaxKeepAliveRequests and KeepAliveTimeout. Discussed in the next section, each one plays a vital role in fine-tuning of the KeepAlive directive.

 

MaxKeepAliveRequests

Sets a limit on the number of requests an individual KeepAlive connection is permitted to handle. Once reached, Apache forces the connection to terminate, and creates a new one for any additional requests.

Determining an ideal setting here is open to interpretation. Generally, you want this value to be at least as high as the largest count of elements (HTML, Text, CSS, Images, Etc..) served by the most heavily trafficked pages on the server.

Rule-of-Thumb:
MaxKeepAliveRequestsSet MaxKeepAliveRequests to double that of the largest count of elements on common pages. (Services like webpagetest.org or gtmetrix.com can count elements on a page).

KeepAliveTimeout

This directive is measured in seconds and will remain idle waiting for additional requests from its initiator. Since these types of connections are only accessible to their initiator, we want to keep KeepAliveTimeout very low. A low value prevents too many KeepAlive connections from locking out new visitors due to connection priority.

Tip:
KeepAliveTimoutA large MaxKeepAliveRequests directive with a very low KeepAliveTimeout allows active visitors to reuse connections while also quickly recovering threads from idle visitors.
Configuration: Set MaxKeepAliveRequests to 500+, Set KeepAliveTimeout to 2
Requirements: Works best on MPM Event.

MPM Event/Worker Optimization

This section details the use and performance considerations that are essential when running Worker based MPMs, including both MPM Event and MPM Worker. These MPMs are considered multi-threaded solutions and some directives behave differently based on the loaded MPM. The information provided in this section is only a portion about Worker based MPMs.

Note:
In Worker based MPMs: ServerLimit, ThreadsPerChild, and MaxRequestWorkers are intrinsically linked with each other. It is essential to understand the role of each one and how changing one affects the others. The following directives govern the fine-tuning of the thread handling capabilities of Apache web servers.
MPM Worker and MPM Event

The two modules, MPM Event, and MPM Worker for most intents and purposes operate identically. The difference is apparent in the way each handles KeepAlive requests. The MPM Worker locks threads for the duration of the KeepAlive process and directly affects the number of available threads able to handle new requests. The MPM Event uses a Listener thread for each child. These Listener threads handle standard requests, and KeepAlive requests alike meaning thread locking will not reduce the capacity of the server. Without thread locking, MPM Event is the superior choice but only in Apache 2.4. Before Apache 2.4 the MPM Event was unstable and prone to problems.

ServerLimit

ServerLimit represents the upper limit of children Apache is allowed. The practical usage for ServerLimit is creating a hard ceiling in Apache to protect against input errors with MaxRequestWorkers. The cap prevents spawning vastly more children than a system can handle, resulting in downtime, revenue loss, reputation loss or even data loss.ServerLimit

ServerLimit ties in directly with the thrashing point discussed earlier in this article. The thrashing point is the maximum number of children Apache can run before memory usage tips the scale into perpetual swap. Match the ServerLimit to the calculated thrashing point to safeguard the server.

 

ThreadsPerChild

Used to define the limit of threads that each Apache child can manage. Every thread running can handle a single request. The default of 25 works well for most cases and is a fair balance between children and threads.ThreadsPerChild

There is an upper limit on this directive as well, and the limit is controlled by the ThreadLimit directive, which defaults to 64 threads. The adjustments to increase ThreadsPerChild past 64 threads also need to be made to ThreadLimit.

Increasing this value allows each child to handle more requests keeping memory consumption down while allowing a larger MaxRequestWorkers directive. A key benefit of running more threads within each child is shared memory cache access. Threads from one child cannot access caches from another child. Boosting the number of threads per child squeezes out more performance due to this sharing of cache data. The major downside for increased threads per child occurs during child recycling. The capacity of the server is diminished by the number of threads configured for each child when that child process is eventually recycled (graceful restart).

MPM Event/Worker

Inversely the opposite reaction is achieved by lowering ThreadsPerChild. Fewer threads per child require more children to run an equal amount of MaxRequestWorkers. Since children are full copies of Apache, this increases Apache’s overall memory footprint but reduces the impact when recycling children. Fewer threads mean fewer potential “stuck” threads during the recycle procedure, keeping the higher capacity of requests available overall children. Having fewer threads per child provides increased shared memory isolation. For instance, dropping ThreadsPerChild to 1 gives the same request isolation of MPM Prefork but also inherits its massive performance tax as well, requiring one child per one request.

Tip:
When setting ThreadsPerChild always consider the server environment and hardware.

  • A memory-heavy shared server hosting numerous independent accounts might opt for a lower ThreadsPerChild, reducing the potential impact of one user affecting another.
  • A dedicated Apache server in a high capacity load balanced configuration can choose to increase ThreadsPerChild significantly for a better overall performance of each thread.

ThreadLimit

Used to set the maximum value of ThreadsPerChild. This directive is a hard ceiling for ThreadsPerChild. It helps protect against typographical errors with the ThreadLimit
ThreadsPerChild directive which could quickly spin a server out of control if too many threads are allowed due to an input error. This setting need to be adjusted in some high-end servers when the system needs more than the default of 64 threads per child.

MaxRequestWorkers / MaxClients

The directive sets the limit for active worker threads across all running children and acts as a soft ceiling with ServerLimit taking control as the hard limit. When the number of total running threads has reached or exceeded MaxRequestWorkers, Apache no longer spawns new children.MaxRequestWorkers/MaxClients

Determining the MaxRequestWorkers is a critical part of server optimization. An optimal setting is based on several changing variables. This means its configuration needs to be reevaluated and tailored periodically over time, changed by watching traffic habits and system resource usage. The Apache status Scoreboard is an effective tool for analysis of Apache performance.

 

It is typical of Worker based MPM systems to run an isolated third-party PHP handler like Mod_fcgid, PHP-FPM, and mod_lsapi. These modules are responsibleMPM Event/Worker2

for processing PHP code outside of Apache and frees up Apache to handle all other non-PHP requests such as HTML, TEXT, CSS, Images, etc… These requests are far less taxing on server resources which allows Apache to handle larger volumes of requests, such as those beyond 400 MaxRequestWorkers.

MinSpareThreads

The least number of Threads that should remain open, waiting for new requests. MinSpareThreads is a multiple of ThreadsPerChild and cannot exceed MaxSpareThreads, though it can match it.

Rule-of-Thumb:
Set MinSpareThreads to equal 50% of MaxRequestWorkers.

Spare threads are idle workers threads. These threads are merely waiting for new incoming requests and are governed by the Apache child process that spawned them. If there are less available threads than MinSpareThreads, The Apache parent will generate a new child with another ThreadsPerChild worth of threads.

MinSpareThreads

MaxSpareThreads

This directive governs the total number of idle threads allowed on the server across all children. Any threads above this limit direct their parent to shut down to reduce memory consumption during off-peak hours.MaxSpareThreads

Having a limit to the number of idle open threads is excellent for smaller servers with hardware constraints. However, it mostly unneeded on today’s modernizing hardware.

Tip:
Configuring Apache as an open throttle is a high-performance configuration for servers with significant RAM and multiple CPU cores. When running the open throttle configuration, all available threads become available at all time. Apache’s memory usage will stay near its peak at all times, a side effect due to running all the configured children into memory preemptively. This configuration will produce the best possible response times from Apache by maintaining persistent open connections ready to do work and removing the overhead of spawning new processes in response to traffic surges.

Configuration: Match both MinSpareThreads and MaxSpareThreads to MaxRequestWorkers.

Requirements: Make sure there is enough server RAM to run all MaxRequestWorkers at once.

StartServers

This directive governs the initial amount of children the Apache Parent process spawns when the Apache service is started or restarted. This is commonly left unchanged since Apache continuously checks the current running children in conjunction with ThreadsPerChild and compare it to MinSpareThreads to determine if more children get forked. This process is repeated perpetually, with a doubling of new children on each iteration, until MinSpareThreads is satisfied.

Rule-of-Thumb:
StartServerManually calculating StartServers is done by dividing MaxRequestWorkers by ThreadsPerChild, rounding down to the nearest whole number. This process forces all children to be created without delay at startup and begins handling requests immediately. This aspect is especially useful in modern Apache servers which require periodic restarts to load in directive changes.

MaxConnectionsPerChild / MaxRequestsPerChild

The number of requests a single Apache child process can handle equals a cumulative total on the child server across all threads it controls. Each request handled by a thread counts toward this limit to its parent. Once the child server has reached its limit, the child is then recycled.
This directive is a stop-gap for accidental memory leaks. Some code executed through Apache threads may contain memory leaks. Leaked memory are portions of memory that subprocess failed to release properly, so they are inaccessible to any outside processes. The longer a leaking program is left running, the more memory it will leak. Setting a MaxConnectionsPerChild limit is a specific method for assuring Apache is periodically recycling programs to reduce the impact of leaked memory on the system. When using external code handlers like Mod_fcgid, PHP-FPM or mod_lsapi, it becomes necessary to set MaxConnectionsPerChild to 0 (unlimited), doing so prevents periodic error pages caused by Apache terminating threads prematurely.

Rule-of-Thumb:
MaxConnectionsPerChild/MaxRequestsPerChild If the server encounters a memory leak never set the MaxConnectionsPerChild / MaxRequestsPerChild too low, instead start with 10,000 and reduce it incrementally.

 

MPM Prefork Optimization

This MPM Prefork section details the use and performance considerations for various directives when running this module. This MPM is a non-threaded multi-processor designed for compatibility. It consists of a single Apache parent process, which is used to govern all new Apache processes also known as children. The following directives show how Apache is capable of performance tuning when using MPM Prefork. Unlike Worker based MPMs, optimizing MPM Prefork is generally simple and straightforward. There is a 1:1 ratio of Apache processes to incoming requests. However, MPM Prefork does not scale well with hardware and the more traffic it encounters, the more hardware it will need to keep up with the pace. It should be noted that some directives behave differently based on which MPM is loaded. The information provided in this section is only the portion about MPM Prefork.

MaxRequestWorkers / MaxClients

Used to control the upper limit of children that the Apache parent server is allowed to have in memory at one time. These children (also called workers) handle requests on a 1:1 ratio. This translates into the maximum number of simultaneous requests the server can handle.

MaxRequestWorkers / MaxClientsIf this directive is too low, Apache under-utilizes the available hardware which translates to wasted money and long delays in page load times during peak hours. Alternatively, if this directive is too high, Apache outpaces the underlying hardware sending the system into thrashing (link to thrashing article) scenario which can lead to server crashes and potential data loss.

 

MinSpareServers

This directive defines a minimum number of spare children the Apache parent process can maintain in its memory. An additional server is a preforked idle Apache child that is ready to respond to a new incoming request. Having idle children waiting for new requests is essential for providing the fastest server response times. When the total idle children on the server drop below this value, a new child is preforked at the rate of one per second until this directive is satisfied. The “one per second” rule is in place to prevent surges of the creation process that overload the server, however, this failsafe comes at a cost. The one per second spawn rate is particularly slow when it comes to handling page requests. So it’s highly beneficial to make sure enough children are preforked and ready to handle incoming requests.

 

Rule of Thumb:
MinSpareServers Default settingNever set this to zero. Setting this to 25% of MaxRequestWorkers ensures plenty resources are ready and waiting for requests.

MaxSpareServers

MasSpareServers controls the maximum number of idle Apache child servers running at one time. An idle child is one which is not currently handling a request but waiting for a new request. When there are more than MaxSpareServers idle children, Apache kills off the excess.

If the MaxSpareServers value is less than MinSpareServers, Apache will automatically adjust MaxSpareServers to equal MinSpareServers plus one.

Like with MinSpareServers, this value should always be altered with available server resources in mind.

Rule of Thumb:
MaxSpareServersSet this to double the value of MinSpareServers.
Tip:
Configuring Apache as an open throttle is a high-performance configuration for servers with significant RAM and multiple CPU cores. When running the open throttle configuration, all available Apache children become available at all times. As a side effect of running open throttle, the Apache memory usage will stay near its peak at all times, due to running all the configured children into memory preemptively. This configuration will produce the best possible response times by maintaining persistent open connections. Furthermore, in response to traffic surges, it removes the overhead that comes from spawning new processes.
Configuration: Match both MinSpareServers and MaxSpareServers to MaxRequestWorkers.
Requirements: Make sure there is enough server RAM to run all MaxRequestWorkers at once.

StartServers

Created at startup, are the initial amount of Apache child servers.

This seldom changed directive only impacts Apache startup and restart processes. Generally not altered because Apache uses internal logic to work out how many child servers should be running.

Many modern servers periodically restart Apache to address configuration changes, rotate log files or other internal processes. When this occurs during a high load traffic surge, every bit of downtime matters. You can manually set the StartServers directive to mirror that of your MinSpareServers to shave off time from the Apache startup.

Rule of Thumb:
StartServersThe StartServers directive should mirror that of MinSpareServers.

 

ServerLimit

The ServerLimit directive represents the upper limit of MaxRequestWorkers. This setting is generally used as a safeguard or ceiling against input errors when modifying MaxRequestWorkers.ServerLimit Default Setting

It becomes necessary to adjusted ServerLimit when the server is expected to handle more than the default of 256 requests simultaneously.

ServerLimit ties in directly with the thrashing point. The thrashing point is the maximum number of children Apache can run before memory usage tips the scale into perpetual swap. Match the ServerLimit to the calculated thrashing point to safeguard the server.

Note:
Increasing ServerLimit is not recommended with MPM Prefork. Running more than 256 simultaneous requests is hardware intensive when using the MPM Prefork module.

ThreadsPerChild Default Settings

MaxConnectionsPerChild / MaxRequestsPerChild

This directive equals the number of requests a single Apache child server can handle.

This directive is a stop-gap for accidental memory leaks. Code executed through Apache may contain faults which leak memory. These leaks add up over time making less and less of the shared memory pool of the child usable. The way to recover from leaked memory is to recycle the affected Apache child process. Setting a MaxConnectionsPerChild limit will protect from this type of memory leakage.

Note:
MaxConnectionsPerChild/MaxRequestsPerChild Rule-of-Thumb: Never set this too low. If the server encounters memory leak issues start with 10,000 and reduce incrementally.

 

Apache Performance Tuning: MPM Modules

The keystone for understanding Apache server performance is by far the MultiProcessing Modules (MPMs). These modules determine the basis for how Apache addresses multiprocessing. Multiprocessing means running multiple operations simultaneously in a system with multiple central processing units (CPU Cores).

There are many MPMs to choose; however, this article focuses on the most commonly used modules found in Liquid Web Linux based servers. These modules are:

The self-regulating MPM Prefork derives its namesake from how it forks or copies itself into new identical processes preemptively to wait for incoming requests. A non-threaded process-based approach at multiprocessing, MPM Prefork runs Apache in a single master parent server process. This parent is responsible for managing any additional child servers that make up its serverpool. While using MPM Prefork, each child server handles only a single request. This focus provides complete isolation from other requests dealt with on the server. MPM Prefork is typically used for compatibility when non-threaded libraries/software, like mod_php (DSO), are required. From an optimization standpoint, MPM Prefork can be sorely lacking when compared to multi-threaded solutions, requiring vastly more resources to reach similar traffic levels as a threaded MPM. It is resource intensive due to its need to spawn full copies of Apache for every request.

MPM Prefork

Rule-of-Thumb:
Avoid using MPM Prefork whenever possible. It’s inability to scale well with increased traffic will quickly outpace the available hardware on most system configurations.

 

A hybrid pre-forking, multithreaded, multiprocessing web server. In the same fashion as MPM Prefork, MPM Worker uses the same approach with a single master parent process governing all children within its serverpool. However, unlike MPM Prefork, these children are multi-threaded processes that can handle dozens of threads (requests) simultaneously. MPM Worker has set the foundation for multithreaded multiprocessing in Apache servers which became stable in Apache 2.2. The threaded configuration allows Apache to service hundreds of requests with ease while retaining only a dozen or so child processes in memory. The MPM Worker make for both a high capacity and low resource solution for web service.

MPM Worker

Note
The KeepAliveTimeOut directive currently defines the amount of time Apache will wait for requests. When utilizing KeepAlive with MPM Worker use the smallest KeepAliveTimeout as possible (1 second preferably).

Based off the MPM Worker source code, MPM Event shares configuration directives with MPM Worker. It works nearly identical to MPM Worker except when it comes to handling KeepAlive requests. MPM Event uses a dedicated Listener thread in each child process. This Listening thread is responsible for directing incoming requests to an available worker thread. The Listening thread solves the issue encountered by MPM Worker which locks entire threads into waiting for the KeepAliveTimeout. The Listener approach of MPM Event ensures worker threads are not “stuck” waiting for KeepAliveTimeout to expire. This method keeps the maximum amount of worker threads handling as many requests as possible.


MPM EventMP

Tip:
MPM Event is stable in Apache 2.4, older versions can use MPM Worker as an alternative.

There is an assortment of additional MPMs available. These are typically part of Apache’s integration into Operating Systems other than Unix based systems. These have specific MPMs which are requirements or utilizing Apache on their respective system types. These types of MPMs are beyond the purview of this article. You can find more information on specific MPM in the MPM Defaults section of the official Apache Documentation.

MPM EventMP

Tip:
We recommend staying away from experimental and unstable MPMs. The unreliable nature of these types of software renders them unsupportable.

 

When considering optimization, it is essential to understand there is no such thing as a one-size-fits-all Apache configuration. Correctly choosing an MPM requires analysis of many moving variables like traffic, site code, server type, PHP Handler and available hardware. Every server is unique making the best MPM an entirely subjective choice.

If your application code does not support multi-threading, then your choice will inevitably be MPM Prefork purely on a compatibility basis. MPM Prefork includes software modules like mod_php (DSO). MPM Worker without KeepAlive performs very well if your application is a high-performance load balanced API system. The scalability and flexibility of MPM Event is a solid choice for hosting multiple small to medium sites in a shared hosting configuration.

Most simple servers setups operate well under the self-governing default configuration of MPM Event, making it an ideal starting point for optimization tuning. Once chosen, an MPM can then move onto Configuration Directives to review which settings pertain to server performance and optimization. Or check out our previous article in this series, Apache Performance Tuning: Swap Memory.