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/.
Last updated 22 Jun 2024
