Line 29: |
Line 29: |
| * Columns : number, mem_used, process_size, processors_used, period | | * 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 ) |
| | | |
− | 3.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 ) | | create a shell script: ( apache_memory_usage_show_to_database.sh ) |
| #/usr/bin | | #/usr/bin |
Line 45: |
Line 102: |
| sleep 120 | | sleep 120 |
| done | | 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> |
| + | <div id="linechart_material"></div> |
| + | </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 |
| + | |
| + | <gallery> |
| + | File:GoogleChart.jpg |
| + | File:GoogleChart1.jpg |
| + | File:GoogleChart2.jpg |
| + | </gallery> |