2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Mark's PlanetPostgreSQL3 / Visualizing sar data
Mark Wong

Visualizing sar data

June 1, 2017/3 Comments/in Mark's PlanetPostgreSQL /by Mark Wong

Hopefully you are now regularly collecting system statistics after reading Tomas Vondra’s defense of using sar.  If you don’t have anything in place that readily visualizes the data, I have some examples that you can hopefully build off on to see everything you need to see.

In addition to sar, the sysstat package also has a utility called sadf to display collected data.  Simply running sadf without any arguments will show you the recently aggregated processor utilization data resulting in:

gentoo 599 2017-05-25 17:10:01 UTC all %user 13.03
gentoo 599 2017-05-25 17:10:01 UTC all %nice 0.01
gentoo 599 2017-05-25 17:10:01 UTC all %system 5.16
gentoo 599 2017-05-25 17:10:01 UTC all %iowait 0.47
gentoo 599 2017-05-25 17:10:01 UTC all %steal 0.00
gentoo 599 2017-05-25 17:10:01 UTC all %idle 81.32

 

If you’re like a database, then you might prefer to consume that data in a different format by using the -d flag, which will pivot the data onto a single line resembling CSV formatted data that looks like:

# hostname;interval;timestamp;CPU;%user;%nice;%system;%iowait;%steal;%idle
gentoo;599;2017-05-25 17:10:01 UTC;-1;13.03;0.01;5.16;0.47;0.00;81.32

But if your monitor is too small to see more than few hours of data without scrolling back and forth, or if you prefer to visualize it in another way then the database friendly format of the data can be feed into gnuplot, a simple graphing utility.

The graph above is produced from the following set of commands that describe that data format, sets the plotting details, and sums the data from the sadf output in order to produce a stacked line chart.  This is just one example of how plots may be produced with gnuplot.

datafile = "sar.dat"
set datafile commentschar ""
set title "Processor Utilization"
set xdata time
set timefmt "%Y-%m-%d %H:%M:%S"
set datafile separator ";"
set terminal pngcairo size 800,500
set output 'sar.png'
set xlabel "Time"
set ylabel "% Utilization"
set xtics rotate
set key below
set grid
plot for [i=5:8:1] \
    datafile using 3:(sum [col=i:8] column(col)) \
    title columnheader(i) \
    with lines

I have a fondness for R, software for statistical computing and graphics.  It might not be as easy to get started with as gnuplot, but please try it out if you have an opportunity.  Like gnuplot, it can be used to produce a similar chart.

There are a few more commands for R (and perhaps I could use some R tips), but the idea is the same as the gnuplot commands above.

df <- read.csv("sar.dat", sep = ";", header=T, comment.char = "")
df$timestamp <- strptime(df$timestamp, "%Y-%m-%d %H:%M:%S")

type <- c("X.user", "X.nice", "X.system", "X.iowait")
label <- c("%user", "%nice", "%system", "%iowait")
color <- rainbow(4)
pch <- seq.int(1, 4)

bitmap("sar-R.png", type="png16m", units="px", width=800, height=500,
       res=150, taa=4, gaa=4)

for (i in 3:1) {
    df[[type[i]]] <- df[[type[i]]] + df[[type[i+1]]]
}

par(las=2)
plot(df$timestamp,
    df$X.user,
    type = "b",
    col = color[1],
    ylim = c(0, max(df$X.user, na.rm = T)),
    main="Processor Utilization",
    xlab="Time",
    ylab="% Utilization")

for (i in 2:4) {
    points(df$timestamp, df[[type[i]]], type = "b", pch = pch[i],
           col=color[i])
}

legend('topright', label, pch=pch, col=color)
grid(col="gray")
dev.off()

As a reminder, this isn’t limited to just the aggregated processor utilization.  You can also visualize the individual processor statistics, statistics from storage devices, memory utilization, network usage and more.  Nor is this data limited to being visualized by gnuplot or R.

Tags: gnuplot, r, sadf, sar, statistics, visualization
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
3 replies
  1. X
    X says:
    June 2, 2017 at 8:05 am

    With newer versions of sadf you can get SVG graphs (sadf -g …)

    Reply
  2. Krystal
    Krystal says:
    November 20, 2018 at 5:02 pm

    I am attempting to replicate your script to run within bash terminal. I am using a 4 feature dataset.

    Could you comment on why I might be getting this error code?
    #! /usr/bin/gnuplot
    datafile = “tmp5.dat”
    set datafile commentschar “”
    set title “Total Population”
    set xdata totals
    set datafile separator “;”
    set terminal pngcairo size 800,500
    set output ‘tmp5.png’
    set xlabel “Zip Code”
    set ylabel “Populations”
    set xtics rotate
    set key below
    set grid
    plot for [i=5:8:1] \
    datafile using 3:(sum [col=i:8] column(col)) \
    title columnheader(i) \
    with lines

    set xdata totals
    ^
    “./censustotals.gp”, line 5: unexpected or unrecognized token

    Reply
    • Mark Wong
      Mark Wong says:
      January 2, 2020 at 11:13 pm

      I might be able to help track down the issue if you can provide the version of gnuplot you are using and a sample of the data.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Future of Postgres-XL Kanban & devops culture at 2ndQuadrant – Part 2
Scroll to top
×