Re: Database load and table design ?
In the last episode (Apr 16), Michael Gale said: > Currently I am using syslog-sql to store syslog data in a mysql > database. The table format is something like: > > IDhostnamefacilityprioritydatemessage > > 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 ? Why not SELECT host, facility, COUNT(*) FROM syslog WHERE date1 > (NOW() - INTERVAL 24 HOUR) GROUP BY host, facility -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database load and table design ?
Hello, Currently I am using syslog-sql to store syslog data in a mysql database. The table format is something like: ID hostnamefacilityprioritydatemessage 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: hostnamehost A host B ... Facility name facility A X XX facility B X xX facility C X XX Is there an easy way to put that into a mysql table ?? Thanks. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
