openark forge

Open source utilities
 

Generating Google charts

Note: As of revision 160, mycheckpoint provides with its own chart rendering, integrated into the various HTML reports. Although still using the Google charts format for chart generation, there is actually no calling upon Google services; all chart rendering is done locally, and no data is sent over the network. For more on this, see Generating html reports.

Various views offer Google charts URL generation:

  • sv_report_chart_sample
  • sv_report_chart_hour
  • sv_report_chart_day
  • sv_report_chart_24_7
  • And the various custom charts views.

Not all variables and metrics are presented with charts. The above views only consider those most popular, which are worth being visually presented.

Each of the first 3 views only utilizes last 256 values for each metric. For example, the DML column in all three views lists up to last 256 values per view. This would mean:

  • Last 256 samples on sv_report_chart_sample (about 21 hours when samples are taken every 5 minutes)
  • Last 256 hours on sv_report_chart_hour (about 10 and a half hours of aggregated data)
  • Last 256 days on sv_report_chart_day

To generate a Google chart URL, simply query:

mysql> SELECT DML FROM sv_report_chart_hour \G
*************************** 1. row ***************************
DML: http://chart.apis.google.com/chart?cht=lc&chs=400x200&chts=303030,12&chtt=Latest+10+days:+Jan+31,+09:00++-++Feb+10,+08:00&chf=c,s,ffffff&chdl=com_select_psec|com_insert_psec|com_delete_psec|com_update_psec|com_replace_psec&chdlp=b&chco=ff8c00,4682b4,9acd32,dc143c,9932cc&chd=s:kjloqrsvy2xz326jegggeegilnposrwwwy21133iffefggiknmrrrruz1x53567khgihjigemnoqusww0x13456ggehgggjhlmpptuvy2z33646khfhghfihjknnoqqttz0yz05mefhfgfgfhhmklmppnnprxrueecfdfehgjlnqsttvz022648fggigihjjnnqsuux11314579mhggghgihlnqrvwywy234699ihfjghhjj,WUXXXWXUXaVUVWYYWUUVXWWYZYYXZabaXXZXWWXYVUVVVUWYYWXXZZaYXYabZWXZXWTTUUVVabbecaZXZYYYXVXbUSTTTTWVXXaZaYXaYZccbYYeYVWVUSUTWUaZYXWVUZXVVUUbUVSSVUTUVUUTUTWZVSVVWWYaUTSSVTWWYYWWXYZXXXZYXYaiVUUUWXWWYZZYaacaYXXYZYXibVWUTTVVaaZWYXXYXXaabaYmZWXWVVWZ,HHHHHHHHHHHHIIIJILKIIIOQNNKMLJJJJJJKJKJLJIHHHHIHHHIHIHIIIHIIIIILHGGGGGGGHHIIIHIHIHIIIIIMGGGGGGGHHHIIIIIIIIIIIIIPHGGGGGHGHHHHHHHHHIHHHHHPGGGFFFGFGGGGGGGGGGGGHGHLFFFFFFGGGGHHHHHHHHIHIIITGGGGGGGGHHIIIIIIIIIIIIIVHGGGGGHHHIIIIIIIIIIJJJIVHGHGGGHH,IHHIIHIHIIHHHHIJHIIIIIIIIIIIIHIHIIIIHIIJHHHIHIIIIHIIIIIIIHIHIIIJHHHHIIHHIIIIIIIHIHIIIIIJIIIIIIIHIHIIIIIIIIIIIIIJIHHHHHHHHHHHHHHHHHHHIHHJHHIIIIHHIIIHHHHHHHHHHHHJHHHHHHHHIHHHHHHHHIIIHHIJIIIIIIIIIIIIIIIIIIIIIIIJIIHIIIIIIIIIIIIIIIIIIIIJIIIIIIII,AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA&chxt=x,y&chxr=1,0,146.95&chxl=0:|1st|2nd|3rd|4th|5th|6th|7th|8th|9th|10th|&chxs=0,505050,10,0,lt&chg=10.04,25,1,2,6.28,0&chxp=0,6.28,16.32,26.36,36.40,46.44,56.48,66.52,76.56,86.60,96.64

The above results in the following chart:

mycheckpoint-dml-chart-hourly-88-b

In the above chart, the peaks are full days. Looking in more detail at recent hours:

