[logs] anyone willing to share proper syslog SQL schema & data typing?

[Marcus J. Ranum]

Jason Haar wrote:
>inserts are fine - it's the searching that's the pig.

OK. Good. I just had to check. :) A couple years ago I had a
really fun discussion with a guy who wanted to move from
append-only flat-files to a SQL database because his boss
thought it'd run faster... Sometimes it seems my only role
in life is to be the "harsh fist of syslog reality check" for
people...   That's when my lasers aren't working, that is...

> And the lack of
>constructs like "search between 6pm - 9pm for any "qmail-smtpd" or
>"spamd" records containing "marcus" in them".

Ok so you need time to be one of the index fields - you might
want to consider undumping the time into seconds since the
millenium so that you can do greater/less than searches
really fast. You might want to make "source program" be
another index field (qmail, sshd, etc) as well as "source host"
be another. The abstract message body, though - if you
are searching for "records containing 'marcus'" you're
basically going to be doing a 'grep' in SQL - my guess is
that you'll find it's easier to retrieve the records you want
but if you're using containing/pattern/like queries your
speedups will only result from your ability to reduce the
search-space by putting time boundaries (which you can
do with flat files based on rotational time) and host/program
names (which you can do with...)

> To do that with a mere
>filesystem would make - well - lots of directories ;-)

It sure would!

Consider this: a filesystem is a multi-way branching tree that
is indexable on a single key (the filename). A database is a
multi-way branching tree that is ...   aah, never mind. :)
I've done file system databases and they are ugly. But
if you're cunning about how you set them up (and go wild
with hardlinks) they can amazingly fast. I'm not trying to
talk you out of SQL... Just don't be surprised if it's not a
panacea.

mjr.