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 For a method of esrtimate table-by-table InnoDB buffer pool use see

Return to the Artful MySQL Tips page