MySQL Monitoring With Cacti Using Percona Monitoring Plugins (1-minute resolution)
26 Jun2013

Today, just like many times before, I needed to configure a monitoring server for MySQL using Cacti and awesome Percona Monitoring Templates. The only difference was that this time I wanted to get it to run with 1 min resolution (using ganglia and graphite, both with 10 sec resolution, for all the rest of our monitoring in Swiftype really spoiled me!). And that’s where the usual pain in the ass Cacti configuration gets really amplified by the million things you need to change to make it work. So, this is a short checklist post for those who need to configure a Cacti server with 1 minute resolution and setup Percona Monitoring Plugins on it.

Configuring Cacti for 60-seconds polling

First of all, we need to take a Cacti server, which by default is configured to do polling every 5 minutes and turn it into a 1 minute polling Cacti. Here is a list of things you need to change/verify to make sure it works properly:

  • Change your RRAs – To be able to store your metrics with 1 minute resolution, you need to change your RRA settings to make sure all newly created RRD files are properly configured. Basically you need to open “Console” -> “Data Sources” -> “RRAs” and make sure it looks like the following:

    Name Steps Rows Timespan
    Hourly (1 Minute Average) 1 10080 21600
    Daily (5 Minute Average) 5 8640 86400
    Weekly (30 Minute Average) 30 4320 604800
    Monthly (2 Hour Average) 120 2160 2678400
    Yearly (1 Day Average) 1440 720 31536000

    More information about this could be found in this really good article.

  • Change your poller settings – now you need to make sure your poller actually collects the data every minute instead of the default 5-minute intervals. To do it, you need to open “Console” -> “Settings” -> “Poller” page and change “Poller Interval” to “Every minute“.

    Now, if you want to switch your poller cron job to run every minute, you could do so and then change “Cron Interval” to be “Every minute” too. But you could keep running your cron job every 5 minutes and the poller script itself would make sure the data is collected every minute (it would collect it a few times before exiting the polling loop.

  • Rebuild your poller cache – after making any changes in your poller interval you need to clear your Cacti’s poller cache or you could start seeing all kinds of weird data on your graphs. To do that, simply go do “Console” -> “System Utilities” and click on “Rebuild Poller Cache“. The cache should be quickly updated.
  • Update your Data Templates – before being able to create any graphs with 1-minute polling interval, you need to update your data templates to actually use 60 sec RRD step and 120 sec RRD heartbeat and change associated RRAs to include “Hourly (1 Minute Average)”. To do that you need to open “Console” -> “Data Templates” and choose whatever template you want to switch to 1-minute polling. On the page you will see “Step” and “Heartbeat” fields. You need to change “Step” to 60 (from 300) and “Heartbeat” to 120 (from 600). Now select “Hourly (1 Minute Average)” in “Associated RRA’s” and click “Save”.

    For more information on how to update your data templates you could check this article.

  • Create new graphs – now you can create new graphs and all of them should be using your new settings. For the old graphs there are two options: delete them and re-create with new settings or just delete associated RRD files from disk (thus losing your historical data). There is a way to update your RRD files using rrdtool, but that is a pretty painful procedure and is left as an exercise for the reader.

Preparing Percona Monitoring Templates for 60-seconds polling

Now that your Cacti installation is working with 1-minute polling intervals, you need to change tune a few things in Percona Monitoring Templates for Cacti to make them work with it correctly. Here is a checklist:

  • Generate a new template with correct polling interval – by default all Percona templates are shipped configured for 5-minute polling intervals. To change that you need to re-generate them using a procedure explained in their documentation. If you’re using their RPM package to deploy the scripts, here is a command that worked for me:
    $ ./pmp-cacti-template
          --mpds port \
          --graph_height 200 \
          --graph_width 800 \
          --poll_interval 10 \
          --script /usr/share/cacti/scripts/ss_get_mysql_stats.php \
          /usr/share/cacti/resource/percona/definitions/mysql.def > percona-mysql-10sec-polling.xml

    This command creates a template file percona-mysql-10sec-polling.xml configuring Cacti to display MySQL graphs with a size of 800x200px and allows you to configure mysql ports for each graph separately.

  • Import your new template to Cacti – now you need to import your newly created template to Cacti. To do that you need to open “Console” -> “Import Templates“, select your template file for uploading, for the “Import RRA Settings” option you need to use the recommended option “Select your RRA settings below” and for the “Associated RRA’s” option you need to make sure you select the hourly RRA so that you can actually see your data down to a minute.
  • Configure metrics collection scripts – one thing I didn’t see mentioned in the docs (or, maybe, I’ve missed it) is configuring the scripts /usr/share/cacti/scripts/ss_*.php for the 60-seconds monitoring interval. Basically, you need to open each of those scripts and change $poll_time variable to 60.

And this is it, now your cacti is configured for 60-seconds polling interval and you could use percona templates to monitor MySQL, memcached, redis, mongo and services in your infrastructure. Enjoy!