How to perform basic MySQL / my.cnf optimisation on a shared cPanel server

269ac00f765508cb74b25d006dbf5bb2

Written by Dennis Nind

Last published at: March 19th, 2019

Before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.
  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.
  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)
  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_sizeshould be set in bytes while max_connection is dimensionless.
  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.
  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

Basic Configuration Changes

innodb_buffer_pool_size

this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM), but may be less in a shared hosting environment, such as cPanel. This is due to the fact memory usage isn't only committed to MySQL, and will also be required for other processes running on the server.

innodb_log_file_size

this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately, crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections

if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

Our shared hosting environments will generally have max_connections set to approx 500, to avoid the 'Too Many Connections' error mentioned above. 

InnoDB Configuration Changes

InnoDB has been the default storage engine since MySQL 5.5 and it is much more frequently used than any other storage engine. That’s why it should be configured carefully, to ensure performance is as optimal as possible.

innodb_file_per_table

this setting will tell InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However, it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+).

With MySQL 5.6, the default value is ON so you have nothing to do in most cases. For previous versions, you should set it to ON prior to loading data as it has an effect on newly created tables only.

innodb_flush_log_at_trx_commit

the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your primary concern is data safety, for instance on a master. However, it can have a significant overhead on systems with slow disks because of the extra fsyncs that are needed to flush each change to the redo logs. Setting it to 2 is a bit less reliable because committed transactions will be flushed to the redo logs only once a second, but that can be acceptable in some situations for a master and that is definitely a good value for a replica. 0 is even faster but you are more likely to lose some data in case of a crash: it is only a good value for a replica.

Our shared hosting environments will generally use...

innodb_flush_log_at_trx_commit  = 0

innodb_log_buffer_size

this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase innodb_log_buffer_size.

The MySQL InnoDB log buffer allows transactions to run without having to write the log to disk before the transactions commit. The size of this buffer is configured with the innodb_log_buffer_size variable.

Sensible values range from 1 MB to 8 MB. The default is 1 MB. The minimum value is 256 kB. A setting of 8M is often big enough for most database needs, but some setups with 20 to 30 MB have been seen.

A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

The innodb_log_buffer_size can be determined with the following command:

SHOW GLOBAL VARIABLES 
LIKE 'innodb_log_buffer_size';

+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| innodb_log_buffer_size | 1048576 |
+------------------------+---------+


Since MySQL 5.0 there is a status called Innodb_log_waits. This status shows the number of times that the log buffer was too small. A wait is required for it to be flushed before continuing.

SHOW GLOBAL STATUS 
LIKE 'innodb_log_waits';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+

If this value is 0 or near innodb_log_buffer_size is defined well. If it is high and continuously growing, increase it or reduce the size of your transactions.

A rough estimate of the transaction size can be found on a system by running the command below before and after the transaction. This gives you a rough feeling about the size. But keep in mind that on the production system you have concurrency. And your transaction could run the same time several times.

SHOW GLOBAL STATUS 
LIKE 'innodb_os_log_written';

+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Innodb_os_log_written | 4423680 |
+-----------------------+---------+

query_cache_size

A variable for debate alone! query_cache_size is in our opinion for a shared hosting environment best left disabled. 

The query cache is a well-known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your MySQL application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it.

skip_name_resolve

when a client connects, the server will perform hostname resolution, and when DNS is slow, establishing the connection will become slow as well. It is therefore recommended to start the server with skip-name-resolve to disable all DNS lookups. The only limitation is that the GRANT statements must then use IP addresses only, so be careful when adding this setting to an existing system.

key_buffer_size

key_buffer_size is a MyISAM variable which determines the size of the index buffers held in memory, which affects the speed of index reads. 

A good rule of thumb for servers consisting particularly of MyISAM tables is for about 25% or more of the available server memory to be dedicated to the key buffer.

A good way to determine whether to adjust the value is to compare the key_read_requests value, which is the total value of requests to read an index, and the key_reads values, the total number of requests that had to be read from disk.

The ratio of key_reads to key_read_requests should be as low as possible, 1:100 is the highest acceptable, 1:1000 is better, and 1:10 is terrible.

The effective maximum size might be lower than what is set, depending on the server's available physical RAM and the per-process limit determined by the operating system.

