I started mycheckpoint back in 2009, as a free & open source lightweight monitoring tool for MySQL. Over some years it evolved and became an actual (lightweight) monitoring solution, used by many. It has a unique and original design, which, alas, is also its bane.

mycheckpoint uses the relational model & SQL to store and query monitored metrics. This leads to quite a sophisticated service, which can make practically anything visible to the user. The raw data is just numbers. but with some SQL-Fu one can generate charts out of it,  (interactive ones as well), human readable reports and full blown email messages. It is still the only common solution I’m aware of that keeps track of variable changes and provides with clear “what changed, when, from value & to_value”. I caught many deployment bugs by just observing this. It’s a single file that provides with full blown HTTP service, alerting, mail notifications, multi-database monitoring, custom monitoring queries, query execution time monitoring, OS metrics, …

While developing mycheckpoint I learned a lot on MySQL status & configuration, complex SQL queries, Python, linux, packaging and more. I got a lot of feedback from users, as I still do (thank you!). Didn’t always manage to fix all bugs or answer all questions.

The design of mycheckpoint does not meet today’s reality. Heck, today there are more counters & variables than possible table columns. The schema-per-monitored-instance design makes for simplicity, but does not fare well with dozens or hundreds of servers to monitor. There is no cross-instance aggregation or visualization of data. The per-10 minute aggregation is too rough. There isn’t a test suite.

Some of the above issues can be fixed, and if you like, the source code is still freely available. I’ll even migrate the entire SVN to GitHub at some stage. But I believe the current state might only be good for small scale deployments;  not something you would consider to scale up with.

For me, there’s nothing more motivating in code development than knowing the code will go public. The efforts in making the code look as best it can, as easily deployable as possibly can, with good documentation, makes for a lot of effort – but very satisfying. Open Source FTW!!!1

If you’re unfamiliar with mycheckpoint, well, the one minute sales pitch is: it’s a free and open source monitoring tool for MySQL, which is extremely easy to install and execute, and which includes custom queries, alerts (via emails), and out of the box HTTP server and charting.

This is mostly a maintenance release, with some long-time requested features, and of course solved bugs. Here are a few highlights:

• Supports MariaDB and MySQL 5.6 (issues with new variables, space padded variables, text-valued variables)
• Supports alerts via function invocation on monitored host (so not only checking alerts via aggregated data like ‘Seconds_behind_master’ but also by SELECT my_sanity_check_function() on monitored instance). See alerts.
• Supports single-running-instance via “–single” command line argument
• Supports strict sql_mode, including ONLY_FULL_GROUP_BY, overcoming bug #69310.
  
• Supports sending of pending email HTML report
• Better re-deployment process
• Better recognizing of SIGNED/UNSIGNED values
• Some other improvements in charting, etc.

mycheckpoint is released under the BSD license.

Downloads are available from the project’s page.

So mycheckpoint works in a completely different way. On one hand, it doesn’t bother with caching. On the other hand, it doesn’t bother with re-reads of data.

There are no staleness issues, the data is consistent as it can get (you can never get a completely atomic read of everything in MySQL), and you can issue as many calculations as you want at the price of one take of monitoring. As in Sheere’s example, you can run Threads_connected/max_connections*100, mix status variables, system variables, meta-variables (e.g. Seconds_behind_master), user-created variables (e.g. number of purchases in your online shop) etc.

mycheckpoint‘s concept is to store data. And store it in relational format. That is, INSERT it to a table.

A sample-run generates a row, which lists all status, server, OS, user, meta variables. It’s a huge row, with hundreds of columns. Columns like threads_connected, max_connections, innodb_buffer_pool_size, seconds_behind_master, etc.

mycheckpoint hardly cares about these columns. It identifies them dynamically. Have you just upgraded to MySQL 5.5? Oh, there’s a new bunch of server and status variables? No problem, mycheckpoint will notice it doesn’t have the matching columns and will add them via ALTER TABLE. There you go, now we have a place to store them.

Running a formula like Threads_connected/max_connections*100 is as easy as issuing the following query:

SELECT Threads_connected/max_connections*100 FROM status_variables WHERE id = ...

Hmmm. This means I can run this formula on the most recent row I’ve just added. But wait, this also means I can run this formula on any row I’ve ever gathered.

