How to check and change innodb buffer pool size in mysql database

 The InnoDB buffer pool size in MySQL determines how much memory is allocated to store frequently accessed data and indexes. To check and change it, follow these steps:

1. Check Current InnoDB Buffer Pool Size

    a. Log in to MySQL

        mysql -u root -p

    b. Run the Following Query

        SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

This will display the current size in bytes.

---

2. Change InnoDB Buffer Pool Size

    a. Edit the MySQL Configuration File

The InnoDB buffer pool size is set in the MySQL configuration file (my.cnf or my.ini).

1. Open the configuration file in a text editor (e.g., nano on Ubuntu):

   sudo nano /etc/mysql/my.cnf

2. Locate the [mysqld] section and add or update the following line:

   innodb_buffer_pool_size = 1G

   Replace 1G with the desired size (e.g., 512M, 2G, etc.).

   • Best Practice: Set the buffer pool size to around 70-80% of your server's total RAM if MySQL is the primary application on the server.

    b. Save and Exit

    Save the changes and exit the text editor.

    c. Restart MySQL Service

        Restart MySQL to apply the changes:

        sudo systemctl restart mysql

---

3. Verify the Change

Log in to MySQL again and run the query to confirm the new buffer pool size:

        SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

---

Additional Notes

• For servers with large memory, consider splitting the buffer pool into multiple instances:

  innodb_buffer_pool_instances = 4

  This setting improves performance for systems with a high buffer pool size.

• If MySQL fails to restart, check the error log for details:

  sudo tail -n 50 /var/log/mysql/error.log