Type-Enhancement - show Innodb_buffer_pool usage via a view

[GoogleCodeExporter]

What component are you suggesting? (view, table, function, procedure)
View

What will this new component do?
Report innodb_buffer_poll usage (Used%, Hit%, Dirty Pages%, Write Wait%)

Characteristics of the component? (e.g. NO SQL for stored function)
Attached file has SQL View which reports Used%, Hit%, Dirty Page% and Write 
Wait% for the innodb_buffer_pool.

Dependencies for component?
MySQL 5.1

Compatibility with what kind of MySQL version/distribution?
MySQL 5.1 and innodb_plugin

OS specific issues?
N/A

Do you expect this to be a heavyweight operation? Why?
No - values are gathered from information_schema.global_status

Anything else?

Please label as "Type-Enhancement"

Original issue reported on code.google.com by patrickz...@gmail.com on 22 Jul 2011 at 2:29

Attachments:

• create_innodb_usage_view.sql

[GoogleCodeExporter]

Thank you.
A few notes:

1. There is no need to do 7-8 JOINs in order to get 7-8 paramaters.
There's only (global_status UNION ALL global_variables) that should be used. 
You can get the variables by:
  MAX(IF(variable_name='innodb_buffer_pool_size', variable_value, NULL))

2. I am wondering how to put this in. See, if I get this inside, then why not 
table case use? Or key_buffer use? Or thread cache? etc.
Which is why I have this `mycheckpoint` project going on: monitoring for MySQL.
But I am not against the idea. I was thinking about providing a 
"semi-monitoring-report", in the spirit of mysqlreport (also provided by 
mycheckpoint).
But, shall I make it structured? Or human-readable?
Perhaps both?
I don't want to go the full way towards re=implementing a monitoring tool. I 
need to think this over some more, but would love to get your input.

Original comment by shlomi.n...@gmail.com on 10 Aug 2011 at 5:15

[GoogleCodeExporter]

As another note, I'm not in favor of doing global statistics. Meaning, the 'Hit 
%' column refers to the hit ratio on the buffer pool ever since the server went 
up.
This is misleading at best: such a status must be tested over a short period of 
time.
I may work this out around `global_status_diff`.

Original comment by shlomi.n...@gmail.com on 10 Aug 2011 at 5:19

[GoogleCodeExporter]

Hi.

I agree with not reimplementing a monitoring tool, I was simply submitting a 
query that I use from time to time when I want a quick snapshop of a instance.  
I have similar queries for Key Cache, Query Cache, Statement Behavior (%table 
scans, %index scans ), Thread Cache, Table Cache, etc.  I can send those to you 
if you are interested - they are all in the same format as the original 
attachment.

Also agree with global statistics for the Hit% - but in this format, it still 
gives an historical picture - how useful it really is, is debatable.

I looked at trying to get similar results using the 
common_schema.global_status_diff view but was not successful.

Thanks
Patrick

Original comment by patrickz...@gmail.com on 10 Aug 2011 at 6:54

[GoogleCodeExporter]

So right now global_status_diff only provides with GLOBAL STATUS and not GLOBAL 
VARIABLES.
I believe I will go further with this, and do provide some sort of a 10 seconds 
monitoring status. For this I'll have to add GLOBAL_VARIABLES as well, and 
provide with the usual list of suspect columns.

Thanks

Original comment by shlomi.n...@gmail.com on 10 Aug 2011 at 7:30

• Changed state: Accepted