Patch: Speed up search with Show=Recent

FAQ

Memberlist

Profile

Patch: Speed up search with Show=Recent

Cacti Forum Index -> Plugin: (MAC Track)

Author

Message

gthe
Cacti User

Joined: 29 Jul 2006
Posts: 94
Location: RU

Posted: Thu Mar 15, 2007 7:10 am Post subject: Patch: Speed up search with Show=Recent

Sorry for my English.
I use mactrack:
Site: 1
Devices: 33
Data Collection Timing: 6 min.
Data Retention Duration: 2 month.
So, simple query with show=recent is very ssssslow.
My idea is to create one more table for store only Recent rows.
Step 0
Backup all!
It is my first work in php.
I did not test it on windows.
Work with mactrack-0.0.1b.
Step 1

Code:

CREATE TABLE `cacti`.`mac_track_recent_ports` (
`row_id` int(10) unsigned NOT NULL auto_increment,
`site_id` int(10) unsigned NOT NULL default '0',
`device_id` int(10) unsigned NOT NULL default '0',
`hostname` varchar(40) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`vlan_id` varchar(5) NOT NULL default 'N/A',
`vlan_name` varchar(50) NOT NULL default '',
`mac_address` varchar(20) NOT NULL default '',
`ip_address` varchar(20) NOT NULL default '',
`dns_hostname` varchar(200) default '',
`port_number` varchar(10) NOT NULL default '',
`port_name` varchar(50) NOT NULL default '',
`date_last` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`first_scan_date` datetime NOT NULL default '0000-00-00 00:00:00',
`count_rec` int(10) unsigned NOT NULL default '0',
`active_last` int(2) unsigned NOT NULL default '0',
PRIMARY KEY (`row_id`),
UNIQUE KEY `port_number` (`port_number`,`ip_address`,`mac_address`,`device_id`,`vlan_id`),
KEY `site_id` (`site_id`),
KEY `description` (`description`),
KEY `mac` (`mac_address`),
KEY `hostname` (`hostname`),
KEY `device_id` (`device_id`),
KEY `ip_address` (`ip_address`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Database for Tracking Device Recent MAC''s';

Then add some change to mactrack_utilities.php:
LINE 35:
before:

Code:

include_once($config['base_path'] . "/include/bottom_footer.php");
break;
case 'mactrack_utilities_purge_scanning_funcs':

after:

Code:

   include_once($config['base_path'] . "/include/bottom_footer.php");
   break;
case 'mactrack_utilities_rebuild_db_recent':
   include_once($config['base_path'] . "/include/top_header.php");
   include_once($config['base_path'] . "/plugins/mactrack/lib/mactrack_functions.php");

   mactrack_utilities();
   mactrack_utilities_db_recent();

   include_once($config['base_path'] . "/include/bottom_footer.php");
   break;
case 'mactrack_utilities_purge_scanning_funcs':

LINE 396:
before:

Code:

</td>
</tr>
<tr bgcolor="#<?php print $colors["form_alternate2"];?>">
<td class="textArea" width="150" valign="top">

after:

Code:

</td>
</tr>
<tr bgcolor="#<?php print $colors["form_alternate1"];?>">
 <td class="textArea" width="150" valign="top">
 <a href='mactrack_utilities.php?action=mactrack_utilities_rebuild_db_recent'>Perform Recent Database Rebuild</a>
 </td>
 <td class="textArea" valign="top">
 Delete all record from mac_track_recent_ports and recreate.
 </td>
</tr>
<tr bgcolor="#<?php print $colors["form_alternate2"];?>">
 <td class="textArea" width="150" valign="top">

mactrack_functions.php
END of file
before

Code:

db_execute("REPLACE INTO mac_track_scan_dates (SELECT DISTINCT scan_date from mac_track_ports);");
mactrack_debug("Finished deleting old records from the main database.");
}
?>

after

Code:

db_execute("REPLACE INTO mac_track_scan_dates (SELECT DISTINCT scan_date from mac_track_ports);");
mactrack_debug("Finished deleting old records from the main database.");
}
/* perform_mactrack_db_recent - This utility removes all record from mac_track_recent_ports and recreate.
*/
function perform_mactrack_db_recent() {
global $colors;
mactrack_debug("Started recreate all records from the mac_track_recent_ports.");
db_execute("TRUNCATE TABLE mac_track_recent_ports");
db_execute("INSERT INTO mac_track_recent_ports
            (site_id, device_id, hostname, dns_hostname, description,
            vlan_id, vlan_name, mac_address, ip_address,
            port_number, port_name, date_last, first_scan_date, count_rec)
            SELECT site_id, device_id, hostname, dns_hostname, description,
            vlan_id, vlan_name, mac_address, ip_address,
            port_number, port_name, max(scan_date), min(scan_date), count(device_id)
            FROM mac_track_ports
GROUP BY site_id,device_id, mac_address, port_number, ip_address, vlan_id");
db_execute("OPTIMIZE TABLE mac_track_recent_ports");
mactrack_debug("Finished recreate all records from the mac_track_recent_ports.");
}
?>

Now add data to new table - see recent_util.png in attach

Step 2.
automaticly fill data after every mactrack polls.

poller_mactrack.php
LINE: 524
before

Code:

   mactrack_debug("Finished updating site table with collection statistics.");
   /* purge the ip address and temp port table */
   db_execute("TRUNCATE TABLE mac_track_temp_ports");

after

Code:

   mactrack_debug("Finished updating site table with collection statistics.");
// update recent table
db_execute("UPDATE mac_track_recent_ports SET active_last=0;");
db_execute("INSERT INTO mac_track_recent_ports " .
               "(site_id, device_id, hostname, dns_hostname, description, " .
               "vlan_id, vlan_name, mac_address, ip_address, " .
               "port_number, port_name, date_last, first_scan_date, count_rec) " .
               "SELECT site_id, device_id, hostname, dns_hostname, description, " .
               "vlan_id, vlan_name, mac_address, ip_address, " .
               "port_number, port_name, scan_date, min(scan_date), count(device_id) " .
               "FROM mac_track_temp_ports " .
"GROUP BY site_id, device_id, mac_address, port_number, ip_address, vlan_id " .
"ON DUPLICATE KEY UPDATE count_rec=count_rec+1, active_last=1 ; " );
mactrack_debug("Finished updating recent table.");
   /* purge the ip address and temp port table */
   db_execute("TRUNCATE TABLE mac_track_temp_ports");

The new table less old - see mysql_recent.png in attach.

Step 3.
Change search with show=recent from mac_track_recent_ports + Opportunity of removal of records.
setup.php
LINE:220
before

Code:

$nav["mactrack_view.php:"] = array("title" => "Mac Track Viewer", "mapping" => "index.php:", "url" => "mactrack_view.php", "level" => "1");
$nav["mactrack_utilities.php:"] = array("title" => "Device Tracking Utilities", "mapping" => "index.php:", "url" => "mactrack_utilities.php", "level" => "1");

after

Code:

$nav["mactrack_view.php:"] = array("title" => "Mac Track Viewer", "mapping" => "index.php:", "url" => "mactrack_view.php", "level" => "1");
$nav["mactrack_view.php:actions"] = array("title" => "Actions", "mapping" => "index.php:,mactrack_view.php:", "url" => "", "level" => "2");
$nav["mactrack_utilities.php:"] = array("title" => "Device Tracking Utilities", "mapping" => "index.php:", "url" => "mactrack_utilities.php", "level" => "1");

mactrack_view.php
LINE:24
before

Code:

define("MAX_DISPLAY_PAGES", 21);
load_current_session_value("report", "sess_mactrack_view_report", "macs");

after

Code:

define("MAX_DISPLAY_PAGES", 21);
$device_actions = array(
1 => "Delete",
);
if (!isset($_REQUEST["action"])) { $_REQUEST["action"] = ""; }
switch ($_REQUEST["action"]) {
case 'actions':
form_actions();
exit;
}
load_current_session_value("report", "sess_mactrack_view_report", "macs");

LINE:57
before

Code:

      $title = "Device Tracking - MAC to IP Report View";
      include_once($config['base_path'] . "/plugins/mactrack/include/top_mactrack_header.php");
      mactrack_view_macs();
      include($config['base_path'] . "/include/bottom_footer.php");

after

Code:

      $title = "Device Tracking - MAC to IP Report View";
      include_once($config['base_path'] . "/plugins/mactrack/include/top_mactrack_header.php");
if ($_REQUEST["m_rowstoshow"] == 2) {
mactrack_view_recent_macs();
}else {
mactrack_view_macs();
}
      include($config['base_path'] . "/include/bottom_footer.php");

LINE:462
before

Code:

   if (($apply_limits) && ($row_limit != 999999)) {
      $query_string .= " LIMIT " . ($row_limit*($_REQUEST["m_page"]-1)) . "," . $row_limit;
   }
}else{

after

Code:

   if (($apply_limits) && ($row_limit != 999999)) {
      $query_string .= " LIMIT " . ($row_limit*($_REQUEST["m_page"]-1)) . "," . $row_limit;
   }
}elseif($_REQUEST["m_rowstoshow"] == 2){
$query_string = "SELECT
row_id, site_name, description, hostname, mac_address, ip_address, dns_hostname, port_number,
port_name, vlan_id, vlan_name, date_last as max_scan_date, count_rec, active_last
FROM mac_track_recent_ports
LEFT JOIN mac_track_sites ON (mac_track_recent_ports.site_id = mac_track_sites.site_id) " .
str_replace("mac_track_ports", "mac_track_recent_ports", $sql_where) .
" ORDER BY mac_track_recent_ports.hostname, mac_track_recent_ports.ip_address, mac_track_recent_ports.count_rec desc, mac_track_recent_ports.port_number";
   if (($apply_limits) && ($row_limit != 999999)) {
      $query_string .= " LIMIT " . ($row_limit*($_REQUEST["m_page"]-1)) . "," . $row_limit;
   }
}else{

END
before

Code:

}
html_end_box(false);
}

?>

after:

Code:

Time of performance of inquiries has increased in tens times

Rezult - see recent_view.png

recent_view.png

Description:

Filesize:

79.86 KB

Viewed:

814 Time(s)

mysql_recent.png

Description:

Filesize:

8.84 KB

Viewed:

814 Time(s)

recent_util.png

Description:

Filesize:

53.74 KB

Viewed:

814 Time(s)

Display posts from previous:

	Cacti Forum Index -> Plugin: (MAC Track)	All times are GMT - 5 Hours
Page 1 of 1