Re: SQLite Clarification

[Clif Flynt <clif@cflynt.com>]

Hi,
  I just talked the audit/sqlite stuff over with Richard Hipp.  
  
  One approach would be to have the audisp application put the data
into a TEMP table, then dump those tables to the main disk tables at
some interval.  After the data is copied to the permanent tables, the
TEMP table is cleared.

  The TEMP tables resides in memory, and are private to the application
- there can be no contention problems putting the data into those tables.

  If writing the temp table to the main table fails because the db is
locked, the temp tables don't get cleared and there is just that much
more to be dumped when the disk is available.

  My experience with SQLite is that dumping multiple records is nearly
as fast as dumping one.  It's a K+N*x type situation where N is the
number of records, K is a "constant", and x is much smaller than K.  I
think the main contributor to K is the disk latency time, while the
main contributor to x is memory access time.

  When it comes to someone gathering so much audit data that the
logging routines get swamped, he agrees that dumping to a flat file is
the fastest way to get the data out of memory and onto the disk.

  He thinks that my current approach of scanning the flat files and
building a one-time database may be the best way to avoid dragging down
the kernel.

  The compromise might be to keep a permanent database, and just update
it with the new records when the reporter is run, or from a cron job,
rather than needing to include all records since the beginning of time
(or when you want to start generating a report from.)

  The way write/read contention problems is handled is that the write
will block for up to N (configurable) milliseconds, and then will
either succeed or fail and return a failure message.

  Clif

-- 
.... Clif Flynt ... http://www.cflynt.com ... clif@cflynt.com ...
.. Tcl/Tk: A Developer's Guide (2nd edition) - Morgan Kauffman ..
..13th Annual Tcl/Tk Conference:  Oct 9-13, 2006,  Chicago, IL ..
.............  http://www.tcl.tk/community/tcl2006/  ............