getting innodb buffer usage

Posted on

Question :

I am running MySQL 5.5.52 I am trying to see how much of my innodb buffer pool is used. If I run ‘show engine innodb status’ I see this:

Total memory allocated 10731520; in additional pool allocated 0
Dictionary memory allocated 1823022
Buffer pool size   639
Free buffers       0
Database pages     638
Old database pages 215
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16622990, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19765731, created 109264, written 174555
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Is there anything in there that tells me how much is used?

If I run MySQL workbench it shows InnoDB Buffer Usage: 79.2% but I
want a way to get this programmatically with SQL not just from a GUI.
How can I do that?

Answer :

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free' and
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total' will enable you to derive this percentage.

Keep in mind a page_free of 0 isn’t fatal or even necessary bad – it just means every once in a while a read will come from disk or page cache. Its the rate at this occurs that is more important to performance of related to the size of the innodb_buffer_pool.

Leave a Reply

Your email address will not be published. Required fields are marked *