[EMAIL PROTECTED] wrote:
"David Gewirtz" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
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 [EMAIL PROTECTED]
-----------------------------------------------------------------------------
