InnoDB_buffer_pool_size

from the Artful MySQL Tips List


One approach is to estimate it from innodb_data_page use:

set @ibpdatapages = (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_data'); 
set @ibptotalpages = (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_pages_total'); 
set @ibppctfull = round(@ibpdatapages * 100.0 / @ibptotalpages, 2 );
select @ibppctfull;

If ibpPctFull >= 95%, innodb_buffer_pool_size should be about 75% of RAM available to MySQL. Otherwise, estimate required innodb_buffer_pool_size from current ibdata use ...

set @ibpsize = (select variable_value from information_schema.global_status where variable_name = 'innodb_page_size');
set @ibpsize = round( @ibpsize * @ibpdatapages / (1024*1024*1024) * 1.05, 2 );
select concat( @ibpsize, 'GB' ) as ibpsize;

Set innodb_buffer_pool_size to min( 75% of RAM, @ipbsize );

For a useful overview see http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/. For a method of esrtimate table-by-table InnoDB buffer pool use see https://www.percona.com/blog/2010/12/09/how-well-does-your-table-fits-in-innodb-buffer-pool/.



Return to the Artful MySQL Tips page