Embedded Files
Configuration
/etc/my.cnfConfiguration
innodb_buffer_pool_size
innodb_buffer_pool_size
Default: 134217728 Bytes (128MB)
SELECT @@innodb_buffer_pool_size;
SELECT variable_name,
variable_value,
variable_value/1024/1024/1024 As 'GB'
FROM performance_schema.global_variables
WHERE variable_name='innodb_buffer_pool_size';
To resize this variable online (without the need to restart MySQL Server)...
SET GLOBAL innodb_buffer_pool_size=6442450944;
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
Buffer pool resizing progress is also logged in the server error log.The most commonly followed practice is to set this value at 70% – 80% of the system RAM e.g. ~6GB or 6442450944 Bytes on a system with 8GB RAM (6GB is 75% of 8GB).
Note that this is for a dedicated MySQL Server. Virus Checkers and other tools may mean this estimate is inaccurate.
innodb_buffer_pool_size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you use a value that is not a multiple then the value will automatically be adjusted so that it is.
Note that this is for a dedicated MySQL Server. Virus Checkers and other tools may mean this estimate is inaccurate.
innodb_buffer_pool_size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you use a value that is not a multiple then the value will automatically be adjusted so that it is.
Scalegrid offer a handy online calculator (linked above). Or consider using a copy of the Google sheet provided below.
If MySQL is not yet installed you can use the output of: free -m to help inform the size decision. e.g. if you intend setting innodb_buffer_pool_size to 5120M then free -m must show a figure in the "available" column that is greater than 5120M.
If there are other processes on the server that would benefit from a higher value in "buff/cache" then you may wish to set the value of innodb_buffer_pool_size to a value closer to that in the "free" column instead (assuming the server has been up long enough for these values to have found levels indicative of normal running).
Opinion: this is a database server, not a file server, therefore the bulk of the available memory should be used by the database. When servers have more than one function you will almost certainly cause resource contention unless the amount of physical memory is sufficiently high to meet all demands. This is a good reason for not running virus scanners on database servers but remember that, if you choose not to deploy a virus scanner, then you need to make sure that there is no way a virus could get on to the server.
If there are other processes on the server that would benefit from a higher value in "buff/cache" then you may wish to set the value of innodb_buffer_pool_size to a value closer to that in the "free" column instead (assuming the server has been up long enough for these values to have found levels indicative of normal running).
Opinion: this is a database server, not a file server, therefore the bulk of the available memory should be used by the database. When servers have more than one function you will almost certainly cause resource contention unless the amount of physical memory is sufficiently high to meet all demands. This is a good reason for not running virus scanners on database servers but remember that, if you choose not to deploy a virus scanner, then you need to make sure that there is no way a virus could get on to the server.
innodb_buffer_pool_chunk_size
innodb_buffer_pool_chunk_size
In MySQL 8, the default for innodb_buffer_pool_chunk_size is 134217728 (128M)
SELECT @@innodb_buffer_pool_chunk_size;
innodb_buffer_pool_instances
innodb_buffer_pool_instances
In MySQL 8, the default for innodb_buffer_pool_instances is 8 and the maximum is 64
(unless innodb_buffer_pool_size is less than 1GB, in which case the default for innodb_buffer_pool_instances is 1)SELECT @@innodb_buffer_pool_instances;
For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB. (1)
Bibliography & References
Bibliography & References
innodb_buffer_pool_sizehttps://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_sizehttps://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.htmlhttps://stackoverflow.com/questions/35932125/mysql-query-sometimes-run-slow-sometimes-fast
innodb_buffer_pool_instances(1) https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.htmlhttps://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instanceshttps://www.percona.com/blog/how-many-innodb_buffer_pool_instances-do-you-need-in-mysql-8/https://www.percona.com/blog/part-two-how-many-innodb_buffer_pool_instances-do-you-need-in-mysql-8-with-a-cpu-bound-workload/
innodb_buffer_pool_instances(1) https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.htmlhttps://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instanceshttps://www.percona.com/blog/how-many-innodb_buffer_pool_instances-do-you-need-in-mysql-8/https://www.percona.com/blog/part-two-how-many-innodb_buffer_pool_instances-do-you-need-in-mysql-8-with-a-cpu-bound-workload/
Page updated
Google Sites
Report abuse