The MySQL sys and ps_helper schemas contain a collection of views, functions and procedures that can be used by administrators to analyze and manage MySQL Servers and their activity.
Many of the objects are targeted against the performance_schema database within MySQL, and provide extra summary data around everyday DBA/Developer/operations use cases.
Both repositories are available on Github:
- MySQL sys schema – contains the most stable set of objects.
- dbahelper / ps_helper – contains experimental objects.
See the MySQL sys schema README file for examples and usage for all objects.
Ah! Looks like great work, and a much needed one!
Also means a feature-not-needed in common_schema (was planning similar content for a while, but with no real action and no real comprehension of the scope of work on this).
Just to be an the official side: are these files licensed under some particular license? (none that I would see)
Hey Shlomi
No license, free and open.. Let’s call it WTFPL.. π
Mark
WTFPL
π
That’s a new one for me!
That’s really nice, especially with 5.6.
Shouldn’t there be a cleaner way of converting between different time units than ROUND(somevalue/1000000, 2) and betwen KB/MB/GB/TB than ROUND(some_bytes/1024/1024,2) ? a BYTES_TO_MB(some_bytes) would be better readable and less error prone.
A bounch of functions for unit conversions would be nice. And then those can be converted to a MySQL fuction or UDF if more performance is needed.
For PostgreSQL there is PGXN (PostgreSQL Extension Network) but for MySQL there isn’t some nice way of managing extensions like common_schema, ps_helper, etc. Yes there is forge.mysql.com, but that’s different.
Good point, I’ll add a format_bytes function, that figures out the best format based on the size..
Thanks for the feedback!
I just noticed that the examples are also in the SQL dump files. Nice! (If only there was a javadoc like tool for SQL…)
There is, it is called ROBOdoc:
http://rfsber.home.xs4all.nl/Robo/
All the docs for the public facing functions in Flexviews were created with it:
http://flexviews.googlecode.com/svn/trunk/manual.html#robo2
Hi Mark,
These views are very helpful.
It would be nice to have views with both formatted and unformatted numbers.
Would you consider adding extra “raw” fields to the views, or add extra views with unformatted (ie, “raw”) numbers? For example perhaps have:
top_global_io_consumers_by_latency
and
top_global_io_consumers_by_latency_raw
The _raw version would have the same columns but without human readable formatting. Or, add _raw columns like avg_read_raw to top_global_io_consumers_by_latency, while also keeping the avg_read column in the current human readable form.
This would make it easy to “snapshot” these views periodically and compare the contents over time without having to deal with the numbers changing from MB -> GB or vice versa when numbers are near a boundary between units.
I am considering this (it’s been asked for a few times now), and will likely include something like it in the next revision.
I would like to suggest an alternative implementation that may be cleaner to implement.
How about a procedure called enable_batch_mode() / disable_batch_mode() which sets a session variable that the helpers can use?
Hrm, I hadn’t considered that – you mean something that the formatting functions would check, and conditionally return either raw or formatted?
In any case, I’ve now added _raw views to ps_helper in the latest version on Github, so this is a little late..
I’m interested in what other users of ps_helper think of this idea however, if you’re interested in this approach, rather than the _raw views, vote here!
Excellent intro to some meaningful data. It’s been a while since I looked at P_S. This really helped
Great work!
Only one remark (a small little bug):
the COMMENT for procedure analyze_statement_digest uses double quotes (“…”) as opposed to standard single quotes (‘…’). Because of this, the script wouldn’t execute to completion on my server because I use ansi quotes (set sql_mode = ‘ANSI_QUOTES,…’).
Again, great work.
Thanks! I’ll try and fix this in my next update (should be doing one soon, just have “real work” getting in the way at the moment).
This is now fixed:
https://github.com/MarkLeith/dbahelper/commit/80706ff874067071d6a1a33bc9aefe3b3f9e559f
I would suggest you update unused indexes to exclude mysql
select * from schema_unused_indexes where object_schema !=’mysql’;
Also, in MySQL 5.6, the 5.5. query select * from latest_file_io limit 10; returns zero rows. The underlying table events_waits_history_long is empty. Is this a specific metric that has to be enabled on top of the 5.6 defaults?
This is now done on the latest version in Github.
Good morning,
These comments are 4+ years old and the GiT files are also 4+ years old. Is this a dead project or is it still maintained?
Thanks
Joe
Hey Joseph,
Indeed, the project has now become an official MySQL project:
https://github.com/mysql/mysql-sys
And has been included in the mainline MySQL product since the 5.7 GA:
http://mysqlserverteam.com/the-mysql-sys-schema-in-mysql-5-7-7/
https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html