MySAR: A sar-like Utility for MySQL

Why a New Utility?

A couple of months back, Tim Procter, Sheeri Cabral and I were discussing about how best to diagnose a MySQL server and/or tune its performance, automating the process as much as possible. The Performance Advisors from MySQL Enterprise do this, but most of our customers don’t have a subscription and Pythian’s collective experience is not necessary reflected by its rules.

In our daily work, we have used Major Heyden’s MySQL Tuner, Mark Leith’s Statpack and our own tools to review a MySQL server configuration parameters. However, all of these tools had limitations in regards of what we wanted to achieve. Our major concern was the need to keep historical data to draw conclusions based on trends and cross check with other time based tools. The conclusion of our conversations was that whatever scripts we would implement, it would need to monitor all configuration and status variables, and their evolution over time.

One day, while looking into slave lag problem, the idea of MySAR popped into my head and a few hours later I was using its first incarnation. I was able to relate the server’s I/O activity peaks with these lags and in turn, discovered that it was caused by a great number of INSERT statements coming in in waves. It was an encouraging outcome for what was nothing more than a proof of concept.

MySAR’s Goal

Major Heyden’s MySQL Tuner, Mark Leith’s Statpack, and our own scripts already do analysis and recommendations. Not wishing to reinvent the wheel, I decided that the best thing to do was to gather as much data as possible and store it in the database, from which it can be extracted to perform any kind of manipulations. My intention, then, is, to limit MySAR to be just a sampling tool. In short, to keep it simple.

What Does MySAR Do?

MySAR does nothing else than record the output of SHOW GLOBAL STATUS, SHOW GLOBAL VARIABLES, and SHOW FULL PROCESSLIST commands — or a configurable subset of their output. The idea is to invoke MySAR from the crontab (or similar tool on Windows) at regular intervals. Data older than 30 days is purged by default to avoid accumulating too much data on disk. We usually run it every ten minutes so it will be roughly in sync with sar’s output.

Where to Get MySAR and Submit Feedback

I have decided to use Bazaar and Launchpad to track the project: https://launchpad.net/mysar. I found it to be the most convenient alternative. Consider the code to be in the alpha stage—not necessarily feature-complete and with bugs. The trunk branch has the most stable version (the current development branch has been merged into the trunk).  But if you just want the latest public tarball, you can use the download link in the project Launchpad page.

Documentation

The documentation is provided in plain text, HTML, and perldoc formats (perldoc mysar.pl) as part of the tarball. It is complete since I use it as a specification document and gets written before the code is complete. You can download the HTML manual (click the “download this file” link on that page).

Please record bugs, questions, suggestions in the appropriate sections of the launchpad site and I’ll address them as best I can. I invite the community at large to contribute with use cases and examples in the Answers section, and I’ll post the most creative contributions as FAQs (along with the real FAQs). I don’t expect too much in the beginning. Hopefully the contributions will start coming in once it proves as useful as it has been for us so far.

Installation

At this point the requirement are only the DBI and DBI::mysql Perl modules. To install follow these steps:

1. Download the latest tarball from the download link on the Launchpad page.
2. Untar on any subdirectory using:

   tar -xzf mysar.tar.gz

3. Create a schema to hold it’s tables (or use an existing schema with the –database option). The default schema name is mysar. Example:

    CREATE DATABASE mysar;

4. Make sure the user already exists and has full access to this schema. Example to grant the permissions:

   GRANT ALL ON mysar.* TO 'username'@'localhost'

5. Run at least once to create the tables. The mimimum command line required is:

   mysar --user username --pass password --status / variables

   Either –status or –variables should be used. Currently there is a bug (Launchpad’s bug #441006) when you run with –status for the first time, which can be safely ignored.

I will be blogging different use cases based on real cases in the near future, including how to easily draw colorful graphics using the Google Charts API, so stay tuned.