There are 3 methods,
Method 1:Method 1: Permanent edit in the config file.
Stop the service anyhow. For the Linux distribution is like sudo service mysql stop
to stop the running service and prevent any interference with other processes. Then edit the Mysql config file by sudoer privilege that based on your installation may vary, for me is like sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Then add the following lines at the end of the file and provide your numbers:
innodb_buffer_pool_size = 24G
innodb-buffer-pool-instances = 12
innodb-buffer-pool-chunk-size = 2G
There is one important point mentioned by the MySQL page. Its summary is the pool size should be equal to the multiplication of the number of instances and chunk size, otherwise would be adjusted to the closest one. One hint may help is to use 50% up to 65% of the memory for pool size to have the best performance.
Method 2:Method 2: define when open MySQL
This method is straightforward with a command like mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4 --innodb-buffer-pool-chunk-size=1073741824;
Method 3:Method 3: within the MySQL terminal
You can use the command inside the MySQL terminal with such a command:
mysql> SET GLOBAL innodb_buffer_pool_size=402653184;