I will give an example of resizing the InnoDB buffer pool in MySQL.
In the InnoDB buffer pool, this is the memory space in which indexes, caches, buffers, etc. are stored.
Connect to MySQL and see the current value of the InnoDB buffer pool (by default 134217728, this is 128 megabytes):
mysql
SELECT @@innodb_buffer_pool_size;
SELECT @@innodb_buffer_pool_chunk_size;
SELECT @@innodb_buffer_pool_instances;
SHOW ENGINE INNODB STATUS;
show global status like '%innodb_buffer_pool_pages%';
Calculate the approximate value:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
Suppose the database is 1 gigabyte, then we increase the InnoDB buffer pool to 2 gigabytes (the value must be approximately equal to innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances and preferably larger than the database size, as well as enough RAM):
SET GLOBAL innodb_buffer_pool_size=2147483648;
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
Also we change innodb_buffer_pool_instances, usually equal to the number of gigabytes of the InnoDB buffer pool, if innodb_buffer_pool_size is 2 GB, then innodb_buffer_pool_instances we specify no more than 2, usually each instance should not be less than 1 GB.
Since the variable is read-only, the following command will not be able to change the value without restarting the mysql server:
SET GLOBAL innodb_buffer_pool_instances=2;
To prevent changes from being reset after restarting the MySQL server, open the configuration file in a text editor:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
And after the mysqld block, add:
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
Restart the MySQL server to apply the value of innodb_buffer_pool_instances:
sudo service mysql restart
If Buffer pool hit rate 1000/1000, then ok:
SHOW ENGINE InnoDB STATUS\G
See also my articles:
Changing InnoDB log file size
Installing and using mysqltuner.pl