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:           16Gi       3.2Gi       8.1Gi       196Mi       4.7Gi        12Gi
   Swap:          2.0Gi         0B       2.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.

   $ sudo mysql
   mysql>

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

4. Record the current innodb_buffer_pool_size value.

   mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
   +-------------------------+-----------+
   | Variable_name           | Value     |
   +-------------------------+-----------+
   | innodb_buffer_pool_size | 134217728 |
   +-------------------------+-----------+
   1 row in set (0.00 sec)

   The value is reported in bytes.

5. Review buffer pool read pressure metrics.

   mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
   +----------------------------------+------------+
   | Variable_name                    | Value      |
   +----------------------------------+------------+
   | Innodb_buffer_pool_read_requests | 1567348210 |
   | Innodb_buffer_pool_reads         | 4829312    |
   +----------------------------------+------------+
   2 rows in set (0.00 sec)

   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/mysql.conf.d/mysqld.cnf

   [mysqld]
   innodb_buffer_pool_size = 8G

   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> SET GLOBAL innodb_buffer_pool_size = 8*1024*1024*1024;

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

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

   $ sudo systemctl restart 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.

   $ sudo systemctl status mysql
   ● mysql.service - MySQL Community Server
        Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
        Active: active (running) since Fri 2025-12-12 21:42:07 UTC; 8s ago
   ##### snipped #####

   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> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
   +-------------------------+------------+
   | Variable_name           | Value      |
   +-------------------------+------------+
   | innodb_buffer_pool_size | 8589934592 |
   +-------------------------+------------+
   1 row in set (0.00 sec)

10. Confirm that InnoDB reports the new buffer pool sizing in status output.

    mysql> SHOW ENGINE INNODB STATUS\G
    *************************** 1. row ***************************
      Type: InnoDB
      Name:
    Status:
    =====================================
    2025-12-12 21:55:24 0x7f0c5c6c0700 INNODB MONITOR OUTPUT
    =====================================
    ##### snipped #####
    BUFFER POOL AND MEMORY
    Total large memory allocated 8790394880
    Dictionary memory allocated  581920
    Buffer pool size   524288
    Free buffers       1024
    Database pages     520000
    Old database pages 191000
    Modified db pages  1350
    ##### snipped #####
    1 row in set (0.01 sec)

    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
     1  0      0 8204120  1012 5032240    0    0     2     9  210  290  3  1 96  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.”