patternsqlMinor
getting innodb buffer usage
Viewed 0 times
innodbusagegettingbuffer
Problem
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:
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?
----------------------
BUFFER POOL AND MEMORY
----------------------
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?
Solution
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free' andSHOW 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.
Context
StackExchange Database Administrators Q#215793, answer score: 4
Revisions (0)
No revisions yet.