Changes (as compared to 4.72) include:
Features:
* This release adds a new major feature to MONyog: Custom SQL Counters (“CSC”) and Custom SQL Objects (“CSO”). For details refer note below and the program documentation.
* Drastically improved performance in log analysis if the option to ‘replace literals’ was selected.
Bug fixes:
*If a query contained the literal substring ‘connect’ MONyog could hang during general log analysis.
More about Custom SQL Counters:
A “Custom SQL Counter” (CSC) is based on any user-defined SQL query returning a result set. The array returned by MySQL from the SQL query populates a MONyog Object (a ”Custom SQL Object” (CSO) in this case) . This is exposed as a javascript array that may be referenced in MONyog counter definitions like any MONyog object.
The primary reason for introducing this feature is to utilize information available in Information_Schema (as well as Performance_Schema of MySQL 5.5+) that is not exposed in the basic SHOW statements we have been using till now. In addition to the fact that – in some cases – I_S has more information than what SHOW returns it is also accessed using a SELECT statement what makes sorting, filtering, JOINs, use of aggregate functions etc. possible. Also MySQL ‘forks’, various plugins and third-party storage engines will often populate I_S with data not available with the ‘upstream’ MySQL server from Oracle. With this new feature you can use such information for monitoring as well.
But CSO’s are not restricted to SELECT FROM I_S and SELECT FROM P_S. Any SQL-statement returning a result set may be specified. Examples:
* Specially framed SHOW statements (using WHERE and LIKE clauses for instance).
* Maintenance statements (example: “CHECK TABLE” – but be aware of (engine-specific) LOCK behaviour with such statements).
* Queries on user data. For instance if you have a support ticket system you may frame a query telling how many support tickets have been inactive for more than 24 hours.
* Also a CALL statement may be used if it returns a single (no more and no less) result set.
This build ships with 13 pre-defined CSO’s. To understand them follow the steps:
1) Go to TOOLs tab .. Customization .. Manage Custom SQL Objects .. Add/Edit Custom SQL Objects. You will see the 13 pre-defined CSO’s display in the left menu. As an example select the ‘DiskInfo’ item. The User Defined SQL-query displays in the ‘SQL’ box. Sample interval and retention timeframe specific for this CSO may be changed as per your preference and you may specify for which MySQL server(s) this particular CSO should be collected. Also note that one or more ‘Key columns’ are defined. This/they must be a column or a set of columns returning (a) unique (set of) value(s) (similar to a UNIQUE KEY in MySQL). Without defining such properly the CSO will not be usable in the following step.
Now enable it and click ‘Save’ when you are done with all. This query now executes on the MySQL server(s) where you defined and enabled it and a MONyog object named ”DiskInfo’ will populate and be exposed for counter definitions.
We will here further enable a few more CSO’s: enable the ‘Table_Size’, Database_Size’, ‘Data_Types’ and ‘Storage_Engine’ pre-defined CSO’s.
2) Go to go to TOOLS tab .. Customization .. Manage Groups and enable the ‘Disk Info’ Group. This pre-defined group contains pre-defined CSC’s using the CSO’s you enabled in step 1).
3) Now go to Monitors/Advisors page, select the ‘Disk Info’ group that now displays at the bottom. You will see 5 new counters in that group that in various ways reference the CSO’s that we just enabled (click the counter name and next ‘Customize’ as usually to see the javascript code). You may customize those further as you would do with any counter in MONyog.
Note that some of the predefined CSO’s require specific server versions and/or configurations (like the ‘InnoDB plugin’ with MySQL 5.1 (or MySQL 5.5+), ndb_cluster enabled and even in one case the server must a PerconaServer build). If this requirement is not met, the SQL query may return NULL for specific values or it may simply return an error. In such cases MONyog will display ‘n/a’ for CSC’s based on non-populated CSO’s.
Also note that as the sample interval and retention frame setting for CSO’s are independent of the setting for built-in SHOW-based counters and also independent of each others, every CSO is handled by a seperate thread by MONyog internally. Also every retrival of a CSO will open a new connection to MySQL, retrive the result and close the connection after data have been retrieved (and MONyog itself will of course report these connections in Dashboard and in Monitors/Advisors .. Connection History .. Attempts). This implementation is chosen in order both to avoid ‘bottlenecks’ in a single connection (as MySQL unfortunately does not allow parallel queries in a single connection) and in order to reduce the number of open connections at any time as much as possible.
How many CSC’s/CSO’s you may have enabled will depend on the capabilities of your system and the number of servers registered. A lot of CSO’s collected with a short sample interval from a lot of MySQL servers will obviously create additional load (I/O and network traffic in particular). A rough guideline is that with a large number of servers and 10 or more CSO’s enabled you should not use sample intervals lower than a minute on an ‘average’ system. If you have only a few CSO’s and/or a few MySQL servers you will be able to use lower sample intervals. We are currently benchmarking this and will publish details as soon as we have and when this new feature has been optimized as much as possible.
We have one significant benchmark currently though: With 500 MySQL servers monitored from a single MONyog instance, 10 of the predefined CSO’s enabled to all MySQL servers with a sample interval of 5 minutes it runs fine causing almost insignificant additional load on a 64 bit Linux box with same hardware configuration as described as our primary test system for MONyog here in this Blog.
Stay tuned at this Blog. Likely more information about this (examples, benchmarks) will appear here soon.
Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php
Comments(1)
