Thread: Database theory question

On Apr 17, 2008, at 2:43 PM, Mag Gam wrote:
> Hi All,
>
> While reading this article, History tables and event logging --
> http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html, I
> realized I try to do event logging in SQL.
>
> My question are: Is SQL a good tool for event logging? Does anyone
> have a sample table sctucture for the most optimal way of event
> logging? Current I have 2 tables. 1 table with timestamps, another
> with event. Can anyone recommend a better way?


Hrm... I'm on a plane so I can't look at the article right now, but a
separate table for timestamps doesn't sound so useful. If you had a
*lot* of events for each timestamp, maybe... since timestamps take 8
bytes you could possibly save space by referencing them with an int
instead; you would save 4 bytes per event. But you'd use at least 24
extra bytes to store the timestamp in a separate table, depending on
Postgres version and CPU architecture.

As for "does it make sense", that depends both on the data that
you're storing and how you're using it. From a space standpoint,
you'll be hard-pressed to beat a text logfile and gzip/bzip2. Even if
you need to search the data, grep can often suffice. On the other
hand, if you're doing a lot of searching or other processing, or if
you have a lot of numeric values that you can store in either int or
float4, a database makes more sense.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Mag Gam wrote:
> While reading this article, History tables and event logging --
> http://www.ibm.com/developerworks/web/library/wa-dbdsgn2.html, I realized I
> try to do event logging in SQL.
>
> My question are: Is SQL a good tool for event logging? Does anyone have a
> sample table sctucture for the most optimal way of event logging? Current I
> have 2 tables. 1 table with timestamps, another with event. Can anyone
> recommend a better way?

   It depends on what you want to accomplish in the end. Just storage,
or do you want to perform "complex" queries?

   I store my firewall & sshd authtentication attempt logs in a
postgreql database. And I've written a python based web front-end to
view the data, and pull out statistics and such. I also have a "Generate
abuse report" link for when there's been a break-in attempt.

   Personally, I see no reason to separate timestamps and event entries,
unless you're getting a lot of events during the same timestamp, and
want to save some space. But there are other factors to consider: How
many events do you get per timestamp? How long times does an insert into
a single table vs two tables take?

   You should probably try the simplest possibly solution first, and see
if you need to make it more complex as you can gather empirical data on
how it performs.

--
Kind regards,
Jan Danielsson