Attached you can find the diff for bwstat. Used version was 0.25
On 07/05/07, Vladimir Vitkov <[EMAIL PROTECTED]> wrote:
the current status is as follows
* all stock indexes removed
* added new indexes as follows:
** stamp_inserted, stamp_updated, ip_src, ip_dst, bytes, packets
the indexes itself are on a single column and not combinations.
* removed the leading "%' from the LIKE clauses
* fixed 2 or 3 small bugs in bwstat (wrong column names)
The net outcome from this is speed increase in the range of 10^1.
Before this optimizations a single page with 64 ip's opened for 240+
secs, now in 10 (700K rows) and the machine this is running on is
fuckin' low-end one :)
--
С уважение,
Владимир Витков
http://www.netsecad.com
http://www.supportbg.com
diff -uNr original/config/db.sql modified/config/db.sql
--- original/config/db.sql 2005-01-31 12:03:45.000000000 +0200
+++ modified/config/db.sql 2007-05-08 09:30:12.000000000 +0300
@@ -1,45 +1,57 @@
-CREATE TABLE access (
- usr int(11) NOT NULL default '0',
- hosts text
-) TYPE=MyISAM;
-
-CREATE TABLE admin (
- usr int(11) NOT NULL default '0',
- admin int(11) NOT NULL default '0'
-) TYPE=MyISAM;
-
-CREATE TABLE hosts (
- host varchar(16) NOT NULL default '127.0.0.1'
-) TYPE=MyISAM;
-
-CREATE TABLE users (
- id int(11) NOT NULL default '0',
- usr varchar(16) NOT NULL default 'user',
- pass varchar(255) NOT NULL default 'pass',
- ip varchar(16) NOT NULL default '127.0.0.1',
- last int(11) NOT NULL default '0'
-) TYPE=MyISAM;
-
-CREATE TABLE acct (
- mac_src char(17) NOT NULL default '',
- mac_dst char(17) NOT NULL default '',
- ip_src char(15) NOT NULL default '',
- ip_dst char(15) NOT NULL default '',
- src_port smallint(5) unsigned NOT NULL default '0',
- dst_port smallint(5) unsigned NOT NULL default '0',
- ip_proto char(6) NOT NULL default '',
- packets int(10) unsigned NOT NULL default '0',
- bytes bigint(20) unsigned NOT NULL default '0',
- stamp_inserted datetime NOT NULL default '0000-00-00 00:00:00',
- stamp_updated datetime default NULL,
- PRIMARY KEY (mac_src,mac_dst,ip_src,ip_dst,src_port,dst_port,ip_proto,stamp_inserted)
-) TYPE=MyISAM;
-
-CREATE TABLE hostdetail (
- host VARCHAR( 16 ) NOT NULL,
- name VARCHAR( 128 ) NOT NULL,
- location VARCHAR( 255 ) NOT NULL,
- phone VARCHAR( 64 ) NOT NULL,
- comments TEXT NOT NULL
-);
-
+CREATE TABLE `access` (
+ `usr` int(11) NOT NULL default '0',
+ `hosts` text,
+ PRIMARY KEY (`usr`),
+ UNIQUE KEY `usr` (`usr`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+CREATE TABLE `acct` (
+ `mac_src` char(17) NOT NULL,
+ `mac_dst` char(17) NOT NULL,
+ `ip_src` char(15) NOT NULL,
+ `ip_dst` char(15) NOT NULL,
+ `src_port` int(2) unsigned NOT NULL,
+ `dst_port` int(2) unsigned NOT NULL,
+ `ip_proto` char(6) NOT NULL,
+ `packets` int(10) unsigned NOT NULL,
+ `bytes` bigint(20) unsigned NOT NULL,
+ `stamp_inserted` datetime NOT NULL,
+ `stamp_updated` datetime default NULL,
+ KEY `stamp_updated` (`stamp_updated`),
+ KEY `stamp_inserted` (`stamp_inserted`),
+ KEY `ip_src` (`ip_src`),
+ KEY `ip_dst` (`ip_dst`),
+ KEY `bytes` (`bytes`),
+ KEY `packets` (`packets`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+CREATE TABLE `admin` (
+ `usr` int(11) NOT NULL default '0',
+ `admin` int(11) NOT NULL default '0',
+ PRIMARY KEY (`usr`),
+ UNIQUE KEY `usr` (`usr`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+CREATE TABLE `hostdetail` (
+ `host` varchar(16) NOT NULL,
+ `name` varchar(128) NOT NULL,
+ `location` varchar(255) NOT NULL,
+ `phone` varchar(64) NOT NULL,
+ `comments` text NOT NULL,
+ UNIQUE KEY `host` (`host`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+CREATE TABLE `hosts` (
+ `host` varchar(16) NOT NULL default '127.0.0.1',
+ UNIQUE KEY `host` (`host`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+CREATE TABLE `users` (
+ `id` int(11) NOT NULL default '0',
+ `usr` varchar(16) NOT NULL default 'user',
+ `pass` varchar(255) NOT NULL default 'pass',
+ `ip` varchar(16) NOT NULL default '127.0.0.1',
+ `last` int(11) NOT NULL default '0',
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `usr` (`usr`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
diff -uNr original/edithost.php modified/edithost.php
--- original/edithost.php 2004-10-26 16:59:09.000000000 +0300
+++ modified/edithost.php 2007-05-08 09:24:44.000000000 +0300
@@ -36,16 +36,17 @@
header("Location: index.php");
}
+if (isset($_POST['update'])){
if ($_POST['update'] == "update") {
$host = $_SESSION['edithost'];
$user = $_POST['user'];
$location = $_POST['location'];
$contact = $_POST['contact'];
$comments = $_POST['comments'];
- $qid = mysql_query("UPDATE hostdetail SET user='$user',location='$location',contact='$contact',comments='$comments' WHERE host='$host'", $sql);
+ $qid = mysql_query("UPDATE hostdetail SET name='$user',location='$location',phone='$contact',comments='$comments' WHERE host='$host'", $sql);
unset($_SESSION['edithost']);
header("Location: hosts.php");
-}
+}}
$hostinfo = gethostinfo($_SESSION['edithost'], $sql);
diff -uNr original/hosts.php modified/hosts.php
--- original/hosts.php 2004-10-05 09:47:22.000000000 +0300
+++ modified/hosts.php 2007-05-08 09:24:44.000000000 +0300
@@ -35,7 +35,7 @@
$error = "";
-
+if (isset($_POST['add'])) {
if ($_POST['add'] == "add") {
$ip = $_POST['ip'];
if (is_ip($ip)) {
@@ -44,24 +44,26 @@
$error = "ip already in list";
} else {
$qid = mysql_query("INSERT INTO hosts VALUES('$ip')", $sql);
- $qid = mysql_query("INSERT INTO hostdetail VALUES('$ip', '', '', '', '')", $sql);
+ $qid = mysql_query("INSERT INTO hostdetail VALUES('$ip', '', '', '', 'please change me')", $sql);
$qid = mysql_query("INSERT INTO status VALUES ('$ip',0,'offline','-','offline',0)", $sql);
}
} else {
$error = "invalid ip";
}
-}
+}}
+if (isset($_POST['remove'])){
if ($_POST['remove'] == "remove") {
$ip = $_POST['iplist'];
$qid = mysql_query("DELETE FROM hosts WHERE host='$ip'", $sql);
$qid = mysql_query("DELETE FROM hostdetail WHERE host='$ip'", $sql);
-}
+}}
+if (isset($_POST['edit'])){
if (($_POST['edit'] == "edit") & ($_POST['iplist'] != "")) {
$_SESSION['edithost'] = $_POST['iplist'];
header("Location: edithost.php");
-}
+}}
?>
diff -uNr original/include/functions.php modified/include/functions.php
--- original/include/functions.php 2005-01-31 11:56:27.000000000 +0200
+++ modified/include/functions.php 2007-05-08 09:24:44.000000000 +0300
@@ -95,9 +95,11 @@
function is_logged_in() {
$ret = false;
- $usr = $_SESSION['usr'];
- if (isset($usr)) {
- $ret = true;
+ if (isset($_SESSION['usr'])) {
+ $usr = $_SESSION['usr'];
+ if (isset($usr)) {
+ $ret = true;
+ }
}
return $ret;
}
@@ -194,7 +196,7 @@
function gethostinfo($hostname, $sql) {
$ret = array('host' => $hostname, 'user' => '', 'location' => '', 'contact' => '', 'comments' => '');
- $qid = mysql_query("SELECT user,location,contact,comments FROM hostdetail WHERE host='$hostname'", $sql);
+ $qid = mysql_query("SELECT name,location,phone,comments FROM hostdetail WHERE host='$hostname'", $sql);
list($user, $location, $contact, $comments) = mysql_fetch_row($qid);
$ret['user'] = $user;
$ret['location'] = $location;
diff -uNr original/include/functions-pmacct.php modified/include/functions-pmacct.php
--- original/include/functions-pmacct.php 2004-10-26 16:59:11.000000000 +0300
+++ modified/include/functions-pmacct.php 2007-05-08 09:24:44.000000000 +0300
@@ -34,11 +34,11 @@
$in = 0;
$out = 0;
$total = 0;
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '%$year-$month-$day $hour%'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '$year-$month-$day $hour%'", $sql);
$row = mysql_fetch_row($qid);
$in = $in + $row[0];
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '%$year-$month-$day $hour%'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '$year-$month-$day $hour%'", $sql);
$row = mysql_fetch_row($qid);
$out = $out + $row[0];
@@ -52,11 +52,11 @@
$in = 0;
$out = 0;
$total = 0;
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '%$year-$month-$day %'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '$year-$month-$day %'", $sql);
$row = mysql_fetch_row($qid);
$in = $in + $row[0];
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '%$year-$month-$day %'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '$year-$month-$day %'", $sql);
$row = mysql_fetch_row($qid);
$out = $out + $row[0];
@@ -70,11 +70,11 @@
$in = 0;
$out = 0;
$total = 0;
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '%$year-$month-%'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '$year-$month-%'", $sql);
$row = mysql_fetch_row($qid);
$in = $in + $row[0];
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '%$year-$month-%'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '$year-$month-%'", $sql);
$row = mysql_fetch_row($qid);
$out = $out + $row[0];
@@ -88,11 +88,11 @@
$in = 0;
$out = 0;
$total = 0;
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '%$year-%'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_dst='$ip' AND stamp_inserted LIKE '$year-%'", $sql);
$row = mysql_fetch_row($qid);
$in = $in + $row[0];
- $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '%$year-%'", $sql);
+ $qid = mysql_query("SELECT SUM(bytes) FROM acct WHERE ip_src='$ip' AND stamp_inserted LIKE '$year-%'", $sql);
$row = mysql_fetch_row($qid);
$out = $out + $row[0];
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
