Registrations to Open-AudIT forums are now closed. To ask any new questions please visit Opmantek Community Questions.

Open-AudIT

What's on your network?
It is currently Thu Mar 28, 2024 10:32 pm

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 21 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Performance
PostPosted: Thu Nov 06, 2008 1:17 am 
Superb product, many thanks for all the hard work
We have been seeing slow performance when clicking on a PC name, I have tracked this back to line 181 of include.php:
$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system, network_card WHERE system_uuid = '$pc' OR system_name = '$pc' OR (net_mac_address = '$pc' AND net_uuid = system_uuid) "
The net_uuid = system_uuid causes MYSQL not to use indexes, so this sql statement was taking 17s to execute. Looking at the code it only appears to use only the first result, adding a LIMIT 1 to the statement has improved things dramatically.
$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system, network_card WHERE system_uuid = '$pc' OR system_name = '$pc' OR (net_mac_address = '$pc' AND net_uuid = system_uuid) LIMIT 1";


Server Info: Windows XP running XAMPP

Auditing: 4400 machines, mainly windows XP
W2K3: Active Directory


Top
  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Fri Nov 07, 2008 5:28 am 
Offline
Helper

Joined: Fri Nov 16, 2007 1:32 am
Posts: 73
Location: Dallas,Texas
Good Find!

_________________
1400 Servers Audited (1 hour interval) Applied via a local scheduler, deployed via GPO.
Running OA on IIS6 Web Server
90% Windows 2k3 Server (std,ent)
5% Windows XP
5% Windows 2000


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Tue Nov 11, 2008 1:58 am 
I've reworked the previously mentioned query, and improved a bit.

comment out the following 2 lines in include.php, they should be 181 and 182, make sure you take a copy of it first.

$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system, network_card WHERE system_uuid = '$pc' OR system_name = '$pc' OR (net_mac_address = '$pc' AND net_uuid = system_uuid) LIMIT 1";
$result = mysql_query($sql, $db);

and replace them with the following.

$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system, network_card WHERE system_uuid = '$pc' OR system_name = '$pc' LIMIT 1";
$result = mysql_query($sql, $db);
if (mysql_num_rows($result) >0 ){
$myrow = mysql_fetch_array($result);}
Else {
$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system left join network_card on net_uuid = system_uuid WHERE net_mac_address = '$pc' LIMIT 1";
$result = mysql_query($sql, $db);
$myrow = mysql_fetch_array($result);}

This replaces the single query, which doesn't use indexes, with 2 seperate ones, both of which should use indexes, and only calls the second one if the first returns no results.

I've also looked at some of our commonly used queries, and added some indexes which make them faster. The most noticable of this is on the home page. The down side it is may make updates slower, as mysql has to add the keys at insert/update time.

you can cut and paste the following into mysql query browser.

use openaudit;
alter table system add index (system_name);
alter table system add index net_ip_address(net_ip_address);
alter table system add index system_first_timestamp(system_first_timestamp);

alter table network_card add index (net_ip_address);
alter table network_card add index net_uuid(net_uuid);

alter table system_audits add index system_audits_uuid(system_audits_uuid);

alter table software add index software_name(software_name);
alter table software add key software_first_timestamp(software_first_timestamp);

alter table software_register add index software_title(software_title)


Perhaps the code owner could look to including these changes in the source, if he is happy with them.


Server Info: Windows XP running XAMPP

Auditing: 4400 machines, mainly windows XP
W2K3: Active Directory


Top
  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Tue Nov 11, 2008 5:31 pm 
Offline
Newbie

Joined: Mon Jan 07, 2008 7:58 pm
Posts: 10
Location: Frankfurt, Germany
All i can say is the this change rocks.

I've tested the first part and the improvement in my case is 3-4 sec. to open the Hardware include.php

Thanks for sharing
Greg

_________________
OS : XP, NT, Windows 2k, Windows 2K3, Ubuntu
Auditing: 1020 machines
LDAP: Active Directory


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Thu Dec 04, 2008 2:26 am 
Offline
Newbie

Joined: Fri Nov 14, 2008 12:25 am
Posts: 12
Can we maybe look at adding this to the snv?

_________________
OA Server: Windows Server 2003 / IIS6
Auditing: 1200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows 2008 Server
LDAP: Active Directory


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Sun Dec 07, 2008 6:49 am 
Offline
Contributor

