MariaDB tuning on CyberPanel: five wins that stick

A fresh CyberPanel install ships MariaDB with defaults that assume it might be sharing a 1 GB box with everything else. On a real server running four or five WordPress sites, those defaults leave most of your RAM sitting idle while queries queue up behind a buffer pool that’s a fraction of the size it should be. We tune MariaDB on every CyberPanel server we hand over, and the same five changes account for nearly all of the gain.

None of this needs a control-panel plugin or a paid tool. It’s a config file, a restart, and a way to check you didn’t overshoot the memory. Here’s the exact set of changes, why each one matters, and the numbers we saw on a box we tuned last month.

Where CyberPanel keeps the config

CyberPanel runs MariaDB, not MySQL, and on OpenLiteSpeed boxes the settings live where you’d expect on a Debian or Ubuntu build: /etc/mysql/mariadb.conf.d/50-server.cnf. Some AlmaLinux installs put it at /etc/my.cnf.d/server.cnf instead. Check both before you edit anything:

ls -la /etc/mysql/mariadb.conf.d/ /etc/my.cnf.d/ 2>/dev/null

Whichever file holds the [mysqld] block is the one you want. Back it up first — cp 50-server.cnf 50-server.cnf.bak — because a bad value here stops MariaDB from starting, and a database that won’t start takes every site on the box down with it. We’ve cleaned up enough of those to make the backup a reflex.

One thing to know about the OpenLiteSpeed stack: LiteSpeed and LSPHP cache a lot at the application layer, so a well-configured site hits the database less than the same site on nginx would. That doesn’t make database tuning optional. It means the queries that do reach MariaDB — logged-in sessions, WooCommerce carts, admin work — are the expensive ones, and those are exactly what the buffer pool protects.

1. InnoDB buffer pool — the one that actually matters

If you change one setting, change this. innodb_buffer_pool_size is how much RAM MariaDB uses to hold table data and indexes in memory instead of reading them off disk. The CyberPanel default is 128 MB. On a 4 GB server that’s leaving roughly 2 GB of usable cache on the table.

The old rule of thumb was 70–80% of RAM for a dedicated database server. CyberPanel boxes aren’t dedicated — LiteSpeed, LSPHP workers, and Redis all want their share — so we set it lower. On a server with nothing but the web stack on it:

innodb_buffer_pool_size = 1G      # 4 GB RAM box
innodb_buffer_pool_size = 2G      # 8 GB RAM box
innodb_buffer_pool_instances = 1

Leave instances at 1 for anything under 2 GB of pool. Splitting a small pool across instances just fragments it. Once the pool is over 4 GB, one instance per gigabyte starts to help with contention, but you’re unlikely to be there on a shared CyberPanel node.

How do you know if the pool is big enough? After the site’s been running a day or two, check the read efficiency:

mariadb -e "SHOW ENGINE INNODB STATUS\G" | grep "Buffer pool hit rate"

You want that hit rate at 999 / 1000 or higher. If it’s lower, the working set doesn’t fit and you can push the pool up. If you’ve got room and the databases are small, don’t bother inflating it past what the data needs — a 2 GB pool for 300 MB of actual data is just reserved RAM you can’t use elsewhere.

2. Let InnoDB flush less often

By default MariaDB flushes the log to disk on every single transaction commit. That’s the safe choice for a bank. For a WordPress or OpenCart site, it’s overkill that turns every comment, every cart update, every options-table write into a disk sync you wait on.

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Setting flush_log_at_trx_commit to 2 means MariaDB writes to the OS every commit but only forces the physical flush once a second. In a crash you could lose up to one second of transactions — for a content site, that’s a risk worth taking for the write throughput you get back. If you’re running something where a lost order is a real problem, leave it at 1 and lean harder on the buffer pool instead.

O_DIRECT tells InnoDB to skip the OS file cache, which otherwise double-buffers data the pool is already holding. On SSD and NVMe — which is nearly every CyberPanel host now — this is the right setting and frees memory the kernel was wasting.

