How to tune InnoDB buffer pool size in MySQL or MariaDB

Right-sizing the InnoDB buffer pool keeps hot table and index pages in memory, which reduces disk I/O and prevents latency spikes that make a database feel “fast” one minute and “why is everything on fire” the next.

InnoDB uses the buffer pool as its primary page cache, storing recently used data and index pages plus internal metadata structures. Reads served from the buffer pool avoid storage round-trips, while modified (“dirty”) pages are flushed to disk in the background, so the buffer pool size directly influences cache hit rate, write smoothing, and overall throughput.

A buffer pool that is too small increases physical reads, while a buffer pool that is too large can push the host into swapping, which is a performance cliff with a cape. Persisted sizing is typically applied via the server configuration and takes effect on restart, so changes should be planned for connection disruption and verified with both server variables and OS memory behavior.

1. 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

2. 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 (sort/join/tmp buffers), OS cache, plus other daemons.

3. Open a privileged mysql client session on the server.

   $ mysql -u root -p

   Password-based login commonly uses mysql -u root -p instead of sudo mysql.

4. 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.

5. 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  |
   +---------------------------------------+-------+

   Hit rate ≈ 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests), where larger is better.

6. Set innodb_buffer_pool_size under [mysqld] in the server configuration.

   $ 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.

   Online resizing is available on many modern versions for testing.
   Example:

   $ mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 256*1024*1024;"

   
   Persistence still requires config (or SET PERSIST on supported MySQL releases).

7. Restart the database service to apply the persisted value.

   $ sudo docker restart sg-mysql
   sg-mysql

   Restarting the database service drops existing connections and cancels in-flight work.

8. Check the service status for a clean start after the restart.

   $ docker ps --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}' | grep -E '^sg-mysql[[:space:]]'
   sg-mysql                mysql:8.0           Up 10 seconds

   If the unit name is mariadb.service or mysqld.service, replace mysql accordingly.

9. 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 |
   +-------------------------+-----------+

10. 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:
    =====================================
    2025-12-25 02:25:51 281472362925824 INNODB MONITOR OUTPUT
    =====================================
    ##### snipped #####
    BUFFER POOL AND MEMORY
    ----------------------
    Total large memory allocated 0
    Dictionary memory allocated 489041
    Buffer pool size   16383
    Free buffers       15213
    Database pages     1166
    Old database pages 450
    ##### snipped #####

    Buffer pool size is shown in pages, where the default page size is typically 16KiB.

11. 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 indicates swapping, where reducing innodb_buffer_pool_size is usually faster than waiting for the host to “recover.”