[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Using SQLite to record Web log file data (a threading
From:       John Stanton <johns () viacognis ! com>
Date:       2006-10-29 16:13:25
Message-ID: 4544D325.2050700 () viacognis ! com
[Download RAW message or body]

drh@hwaci.com wrote:
> "David Gewirtz" <david@ZATZ.com> wrote:
> 
>>I've been exploring SQLite for a number of applications, but one I'd like to
>>embark on soon is using SQLite to record Web site log file data, so I can
>>perform SQL-based analysis on my logs, rather than using some thing like
>>Analog.
>>
>>Unfortunately, each Web access on the server is likely to be in its own
>>thread. The scenario I'd probably have to build might go something like
>>this:
>>
>>* Web server launches
>>* SQLite issues sqlite3_open to log db, gets log_ID
>>* Web server handles user 1 in thread 1, which writes to log db
>>* Web server handles user 2 in thread 2, which writes to log db
>>* Web server handles user n in thread n, which writes to log db
>>* Web server handles admin request for log analysis, which reads from log db
>>* Web server begins shutdown, closes log db
>>* Web server shuts down
>>
>>>From my reading, it's just not clear to me whether this is bad behavior for
>>SQLite 3.3.7. Can SQLite handle this sort of sequence reliably. If not, any
>>suggestions about how I might proceed or how I should think about it?
>>
> 
> 
> The way I handle this at www.sqlite.org is that web log data just
> gets appended to an ordinary text file.  Then when I want to do
> analysis, I make a copy of the text file and import it into an
> SQLite database.  I think do queries against the SQLite database
> to extract the information I want to know.
> 
> You could perhaps automate this so that a background task took
> the unprocessed tail of your log file and added it an SQLite
> database every 10 minutes or so.  Or every 30 seconds.  Just 
> rig it so that you only have one process trying to write at
> a time and so that you do not have to take transaction overhead
> for every single web hit.
> 
> SQLite itself is not the best tool for doing multiple concurrent
> writes from different threads or processes.
> 
> --
> D. Richard Hipp  <drh@hwaci.com>
> 
> 
> -----------------------------------------------------------------------------
> To unsubscribe, send email to sqlite-users-unsubscribe@sqlite.org
> -----------------------------------------------------------------------------
> 
I made Sqlite accept log data and analysis from a web server.  From a 
multi-threaded web server I used a seperate thread to handle the DB 
insertion.  It is driven from a queue of log transactions posted by the 
active threads.  That way it is single streamed and has no contention 
problems and the active threads have a very fast mechanism to post their 
log data, just queue a pointer to the buffer used by the thread.  Each 
time a thread activates it gets a freed buffer from a pool.  When the 
log thread finishes a transaction it drops the buffer onto the free list.

The advantage of this approach is that it gives real time log analysis. 
  To that end some frequently accessed summaries are maintained.  Just 
to maintain the log in a simple form in an Sqlite database offers 
little, if any, advantage over keeping a text file and analysing it from 
time to time.

-----------------------------------------------------------------------------
To unsubscribe, send email to sqlite-users-unsubscribe@sqlite.org
-----------------------------------------------------------------------------

[prev in list] [next in list] [prev in thread] [next in thread]