SQLite as a Log?

[James Gray]

In a current project we're discussing using SQLite for all application
logging.

This is a heavy multiprocessing environment, which was one of our
reasons for considering it. We also love the idea of structured
queries helping us debug issues: show me any logged errors between
these times.

The minus of course is a slight loss in transparency. For example,
you can't just tail a log file when you want.

Anyway, I would love to hear from anyone who has done this. Did you
like it? What were the downsides I am missing?

Thanks in advance for any information you can provide.

James Edward Gray II

 

[Joel VanderWerf]

In a current project we're discussing using SQLite for all application
logging.

This is a heavy multiprocessing environment, which was one of our
reasons for considering it. We also love the idea of structured queries
helping us debug issues: show me any logged errors between these times.

The minus of course is a slight loss in transparency. For example, you
can't just tail a log file when you want.

Anyway, I would love to hear from anyone who has done this. Did you
like it? What were the downsides I am missing?

Thanks in advance for any information you can provide.

James Edward Gray II

IIRC one writer blocks the entire sqlite db. You could avoid this using
a gatekeeper server for writing to the log.

Re tailing log files: I avoid that anyway, even with file logging,
because it doesn't know about log rotation. The alternative I've found
helpful is to set up a drb service that lets a process listen for log
entries.

 

[Tim Pease]

In a current project we're discussing using SQLite for all
application logging.

This is a heavy multiprocessing environment, which was one of our
reasons for considering it. We also love the idea of structured
queries helping us debug issues: show me any logged errors between
these times.

The minus of course is a slight loss in transparency. For example,
you can't just tail a log file when you want.

Anyway, I would love to hear from anyone who has done this. Did you
like it? What were the downsides I am missing?

Thanks in advance for any information you can provide.

Have you looked into using CouchDB as the logging destination instead?

I've started a branch of my logging gem that allows you to do this,
but I've had to table it due to other obligations.

The goal is to just dump everything to CouchDB with some mnemonics on
the messages. Then you create views to see all messages for a given
application. All error messages in the past week, etc.

Blessings,
TwP

 

[Ken Bloom]

IIRC one writer blocks the entire sqlite db. You could avoid this using
a gatekeeper server for writing to the log.

Re tailing log files: I avoid that anyway, even with file logging,
because it doesn't know about log rotation. The alternative I've found
helpful is to set up a drb service that lets a process listen for log
entries.

tail -F is useful following a log across log rotations. It follows the
file by filename, not by file descriptor. (I learned this trick debugging
an embedded system that rotated its logs every 5 minutes or so.)

--Ken

 

[Ken Bloom]

In a current project we're discussing using SQLite for all application
logging.

Consider a concurrent RDBMS instead if you can deploy it in your
environment.

If you want to tail the log, then it shouldn't be too hard to write a
script to do it. For a project I run, to look at the last 15 log entries,
I have a shell function:

atlog ()
{
$sql atman -t -e 'select * from atman_log order by time desc limit 15'
}

(Where $sql starts with "mysql" and then adds login information)

If you want to do continuous monitoring, you could use a loop (maybe
write a ruby script) that checks every 2 seconds or something, and
selects all of the log entries that are more recent than the last check.

 

[Matt Williams]

In a current project we're discussing using SQLite for all application
logging.

Have you considered splunk? That way you can do queries and event
correlation easily.

They have a community version which allows you to collect 500MB of logs
a day...

 

[James Gray]

The alternative I've found helpful is to set up a drb service that
lets a process listen for log entries.

That's very interesting. Thanks for the tip.

James Edward Gray II

 

[James Gray]

Have you looked into using CouchDB as the logging destination instead?

Consider a concurrent RDBMS instead if you can deploy it in your
environment.

I appreciate the suggestions but this is for installed software, so I
want to keep the dependencies pretty light. That's why I'm thinking
of SQLite.

James Edward Gray II

 

[James Gray]

Have you considered splunk? That way you can do queries and event
correlation easily.

They have a community version which allows you to collect 500MB of
logs
a day...

I need a local solution. Thanks for the idea though.

James Edward Gray II

 

[Matt Williams]

I need a local solution. Thanks for the idea though.

I saw you wanted to keep it light, so yeah, it is probably not the best
solution.
However, you can log to a local database -- as I understand it, that's
what we're doing at work...

Another option would be to use syslog & batch load (every N minutes)
into sqlite....

Good Luck!

Matt

 

[Jeremy Hinegardner]

Have you looked into using CouchDB as the logging destination instead?

I've started a branch of my logging gem that allows you to do this, but
I've had to table it due to other obligations.

The goal is to just dump everything to CouchDB with some mnemonics on the
messages. Then you create views to see all messages for a given
application. All error messages in the past week, etc.

I'll have to take a look at that and possibly do something similar with
Amalgalite. Maybe an Amalgalite Appender? That might also fill what James is
thinking about.

enjoy,

-jeremy

 

[James Gray]

I'll have to take a look at that and possibly do something similar
with
Amalgalite. Maybe an Amalgalite Appender? That might also fill
what James is thinking about.

Yeah, that would be really neat.

James Edward Gray II