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 ;




Return to the Artful MySQL Tips page