Track buffer pool use

from the Artful MySQL Tips List

During execution of demanding queries, up to version 5.6 run this ...

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct 
FROM 
  (SELECT variable_value num FROM information_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
  (SELECT variable_value num FROM information_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

and in 5.7 run this ...

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct 
FROM
  (SELECT variable_value num FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
  (SELECT variable_value num FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Ordinarily, innodb_buffer_pool_size should use up to 75% of RAM available to MyZQL. If other engines are used significantly on the server, you may need to set aside RAM for them too. Estimate the proportion of RAM used by InnoDB with this query ...

SELECT 
  engine,
  count(*) as Tables,
  concat(round(sum(table_rows)/1000000,2),'M') Rows,
  concat(round(sum(data_length)/(1024*1024*1024),2),'G') Data,
  concat(round(sum(index_length)/(1024*1024*1024),2),'G') Indexes,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') Total,
  round(sum(index_length)/sum(data_length),2) IdxFrac
FROM information_schema.tables
WHERE engine is not null 
  AND table_schema not in ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine
ORDER BY sum(data_length+index_length) DESC ;

Last updated 11 Jun 2024

Return to the Artful MySQL Tips page