Tune MariaDB on a cPanel server for shared hosting

cPanel installs MariaDB with upstream defaults. Those defaults assume a small machine and a single application — a 128 MB buffer pool, 151 connections, a table cache sized for a few hundred tables. Put 400 cPanel accounts and 3,000 WordPress databases on top of that and the server starts failing in ways that look like slow disks or a bad neighbour, but are really just an undersized cache.

This is the tuning pass for a shared or reseller cPanel box that also runs Apache or LiteSpeed on the same hardware. It is not a tuning guide for a dedicated database node — if MySQL has the machine to itself, the numbers here are too conservative and you want a separate MySQL profile in WHM instead.

Measure first

Never tune a server that has just been restarted. MariaDB's status counters are cumulative since startup, so anything under 48 hours of uptime gives you noise.

mysqladmin status
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Max_used_connections','Opened_tables','Open_files',
   'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests')"

Three numbers tell you almost everything:

• Max_used_connections near your max_connections — you are hitting the ceiling and users are seeing "Too many connections" instead of pages.
• Opened_tables climbing by thousands per day — table_open_cache is too small and the server is reopening the same files constantly.
• Innodb_buffer_pool_reads divided by Innodb_buffer_pool_read_requests above about 1% — your working set does not fit in RAM and InnoDB is going to disk.

MySQLTuner is worth running as a second opinion, but treat it as a prompt, not a prescription — it does not know that Apache and PHP-FPM need the RAM it wants to hand to InnoDB.

curl -L https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -o /root/mysqltuner.pl
perl /root/mysqltuner.pl --nocolor

Where the config lives

Edit /etc/my.cnf. cPanel does not rewrite that file during upcp, so your changes survive updates. Restart through cPanel's script rather than systemctl so chkservd sees a clean state:

/scripts/restartsrv_mysql
tail -50 /var/lib/mysql/$(hostname -s).err

On CloudLinux with MySQL Governor, /etc/my.cnf still applies — but change database versions only through Governor's tooling, never the WHM upgrade page, and re-check the file after a Governor upgrade. Version changes stage copies of the config aside, and a half-finished upgrade can leave mysqld running without your includes. The MariaDB upgrade path on cPanel covers that sequence properly.

The five settings that matter

innodb_buffer_pool_size

The single highest-impact value. On a box that also serves HTTP, budget 25–35% of total RAM, not the 70–80% that generic tuning guides recommend. Apache or LiteSpeed workers, PHP-FPM pools, and Imunify360's scanner all need memory, and MariaDB is the process the OOM killer picks when it runs out.

max_connections and per-connection buffers

Raise max_connections only as far as your RAM allows, because every connection allocates its own sort, join, and read buffers on top of the buffer pool. This is where copied-from-a- gist configs kill servers: sort_buffer_size = 32M with 300 connections is a 9.6 GB liability that never shows up until a traffic spike. Leave the per-connection buffers at their defaults — 2 MB sort, 256 KB join — and let the buffer pool do the work.

If you are hitting the connection ceiling on a shared box, the cause is usually one account, not global demand. Cap it with MySQL Governor in abusers mode rather than raising the limit for everyone.

table_open_cache and table_definition_cache

The setting nobody sets, and the one that hurts most on shared hosting. Each open table costs a file descriptor and a cache slot; 3,000 WordPress sites is roughly 36,000 tables. With the default cache, MariaDB evicts and reopens tables continuously, which shows up as high system CPU and mysterious latency. Size both at roughly your total table count, capped by what your file descriptor limit allows.

mysql -e "SELECT COUNT(*) FROM information_schema.tables"

open_files_limit

Raising the table cache without raising file descriptors just moves the error. Set the systemd limit and the MariaDB variable together:

mkdir -p /etc/systemd/system/mariadb.service.d
cat > /etc/systemd/system/mariadb.service.d/limits.conf <<'EOF'
[Service]
LimitNOFILE=100000
EOF
systemctl daemon-reload
/scripts/restartsrv_mysql

innodb_flush_log_at_trx_commit

The default of 1 flushes to disk on every commit — fully durable, and the reason write-heavy shared servers stall on spinning disks or throttled network storage. Setting 2 flushes once per second instead.

A starting config by RAM

For a cPanel server running web and database on the same machine. Adjust after two days of counters, not before.

Written into /etc/my.cnf for a 16 GB server:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_connections = 250
table_open_cache = 8000
table_definition_cache = 8000
tmp_table_size = 128M
max_heap_table_size = 128M
open_files_limit = 60000
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2

On MariaDB 10.5 and later you can change innodb_log_file_size and simply restart — the server resizes the redo log itself. There is no manual log file removal step any more.

What not to touch

• The query cache. It is off by default in current MariaDB and should stay off. Under concurrency its global mutex costs more than it saves, and it is gone entirely in MySQL 8.
• skip-name-resolve. It genuinely speeds up connection setup, but it breaks every grant written against a hostname — including Additional Access Hosts that your customers added as names rather than IPs in cPanel's Remote MySQL. Audit SELECT DISTINCT host FROM mysql.user before enabling it.
• log_queries_not_using_indexes. On shared hosting it writes gigabytes a day. Use the slow query log with long_query_time = 2 instead.
• innodb_thread_concurrency. Leave it at 0. Manual values were useful fifteen years ago.

After the change

Watch for two days, then re-read the same counters. If Innodb_buffer_pool_reads is still climbing fast and you have no RAM left to give, the problem is queries, not configuration — pull the slow log and find the offender:

mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log

On CloudLinux, dbtop maps that back to a cPanel user in seconds, and LVE Manager statistics tell you whether the same account is also burning CPU and I/O. Most "the database server is slow" tickets end at one plugin doing unindexed wp_postmeta lookups, and no amount of buffer pool fixes that. An object cache in front of it — Redis or LiteSpeed's LSMCD — removes far more queries than tuning ever will.

Next steps

• MySQL Governor modes — when to use abusers, all, or off
• Upgrade MariaDB or MySQL on cPanel/WHM without breaking it
• Tune PHP-FPM pools on cPanel

Per-user database limits need a CloudLinux license — Governor and LVE are the only tools that stop one account from spending the buffer pool you just sized.