Database speed can often be an important performance bottleneck on shared servers, causing either high CPU load and RAM usage or a very slow query execution.

Poor database performance can even cause downtime to the entire server, as SQL operations from all domains build up in the queue and wait to be executed. It can directly affect the revenues of your company, by effectively limiting the number of packages that you can sell on a particular server.

Optimizing a database is a very complex process that depends on the specifics of your server environment; here are some basic tweaks to improve its performance.

Stay up to date

Modern WHM installations provide several different versions of MySQL and MariaDB. Both MySQL and MariaDB are very solid database platforms that have specific advantages, but MariaDB is officially recommended by the developers of cPanel.

Regardless of your choice, it is very important to use the latest version available in WHM. Since each release provides bug fixes and a boost in performance, upgrading will have a significant impact on the server.

WHM offers an automated upgrade tool that can be found at Home > SQL Services > MySQL/MariaDB Upgrade. The tool also allows you to switch between MySQL and MariaDB, but it’s not possible to revert to an older version after an upgrade.

Before stating the process, make sure that you create a full backup of your databases.

The script will check your system and display a number of warnings that you’ll have to accept in order to proceed, here are for example the specific warnings when upgrading from MySQL 5.7 to MariaDB 10.2:

All new database versions enable strict mode by default, which can cause problems on some applications.

In order to disable it, edit the /etc/my.cnf file from the command prompt and add this line at the end:

You will then have to restart the SQL service from the WHM interface.

Tweak Settings

The default settings provided by WHM are quite generic and tweaking them can provide a noticeable performance boost.

Open a command prompt and edit the /etc/my.cnf file, start by disabling performance schema, which is turned on by default but uses a lot of RAM for little benefit.

You just have to add this line in the [mysqld] block:

Perhaps the most important setting that impacts database performance is InooDB buffer pool size. On a dedicated SQL server, this should be equal to 70-80% of the total system RAM. If the database runs on the WHM server, set it to 30-50% of the available RAM.

You will also have to adjust the number of buffer pool instances, using one instance for every 1GB of buffer pool size.

These settings are also added under the [mysqld] block, for example:

You will have to restart the SQL service in order for the new settings to be applied.

In order to further tweak the my.cnf file, you can download and run the MySQLTuner script or use the configuration wizard provided by Percona to generate a new one. Keep in mind that altering some settings can have dangerous effects, so read the documentation carefully before changing them.

For another minor performance boost, disable MySQL disk calculations from Home > Server Configuration > Tweak Settings > SQL.

Tweaking the actual queries is also very important, so check the SQL slow log for any repeating issues and ask the website developers to optimize them.

MySQL Governor

If SQL performance continues to be a major problem in a shared hosting environment, you can also consider moving to CloudLinux in order to use the powerful features of MySQL Governor.

While in a normal WHM environment one user’s database can use a disproportionate amount of system resources, MySQL Governor allows the administrator to monitor and restrict MySQL usage for every account.

Several limits can be enforced: CPU speed, read and write operations, as well as a restriction on the number of simultaneous connections.

It also provides a monitoring mode that displays instant and historical database usage, allowing you to quickly identify any abusive accounts.

Another important feature of MySQL governor is that it includes the latest database software versions, which are not yet supported by a normal WHM installation.

While WHM only provides MySQL 5.7 and MariaDB 10.2, MySQL governor can install the new MySQL 8.0, MariaDB 10.3 and Percona 5.6.

These latest releases provide important performance boosts but their use with cPanel and older applications is still in an experimental stage, so you should run some tests in a development environment before using them in production.

Tweaking SQL settings in order to achieve perfect performance is extremely difficult and you will need assistance from a SQL expert or a very experienced system administrator.

The goal of this article is to allow you to do some basic performance tuning that can be a solid starting platform for your server.