This post continues Generating Google line charts with SQL, part I, in pursue of generating time series based image charts.

We ended last post with the following chart:

  [Read more...]

In this series of posts I wish to show how Google Charts can be generated via SQL. We discuss the Google Charts limitations which must be challenged, and work towards a simple chart.

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:

  [Read more...]

Revision 170 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

• 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

  [Read more...]

Revision 132 of mycheckpoint has been released. New and updated in this revision:

• Custom monitoring: monitoring & charting for user defined queries
• HTML reports for custom monitoring
• Process list dump upon alert notifications

Custom monitoring & charts

Custom monitoring allows the user to supply with a query, the results of which will be monitored.

That is, mycheckpoint monitors the status variables, replication status, OS metrics. But it cannot by itself monitor one’s application. Which is why a user may supply with such query as:

SELECT COUNT(*) FROM shopping_cart WHERE is_pending=1

Such a query will tell an online store how many

  [Read more...]

If you have been wondering why the overview and graphs pages say “no data to display” on the graphs when you first install Kontrollbase, it’s because there’s no data in the database being returned from the queries that generate the graphs – this is because a new install has no data to graph. This has [...]

I’m having my usual fun with charts. Working on mycheckpoint, I’ve generated monitoring charts using the Google Chars API. But I’ve also had chance to experiment and deploy interactive charts, JavaScript based. In particular, I used and tweaked dygraphs.

I’d like to note some differences in using charts of both kinds. And I think it makes a very big difference.

Static charts

I’ll call any image-based chart by “static chart”. It’s just a static image. Example of such charts are those generated by Google Image Charts (they now also have new, interactive charts), or RRDtool. Show below is

  [Read more...]

Revision #88 of mycheckpoint is released. In this revision:

• Disk space monitoring
• Improved charting
• Enhanced auto-deploy
• And more…

Disk space monitoring

mycheckpoint now monitors (on Linux only) three mount points:

The “/” (root) mount point

The datadir mount point

The tmpdir mount point

It may well be the case that two of the above (or perhaps all three of them) share the same mount point. For example, if there isn’t any particular partition for “/tmp“, it is possible that the tmpdir (by default

  [Read more...]

Revision 76 of mycheckpoint comes with quite a few improvements, including:

• OS monitoring (CPU, load average, memory)
• Auto-deploy
• Improved charting
• Brief HTML reports
• 24/7 charts

OS Monitoring

When monitoring the local machine, mycheckpoint now monitors CPU utilization, load average, memory and swap space.

This only applies to the Linux operating system; there is currently no plan to work this out for other operating systems.

Examples:

mysql> SELECT os_cpu_utilization_percent FROM sv_report_chart_sample;

  [Read more...]

Rev. 57 of mycheckpoint has been released and is available for download.

New and updated in this revision:

• Remote host monitoring
• Improved charting
• Flexible charting
• Fix to questions vs. queries issues

Remote host monitoring

It is now possible to monitor one host, while writing into another. Either or both could be remote hosts:

mycheckpoint

  [Read more...]

Time for another charting SQL query. I wish to present a single-query generated multi-line/area chart. I’ll walk through some of the steps towards making this happen. By the end of this post I’ll present some real-data charts, area charts and colored charts.

+---------+-----------------------------------------------------------------------------------+
| y_scale | chart                                                                             |
+---------+-----------------------------------------------------------------------------------+
| 1       | ****---------#######----------------------------------------*******--------###### |
| 0.88    | ----***---###-------##-----------------------------------***-------***---##------ |
| 0.75    |

  [Read more...]

My other half says I’m losing it. But I think that as an enthusiast kernel developer she doesn’t have the right to criticize people. (”I like user space better!” – she exclaims upon reading this).

Shown below is a (single query) SQL-generated pie chart. I will walk through the steps towards making this happen, and conclude with what, I hope you’ll agree, are real-world, useful usage samples.

+----------------------------------------------------------------------+
| pie_chart                                                            |
+----------------------------------------------------------------------+
|                                                                      |
|                         ;;;;;;;;;;;;;;;;;;;;;                        |
|                  oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;                 |
|

  [Read more...]

I wasn’t sure I was to go this far. After catching breath the following have been added to Generic, auto scaling, scaled SQL graphs, and these will conclude my current hacks:

• Displaying X-axis min/max values.
• Support for Y-axis values precision.
• Support for pre-defined scale range.

The addition of the above makes for presentable, usable graphs. See also sample graphs at the end of this post.

Step 8: adding X-axis values

I add minimum/maximum X-scale values to the graph. What was just ordering_column before, now turns to be the x in the y = f(x) function.

SELECT
  y_scale,
  horizontal_bar
  FROM
  (
  SELECT
    @row_number := @row_number+1,
    CASE

  [Read more...]

In Rotating SQL graphs horizontally, I have shown how to rotate an ASCII SQL graph into horizontal position.

I was dissatisfied with some parts of the solution, which I will show now how to fix:

• I had to manually scale the graph values so as to fit nicely into screen.
• I had to rely on hard coded scaling schemes.
• I had to rely on hard coded column names.
• I had no y-axis legend.

I will now present an SQL query which allows for pluggable queries, which creates self, auto scaling graphs, along with y-axis scales.

Using deeply nested subqueries, we will evolve a simple SELECT query into an elaborate graph. I will present the many steps required, followed by explanations and sample results. But in the end

  [Read more...]