If you don't make use of MyISAM tables at all, you can set this to a very low value, such as 64K.

The most common way to calculate the ideal value for the  key_buffer_size is to make it 'not higher' than the MYI files. Regarding the calculations:

[root@golf ~]# find /var/lib/mysql/ -name *.MYI -ls | awk '{s = s + $7} END {print s/1048576 " MB"}'
1638.17 MB

Based on the above results, a good value to be set would be around 1.5G and can be set by doing the following...

mysql> SET GLOBAL key_buffer_size=1.5G

You can also define the key_buffer_size directly inside the my.cnf file so that the values remain following a service restart.

tmp_table_size

MySQL tmp_table_size is the maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. The on-disk table is expensive and affects your database performance. The temporary table may be created when you do many advanced GROUP BY queries, multiple tables joining without proper indexing. The default size is enough for most of the cases.  However, to find out the tuned value follow the steps: 

1. Find out the current value of tmp_table_size

mysql> show global variables like 'tmp_table_size';
+----------------+-----------+
| Variable_name  | Value     |
+----------------+-----------+
| tmp_table_size | 268435456 |
+----------------+-----------+
1 row in set (0.00 sec)

2. Find out the percentage of tables created on disk

mysql> show global status like 'created_tmp_disk_tables';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 278571 |
+-------------------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'created_tmp_tables';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| Created_tmp_tables | 1617536 |
+--------------------+---------+
1 row in set (0.00 sec)
Tmp_disk_tables=((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))
= ((278571*100/(1617536 + 278571))
= 14.69%

Created disk tmp tables ratio seems fine. If it exceeds 25% you may consider increasing tmp_table_size. If it is required set it by the command

mysql> set global tmp_table_size=268435456;
Query OK, 0 rows affected (0.00 sec)

It is to be mentioned that the tmp_table_size and max_heap_table_size should be the same value. The overall goal to optimize MySQL tmp_table_size should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok. If the percentage of tables created on disk exceeds 50% then first check your database is indexed properly especially for joining and group by columns. After tuning index, observe some days and adjust tmp_table_size.



MySQLTuner

The MySQLTuner script assesses your MySQL installation, and then outputs suggestions for increasing your server’s performance and stability.

  1. Download the MySQLTuner script:

    wget http://mysqltuner.com/mysqltuner.pl
    
  2. Change the scripts permissions to be executable:

    chmod +x mysqltuner.pl
    
  3. Run the mysqltuner.pl script. You will be prompted to enter in your MySQL administrative login and password:

    ./mysqltuner.pl
    
  4. The script will return results similar to the output below:

      
    >> MySQLTuner 1.4.0 - Major Hayden 
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering
    Please enter your MySQL administrative login: root
    Please enter your MySQL administrative password:
    [OK] Currently running supported MySQL version 5.5.41-0+wheezy1
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in InnoDB tables: 1M (Tables: 11)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 11
    
    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
    [OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
    [OK] Slow queries: 0% (0/113)
    [OK] Highest usage of available connections: 0% (1/151)
    [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
    [!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
    [OK] Thread cache hit rate: 97% (1 created / 42 connections)
    [OK] Table cache hit rate: 24% (52 open / 215 opened)
    [OK] Open file limit used: 4% (48/1K)
    [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
    [OK] InnoDB buffer pool / data size: 128.0M/1.2M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    
    MySQLTuner offers suggestions regarding how to better the database’s performance. If you are wary about updating your database on your own, following MySQLTuner’s suggestions is one of the safer ways to improve your database performance.


Making changes to your MySQL Configuration

When altering the MySQL configuration, be alert to the changes and how they affect your database. Even when following the instructions of programs such as MySQLTuner, it is best to have some understanding of the process.

The MySQL configuration file stored in the following location: /etc/mysql/my.cnf.

Note

Prior to updating your MySQL configuration, create a backup of the my.cnf file:

cp /etc/mysql/my.cnf ~/my.cnf.backup

Best practice suggests that you make small changes, one at a time, and then monitor the server after each change. You should restart MySQL after each change:

For distributions using systemd:

systemctl restart mysqld

For distributions with different init systems:

service mysql restart

When changing values in the my.cnf file, be sure that the line you are changing hasn’t been commented out with the pound (#) prefix.