Shared hosting is an unusual database workload: thousands of small schemas, most of them idle, a few of them busy, all of them InnoDB, all of them WordPress-shaped. The tuning advice that circulates online is written for a single large application and much of it is wrong for this case. MariaDB 11.x and 12.x also changed defaults enough that copying a my.cnf from a 10.3 server actively hurts. This guide gives settings that we apply on cPanel shared servers, with the reasoning, so you can adjust for your own hardware.
Table of Contents
Short answer: On a cPanel shared server set innodb_buffer_pool_size to the smaller of your total InnoDB data size and roughly 25 to 35 percent of RAM, innodb_log_file_size to 1 to 2 GB, innodb_flush_method = O_DIRECT, and raise innodb_io_capacity to around 2000 on NVMe. Leave innodb_flush_log_at_trx_commit = 1, remove obsolete variables such as query_cache_size, and put everything in /etc/my.cnf.d/tuning.cnf rather than the cPanel-managed /etc/my.cnf. Judge the result by the buffer pool miss ratio and Innodb_log_waits after a day of traffic, not by feel.
Start by measuring, not guessing
free -g
mariadb -e "SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';"
mariadb -e "SELECT ROUND(SUM(data_length+index_length)/1024/1024/1024,1) AS gb FROM information_schema.TABLES WHERE ENGINE='InnoDB';"
du -sh /var/lib/mysqlThree numbers matter: total RAM, total InnoDB data plus indexes, and the current buffer pool hit rate. Innodb_buffer_pool_reads divided by Innodb_buffer_pool_read_requests is the miss ratio; on a healthy shared server it is below one percent. If it is higher, the pool is too small for the working set. The MySQL health snapshot script captures all of these on a schedule so you have a baseline before changing anything.
Buffer pool
The old rule of “70 percent of RAM” assumes a dedicated database server. On a cPanel host the same RAM is shared with PHP-FPM or LSAPI, Apache or LiteSpeed, Exim, Dovecot, ClamAV and the panel itself. A workable rule is: buffer pool equal to the smaller of the total InnoDB data size and 25 to 35 percent of RAM, rounded to a multiple of 1 GB. On a 64 GB server with 30 GB of InnoDB data, 20 GB is a sensible starting point:
[mysqld]
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 8Since 10.5 the buffer pool is resizable online, so you can test without a restart:
mariadb -e "SET GLOBAL innodb_buffer_pool_size = 20 * 1024 * 1024 * 1024;"Watch the miss ratio for a day. If it does not improve, the working set was already cached and the extra memory is better given to PHP.
Redo log
The redo log changed substantially in 10.8 and later: there is one file, ib_logfile0, its size is set by innodb_log_file_size, and it can be resized online. The old advice about innodb_log_files_in_group no longer applies and the variable is gone. A small log forces frequent checkpoints and write amplification; a huge log lengthens crash recovery. For shared hosting, 1 to 2 GB is the range:
innodb_log_file_size = 2G
innodb_log_buffer_size = 64Minnodb_flush_log_at_trx_commit deserves a deliberate choice. 1 is the default and durable: every commit reaches disk. 2 writes at commit but syncs once a second, so a power loss (not a MariaDB crash) can lose up to a second of transactions. On a shared server where most customers are WordPress and the disk is NVMe, 1 is affordable and is what we recommend; set 2 only on slow storage where commit latency is measurably hurting page loads, and document that you did.
I/O settings
On NVMe or any SSD, the defaults from 10.x underestimate what the disk can do:
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8O_DIRECT avoids double caching between the OS page cache and the buffer pool; it is correct on almost every modern setup, but check that the filesystem supports it (ext4 and XFS do). innodb_flush_neighbors = 0 is right for SSDs and is now the default in 11.x, listed here so it is explicit. The I/O capacity numbers are conservative for NVMe; raising them further mainly helps bulk write workloads, which shared hosting rarely has.
Per-table files and file handles
innodb_file_per_table has been the default for a long time and must stay on for a shared server, because it is what allows disk space to be reclaimed when a customer drops a table. With thousands of schemas the number of open files becomes the constraint:
open_files_limit = 65535
table_open_cache = 8000
table_definition_cache = 8000
innodb_open_files = 8000Also raise the systemd limit, since my.cnf cannot exceed it:
mkdir -p /etc/systemd/system/mariadb.service.d
printf '[Service]\nLimitNOFILE=65535\n' > /etc/systemd/system/mariadb.service.d/limits.conf
systemctl daemon-reloadThings not to set. query_cache_size is gone; remove it. innodb_additional_mem_pool_size, innodb_log_files_in_group, storage_engine and the other removed variables in our unknown variable guide will stop the server from starting. tmp_table_size and max_heap_table_size above 64M each are rarely useful and, multiplied by max_connections, are how servers run out of memory. max_connections itself should match what PHP can actually open; see our too many connections guide for the arithmetic.
Apply and restart
Put the settings in /etc/my.cnf.d/tuning.cnf rather than editing /etc/my.cnf, which cPanel manages, and restart in a maintenance window:
mariadb -e "SET GLOBAL innodb_fast_shutdown = 0;"
/scripts/restartsrv_mysql
tail -30 /var/lib/mysql/$(hostname).errThe slow shutdown matters when the redo log size changes; the new size is applied at startup only when the log is clean.
Common pitfall. Sizing the buffer pool from du -sh /var/lib/mysql, which includes binary logs, undo logs and the redo log, none of which live in the pool. Use the information_schema query above, or SHOW TABLE STATUS, for the real InnoDB data size.
Verify
After a day of production traffic compare against the baseline:
mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_wait_free','Innodb_log_waits','Innodb_os_log_written','Uptime');"Innodb_buffer_pool_wait_free and Innodb_log_waits should be zero or nearly so; any steady growth means the pool or the log is still too small. Keep the previous tuning.cnf in version control so the change can be reverted if PHP starts swapping, and revisit the numbers whenever the server’s RAM or its customer count changes materially.
InnoDB tuning MariaDB at a glance
Official documentation: MariaDB documentation, cPanel & WHM documentation, Linux man pages.
Related guides: Fix “Too many connections” on MariaDB/MySQL (cPanel) · MariaDB won’t start after an upgrade: InnoDB recovery, mariadb-upgrade and sql_mode issues · Installing and switching PHP versions in EasyApache 4 (8.2 to 8.5).
Frequently asked questions
How big should innodb_buffer_pool_size be on a shared hosting server?
Take the smaller of your total InnoDB data plus indexes (from information_schema.TABLES, not du) and about 25 to 35 percent of RAM, rounded to whole gigabytes. The rest of the memory is needed by PHP, the web server, mail and the panel, so the dedicated-database rule of 70 percent does not apply.
Do I need to restart MariaDB to change the buffer pool or redo log size?
The buffer pool can be resized online with SET GLOBAL innodb_buffer_pool_size, and the redo log has been resizable online since 10.8. Persisting either change still requires the value in a .cnf file, and a clean slow shutdown (innodb_fast_shutdown = 0) is the safest way to apply a new log size.
Is innodb_flush_log_at_trx_commit = 2 safe on a cPanel server?
It is safe against a MariaDB crash but can lose up to a second of committed transactions on a power loss or kernel panic. On NVMe the durable default of 1 costs little, so keep it unless commit latency on slow storage is measurably hurting page loads, and document the decision if you change it.