With mycheckpoint you can generate graphs retroactively using new formulas. The data is there, vanilla style. Any formula which can be calculated via SQL is good to go with. Plus, you get the benefit of cross referencing in fun ways: cross reference to the timestamp at which the sample was taken (so, for example, ignore the spikes generated at this and that timeframe due to maintenance. Don’t alert me on these), to system issues like load average or CPU usage (show me the average Seconds_behind_master when load average is over 8, or the average load average when slow query rate is over some threshold. You don’t do that all the time, but when you need it, well, you can get all the insight you ever wanted.

Actually storing the monitored data in an easy to access format allows one to query, re-query, re-formulate. No worries about caching, you only sample once.

For completeness, all the above is relevant when the data is of numeric types. Other types are far more complicated to manage (the list of running queries is a common example).

The matter of keeping the documentation faithful is a topic of interest. I’d like to outline a few documentation bundling possibilities, and the present the coming new documentation method for common_schema. I’ll talk about any bundling that is NOT man pages.

High level: web docs

This is the initial method of documentation I used for openark kit and mycheckpoint. It’s still valid for mycheckpoint. Documentation is web-based. You need Internet access to read it. It’s in HTML format.

Well, not exactly HTML format: I wrote it in WordPress. Yes, it’s HTML, but there’s a lot of noise around (theme, menus, etc.) which is not strictly part of the documentation.

While this is perhaps the easiest way to go, here’s a few drawbacks:

• You’re bound to some framework (WordPress in this case)
• Docs are split between MySQL database (my underlying WordPRess storage) & WordPress files (themes, style, header, footer etc.)
• Documentation is separate from your code – they’re just not in the same place
• There is no version control over the documentation.

The result is a single source of documentation, which applies to whatever version is latest. It’s impossible to maintain docs for multiple versions. You must manually synchronize your WordPress updates with code commits (or rather – code release!).

Mid level: version controlled HTML docs

I first saw this approach on Baron’s Aspersa gets a user manual post. I loved it: the documentation is HTML, but stored as part of your project’s code, in same version control.

This means one can browse the documentation (openark kit in this example) exactly as it appears in the baseline. Depending on your project hosting, one may be able to do so per version.

The approach has the great benefit of having the docs tightly coupled with the code in terms of development. Before committing code, one updates documentation for that code, then commits/releases both together.

You’re also not bound to any development framework. You may edit with vim, emacs, gedit, bluefish, eclipse, … any tool of your choice. It’s all down to plain old text files.

Mid level #2: documentation bundling

One thing I started doing with common_schema is to release a doc bundle with the code. So one can download a compressed bundle of all HTML files. That way one is absolutely certain what’s the right documentation for revision 178. There’s no effort about it: the docs are already tightly coupled with code versions. Just compress and distribute.

Low level: documentation coupled with your code

Perl scripts can be written as Perl modules, in which case they are eligible for using the perldoc convention. You code your documentation within your script itself, as comment. Perldoc can extract the documentation and present in man-like format. Same happens with Python’s pydoc. Baron’s When documentation is code illustrates that approach. Maatkit (now Percona Toolkit) has been using it for years.

This method has the advantage of having the documentation ready right within your shell. You don’t need a browser, nor firewall access. The docs are just there for you in the same environment where you’re executing the code.

SQL Low level: CALL for help()

common_schema is a different type of project. It is merely a schema. There’s no Perl nor Python. One imports the schema into one’s MySQL server.

What’s the low-level approach for this type of code?

For common_schema I use three levels of documentation: the mid-level, where one can browse through the versioned docs, the 2nd mid-level, where one can download bundled documentation, and then a low-level approach: documentation embedded within the code.

MySQL’s documentation is also built into the server: see the help_* tables within the mysql schema. The mysql command line client allows one to access help by supporting the help command, e.g.

mysql> help create table;

The client intercepts this command (this is not server side command) and searches through the mysql.help_* docs.

With common_schema, I don’t have control over the client; it’s all on server side. But the code being a schema, what with stored routines and tables, it’s easy enough to set up documentation.

As of the next version of common_schema, and following MySQL’s method, common_schema provides a help table:

DESC help;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| topic        | varchar(32) | NO   | PRI | NULL    |       |
| help_message | text        | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

And a help() procedure, so that you can call for help(). The procedure will look for the best matching document based on your search expression:

root@mysql-5.1.51> CALL help('match');
+-------------------------------------------------------------------------------+
| help                                                                          |
+-------------------------------------------------------------------------------+
|                                                                               |
| NAME                                                                          |
|                                                                               |
| match_grantee(): Match an existing account based on user+host.                |
|                                                                               |
| TYPE                                                                          |
|                                                                               |
| Function                                                                      |
|                                                                               |
| DESCRIPTION                                                                   |
|                                                                               |
| MySQL does not provide with identification of logged in accounts. It only     |
| provides with user + host:port combination within processlist. Alas, these do |
| not directly map to accounts, as MySQL lists the host:port from which the     |
| connection is made, but not the (possibly wildcard) user or host.             |
| This function matches a user+host combination against the known accounts,     |
| using the same matching method as the MySQL server, to detect the account     |
| which MySQL identifies as the one matching. It is similar in essence to       |
| CURRENT_USER(), only it works for all sessions, not just for the current      |
| session.                                                                      |
|                                                                               |
| SYNOPSIS                                                                      |
|                                                                               |
|                                                                               |
|                                                                               |
|        match_grantee(connection_user char(16) CHARSET utf8,                   |
|        connection_host char(70) CHARSET utf8)                                 |
|          RETURNS VARCHAR(100) CHARSET utf8                                    |
|                                                                               |
|                                                                               |
| Input:                                                                        |
|                                                                               |
| * connection_user: user login (e.g. as specified by PROCESSLIST)              |
| * connection_host: login host. May optionally specify port number (e.g.       |
|   webhost:12345), which is discarded by the function. This is to support      |
|   immediate input from as specified by PROCESSLIST.                           |
|                                                                               |
|                                                                               |
| EXAMPLES                                                                      |
|                                                                               |
| Find an account matching the given use+host combination:                      |
|                                                                               |
|                                                                               |
|        mysql> SELECT match_grantee('apps', '192.128.0.1:12345') AS            |
|        grantee;                                                               |
|        +------------+                                                         |
|        | grantee    |                                                         |
|        +------------+                                                         |
|        | 'apps'@'%' |                                                         |
|        +------------+                                                         |
|                                                                               |
|                                                                               |
|                                                                               |
| ENVIRONMENT                                                                   |
|                                                                               |
| MySQL 5.1 or newer                                                            |
|                                                                               |
| SEE ALSO                                                                      |
|                                                                               |
| processlist_grantees                                                          |
|                                                                               |
| AUTHOR                                                                        |
|                                                                               |
| Shlomi Noach                                                                  |
|                                                                               |
+-------------------------------------------------------------------------------+

I like HTML for documentation. I think it’s a good format, provided you don’t start doing funny things. Perhaps TROFF is more suitable; certainly more popular on Unix machines. But I already have everything in HTML. So, what do I do?

My decision was to keep documentation in HTML, and use the handy html2text tool to do the job. And it does it pretty well! The sample you see above is an automated translation of HTML to plain text.

I add a few touches of my own: SELECTing long texts is ugly, whether you do it via “;” or “\G“. The help() routine breaks the text by ‘\n‘, returning a multi row result set. The above sample makes for some 60+ rows, nicely formatted, broken from the original single text appearing in the help table.

So now you have an internal help method for common_schema, right where the code is. You don’t have to leave the command line client in order to get help.

Giuseppe offered me the idea for this, even while my own thinking about it was in early stages.

The next version of common_schema will be available in a few weeks. The code is pretty much ready. I just need to work on, ahem…, the documentation.

We ended last post with the following chart:

http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL

which has a nice curve, and a proper y-legend, but incorrect x-legend and no ticks nor grids.

To date, Google Image Charts do not support time-series charts. We can’t just throw timestamp values and expect the chart to properly position them. We need to work these by hand.

This is not easily done; if our input consists of evenly spread timestamp values, we are in a reasonable position. If not, what do we do?

There are several solutions to this:

• We can present whatever points we have on the chart, making sure to position them properly. This makes for an uneven distribution of ticks on the x-axis, and is not pleasant to watch.
• We can extrapolate values for round hours (or otherwise round timestamp resolutions), and so show evenly spread timestamps. I don’t like this solution one bit, since we’re essentially inventing values here. Extrapolation is nice when you know you have nice curves, but not when you’re doing database monitoring, for example. You must have the precise values.
• We can do oversampling, then group together several measurements within round timestamp resolutions. For example, we can make a measurement every 2 minutes, yet present only 6 measurements per hour, each averaging up 10 round minutes. This is the approach I take with mycheckpoint.

The latest approach goes even beyond that: what if we missed 30 minutes of sampling? Say the server was down. We then need to “invent” the missing timestamps. Note that we invent the timestamps, we do not invent values. We must present the chart with missing values on our invented timestamps.

I may show how to do this in a future post. Meanwhile, let’s simplify and assume our values are evenly spread.

Sample data

We use google_charts.sql. Note that the timestamp values provided in Part I of this post is skewed, so make sure to use this file.

x-axis values

We use chxl to present with x-axis values. We may be tempted to just list all values. Would that work?

Sadly, no, for two reasons:

1. Google is not smart enough; whatever we throw at it, it will try to present. So, if we have 288 rows, that’s 288 x-axis values. Not enough room, to be sure! Smarter implementations would automatically hide some values, so as only to present with non-overlapping values.
2. Our URL will turn out to be too long. Remember: 2048 characters is our maximum limit for GET request!

Also, we must format our timestamp to be of minimal width. In our example, we have a 24 hour range. We therefore present timestamps in hh:MM format. So, a naive approach would be to:

SELECT
  CONCAT(
    'http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,',
    ROUND(min_value, 1), ',',
    ROUND(max_value, 1),
    '&chd=s:',
    GROUP_CONCAT(
      IF(
        data IS NULL,
        '_',
        SUBSTRING(
          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
          1+61*(data - min_value)/(max_value - min_value),
          1
        )
      )
      SEPARATOR ''
    ),
   '&chxl=0:|',
    GROUP_CONCAT(
      DATE_FORMAT(ts, '%H:%i')
      SEPARATOR '|'
    )
  ) FROM chart_data, chart_data_minmax

The resulting URL is just too long.

Solution? Let’s only consider round hour timestamps! Our next attempt looks like this (we also throw in chxs, to show ticks):

SELECT
  CONCAT(
    'http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,',
    ROUND(min_value, 1), ',',
    ROUND(max_value, 1),
    '&chd=s:',
    GROUP_CONCAT(
      IF(
        data IS NULL,
        '_',
        SUBSTRING(
          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
          1+61*(data - min_value)/(max_value - min_value),
          1
        )
      )
      SEPARATOR ''
    ),
   '&chxs=0,505050,10,0,lt',
   '&chxl=0:|',
    GROUP_CONCAT(
      IF(
        MINUTE(ts) = 0,
        DATE_FORMAT(ts, '%H:%i'),
        NULL
      )
      SEPARATOR '|'
    )
  ) FROM chart_data, chart_data_minmax

and results with:

http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&chxs=0,505050,10,0,lt&chxl=0:|00:00|01:00|02:00|03:00|04:00|05:00|06:00|07:00|08:00|09:00|10:00|11:00|12:00|13:00|14:00|15:00|16:00|17:00|18:00|19:00|20:00|21:00|22:00|23:00

Too messy, isn’t it?

A word about ticks

You would think: OK, then, let’s just present every 4 round hours timestamps. But there’s a catch: a tick will show only when there’s an x-axis value. It’s nice to have a tick for every hour, but we only want to present values every 4 hours.

Fortunately, we can provide with an unseen value: a space (URL encoded as ‘+‘). So we complicate things up a bit on the chxl to read:

SELECT
  CONCAT(
    'http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,',
    ROUND(min_value, 1), ',',
    ROUND(max_value, 1),
    '&chd=s:',
    GROUP_CONCAT(
      IF(
        data IS NULL,
        '_',
        SUBSTRING(
          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
          1+61*(data - min_value)/(max_value - min_value),
          1
        )
      )
      SEPARATOR ''
    ),
   '&chxs=0,505050,10,0,lt',
   '&chxl=0:|',
    GROUP_CONCAT(
      IF(
        MINUTE(ts) = 0,
        IF(
          HOUR(ts) MOD 4 = 0,
          DATE_FORMAT(ts, '%H:%i'),
          '+'
        ),
        NULL
      )
      SEPARATOR '|'
    )
  ) FROM chart_data, chart_data_minmax

and get:

http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&chxs=0,505050,10,0,lt&chxl=0:|00:00|+|+|+|04:00|+|+|+|08:00|+|+|+|12:00|+|+|+|16:00|+|+|+|20:00|+|+|+

OK, I cheated

Who says sample data starts with a round hour? We have that hidden assumption here, since the first tick is necessarily a round hour in our code. Yet our data may start at 12:35, for example. Sorry, you’ll have to dig into mycheckpoint’s source code to see a thorough solution. It’s just too much for this post.

Grids

Let’s wrap this up with grids. Grids work by specifying the step size (in percent of overall height/width) and initial offset (again, in percent).

Wouldn’t it be nicer if grids were automatically attached to ticks? I mean, REALLY! What were those guys thinking? (I know, they’re doing great work. Keep it up!)

Problem is, I have no idea how Google chooses to distribute values on the y-axis. I don’t know where y-axis ticks will be placed. So on y-axis, I just choose to split charts to 4 even parts, and draw horizontal grids between them. Percent is 25 (100/4), offset is 0.

But I do have control over the x-axis. In our case, I know how many ticks we’ll be having. Plus, I made life easier by assuming we start with a round hour, so no offset is required.

Umm… How many ticks do we have? Easy: the number of round hours. This can be calculated by: SUM(MINUTE(ts) = 0. Actually, we need to take 1 off.

We now build the chg parameter:

SELECT
  CONCAT(
    'http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,',
    ROUND(min_value, 1), ',',
    ROUND(max_value, 1),
    '&chd=s:',
    GROUP_CONCAT(
      IF(
        data IS NULL,
        '_',
        SUBSTRING(
          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
          1+61*(data - min_value)/(max_value - min_value),
          1
        )
      )
      SEPARATOR ''
    ),
   '&chxs=0,505050,10,0,lt',
   '&chxl=0:|',
    GROUP_CONCAT(
      IF(
        MINUTE(ts) = 0,
        IF(
          HOUR(ts) MOD 4 = 0,
          DATE_FORMAT(ts, '%H:%i'),
          '+'
        ),
        NULL
      )
      SEPARATOR '|'
    ),
   '&chg=', ROUND(100.0/((SUM(MINUTE(ts) = 0) -1)), 2), ',25,1,2,0,0'
  ) FROM chart_data, chart_data_minmax

and get:

http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL&chxs=0,505050,10,0,lt&chxl=0:|00:00|+|+|+|04:00|+|+|+|08:00|+|+|+|12:00|+|+|+|16:00|+|+|+|20:00|+|+|+&chg=4.35,25,1,2,0,0

Phew!

Conclusion

So we haven’t worked on offsets. And, this is a single line chart. What about multiple lines? Legend? The following chart:

is harder to achieve. I’m leaving this up to you!

I’m going to present the algorithm I use in mycheckpoint, a MySQL monitoring utility, which generates Google charts by raw data using views. An example of such chart follows:

http://chart.apis.google.com/chart?cht=lc&chs=370x180&chts=303030,12&chtt=Latest+24+hours:+Nov+9,+05:50++-++Nov+10,+05:50&chf=c,s,ffffff&chdl=Rentals+rate:+custom_1_psec&chdlp=b&chco=ff8c00&chd=s:GDGKGFLFGMJHRLMPPNULJRPLTOPRUMYPPVRNbQUSUSbSNWUOfSWTObVSUVWSVYVPbTPjfTbRTdXReUWhcTQRQZbTWYVYPaVZXdYYWPTabYUTbW99QLgLNIOIRNNMIKRJEHGFHGJGGFIFDFGDK&chxt=x,y&chxr=1,0,8.720000&chxl=0:|+||08:00||+||12:00||+||16:00||+||20:00||+||00:00||+||04:00||&chxs=0,505050,10,0,lt&chg=4.17,25,1,2,0.69,0&chxp=0,0.69,4.86,9.03,13.20,17.37,21.54,25.71,29.88,34.05,38.22,42.39,46.56,50.73,54.90,59.07,63.24,67.41,71.58,75.75,79.92,84.09,88.26,92.43,96.60&tsstart=2010-11-09+05:50:00&tsstep=600

mycheckpoint does not actually call on Google to do the chart rendering, but invokes its own JavaScript code to visualize the URL locally.

Here are some downsides for using Google charts:

• The URL cannot be as long as you like. 2048 characters is an upper bound you’ll want to keep behind. [Google charts POST method calls are available, which leads to 16K equivalent of URL length — this is still not too helpful due to the nature of POST calls]
• Features are inconsistent. To specify label or tick positions, one must specify exact positions. To specify grid positions, one must supply with step, offset, etc. There are more such inconsistencies.
• Google charts are not too friendly. Taking the ticks and grids example from above, there really shouldn’t be a reason why grids would not be automatically generated according to ticks definitions. But we are required to specify positions for the ticks as well as for the grids.
• There is no support for time-series. One must translate time as x-axis values.
• Perhaps most intimidating to many people: to generate a Google chart, once must send data to Google. Which is the main reason I used local JavaScript rendering.

Anyway, let’s build a very simple chart. Since I will not cover everything in this post, we make for some relaxed conditions.

Sample data

Consider the chart_data table, below:

SELECT * FROM chart_data;
+-----+---------------------+-------------------+
| id  | ts                  | data              |
+-----+---------------------+-------------------+
|   1 | 2011-02-01 09:59:15 |  1.99666833293656 |
|   2 | 2011-02-01 09:59:15 |  7.94677323180245 |
|   3 | 2011-02-01 09:59:15 |  17.7312123996804 |
|   4 | 2011-02-01 09:59:15 |   31.153467384692 |
|   5 | 2011-02-01 09:59:15 |  47.9425538604203 |
|   6 | 2011-02-01 09:59:15 |  67.7570968074042 |
|   7 | 2011-02-01 09:59:15 |  90.1904762132767 |
|   8 | 2011-02-01 09:59:15 |  114.776974543924 |
|   9 | 2011-02-01 09:59:15 |  140.998843732947 |
|  10 | 2011-02-01 09:59:15 |  168.294196961579 |
|  11 | 2011-02-01 09:59:15 |  196.065619213516 |
|  12 | 2011-02-01 09:59:15 |  223.689380632134 |
|  13 | 2011-02-01 09:59:15 |   250.52512820847 |
...

Find complete SQL script in google_charts.sql

In the above table we assume the timestamp values are evenly distributed. This is because of the aforementioned fact that Google Charts do not support time-series. So, if the given timnestamps are not evenly distributed, or maybe some values are skipped, we must manually compensate for that. In mycheckpoint I do that. In this blog post, we skip it.

Axis boundaries

By default, Google Charts expect data to lie within the range 0..100. We need to reset the scale to match our minimum/maximum values, and lay out our values within that range.

We will be using views in this solution, and here is the first view:

CREATE OR REPLACE VIEW
 chart_data_minmax
AS
 SELECT MIN(data) AS min_value, MAX(data) AS max_value
FROM
 chart_data
;

In our example we will just settle with the min/max values. In real worlds we may:

• Choose 0 to be the lower value, even if the minimal value is positive
• Choose to round min-max values to a nearest power of 10.

The axis boundaries go under the following parameters:

• chxt=x,y (we hard code bottom axis, left axis)
• chxr=1,min,max (with min, max as computed by the above)

The following query produces these params:

SELECT CONCAT('&chxt=x,y&chxr=1,', ROUND(min_value, 1), ',', ROUND(max_value, 1)) AS minmax FROM chart_data_minmax;
+---------------------------------+
| minmax                          |
+---------------------------------+
| &chxt=x,y&chxr=1,-4716.6,5340.0 |
+---------------------------------+

Data values

Next, we have some 244 rows of data. Data is double. Can we be certain this all fits within 2048 characters? What if we had more rows?

Alas, we must make a trade-off. We will sacrifice accuracy in favor of a short URL. We will use simple encoding for our data. This means just one character per value, encoded A-Za-z0-9. This means just 62 distinct values.

So we must translate each data value into the range 0..61, and then into a character. First step is:

SELECT (data - min_value)/(max_value - min_value) AS range_01 FROM chart_data, chart_data_minmax;
+---------------------+
| range_01            |
+---------------------+
|   0.469204617275163 |
|   0.469796281944551 |
|   0.470769223945374 |
|   0.472103901937297 |
|   0.473773369865649 |
|   0.475743682188048 |
|   0.477974405550919 |
...

The above translates the data into the 0..1 range. Next is to simply multiply by 61:

SELECT 61*(data - min_value)/(max_value - min_value) AS range_0_61 FROM chart_data, chart_data_minmax;
+-------------------+
| range_0_61        |
+-------------------+
|  28.6214816537849 |
|  28.6575731986176 |
|  28.7169226606678 |
|  28.7983380181751 |
|  28.9001755618046 |
|  29.0203646134709 |
|  29.1564387386061 |
|  29.3055730348683 |
|  29.4646269983728 |
|  29.6301923928522 |
...

To translate into a simple format character we parse a constant string:

SELECT
  SUBSTRING(
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
    1+61*(data - min_value)/(max_value - min_value),
    1
  ) AS c FROM chart_data, chart_data_minmax;
+------+
| c    |
+------+
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| e    |
| e    |
...

Oh, and we should handle NULLs, as well, so:

SELECT
  IF(
    data IS NULL,
    '_',
    SUBSTRING(
      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
      1+61*(data - min_value)/(max_value - min_value),
      1
    )
  ) AS c FROM chart_data, chart_data_minmax;

+------+
| c    |
+------+
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| d    |
| e    |
| e    |
...

And, to present it as a char sequence, we use the chd parameter and call GROUP_CONCAT to the rescue:

SELECT
  CONCAT('&chd=s:',
    GROUP_CONCAT(
      IF(
        data IS NULL,
        '_',
        SUBSTRING(
          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
          1+61*(data - min_value)/(max_value - min_value),
          1
        )
      )
      SEPARATOR ''
    )
  ) AS c FROM chart_data, chart_data_minmax
\G
*************************** 1. row ***************************
c: &chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL

Wrap up

Combining the above two queries, with some URL necessities, we get:

SELECT
  CONCAT(
    'http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,',
    ROUND(min_value, 1), ',',
    ROUND(max_value, 1),
    '&chd=s:',
    GROUP_CONCAT(
      IF(
        data IS NULL,
        '_',
        SUBSTRING(
          'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
          1+61*(data - min_value)/(max_value - min_value),
          1
        )
      )
      SEPARATOR ''
    )
  ) FROM chart_data, chart_data_minmax
;

And get:

http://chart.apis.google.com/chart?cht=lc&chs=400x200&chtt=SQL%20chart&chxt=x,y&chxr=1,-4716.6,5340.0&chd=s:dddddddddeeeeeefffffffffeeeedddcccbbaaZZZYYYXXXXXXXXXYYYZZabbcdeefghhijkkllmmmmmmmmllkkjihgfedcbZYXWVUTSRRQQPPPPQQQRSTUVWXZacdfgijlmnpqrssttuuuttssrqonmkigfdbZXVTSQONMLKJIIIIIIJKLMOPRTVXZbegilnprtvwyz01111110zyxvtrpnkifcaXUSPNLJHFECBBAAABBCEFHJLNQTWZcfilortwy1346789999876420yvspmjfcYVSOL

The above chart depicts our visualized data. Alas, x-axis ranges are wrong; no ticks nor grids are yet presented.

Granularity is not the best, either: the different 62 values are spread across a 200 pixel high chart, making for a 3 pixel distinction between two values, evident on the left area of the chart.

Continues on Generating Google line charts with SQL, part II.

• Aggregation tables: aggregated data makes for fast reports on previously slow queries.
• Enhanced charting: interactive charts now present time stamps dynamically (see demo); “Zoom in” charts are available (see demo) on mycheckpoint‘s HTTP server.
• RPM distribution: a “noarch” RPM mycheckpoint build is now available.
• Initial work on formalizing test environment

mycheckpoint celebrates one year of existence!

Aggregation tables

I really wanted to avoid using these: everything was so more beautiful with one single dataset and dozens of supporting views (OK, the views themselves are hardly “beautiful”).

However it was impossible (for my level of expertise) to optimize query performance what with all those views on per-hour and per-day aggregation. The GROUP BYs and the JOINs did not make it possible for condition pushdown (i.e. using MERGE algorithm) where desired.

As result, mycheckpoint now manages aggregation tables: per-hour and per-day. The impact on sample taking is neglect able (making for two additional fast queries), but the impact on reading aggregated data is overwhelming. Generating a HTML full report could take a few minutes to complete. It now returns in no time. This makes charting more attractive, and allows for enhanced charting, such as zooming in on charts, as described following.

Aggregation tables will automatically be created and retroactively populated upon using revision 208. There’s nothing special to do; be advised that for one single execution of mycheckpoint, many INSERT queries are going to be executed. Shouldn’t take more than a couple minutes on commodity hardware and a few months of history.

It is possible to disable aggregation tables, or make for a complete rebuild of tables; by default, though, aggregation is ON.

Enhanced charting

Two enhancements here:

1. The interactive line charts already know how to update legend data as mouse hovers over them. Now they also present accurate date & time. This provides with fully informative charts.
2. As with other monitoring tools, it is possible to “zoom in” on a chart: zooming in will present any chart in “last 24 hours”, “last 10 days” and “complete history” views, magnified on screen. See demo here.

RPM distribution

No excuse for this being so late, I know. But RPM distribution is now available. Yeepee!

This is a noarch distribution, courtesy of Python’s distutils; you should be able to install the package on any RPM supporting platform. I have only tested in on CentOS; feedback is welcome.

Future plans

Work is going on. These are the non-scheduled future tasks I see:

• Monitoring InnoDB Plugin & XtraDB status.
• A proper man page.
• Anything else that interests me & the users.

Try it out

Try out mycheckpoint. It’s a different kind of monitoring solution. Simple monitoring (charting) is immediate. For more interesting results you will need basic SQL skills, and in return you’ll get a lot of power under your hands.

• Download mycheckpoint here
• Visit the project’s homepage
• Browse the documentation
• Report bugs

mycheckpoint is released under the New BSD License.

Umm, I’ll repeat this last one: mycheckpoint is released under the New BSD License. Still, and will continue to be. Thanks for the good advice by Lenz, Domas and others.

The code is derived from oak-online-alter-table, part of openark-kit, a toolkit I’m authoring. Looking at the documentation I can see many ideas were incorporated as well. And of course many things are different, a lot of work has been put to it by MySQL@Facebook.

openark-kit is currently released under the new BSD license, and, as far as I can tell (I’m not a lawyer), Facebook’s work has followed the license to the letter. It is a strange thing to see your code incorporated into another project. While I knew work has begun on the tool by Facebook, I wasn’t in on it except for a few preliminary email exchanges.

And this is the beauty

You release code under open source license, and anyone can pick it up and continue working on it. One doesn’t have to ask or even let you know. Eventually one may release back to the community improved code, more tested (not many comments on oak-online-alter-table in the past 18 months).

It is a beauty, that you can freely use one’s patches, and he can then use yours.

And here is my concern

When I created both openark-kit and mycheckpoint, I licensed them under the BSD license. A very permissive license. Let anyone do what they want with it, I thought. However Facebook’s announcement suddenly hit me: what license would other people use for their derived work?

The OSC has been release under permissive license back to the community (again, I am not a lawyer). But, someone else could have made it less friendly. Perhaps not release the code at all: just sell it, closed-source, embedded in their product. And I found out that I do not want anyone to do whatever they want with my code.

I want all derived work to remain open!

Which is why in next releases of code I’m authoring the license will change to less permissive and more open license, such as GPL or LGPL. (Of course, all code released so far remains under the BSD license).

• HTTP server: mycheckpoint can now act as a web server. Point your browser and start browsing through HTML reports. See mock up demo.
• Interactive charts: HTML line charts are now interactive, presenting with accurate data as you move over them. See sample.
• Enhanced auto-deploy: now auto-recognizing failed upgrades.
• Reduced footprint: much code taken out of the views, leading to faster loading times.
• Better configuration file use: now supporting all command line options in config file.
• Remote host monitoring accessibility: now supporting complete configurable accessibility details.
• Bug fixes: thanks to the bug reporters!

mycheckpoint is free, simple, easy to use (now easier with HTTP server) and useful. I encourage you to try it out: even compared with other existing and emerging monitoring tools, I believe you will find it a breeze; it’s low impact and lightness appealing; it’s alerts mechanism assuring; its geeky SQL-based nature with ability to drill down to fine details — geeky-kind-of-attractive.

</encouragement>

HTTP server

You can now run mycheckpoint in http mode:

bash$ mycheckpoint http

mycheckpoint will listen on port 12306, and will present you with easy browsing through the reports of your mycheckpoint databases.

The http server automatically detects those schemata used by mycheckpoint, and utilizes the existing HTML views, integrating them into the greater web framework.

While in http mode, mycheckpoint does nothing besides serving web pages. It does not actively exercise monitoring: you must still use the usual cron jobs or other scheduled tasks by which you invoke mycheckpoint for monitoring.

The http server is directed at a single MySQL server, as with the following example:

bash$ mycheckpoint --host=slave1.localdomain --port=3306 --http-port=12306 http

It is assumed that this server has the monitoring schemata.

See mock up demo. The demo uses presents with real output from a mycheckpoint HTTP server; I haven’t got the means to put up a live demo.

Interactive charts

The openark line charts, used in the HTML reports, are now interactive. As you scroll over, the legend presents you with series values.

No more “I have this huge spike once every 4 hours, which reduces all other values to something that looks like zero but is actually NOT”. Hover, and see the real values.

See sample.

Enhanced auto-deploy

The idea with mycheckpoint is that it should know how to self upgrade the schema on version upgrade (much like automatic WordPress upgrades). mycheckpoint does bookkeeping of installed versions within the database, and upgrades by simple comparison.

It now, following a couple of reported bugs, also recognizes failure of partial, failed upgrades. This adds to the automation of mycheckpoint‘s installation.

Reduced footprint

Some of mycheckpoint‘s views are complicated, and lead to a large amount of code in view declaration. This leads to increased table definition size (large .frm files). There has been some work to reduce this size where possible. Work is still ongoing, but some 30% has been taken off already. This leads to faster table (view) load time.

Better configuration file use

Any argument supported on the command line is now also supported in the config style. Much like is handled with MySQL. For example, one can issue:

mycheckpoint --monitored-host=sql02.mydb.com  --monitored-user=monitor --monitored-password=123456

But now also:

mycheckpoint

With the following in /etc/mycheckpoint.cnf:

[mycheckpoint]
monitored_host     = sql02.mydb.com
monitored_user     = monitor
monitored_password = 123456

Rules are:

• If an option is specified on command line, it takes precedence over anything else.
• Otherwise, if it’s specified in the configuration file, value is read from file.
• Otherwise use default value is used.
• On command line, option format is xxx-yyy-zzz: words split with dash/minus character.
• On configuration file, option format is xxx_yyy_zzz: words split with underscore. Unlike MySQL configuration format, dashes cannot be used.
• If an option is specified multiple times on configuration file — well — I have the answer, but I won’t tell. Just don’t do it. It’s bad for your health.

Future plans

Work is going on. These are the non-scheduled future tasks I see:

• Monitoring InnoDB Plugin & XtraDB status.
• A proper man page.
• Anything else that interests me & the users.

Try it out

Try out mycheckpoint. It’s a different kind of monitoring solution. Simple monitoring (charting) is immediate. For more interesting results you will need basic SQL skills, and in return you’ll get a lot of power under your hands.

• Download mycheckpoint here
• Visit the project’s homepage
• Browse the documentation
• Report bugs

mycheckpoint is released under the New BSD License.

• Improved custom queries: lifting of limitations from previous, introductory revision; better HTML presentation
• Local, inline charting: no rendering of Google Charts, unless explicitly requested. All charts are now rendered locally using JavaScript. No data is now sent over the network.
• Page/Swap I/O monitoring: now monitoring for page ins and outs, swap ins and outs (Linux only).
• Improved HTML reports: several improvements on presentation (see sample, more follow).

Improved custom queries

Some limitations, introduced in revision 132, are now lifted. New features are introduced.

• There is now no limit to the number of custom queries (well, an INT limit).
• In fact, the data tables adjust themselves to the existing custom queries in the form of auto-deploy: once a new custom query is added or an old one removed, mycheckpoint will add or remove the relevant columns from the data tables.
• The chart_order column is now utilized: HTML reports which include custom query charts now order those charts according to chart_order values. This makes for nicer reports.
• The standard HTML brief report (SELECT html FROM sv_report_html_brief) now automatically includes all custom charts. The HTML brief report is the report one usually wants to look at: it provides with the latest 24 hours metrics for selected values. It now becomes a centralized place for all that is interesting in the past 24 hours.
• Custom queries are now allowed to return NULL, treated as a missing value. This is a bugfix from previous revisions.

Local charting

Motivation for local charting is clear: no one likes having their data being sent over the network. And no one likes Google to know about their DML values.

I’ve been playing around with quite a few charting solutions, and have gone into depths with two of them, adding and rewriting quite a lot of code. Eventually, I settled on my very own rendering. Here’s what I’ve seen & tested:

• dygraphs: a very nice time series charting library. I’ve presented a use case on a previous post.
  • Pros: slick, easy to work with.
  • Cons: uses HTML Canvas for rendering. This is fine on Firefox, Chrome, Safari, you name it. This isn’t fine on IE, which does not support Canvas. There’s ExplorerCanvas, a hack tool which converts canvas to IE’s VML, but it is far from being satisfactory: it is sloooow. Very, very slow. It is slow with one chart; but loading of 21 charts, as I do in some of mycheckpoint‘s reports can take long minutes on Internet explorer.
  • Cons: Only provides with a time series chart. No scatter plots.
• Because they’re using ExplorerCanvas for IE, flot, jqPlot etc., are all unacceptable.
• gRaphael: very slick charts based on Raphael. The original line charts are very basic, and I have invested a lot of time rewriting a great deal (you can find it all here). Raphael uses VML on IE, and SVG for all other browsers.
  • Pros: very slick. Supports various chart types, including line (though not time-series) and scatter.
  • Cons: slooooooooow when instantiating multiple charts. Unbearably slow, both on Firefox and IE. Slow as in minutes of waiting.

In addition, all of the above solutions were quite heavyweight: at about 45KB to start with, then add ExplorerCanvas or jQuery, or Raphael as supporting libraries, these became a real burden.

So, I had some time to spare (business is fine, thank you. I was a bit Ill. I’m feeling well now, thank you), and was upset what with all the time I invested in the above coding. And I decided to invest even more time, and build my own charts.

Enter openark-charts.

Currently, these line charts and scatter charts know how to parse a Google Image chart URL (only some features supported — only those I’m actually using with mycheckpoint). These are not full blown solutions: they come to serve mycheckpoint. And they do so nicely, if I may say so. Using Canvas for most browsers, or VML for IE, these very small pieces of code (10K for line chart, 6K for scatter chart, minified) load fast, use very little memory, and do their work well.

Granted, neither provides with interactive features: this is planned for the future.

Page/swap I/O monitoring

(Linux only) mycheckpoint now reads /proc/vmstat to get the pageins, pageouts, swapins and swapouts (since last reboot). I was actually looking at completely different places on the /proc file system to get swap info, and was frustrated with the complexity involved, till I bumped on /proc/vmstat… New tricks every day!

Improved HTML reports

This is mostly HTML make-up. Some minimal design, some more details thrown into the HTML pages (name of DB, MySQL version, mycheckpoint version). A little more verbosity; all sorts of stuff which was neglected so far.

Here are some show off examples of the new HTML views: [full report], [brief report], [24/7 report], [custom full report], [custom brief report], [alert pending report].

All HTML views now utilize the new openark-charts, and none renders charts with Google charts. This means when you use your HTML view, your data is safe. No data is sent over the net. All charts are rendered using Javascript, which is loaded and executed locally.

But if you like, there’s a [url] link next to each chart, which leads to a (online) Google chart image. Why? Because neither HTML Canvas nor VML allow for a complete rendering of the charts to an image. So this is a way for one to retrieve & store a chart’s image. Don’t use it if you see no reason for it; it’s just there.

And I even threw in rounded corners (IE users: only as of Windows 7).

Future plans

Work is going on. These are the non-scheduled future tasks I see:

• Monitoring InnoDB Plugin & XtraDB status.
• Interactive charts. See my earlier post.
• A proper man page.
• Anything else that interests me.

Try it out

Try out mycheckpoint. It’s a different kind of monitoring solution. You will need basic SQL skills, and in return you’ll get a lot of power under your hands.

• Download mycheckpoint here
• Visit the project’s homepage
• Browse the documentation
• Report bugs

mycheckpoint is released under the New BSD License.