sar-sql: The Script Formerly Known as MySAR

Posted in: Technical Track

As pointed out by Schlomi Noach on my last blog, MySAR was already taken by a project related to Squid reports with MySQL. I decided then to look for a new name, and as I posted initially, I want to keep the sar prefix to describe the script’s purpose by association with the OS utility of the same name. I brainstormed many names. I liked Dave Edwards’s suggestion: SARkila, but it sounds too close to tequila, so I settled with Sheeri Cabral’s suggestion: sar-sql.

The title of the Launchpad page already reflects the change. What remains to be done is: a) change the name of the Perl script and documentation; and b) change the Launchpad URL. It is likely that I will change the name of the script when I release version 1.x (see below). I’m not sure of all the implications in Bazaar regarding the URL change, so that task will have to wait for now.

Now a little more info on the status of the project.

Bug Fixes

In the latest trunk there are two patches. One corresponds to Bug #455870, which should be fixed. I’m excited since this bug was posted by a user. (Yay!)

The 2nd patch refers to a bug that came up during an implementation in a client, in a master-slave configuration (in hindsight, I should have thought about it earlier). The snapshots from the master were being replicated to the slave, so when the script ran on the slave, the autoincrement values were in conflict and stopped replication. In the latest patch (build 16), I have added a column that records the server id, as in SHOW GLOBAL VARIABLES LIKE 'server_id' to distinguish the master and slave data. I like the fact that it is possible to query the master and slave snapshots side by side to diagnose slave lag. This change makes the last version in the trunk incompatible with the newer ones, which triggered the creation of the first README file.

I will be posting a new tarball soon.

Road Map

Believe or not, there are enough ideas in the queue to justify a road map. Here’s a summary list and you’re welcome to comment on it. I promise to review all the suggestions.

Code Review

As I mentioned above, the script currently works, but it’s far from optimal. So I’ll be changing the code base with two objectives. 1) Increase overall efficiency; and 2) facilitate future improvements. The more profound code changes will be implemented in version 0.x (current version). I believe that messy code leads to bugs, so the cleaner it is, the less likely I’ll break existing functionality while adding new one.

Command Line Syntax Changes

Adding new functionality implies that the script will perform more tasks which in turn will add overhead to each one of them. To minimize this overhead, I am working on a different command line syntax and the corresponding underlying code. This change will happen in version 1.x (the next version) since the code has to be cleaned up to be effective and works as it is. The command line syntax change will imply rewriting any wrapper script that might be in place to invoke in the crontab, so this will be the opportunity to rename the script as well.


The script is so simple that distributing a tarball is enough for now, but I’d like to have an installer and possibly a package that will take care of the Perl modules dependencies, schema initialization and user credentials verification / creation. I have no target version for this. I’ll shoot for 1.x, but it’s likely to go into 2.x.


I’m still working on plenty of use cases, best practices, and companion scripts examples. In time, the example scripts and documentation will be part of the complete package and installed along side with the main utility. In the meantime, just follow the blogs and the links to them in the Launchpad announcements page.

Community Participation

Having a user file a bug, Schlomi’s bringing up the name issue, and Sheeri contributing with her sanity checks are a great help, but I invite all of you out there to participate through comments to my blogs, bug reports, questions in the Launchpad page, and replying to my tweets.

I will be participating in OpenSQL Camp in Portland, OR next month. I’m sure I will have an opportunity to review my ideas with the old friends I’ll be meeting there. Stay tuned.

Interested in working with Gerry? Schedule a tech call.

About the Author

I have been working with databases for the last 20 years. Prior to Pythian worked for MySQL for 6 years.

6 Comments. Leave new

Justin Swanhart
October 28, 2009 5:40 pm

Seems like you are reinventing the wheel.

There are so many pluggable systems that generate and save metrics that it seems silly to reinvent another one.

Nagios – active/NRPE checks can collect metrics directly to RRD, but RRD is lossy

EMT – wrap iostat,sar,vmstat,etc and collect CSV files – but there is no way to centrally collect and aggregate them

MonAMI – pluggable monitoring agent that can forward probes to multiple places (nagios, ganglia, mysql collection etc)

Cacti – has its own poller to collect stats for CPU/MySQL/etc into RRD

And more…

Why keep reinventing the wheel?

I’m becoming a huge fan of MonAMI, as it is a single agent that pushes data to multiple systems for processing.


As I explained in my initial article: MySAR: A sar-like Utility for MySQL ( and the follow up: MySAR, a Sidekick for Other Monitoring Tools (, this script is not intended to replace any monitoring tool.

Example #1: At Pythian, we rarely have access to any of the monitoring tools that the customer might have installed, but we still need to look at a snapshot of what happened when there is an incident. This script gives us a quick and dirty way to query the global status and/or processlist at the time. It has very low overhead and it can be enabled and disabled at will on the crontab.

Example #2: The reason it has *sar* in the name is not casual. Many system administrators that use these monitoring systems, still consult its output for quick and dirty diagnosis and nobody argues about it’s usefulness.

Before starting to code, I spend some time googling for similar utilities, and found none that would provide any persistency of the values in a database.

Using *sar-sql* as a monitoring tool is not going to work. Monitoring tools provide alarms, graphics and notifications which are beyond the scope of this project.

My $.02

Justin Swanhart
October 29, 2009 12:32 am

I’m sorry. I assumed that it was collecting SAR data and persisting it into a mysql database.

Giannis Stoilis
October 29, 2009 1:44 am


I am the original MySAR maintainer. It’s great to finally see someone pick up where I left off. Good luck!


Giannis — Gerry’s tool was named “mysar”, just like your tool, but it’s completely different. When someone let him know that there was already a tool named “mysar”, Gerry changed the name, and that was one of the points of this post — Gerry’s tool is now called “sar-sql” and is different from your “mysar” tool.

Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL |
November 10, 2009 9:17 am

[…] recently, a somewhat similar project, sar-sql was announced by Gerry Narvaja (Ex-Pythian). When sar-sql (formerly MySAR) was announced, my own […]


Leave a Reply

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