Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I already have system availability monitoring for all my MySQL instances in Nagios. I'm looking to get some trending monitoring going for activity. It'd be nice to see how all the information_schema stats trend over time, to help identify sources of problems etc.
I've seen Cacti exists and seems pretty popular.
What have your experiences with Cacti been (pros/cons)?
Are there any other common solutions I should investigate?
If you're looking for graphs, Cacti (or Munin) will work. I've personally only used Munin.
However, I wouldn't put much stock in graphs to troubleshoot problems that arise. If it's showing in the graphs, it's been going on too long.
I would get familiar with the maatkit or percona tools (I think percona is a fork of maatkit) for trouble shooting problems.
There are open source tools from Percona. Try pt-mysql-summary and pt-variable-advisor.
I wrote a daemon that logs the results of SHOW GLOBAL STATUS and SHOW MUTEX STATUS every x seconds to graphite or a CSV. You may find it useful: https://github.com/9minutesnooze/mysampler. It can provide you insight into all the MySQL status information at whatever granularity you choose. There isn't any documentation yet, but I have found the output highly useful, either by pulling the CSV into gnuplot or (better) viewing the results in graphite to correlate with other metrics. I plan on extending it soon to parse SHOW ENGINE INNODB STATUS as well. I'm sure there is other data from INFORMATION_SCHEMA that would be valuable to add in, but I haven't gotten there yet.
For slightly less granular and more processed results, the Percona Cacti Templates are very good, but only show problems that have existed for a "long" time. They do show some interesting trending information and, as with most charts and graphs, the differences over time are what tend to be most enlightening.
Jet Profiler is great for monitoring MySQL. Works like SQL Server profiler, but it includes graphs and charts.
If you only want to see live, customizable trending graphs over the past 2-5 minutes, you should use MySQL Administrator. Believe it or not, although it went EOL December 18, 2009, the last version of MySQL Administrator is still available for download for free. Hey, I still it them today.
Click here and get it while Oracle isn't looking ... QUICK !!!