Joined: Fri Sep 28, 2007 12:07 am
Posts: 189
Indexing does make for better SQL query performance. Any idea if it will be added?

_________________
OA Deployment:
Windows 2003 with XAMPP install
80 Windows Servers
250 Windows workstations (mixed XP and 2000)
5 MACs
Multiple printers, switches, routers, firewalls, and other servers (ESX, AIX etc.)


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Tue Dec 16, 2008 2:38 am 
Offline
Contributor

Joined: Fri Jul 28, 2006 6:30 am
Posts: 157
Location: London
[quote="jbsclm"] This replaces the single query, which doesn't use indexes, with 2 seperate ones, both of which should use indexes, and only calls the second one if the first returns no results.


Rather than use two queries I've consolidated it into one like so:
[code] SELECT * FROM
((SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain
FROM system WHERE system_uuid = '$pc' OR system_name = '$pc')
UNION
(SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain
FROM system, network_card WHERE net_mac_address ='$pc' AND net_uuid = system_uuid))
AS U LIMIT1
[/code]
I'll update SVN with the change later.

[quote="jbsclm"]I've also looked at some of our commonly used queries, and added some indexes which make them faster. The most noticable of this is on the home page. The down side it is may make updates slower, as mysql has to add the keys at insert/update time.

Maybe you could identify the queries that could benefit from these additional indexes - I just want to make sure that we're using a good query first before we try fixing it with indexes. Of the indexes you mention, the following are already in the latest SVN version:

[quote="jbsclm"] alter table system add index (system_name);
alter table system add index system_first_timestamp(system_first_timestamp);

alter table software add index software_name(software_name);
alter table software add key software_first_timestamp(software_first_timestamp);

_________________
Cheers, Nick.

[size=85]OA Server: Windows Server 2003 / Apache 2
Auditing: 1600 Workstations, 200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows Vista
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Tue Dec 16, 2008 2:51 am 
Offline
Contributor

Joined: Fri Jul 28, 2006 6:30 am
Posts: 157
Location: London
New query added to SVN 1102.

_________________
Cheers, Nick.

[size=85]OA Server: Windows Server 2003 / Apache 2
Auditing: 1600 Workstations, 200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows Vista
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Tue Dec 16, 2008 7:02 am 
Thanks for that, my technique was to grep the code for joins and then look at whether we were likely to use them. If the join fields weren't indexed I added the index there. I have also turned on the slow query log in mysql, and picked them out of there. The slowest query at the moment is the general search box on the right, it searches many fields, the problem with ours is the software table, we have 4000+ PCs with 150 software titles, so the software table is 600,000 rows and the query seems to do a full scan on it. I am hoping to get some time to have a look at this between Christmas and New Year.


Top
  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Wed Dec 31, 2008 2:07 am 
I've gone through search.php and changed a number of queries to use joins, rather than equality on fields. The greatest impact was on searching the software table, according to the MYSQL slow query log the time for this query has gone from 40+ seconds to under 10.

See the attached file for new version of search.php. I've commented the queries I've changed.


Attachments:
File comment: updated search.php
search_php.zip [3 KiB]
Downloaded 354 times
Top
  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Wed Dec 31, 2008 6:28 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Added at SVN 1110 thanks, does seem to have improved things up quite a bit. :D

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Fri Jan 02, 2009 7:16 pm 
I've changed the order of the joins, search times have improved again.
The original 40+ seconds for the software query is down to 2.
As a different issue searching for a numeric, eg 5000 seems to hit on IP addresses after they have been through the ip_trans_to function, I'll try and understand what this is intendede to achieve.

The improved search.php is attached.


Attachments:
search_php.zip [2.99 KiB]
Downloaded 353 times
Top
  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Fri Jan 02, 2009 11:49 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Next back in the office on Monday, will post to SVN then. Thanks.

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Mon Jan 05, 2009 8:02 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
OK Just posted that . Thanks. :D

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
 Post subject: Re: Performance
PostPosted: Wed Jan 07, 2009 3:17 am 
Offline
Open-AudIT Fellow

Joined: Thu May 17, 2007 5:47 pm
Posts: 568
Location: Italy
Added back again the searching abilities for systems' services and for description/manufacturer into the "Other" table. Probably the search.php version used by jbsclm was outdated.
SVN rev. 1113

_________________
Edoardo


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 21 posts ]  Go to page 1, 2  Next

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group