InnoDB Buffer Pool Settings That Matter on a VPS

Most MariaDB performance folklore is a list of fifty variables copied from a blog post written for a dedicated 64 GB box. On a VPS the list collapses. InnoDB either keeps your working set in the buffer pool or it reads pages from High-Speed SSD on every query. Everything else is a rounding error until that pool is sized against the RAM you actually have, after the operating system, the application, and a safety margin.

A Netbay instance in Lucknow DC01 gives you Intel Xeon Platinum cores and High-Speed SSD. It does not give you a free pass to set innodb_buffer_pool_size to 90 percent of RAM on a host that also runs PHP or Node. The buffer pool is a cache, not a dare.

Size the pool from free RAM, not from a percentage meme

Measure RAM first. Subtract the application, the page cache you still want for binaries and logs, and a 1 GB floor for the OS. What remains is the budget for InnoDB. On a dedicated database VPS with 8 GB, 5 to 6 GB is a common landing zone. On a shared app-plus-database VPS with 4 GB, 1 to 1.5 GB is safer than 3 GB that will swap.

Set the pool in a drop-in, then restart. A dynamic SET GLOBAL is fine for an experiment; it is not how you want a reboot to behave.

# /etc/mysql/mariadb.conf.d/91-innodb.cnf
[mysqld]
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 1
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
max_connections = 80
table_open_cache = 400

innodb_buffer_pool_instances can stay at 1 until the pool is several gigabytes and you see contention on the pool mutex. Splitting a 1 GB pool into eight instances wastes structures. innodb_file_per_table keeps each table in its own tablespace so DROP and TRUNCATE actually return disk to the filesystem.

After restart, confirm the server took the value. SHOW VARIABLES lies less than your memory of what you typed.

SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_log_file_size',
  'innodb_flush_log_at_trx_commit',
  'innodb_flush_method'
);
SHOW ENGINE INNODB STATUS;

Read the BUFFER POOL AND MEMORY section. Pages made young, pages read, and buffer pool hit rate tell you whether the working set fits. A hit rate that lives at 99.9 percent is the goal. A hit rate that dips while disk reads climb means the pool is too small or the query set is scanning tables it should not.

The two durability knobs people turn for the wrong reason

innodb_flush_log_at_trx_commit = 1 fsyncs the redo log at every commit. That is the default and the correct value when a lost transaction is an incident. Setting it to 2 flushes to the OS cache once a second. You can gain commit latency and lose up to about a second of committed work on a kernel crash. That trade is real. Make it on purpose for a session store, not for orders or ledgers.

innodb_log_file_size (or innodb_redo_log_capacity on newer series) controls how much redo you can write before a checkpoint stall. Too small, and busy write workloads hitch while InnoDB checkpoints. Too large, and crash recovery takes longer. For a typical 5 GB pool, 512 MB of redo is a sane start. Change it only during a planned restart; MariaDB has to recreate the logs.

innodb_flush_method = O_DIRECT skips the OS page cache for InnoDB data files so you do not double-cache the same pages in the buffer pool and in Linux. On High-Speed SSD this is the usual choice. Do not combine a huge pool with a huge OS cache and then wonder why the OOM killer visited mysqld.

Leave the rest of the my.cnf folklore alone

query_cache is gone or harmful; do not re-enable it. tmp_table_size and max_heap_table_size can grow a little if you see many disk temporary tables, but they are per-session and will multiply by max_connections. Raising max_connections from 80 to 800 on a 4 GB VPS is how you run out of RAM without a single slow query.

Tune from evidence:

• High disk reads in INNODB STATUS and a falling hit rate: grow the pool, or fix the query that scans a table larger than RAM.
• Commit latency spikes with fsync times in the OS: check disk wait, not a random innodb_io_capacity from a hardware blog.
• Threads_running climbing while CPU is idle: lock waits, not buffer pool.

Revisit the pool after the schema is real. An empty database makes every cache look brilliant. Load a production-sized copy, run the application's real query mix, and only then decide whether the VPS needs more RAM or the SQL needs an index.

The buffer pool is the setting that decides whether InnoDB is a memory engine with a disk backup or a disk engine with a small cache. Size it from free RAM, keep commit flushing honest, and ignore the fifty-line my.cnf until evidence says otherwise. If the working set needs more RAM, resize the VPS on Netbay in Lucknow from the dashboard or the API at netbayhosts.in and restart MariaDB once.