From 21c641cb28510fe775b2f11c17744d74e972b018 Mon Sep 17 00:00:00 2001 From: Florian Schaal <florian@schaal-24.de> Date: Mon, 21 Oct 2013 10:34:41 -0400 Subject: [PATCH] add database-size monitoring. the results stored in the database may be used to add the db-size to the web-quota, too. --- server/lib/classes/db_mysql.inc.php | 27 ++++++++ server/lib/classes/cron.d/100-monitor_database_size.inc.php | 120 ++++++++++++++++++++++++++++++++++++++++ interface/lib/classes/functions.inc.php | 8 ++ 3 files changed, 153 insertions(+), 2 deletions(-) diff --git a/interface/lib/classes/functions.inc.php b/interface/lib/classes/functions.inc.php index 50b3e6c..5b98c20 100644 --- a/interface/lib/classes/functions.inc.php +++ b/interface/lib/classes/functions.inc.php @@ -276,7 +276,13 @@ return intval($string); } } - + + /** + * Function to change bytes to kB, MB, GB or TB + * @param int $size - size in bytes + * @param int precicion - after-comma-numbers (default: 2) + * @return string - formated bytes + */ public function formatBytes($size, $precision = 2) { $base=log($size)/log(1024); $suffixes=array('','k','M','G','T'); diff --git a/server/lib/classes/cron.d/100-monitor_database_size.inc.php b/server/lib/classes/cron.d/100-monitor_database_size.inc.php new file mode 100644 index 0000000..4a248ad --- /dev/null +++ b/server/lib/classes/cron.d/100-monitor_database_size.inc.php @@ -0,0 +1,120 @@ +<?php + +/** +Copyright (c) 2013, Marius Cramer, pixcept KG +Copyright (c) 2013, Florian Schaal, info@schaal-24.de +All rights reserved. + +Redistribution and use in source and binary forms, with or without modification, +are permitted provided that the following conditions are met: + + * Redistributions of source code must retain the above copyright notice, + this list of conditions and the following disclaimer. + * Redistributions in binary form must reproduce the above copyright notice, + this list of conditions and the following disclaimer in the documentation + and/or other materials provided with the distribution. + * Neither the name of ISPConfig nor the names of its contributors + may be used to endorse or promote products derived from this software without + specific prior written permission. + +THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND +ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED +WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. +IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, +INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, +BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, +DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY +OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING +NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, +EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +*/ + +class cronjob_monitor_database_size extends cronjob { + + // job schedule + protected $_schedule = '*/5 * * * *'; + protected $_run_at_new = true; + + private $_tools = null; + + /** + * this function is optional if it contains no custom code + */ + public function onPrepare() { + global $app; + + parent::onPrepare(); + } + + /** + * this function is optional if it contains no custom code + */ + public function onBeforeRun() { + global $app; + + return parent::onBeforeRun(); + } + + public function onRunJob() { + global $app, $conf; + + /* used for all monitor cronjobs */ + $app->load('monitor_tools'); + $this->_tools = new monitor_tools(); + /* end global section for monitor cronjobs */ + + /* the id of the server as int */ + $server_id = intval($conf['server_id']); + + /** The type of the data */ + $type = 'database_size'; + + /** The state of the database-usage */ + $state = 'ok'; + + /** Fetch the data of ALL databases into an array */ + $records = $app->db->queryAllRecords("SELECT database_name, sys_groupid FROM web_database WHERE server_id = $server_id"); + if(is_array($records) && !empty($records)) { + $data = array(); + for ($i = 0; $i < sizeof($records); $i++) { + $data[$i]['name'] = $records[$i]['database_name']; + $data[$i]['size'] = $app->db->getDatabaseSize($data[$i]['name']); + $data[$i]['client_id'] = $records[$i]['sys_groupid']; + } + } + + $res = array(); + $res['server_id'] = $server_id; + $res['type'] = $type; + $res['data'] = $data; + $res['state'] = $state; + + /* + * Insert the data into the database + */ + $sql = 'REPLACE INTO monitor_data (server_id, type, created, data, state) ' . + 'VALUES (' . + $res['server_id'] . ', ' . + "'" . $app->dbmaster->quote($res['type']) . "', " . + 'UNIX_TIMESTAMP(), ' . + "'" . $app->dbmaster->quote(serialize($res['data'])) . "', " . + "'" . $res['state'] . "'" . + ')'; + $app->dbmaster->query($sql); + + /* The new data is written, now we can delete the old one */ + $this->_tools->delOldRecords($res['type'], $res['server_id']); + + parent::onRunJob(); + } + + /* this function is optional if it contains no custom code */ + public function onAfterRun() { + global $app; + + parent::onAfterRun(); + } + +} + +?> diff --git a/server/lib/classes/db_mysql.inc.php b/server/lib/classes/db_mysql.inc.php index 23ba8a6..acd86aa 100644 --- a/server/lib/classes/db_mysql.inc.php +++ b/server/lib/classes/db_mysql.inc.php @@ -265,6 +265,31 @@ } + /** + * Function to get the database-size + * @param string $database_name + * @return int - database-size in bytes + */ + public function getDatabaseSize($database_name) { + global $app; + include('lib/mysql_clientdb.conf'); + /* Connect to the database */ + $link = mysql_connect($clientdb_host, $clientdb_user, $clientdb_password); + if (!$link) { + $app->log('Unable to connect to the database'.mysql_error($link),LOGLEVEL_DEBUG); + return; + } + /* Get database-size from information_schema */ + $result=mysql_query("SELECT SUM(data_length+index_length) FROM information_schema.TABLES WHERE table_schema='".$database_name."';",$link); + $this->close; + if (!$result) { + $app->log('Unable to get the database-size'.mysql_error($link),LOGLEVEL_DEBUG); + return; + } + $database_size = mysql_fetch_row($result); + return $database_size[0]; + } + //** Function to fill the datalog with a full differential record. public function datalogSave($db_table, $action, $primary_field, $primary_id, $record_old, $record_new, $force_update = false) { global $app,$conf; @@ -645,4 +670,4 @@ } - ?> \ No newline at end of file + ?> -- Gitblit v1.9.1