Sizing MariaDB's buffer pool on a small VPS
Debian ships the setting commented out, so a multi-gigabyte database runs on a 128 MB cache and burns CPU re-reading the same pages forever.
If your database server sits at 20% CPU doing apparently nothing, check one setting before anything else. On Debian and Ubuntu, innodb_buffer_pool_size ships commented out in /etc/mysql/mariadb.conf.d/50-server.cnf, which leaves it at the built-in default of 128 MB no matter how much memory the machine has.
128 MB against a 9 GB database is a 1.4% cache ratio. Practically every query reads pages off disk, and the CPU time you are watching is largely the cost of doing that over and over.
Find out what you are actually caching
SELECT table_schema,
ROUND(SUM(data_length+index_length)/1048576) AS mb
FROM information_schema.tables
GROUP BY 1 ORDER BY 2 DESC;That gives you the size of the working set the pool is competing with. Compare it against what the pool is actually set to:
SELECT @@innodb_buffer_pool_size/1048576 AS pool_mb;Choosing a number
The figure quoted everywhere is 70–80% of RAM. That advice is for a machine that does nothing but run the database. On a VPS that also serves PHP, nginx, mail and perhaps containers, a quarter of RAM is a far more honest starting point — and you should cap it at the size of the data plus some headroom, because reserving 4 GB of cache for a 40 MB database achieves nothing.
# a reasonable rule for a shared box
pool = min( RAM * 0.25 , data_size * 1.25 + 128MB )innodb_buffer_pool_chunk_size (128 MB by default). Pick a multiple of 128 yourself, or the value you set and the value in effect will differ and you will not be able to verify your own change.Two settings worth adding beside it
innodb_flush_method = O_DIRECT— stops the OS caching the same pages the pool already holds. The duplicate copy is pure waste.innodb_flush_neighbors = 0— on SSD there is no benefit to writing neighbouring pages together.
It will not take effect live
This is the part that catches people. On MariaDB 10.11, SET GLOBAL innodb_buffer_pool_size answers Truncated incorrect value and silently keeps the old size. The pool was reworked and can no longer be resized at runtime the way older versions allowed. A restart is the only way.
Because a restart is required, validate the config before you take the service down. A typo in a conf.d file does not fail when you save it — it fails at the next start, which might be weeks later when nobody remembers editing anything:
mariadbd --defaults-file=/tmp/new.cnf --help --verbose >/dev/null
# any output on stderr = do not restartAlso worth reclaiming
While you are in there: key_buffer_size caches MyISAM indexes and aria_pagecache_buffer_size caches Aria pages. Debian sets both to 128 MB. If your schema is entirely InnoDB — check with a GROUP BY engine — that is a quarter of a gigabyte reserved for nothing at all. Drop them to 16 MB and 32 MB.
Alien XP
Runs the servers behind AlphaPanel and writes up what breaks.
