Pythian Group

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. Read the rest of this entry . . .

I’m sure that most people have at least one of the tools listed in Ronald Bradford’s article: Monitoring MySQL Options. Many of these tools, such as Nagios and Cacti, also monitor the operating system. However, in the same way that a quick look at sar’s output can give you some insight on the OS, with MySAR you can do the same for the MySQL server. This is especially useful when  it is not possible to access a monitoring tool’s graphic interfaces.

What Was Going On Around 2:30pm?

This is a question a customer asked us. To answer it we ran MySAR for a few days and queried the results for analysis. Looking at the data, we determined that the number of INSERT operations was significantly higher than any other, so we queried for the Com_insert status values. Com_insert is a counter that accumulates the number of INSERTs issued since the last server start (or since the last FLUSH STATUS command). For details on the variables available check Chapter 1. mysqld Options/Variables Reference.

The initial query we used was: Read the rest of this entry . . .

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.

Read the rest of this entry . . .