# InnoDB Tuning MariaDB 11/12: Shared Hosting Settings Source: https://srvscripts.com/guides/innodb-tuning-mariadb-shared-hosting/ Updated: 2026-10-03 Publisher: srvScripts (https://srvscripts.com/) 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. In short: 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. **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/mysql ``` Three 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](/scripts/mysql-health-snapshot/) 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 = 8 ``` Since 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 = 64M ``` `innodb_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 = 8 ``` `O_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 = 8000 ``` Also 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-reload ``` **Things 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](/guides/mariadb-unknown-variable-after-upgrade/) 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](/guides/fix-mysql-too-many-connections-cpanel/) 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).err ``` The 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](https://mariadb.com/docs/), [cPanel & WHM documentation](https://docs.cpanel.net/), [Linux man pages](https://man7.org/linux/man-pages/). **Related guides:** [Fix “Too many connections” on MariaDB/MySQL (cPanel)](https://srvscripts.com/guides/fix-mysql-too-many-connections-cpanel/) · [MariaDB won’t start after an upgrade: InnoDB recovery, mariadb-upgrade and sql_mode issues](https://srvscripts.com/guides/mariadb-not-starting-after-upgrade/) · [Installing and switching PHP versions in EasyApache 4 (8.2 to 8.5)](https://srvscripts.com/guides/easyapache-4-php-versions-8-2-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.