Request: What do you want in a OpenSource MySQL Monitoring solution?

What would you like to see in a free enterprise-grade monitoring system for your daily MySQL needs?I’m rewriting Monolith – MySQL DBA Console from the ground up. This will be version 2 and I would like to get some input from the global MySQL community.So far I am going with the following; comment with any improvements/additions.

  • Variable interval polling of server statistics
  • Over 50 different alerts (see list below)
  • Graphing of various server statistics (see list below)
  • Tuning recommendations with cnf file changes to apply to server
  • Change control documents for recommended performance/security tuning
  • Threshold based alerting with multiple alert groups: info,warn,critical
  • Sorting/ordering of servers via groups. ie: client -> dev,stage,prod
  • RSS feeds for each alert group
  • XML export with user defined fields for external applications (API of sorts)
  • Slave server alerts, IO thread / SQL thread, seconds behind master (with threshold)
  • Various general stats: version, system ram size, # of schema, default table type
  • Ability to view current cnf file from server
  • Ability to view current global variables as a list
  • Email / pager reporting for alerts (choose warn/crit alerts to report on)

Alerts - can be enabled or disabled for reporting on a per server basis

  • number of connection failures (threshold)
  • number of connections errors (threshold)
  • binary logging not enabled
  • sync_binlog not set
  • max_used_connections too high compared to max_connections (ratio over 85% utilization)
  • query cache size too small: improper utilization
  • query cache size too large: improper utilization
  • memory usage over 85% of system ram
  • table scans excessive
  • tmp table to disk ratio too high
  • innodb buffer too small
  • innodb buffer too large (resources can be used elsewhere)
  • key buffer too large (resources can be used elsewhere)
  • key buffer too small
  • sort buffer too small
  • sort buffer too large (resources can be used elsewhere)
  • join buffer too small
  • join buffer too large (resources can be used elsewhere)
  • open_files_limit too small compared to open_files usage ratio
  • table locks too high
  • table cache too small
  • table cache too large (resources can be used elsewhere)
  • thread cache too small
  • thread cache too large (resources can be used elsewhere)
  • binlog size too small
  • excessive disk tmp table usage
  • flush time not zero value
  • indexes not being utilized properly
  • innodb doublewrite buffer enabled
  • innodb flush method not properly set
  • innodb transaction isolation level not properly set for ACID
  • innodb log wait time too high
  • conncurrent insert not set correctly
  • query cache not enabled
  • table locks too high
  • thread cache not enabled
  • concurrent queries too high (threshold)
  • binary logs not set to auto-purge
  • binary logging not enabled
  • slave not active if server is set to slave
  • slave sql thread stopped
  • slave io thread stopped
  • slave behind master too long (threshold)
  • slave not set as read-only
  • slave relay logs not automatically purged
  • account has global privs
  • account set to old_password
  • accounts able to be added with empty password
  • account has grant option
  • root can login remotely
  • root has no password
  • blank username detected
  • test database exists
  • usage user exists
  • general query log enabled
  • CPU usage over threshold
  • Load Average over threshold

Graphs - default

  • Query cache utilization, in/out hit ratio
  • Temp tables usage
  • Temp files usage
  • Open tables usage
  • Thread cached / created
  • Connections
  • Query rate
  • Data size, Index size, overall size
  • Uptime
  • CPU usage
  • I/O usage
  • Load average
  • Data transfer in/out
  • Memory allocation
  • Swap usage
  • Slave delay (if configured as a slave)

As mentioned before, this is a OSS application that has no license or subscription fees and will be updated on a regular basis. It’s a mix of PHP for the web side and Perl for the system polling and reporting processes. The installer will be new, web based, and a simple process, much improved from the current method.Please add your comments now, as I am drawing up plans this week for version 2. :)

13 thoughts on “Request: What do you want in a OpenSource MySQL Monitoring solution?

  1. ngocha85 says:

    Great idea!

  2. Matt,

    My big suggestion would be to separate storage and graphics. There are over 200 counters in MySQL alone and even more on various other levels. It is great to have these stored and being available for graphics if there is a problem.

    Plus it is very nice to have “archiving” of some things at periodic intervals – processlist, show innodb status etc can be very helpful.

    Another note about monitoring system – make sure config can be easily scriptable or there is an API – you really do not want to manually configure things on more than few servers.

  3. Matic says:

    I’d very much like to see support for MySQL Cluster. I’d also like to see query statistics. Query statistics are much like web server statistics, which show which file/vhost got the most hits. In MySQL case, it could show how many queries are executed in certain tables. It could also show which query executed the most times (and therefore, we could see which one needs optimization in application side, so it doesn’t get executed that many times). The variables in the query must of course be variable, so if once we select user_id = 5 and once user_id = 31, it should be counted together, as the same type of query.

  4. PaT says:

    I’m currently writing my own web based monitoring system. I already have, or planned to have, most features you discribe but I added “real-time” graph, updating its value every second. Many Javascript framework (or flash library) give easy tool to do this. That’s cool to have your web based monitoring system looks like a realtime application.

    I don’t know Monolith, maybe it already have that kind of graph..

  5. admin says:

    Peter: Great ideas. Monolith is already archiving all global statistics in the reporting_variables table so this will be a continuing trend. As for innodb status and process list, those are added to the list.

    PaT: I would advise against the real-time stats updating for high traffic servers. Running the “show global status” command or selecting things from INFORMATION_SCHEMA every second will certainly impact performance on a busy server.

    I’ll add a variable to the config that allows the user to specify the number of minutes for the servers to be polled, but only minutes – not seconds.

    Matic: That’s a great idea, however I think it’s best left to people that are already writing such tools. I believe Maatkit has a query analyzer available. I will add the query analysis graphs for select/insert/update/delete.

  6. PaT says:

    I totally agree. I said every second but that is completely configurable. But I believe it can be very useful in many cases. And the monitoring tool won’t hit the server unless you want/need it. That must be used wisely..

  7. NuLL says:

    God – this sounds great :)

  8. Keith Murphy says:

    Matt,

    I have followed your project for some time. Thanks for all the hard work!!

    Sounds like you really have things moving along well. I would think that it would be very good to add some more “sys admin” type of monitoring….at least server load as a rough metric of usage, maybe raw i/o stats of some type..

  9. admin says:

    Keith, good idea. Added CPU/Load Avg to reporting and graphing.

    Giuseppe, that’s going to be a very useful project.

  10. Henrik Ingo says:

    Integration with “general purpose” monitoring tools, eg. Nagios. Especially for real-time monitoring, why should database monitoring be it’s own tool when you try to see everything else in one place?

    For some things like say you develop a query analyzer or some other “drill down” functionality, then a dedicated tool is warranted.

  11. admin says:

    Henrik: Nagios can provide simple host availability checks for mysql via port checking. However, integrating a complex performance monitoring and usage analytics application such as Monolith with Nagios doesn’t make much sense. If Nagios offered all of the features that I’ve listed then I wouldn’t be taking to time to write this app.

    However, using Monolith to send email notifications to Nagios (or other systems that can parse emails) for alerts is going to be possible and is already in the works. That way you can make use of the escalation features of Nagios while still getting the features of Monolith for reporting and analytics.

  12. [...] Reid asks his readers, What do you want in a OpenSource MySQL Monitoring solution? He asks because, as he says, “I’m rewriting Monolith – MySQL DBA Console from [...]

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>