--On Tue, 22 Feb 2000 17:36:56 Eddie Corns wrote: <snip> >This is how I understand it. Imagine you were creating a database to hold >this sort of data, something that would surely spring to mind would be create >a sequential list of records such that as you do each poll you just write into >the _next_ record. This would be the fastest you could do. However when >dealing with SQL (probably similar on other RDBMSs) a) you have no idea >whether it will put sequential records next to each other and b) you have no >way of telling it you are doing things in sequence anyway. This means that >for every single record it has to actually search for the place to insert the >record. Actually you don't know or care which order it puts the data into the database table - you only care about the order it comes out when you need to process or report on it. That's when you use the ORDER BY clause on your SQL SELECT statement. If you've properly built your indexes and keys this should be fairly fast - that's what SQL databases are good at. Of course it means you must save the timestamp of the measurement if you want to use an ORDER BY clause to re-create the time sequence of measurements.
> >In practical terms, my first attempt to update the database by doing an UPDATE >command for each poll required several hours for each iteration, however once >I converted to outputting the data to a file then importing it, it only took a >few seconds! I assume Stuart was noticing similarly huge differences. > Not so huge as that, but as someone pointed out the SQL*Loader is the fastest way to get stuff into a table. We also have some code that uses Oracle's Utl_File package to do reads/writes to OS files, that's actually not that much slower (about 50% longer time), but a lot depends on your table indexes and where you put the COMMIT point. And again, you don't care about the order of rows going in to the table, the SELECT statement will take care of that when they need to be processed.
>I've just discovered that most of the time now (of the 2 minutes per >iteration) is between the SNMP walk command (that gets the ifDescr) and >timeouts for hosts that don't respond. I've just recoded a test version of >the walk code to do the parallel SNMP gets that I do for the main data >gathering and have reduced the time from 1 minute 20 seconds to about 8 >seconds. I also tried various other versions including sending out every >single SNMP get request asynchronously to all ifDescr.X for all 80+ hosts >(after extracting X from ifNumber.0), then waiting for all the results, >that one was still 45 seconds. > This is intriguing. Would you be willing to post a snippet of code where you do the parallel polls? Do you have any thoughts on the resources (memory, cpu) used compared to a straight walk?
Thanks Stuart
--== Sent via Deja.com http://www.deja.com/ ==-- Share what you know. Learn what you don't. -- !! This message is brought to you via the `tkined & scotty' mailing list. !! Please do not reply to this message to unsubscribe. To subscribe or !! unsubscribe, send a mail message to <tkined-request@ibr.cs.tu-bs.de>. !! See http://wwwsnmp.cs.utwente.nl/~schoenw/scotty/ for more information.
This archive was generated by hypermail 2b29 : Mon Jan 08 2001 - 15:27:37 MET