3. Kill the query cache

This one is counterintuitive. The MariaDB query cache sounds like it should speed things up, and on a low-traffic site from 2012 it did. On a busy multi-site box it’s a bottleneck: every write to a table invalidates every cached query for that table, and the cache takes a global lock to do it. Under concurrency, that lock becomes the thing everything waits on.

query_cache_type = 0
query_cache_size = 0

MariaDB 10.5 and up already default the query cache off, but plenty of CyberPanel installs were provisioned on 10.3 or 10.4 and carried the old default forward through upgrades. Check what you’ve actually got running:

mariadb -e "SHOW VARIABLES LIKE 'query_cache_type';"

If it comes back ON, turn it off. LiteSpeed’s cache and Redis object caching cover the same ground far better and without the lock.

4. Size the connection and thread settings to reality

max_connections defaults high — often 151 — which sounds generous until you realize each connection reserves per-thread buffers. Set it to what your sites actually open. A handful of WordPress sites behind LSPHP rarely need more than 60–80 concurrent database connections, and capping it lower keeps a runaway plugin from spawning connections until the server runs out of memory and the OOM killer takes MariaDB down.

max_connections = 80
thread_cache_size = 16
table_open_cache = 2000

We’ve walked into more than one “the site keeps going down at random” job that turned out to be MariaDB getting OOM-killed because max_connections times the per-thread buffers exceeded what was left after the buffer pool. Right-size it and the crashes stop.

5. Run MySQLTuner, then ignore most of what it says

MySQLTuner is a free Perl script that reads your running server’s stats and suggests changes. It’s useful, but it’s a starting point, not gospel — it doesn’t know CyberPanel shares the box with LiteSpeed, and it’ll happily tell you to hand 80% of your RAM to the buffer pool on a server that can’t spare it.

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

Let the server run under normal traffic for at least a day before you trust its numbers — it bases recommendations on uptime, and a freshly restarted server gives useless advice. Read the “Variables to adjust” section at the bottom, sanity-check each suggestion against how much RAM you actually have free, and apply the ones that fit. The buffer pool hit rate and the “InnoDB Write Log efficiency” lines are the ones we pay attention to.

Apply it and check nothing broke

After editing the config, restart MariaDB and confirm it came back up before you walk away:

systemctl restart mariadb
systemctl status mariadb --no-pager
mariadb -e "SELECT @@innodb_buffer_pool_size;"

That last line reads the value back in bytes so you can confirm the setting took. Then watch memory for a few minutes with free -h and make sure you haven’t pushed the box into swap. If MariaDB refuses to start, the log at /var/log/mysql/error.log (or journalctl -u mariadb) tells you which line it choked on — usually a buffer pool set larger than the RAM available.

On the box we tuned last month — a 4 GB Hetzner server running five WordPress sites, one of them a low-volume WooCommerce store — the buffer pool change alone dropped average query time on the WooCommerce admin from noticeable lag to instant, and the load average during the nightly backup window fell by about half. The whole change took fifteen minutes plus a day of watching the hit rate settle.

When tuning isn’t the answer

Tuning only recovers the headroom bad defaults gave away. It won’t fix a genuinely undersized server, and it won’t fix a query that scans a million rows because a plugin forgot an index. If your buffer pool hit rate is already at 1000/1000 and the site’s still slow, the problem is somewhere else — slow queries, a missing index, or a box that’s simply out of headroom. That’s a different job, and we’re happy to look at it.

If you’d rather not touch my.cnf yourself, tuning MariaDB is part of what we do on every CyberPanel server we manage, and it’s a standard step in our CyberPanel support work. If you’re still deciding whether CyberPanel is even the right panel for your setup, we wrote up how CyberPanel compares to cPanel from running both in production. Send us the server details and we’ll tell you what’s worth changing — usually for a flat fee, no retainer.

These numbers assume a server that was set up properly in the first place. If yours is new, start from our notes on building a CyberPanel VPS from scratch and come back to the database once it is hardened.