It is always bit tricky to decide how much memory should be allocated to innodb_buffer_pool to gain optimum performance out of MySQL.
It is useful to keep the innodb data and index information in RAM to quickly address it and avoid i/o operations in disk blocks.
Below query can be helpful to make decision of the buffer size.
mysql> 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;
The innodb_buffer_pool should be set to GB which is shown as the result of the above query.
Applied that you have sufficient amount of RAM installed to fulfil the operation requirements on server.
Hope the above query got you the value to set your innodb buffer pool size. The innodb buffer pool size should be high enough to hold all indexes. But at the same time the innodb_buffer_pool_size should not be too high that MySQL process occupy system memory unnecessarily which could be used for other resources on server.
It is useful to keep the innodb data and index information in RAM to quickly address it and avoid i/o operations in disk blocks.
Below query can be helpful to make decision of the buffer size.
mysql> 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;
The innodb_buffer_pool should be set to GB which is shown as the result of the above query.
Applied that you have sufficient amount of RAM installed to fulfil the operation requirements on server.
Hope the above query got you the value to set your innodb buffer pool size. The innodb buffer pool size should be high enough to hold all indexes. But at the same time the innodb_buffer_pool_size should not be too high that MySQL process occupy system memory unnecessarily which could be used for other resources on server.