mysql> SELECT DML FROM sv_report_chart_sample \G
*************************** 1. row ***************************
DML: http://chart.apis.google.com/chart?cht=lc&chs=400x200&chts=303030,12&chtt=Latest+24+hours:+Feb+9,+08:50++-++Feb+10,+08:50&chf=c,s,ffffff&chdl=com_select_psec|com_insert_psec|com_delete_psec|com_update_psec|com_replace_psec&chdlp=b&chco=ff8c00,4682b4,9acd32,dc143c,9932cc&chd=s:ZcUagbahWZgdchYdeggehbfkdjlefmggjbgskgohesndnherierhhkmcwif4lfvbp7emxbn7ep9antnq6bp2mr4aqyqjUSdTYbSXeVXbTVaVVbZVXeWYVWZXWYUYUfWZXWYaXXbYYcXaZVWgU,RSRSVUTTSUUSTRQTTRSSNPTNOSOSVPSTPPUPQSPQSQRRQOVQSQRRRSPPRPRRQRURUUTSTRUTRTUSVSUTTPVQVTRPUPRUVOkjkXSRSPQSPOSNOPVRPPQOQSQQROOROOQQOQOPQOPRPPQPRSTUb,EGFEGGFGFFGGFGGEGGFGGEGGFGGFGGFFGFFGGGGGFGGGGFFGFFGGFHGFGGFGGFGFGGHGGGHGGHGGGGHHGGGGGHGGFGHHMFWWYJEFEFFEEFEEFEFFEFFEFFEEFEEGDFFEEFEEFEFFEFFFFFEGF,GGGGGGGFGGGFGGGGGFGGFFHEFHFGHFFHFFHFGHFFGFFGGFGGFGGGFGGGGFFGFGHFFGGFGFFGFGGFGFGGGFGFGHGFHFGGFILFFHFGHFFHFFGFFGGGFFGFGGFFGGGHFFGGGGFGGFGHFGGFGGFGF,AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA&chxt=x,y&chxr=1,0,205.035000&chxl=0:||+||12:00||+||16:00||+||20:00||+||00:00||+||04:00||+||08: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.6

The above translates to the following image:

mycheckpoint-dml-chart-sample-88-b

Available charts

In order to detect the available charts, issue:

mysql> DESC sv_report_chart_sample;
+------------------------------------------+----------+------+-----+---------+-------+
| Field                                    | Type     | Null | Key | Default | Extra |
+------------------------------------------+----------+------+-----+---------+-------+
| uptime_percent                           | longblob | YES  |     | NULL    |       |
| innodb_read_hit_percent                  | longblob | YES  |     | NULL    |       |
| innodb_io                                | longblob | YES  |     | NULL    |       |
| innodb_buffer_pool_used_percent          | longblob | YES  |     | NULL    |       |
| innodb_estimated_log_mb_written_per_hour | longblob | YES  |     | NULL    |       |
| innodb_row_lock_waits_psec               | longblob | YES  |     | NULL    |       |
| bytes_io                                 | longblob | YES  |     | NULL    |       |
| myisam_key_buffer_used_percent           | longblob | YES  |     | NULL    |       |
| myisam_key_hit                           | longblob | YES  |     | NULL    |       |
| DML                                      | longblob | YES  |     | NULL    |       |
| questions                                | longblob | YES  |     | NULL    |       |
| innodb_rows                              | longblob | YES  |     | NULL    |       |
| tmp_tables                               | longblob | YES  |     | NULL    |       |
| read_patterns                            | longblob | YES  |     | NULL    |       |
| table_locks_waited_psec                  | longblob | YES  |     | NULL    |       |
| table_cache_use                          | longblob | YES  |     | NULL    |       |
| opened_tables_psec                       | longblob | YES  |     | NULL    |       |
| connections_psec                         | longblob | YES  |     | NULL    |       |
| connections_usage                        | longblob | YES  |     | NULL    |       |
| thread_cache_use                         | longblob | YES  |     | NULL    |       |
| threads_created_psec                     | longblob | YES  |     | NULL    |       |
| relay_log_used_mb                        | longblob | YES  |     | NULL    |       |
| seconds_behind_master                    | longblob | YES  |     | NULL    |       |
| seconds_behind_master_psec               | longblob | YES  |     | NULL    |       |
| estimated_slave_catchup_seconds          | longblob | YES  |     | NULL    |       |
| os_cpu_utilization_percent               | longblob | YES  |     | NULL    |       |
| os_loadavg                               | longblob | YES  |     | NULL    |       |
| os_memory                                | longblob | YES  |     | NULL    |       |
| os_mountpoints_usage_percent             | longblob | YES  |     | NULL    |       |
+------------------------------------------+----------+------+-----+---------+-------+

The list may change in time.

Modifying chart size

Chart size is by default 370×180 pixels. There are two ways to change these settings:

  1. When running mycheckpoint deploy, it is possible to specify other dimensions. See Usage page.
  2. Manually updating table data (though this will be overwritten on next deploy):
UPDATE charts_api SET chart_width=500, chart_height=360

Aggregating chart data

There are various views which aggregate charts into HTML report files. See Generating html reports. While these use the Google charts URL format, they do not actually call upon Google. Instead, local Javascript invocation is used to render the charts.

Google charts

Do take note that if you do call upon a Google Charts URL, you are sending data to Google. If this concerns you, or you are unable to download images from Google due to firewall settings, you may try other charting solutions, which support the Google Charts API.

mycheckpoint‘s HTML views use local Javascript rendering of the charts. You may also check out JFree’s Eastwood, for example.

To update the charts API, issue an UPDATE query. For example:

UPDATE charts_api SET service_url = 'http://localhost/eastwood/chart'

The service url does not come into play on mycheckpoint‘s HTML views. It is disregarded.

 
Powered by Wordpress and MySQL. Theme by openark.org