Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » Commands & Shells, GNUPlot

Using GNUPlot to chart MySQL data

Submitted by on November 20, 2005 – 12:17 pm 8 Comments

The following Korn shell function connects to a MySQL database and extracts specified data, which is then plotted as a function of time using GNUPlot.  Using correct syntax is critical. Note the use of single- and double-quotes.  In the example below, “using 2:4” mean that that the second column in your database table will be the X-axis values and the fourth column – the Y-axis value F(x). The graph below utilizes Bezier smoothing. This makes the chart look cleaner, but it may not be appropriate for your data.

GNUPlot_Update_Func() {

#------ Client Disk Allocation and Utilization vs. Time ------

$MYSQL --column-names=0 -u$DBUSER -p$DBPASS $DBNAME << EOF | while read HOSTNAME
SELECT DISTINCT host_name FROM sandisk_clients ORDER BY host_name;
EOF
do
$MYSQL --column-names=0 -u$DBUSER -p$DBPASS $DBNAME < "${TMPDIR}/${HOSTNAME}_gnuplot_client.dat"
SELECT host_name, line_datetime, SUM(alloc_kb), SUM(used_kb) 
FROM sandisk_clients 
WHERE host_name LIKE '$HOSTNAME'
GROUP BY YEAR(line_datetime), MONTH(line_datetime), DAYOFMONTH(line_datetime);
EOF
echo "set title '$HOSTNAME'"					 >  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set xdata time"						>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set key box"						>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set key bottom right"					>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set size 1.5,1.5"						>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set xlabel 'Date'"					>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set ylabel 'Size, Kb' font 'Arial,12'"			>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set autoscale"						>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo 'set timefmt "%Y-%m-%d %H-%M-%S"'				>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set term png color"					>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "set output '${CHARDIR}/${HOSTNAME}_client_001.png'"	>>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"
echo "plot '${TMPDIR}/${HOSTNAME}_gnuplot_client.dat' using 2:4 title 'Allocated, Kb' smooth bezier with
linespoints, '${TMPDIR}/${HOSTNAME}_gnuplot_client.dat' using 2:5 title 'Used, Kb' smooth bezier with linespoints" >>  "${TMPDIR}/${HOSTNAME}_gnuplot_client.gnu"

$GNUPLOT  /dev/null 2>&1
done
}

Here’s an example of the resulting image:

GNUPlot chart

Print Friendly, PDF & Email

8 Comments »

  • cardskid22 says:

    I have a MySQL database created, and want to extract that information in a 4 by 2 table.

    How do I write this in PHP so that the computer knows to put info into a 4 by 2 table?

  • henryshensbcglobalnet says:

    How do you backup and restore a MySQL database on your system without using the plain dump?

  • Dark_LovexXx says:

    I have an excel spreadsheet that lists all the zipcodes in my city. I need to put these zipcodes into mysql database, but inputting them individually would take ages. Is there any way to import a specific format that MySQL will recognize?

  • stingerms says:

    How to store youtube video links into mysql database? And set it up on php page?

  • Chester says:

    I’m currently connected to a local Microsoft SQL server. I’m new to windows programming and i need the code snippets to connect my windows C# application to a remote Mysql database. I have a website which is connected to my web host’s Mysql database. I want to be able to connect my windows C# app. to that same data base. Need your expert advice. By the way, I’m using visual studio.

  • dubmecrazy3 says:

    Hello to all, I want to upload an image to a mysql database using a form in jsp. I’m using Netbeans 6.5, and I’m not quite sure how to actually use servlets just yet.
    Any help is appreciated thanx in advance.

  • Mark says:

    I am trying to use a webform to post data to my MySQL database. I have all of this set up on my website that is hosted on yahoo. I have php MyAdmin installed on the server to manage the database. For some reason, I can’t get my webform to post the info to my database. Can anyone help?

  • Lucas H says:

    I got this download manager for my site called CFiles. It says i need to use mySQL database. My host has the option to use it but the problem is i dont know how. Any help?

Leave a Reply

%d bloggers like this: