Google charts

	Skill level: Medium
	The instructions on this page require a basic knowledge of linux.

Is this article helpful to you?
Please consider donating or volunteering
Thank you!

About

"Charts Google chart tools are powerful, simple to use, and free. Try out our rich gallery of interactive charts and data tools.

Forum discussion

This how-to can be discussed on the forums here

The rational for behind this is : You have a database / source of information that you would like to graph. In this instance, I needed to see in fairly real-time, the number of http processes as well as the size of these processes.

It could be applied to a multitude of scenarios.

Getting Started

1. Find the relevant info re; http

ps -ylC httpd | awk '{x += $8;y += 1} END {print "Apache Memory Usage (MB): "x/1024; print "Average Proccess Size (MB): "x/((y-1)*1024)}' && echo -n 'current Apache Processes : ' && ps -C httpd --no-headers | wc -l

This outputs the following information;

Apache Memory Usage (MB): 1737.91
Average Proccess Size (MB): 75.5611
current Apache Processes : 23

2. Now we must create a database to hold the information -( how you name the database, table and the columns is up to you ), I'll use the following :

• Database name : apache_stats
• Table name : performance
• Columns : number, mem_used, process_size, processors_used, period

the sql is below :

-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: apache_stats
-- ------------------------------------------------------
-- Server version       5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `performance`
--

DROP TABLE IF EXISTS `performance`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `performance` (
 `number` int(11) NOT NULL AUTO_INCREMENT,
 `mem_used` int(10) unsigned NOT NULL,
 `process_size` int(10) unsigned NOT NULL DEFAULT '0',
 `processors_used` int(10) unsigned NOT NULL,
 `period` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`number`),
 KEY `index` (`number`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-11-26 23:35:11

Save the above into a file called : apache_memory_usage_show_to_database.sql

3. Create the database:

#mysql  < apache_memory_usage_show_to_database.sql

Set the user access :

#mysql
mysql> GRANT ALL PRIVILEGES ON apache_stats.* TO stats@localhost IDENTIFIED BY 'Your Password';

mysql> flush privileges;

4.Prepare the collection of the relevant parameters that you want to monitor ( in this case, http stats )

create a shell script: ( apache_memory_usage_show_to_database.sh )

#/usr/bin
while true
do
LOG=outlog.txt
OUTPUT=$(ps -ylC httpd | awk '{x += $8;y += 1} END {print "Apache Memory Usage (MB): "x/1024; print ":Average Proccess Size (MB): "x/((y-1)*1024)}' && echo -n ':Apache Processes: ' && ps -C httpd --no-headers | wc -l  )
DAY=$(date +"%F"+"%T")
now=$(date +'%Y-%m-%d %H:%M:%S')

echo $OUTPUT \'$DAY\' | awk  '{print "INSERT INTO performance (mem_used, process_size, processors_used,period) VALUES  ( "$5", "$10", "$13", "$14" );"}' | mysql --user=username --password=password database_name
sleep 120
done

This script will insert the values for the http memory used , http process size, number of http processes running and the date+time into the database every 2 minutes

Make the script executable:

chmod 755 apache_memory_usage_show_to_database.sh

5. Create a method to run the script above - ( there are better ways to do this , but I've just used the following for now ):

nohup ./apache_memory_usage_show_to_database.sh &

6.Wait 10 minutes or so while data is written to the database, the check whats been written :

#mysql

mysql> use apache_stats

mysql> select * from performance limit 5;

 +-------+----------+--------------+-----------------+---------------------+
| index | mem_used | process_size | processors_used | date                |
+-------+----------+--------------+-----------------+---------------------+
|     1 |      195 |           18 |              11 | 2015-08-18 16:26:48 |
|     2 |      195 |           18 |              11 | 2015-08-18 16:27:29 |
|     3 |      195 |           18 |              11 | 2015-08-18 16:28:48 |
|     4 |      195 |           18 |              11 | 2015-08-18 16:29:29 |
|     5 |      195 |           18 |              11 | 2015-08-18 16:30:48 |
+-------+----------+--------------+-----------------+---------------------+
5 rows in set (0.00 sec)

OK , so now we have the data been monitored, I now need a way to present / view this in an ibay ( set the permissions appropriate for your situation ) :

7.Create an iBay in server-manager :

Information bay name : stats
Description : apache stats
Group : admin
User access via file sharing or user ftp :Write = admin, Read = Group
Public access via web or anonymous ftp : Local network (password required )
Execution of dynamic content (CGI, PHP, SSI) : enabled
Force secure connections : Disbaled

So now we have the place holder for the Google Chart scripts .

8. Create the needed scripts ( working example below ) and transfer them to the iBay created above :

Script to monitor http memory usage (mysqli_apache_memory_used.php ) :

<?php
// Hostname: 127.0.0.1, username: your_user, password: your_pass, db: your_db

$mysqli = new mysqli('localhost', 'username', 'password', 'db_name');

// Oh no! A connect_errno exists so the connection attempt failed!
if ($mysqli->connect_errno) {
    // The connection failed. What do you want to do?
    // You could contact yourself (email?), log the error, show a nice page, etc.
    // You do not want to reveal sensitive information

    // Let's try this:
    echo "Sorry, this website is experiencing problems.";
// Something you should not do on a public site, but this example will show you
    // anyways, is print out MySQL error related information -- you might log this
    echo "Error: Failed to make a MySQL connection, here is why: \n";
    echo "Errno: " . $mysqli->connect_errno . "\n";
    echo "Error: " . $mysqli->connect_error . "\n";

    // You might want to show them something nice, but we will simply exit
    exit;
}
// Perform an SQL query
//$query = "SELECT number, period, process_size, processors_used  FROM performance LIMIT 50";
$query ="SELECT number, period, process_size, processors_used, mem_used  FROM performance WHERE period > DATE_SUB(NOW(), INTERVAL 48 HOUR)";
if (!$result = $mysqli->query($query)) {
    // Oh no! The query failed.
    echo "Sorry, the website is experiencing problems.";

    // Again, do not do this on a public site, but we'll show you how
    // to get the error information
    echo "Error: Our query failed to execute and here is why: \n";
    echo "Query: " . $sql . "\n";
    echo "Errno: " . $mysqli->errno . "\n";
    echo "Error: " . $mysqli->error . "\n";
    exit;
}

// Phew, we made it. We know our MySQL connection and query
// succeeded, but do we have a result?
if ($result->num_rows === 0) {
    // Oh, no rows! Sometimes that's expected and okay, sometimes
    // it is not. You decide. In this case, maybe actor_id was too
    // large?
    echo "We could not find a match , sorry about that. Please try again.";
    exit;
}
$qresult = $mysqli->query($query);
$results = array();

while($res = $qresult->fetch_assoc()) {
	$results[] = $res;
}
$curvechart_data = array();
foreach($results as $result)
{
	//$curvechart_data[] = array($result['period'],(int)$result['process_size'], (int)$result['processors_used'],(int)$result['mem_used']);
$curvechart_data[] = array($result['period'],(int)$result['mem_used']);
}
$curvechart_data = json_encode($curvechart_data);
//echo $curvechart_data;

// The script will automatically free the result and close the MySQL
// connection when it exits, but let's just do it anyways
//$result->free();
//$mysqli->close();


$html =<<<XYZ

<html>
<head>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
    google.load('visualization', '1.1', {packages: ['line']});
    google.setOnLoadCallback(drawChart);

    function drawChart() {

      var data = new google.visualization.DataTable();
                data.addColumn('string','Period');
		//data.addColumn('number', 'No. Processors');
                //data.addColumn('number', 'Process size');
		data.addColumn('number', 'Memory Used');
                data.addRows({$curvechart_data});

var options = {
        chart: {
          title: 'Apache HTTP Memory used ',
          subtitle: 'in last 48 Hrs '
        },colors:['red']
        width: 1400,
        height: 700
      };

      var chart = new google.charts.Line(document.getElementById('linechart_material'));

      chart.draw(data, options);
    }
  </script>
</head>
<body>

</body>
</html>

XYZ;

echo $html;
echo date('l jS \of F Y h:i:s A');

?>

Note : wikimedia seem to have eaten some lines from above code snippet ??? Goto the forums and copy it from there : http://forums.contribs.org/index.php/topic,52107.0.html

9.View the graphs : Now go to your ibay and you can view the chart ( Sample chart attached ):

GoogleChart.jpg below, refers to the above code & instructions. ( Googlechart1.jpg [shows http processes and process size] & GoogleChart2.jpg [ shows memory usage as gauge ]

http://server-name/ibay/mysqli_apache_memory_used.php

• 
• 
•