List:General Discussion« Previous MessageNext Message »
From:Michael Gale Date:April 16 2005 11:10pm
Subject:Database load and table design ?
View as plain text  
Hello,

	Currently I am using syslog-sql to store syslog data in a mysql 
database. The table format is something like:

ID	hostname	facility	priority	date	message


ID is auto incrementing



Now I am writting a perl app to calculate stats based on the data in the 
  table which will get run everyday. It is currently doing a loop, here 
is an example:

Select count(facility) from syslog WHERE machine = '$srv' AND facility = 
'$fac' AND date1 > (NOW() - INTERVAL 24 hour)

Now $srv is the name of the host and $fac is the facility name. This 
select statement is in a loop that loops through each server and each 
facility.

When this is running it puts a load on the DB, since there could be 
about 20 host, each with 6 facilities, which equals about:

600 - The number of times that the select statement would be run ....

I suppose that running the query:

select host,facility from WHERE date1 > (NOW() - INTERVAL 24 hour)

and letting perl do the math on the selected results would be less of a 
system load ?

Is there a better way ?

Also I am going to display the results using the following format:


			hostname	host A		host B	...

Facility name

facility A		   X		  X		   X
	
facility B		   X		  x		   X

facility C		   X		  X		   X

....


Is there an easy way to put that into a mysql table ??

Thanks.

Michael



Thread
Database load and table design ?Michael Gale17 Apr
  • Re: Database load and table design ?Dan Nelson17 Apr