How to Set InnoDB Buffer Pool Size in MySQL

To set the innodb_buffer_pool_size in MySQL, add or modify the setting in the my.cnf or my.ini configuration file under the [mysqld] section. Then restart the MySQL server for the change to take effect. You can also set it dynamically using SET GLOBAL innodb_buffer_pool_size = value; on MySQL 8.0.14 or later.

Syntax

The innodb_buffer_pool_size is set in the MySQL configuration file or dynamically via SQL. It defines the amount of memory allocated to the InnoDB buffer pool, which caches data and indexes for faster access.

• innodb_buffer_pool_size = size: Sets the buffer pool size in bytes (you can use suffixes like M for megabytes or G for gigabytes).
• Set under the [mysqld] section in my.cnf or my.ini.
• Dynamic change possible in MySQL 8.0.14+ with SET GLOBAL.

ini

[mysqld]
innodb_buffer_pool_size=1G

Example

This example shows how to set the InnoDB buffer pool size to 2 gigabytes in the MySQL configuration file and how to change it dynamically in MySQL 8.0.14 or later.

sql

# In my.cnf or my.ini under [mysqld]
innodb_buffer_pool_size=2G

-- After restarting MySQL, check the value:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Dynamically change buffer pool size (MySQL 8.0.14+):
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB in bytes

-- Verify change:
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Output

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

mysql> SET GLOBAL innodb_buffer_pool_size = 2147483648;
Query OK, 0 rows affected (0.01 sec)

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

Common Pitfalls

• Not restarting MySQL: Changes in the config file require a server restart to apply.
• Setting too large value: Allocating more memory than available can cause system instability.
• Dynamic changes limitations: Dynamic resizing is only supported in MySQL 8.0.14 and later.
• Using wrong units: Always use proper suffixes (M, G) or bytes for size values.

sql

# Wrong: No restart after config change
# Right: Restart MySQL server after editing my.cnf

# Wrong dynamic change on older MySQL versions
SET GLOBAL innodb_buffer_pool_size = 1073741824; -- Fails on <8.0.14

# Correct dynamic change on 8.0.14+
SET GLOBAL innodb_buffer_pool_size = 1073741824;

Quick Reference

Setting	Description	Example
innodb_buffer_pool_size	Memory size for InnoDB buffer pool	innodb_buffer_pool_size=2G
Location	Set under [mysqld] in my.cnf or my.ini	[mysqld] innodb_buffer_pool_size=1G
Dynamic change	Use SET GLOBAL on MySQL 8.0.14+	SET GLOBAL innodb_buffer_pool_size=1073741824;
Restart required	Yes, if changed in config file	sudo systemctl restart mysql

Key Takeaways

Set innodb_buffer_pool_size in my.cnf under [mysqld] and restart MySQL to apply.

Use suffixes like M or G to specify size clearly (e.g., 1G for 1 gigabyte).

Dynamic resizing is supported only in MySQL 8.0.14 and later using SET GLOBAL.

Avoid setting buffer pool size larger than available system memory.

Always verify the current buffer pool size with SHOW VARIABLES LIKE 'innodb_buffer_pool_size';