Right-sizing the InnoDB buffer pool keeps hot table and index pages in memory, which reduces physical reads and smooths the latency spikes that show up when a database starts falling back to storage too often.
InnoDB uses the buffer pool as its main cache for table pages, index pages, and related control structures. The current size is exposed by innodb_buffer_pool_size, while Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads show whether the working set is mostly being served from memory or repeatedly missing cache and hitting disk.
A buffer pool that is too small increases read pressure, while a buffer pool that is too large can push the host into swapping and make performance worse instead of better. Current MySQL still supports online resizing, but recent MariaDB releases only grow live up to innodb_buffer_pool_size_max set at startup, so the safest cross-product tuning path is still an on-disk option-file change followed by a controlled restart and verification pass.
Steps to tune InnoDB buffer pool size in MySQL or MariaDB:
- Check total RAM plus swap configuration on the database host.
$ free -h total used free shared buff/cache available Mem: 11Gi 4.4Gi 178Mi 89Mi 7.4Gi 7.3Gi Swap: 4.0Gi 0B 4.0Gi - Record the current innodb_buffer_pool_size value.
$ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+
The value is reported in bytes.
- Review buffer pool read pressure metrics.
$ mysql --table -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';" +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 16130 | | Innodb_buffer_pool_reads | 1023 | +---------------------------------------+-------+
If Innodb_buffer_pool_reads keeps climbing quickly relative to Innodb_buffer_pool_read_requests after the working set is warm, the cache is usually too small for the workload.
- Choose a starting innodb_buffer_pool_size value.
Dedicated database host: start near 70-80% of RAM.
Mixed workloads: start near 40-60% of RAM.
Leave headroom for per-connection memory, other daemons, and the extra overhead that MySQL documents outside the raw buffer pool allocation. - Print the server option-file search order before deciding where to persist the change.
$ server_bin=$(command -v mariadbd || command -v mysqld) $ "$server_bin" --verbose --help 2>/dev/null | sed -n '/Default options are read from the following files in the given order:/,+3p' Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf ##### snipped #####
Use the discovered include chain instead of guessing which directory wins on the local host.
- Add or update a dedicated server override file with the target buffer pool size.
$ sudoedit /etc/mysql/conf.d/buffer-pool.cnf
[mysqld] innodb_buffer_pool_size = 256M
MySQL on Debian or Ubuntu commonly uses /etc/mysql/mysql.conf.d/mysqld.cnf.
MariaDB on Debian or Ubuntu commonly uses /etc/mysql/mariadb.conf.d/50-server.cnf.
RHEL-family defaults are often /etc/my.cnf.MySQL can grow live when the requested size matches innodb_buffer_pool_chunk_size multiplied by innodb_buffer_pool_instances.
Current MariaDB grows live only up to innodb_buffer_pool_size_max set at startup, so larger requests are ignored with a warning. - Confirm that the server reads the new option before restarting.
$ my_print_defaults mysqld | grep '^--innodb_buffer_pool_size=' --innodb_buffer_pool_size=256M
If this returns nothing, the file is in the wrong path, under the wrong option group, or overridden later in the include chain.
- Restart the database service to apply the persisted value.
$ sudo systemctl restart mysql
Restarting the database service drops active connections and interrupts in-flight work.
Replace mysql with mariadb or mysqld when that is the unit name on the host.
- Verify that the running server reports the intended innodb_buffer_pool_size value.
$ mysql --table -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 268435456 | +-------------------------+-----------+
- Confirm that InnoDB reports the new buffer pool sizing in status output.
$ mysql -u root -p -e "SHOW ENGINE INNODB STATUS\\G" *************************** 1. row *************************** Status: ===================================== 2026-04-09 15:15:14 281472232054528 INNODB MONITOR OUTPUT ===================================== ##### snipped ##### BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 526498 Buffer pool size 16384 Free buffers 8737 Database pages 1220 ##### snipped #####
Buffer pool size is shown in pages, where the default page size is typically 16KiB.
- Watch swap activity during a representative load window after the change.
$ vmstat 1 5 procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 6 0 0 218572 403456 7330324 0 0 0 33 6 7 0 0 99 0 0 ##### snipped #####
Non-zero si or so under normal workload indicates swapping, where reducing innodb_buffer_pool_size is usually faster than waiting for the host to recover.
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
