Hi all,
>
>
>
Just wondering how people are dealing with tables that are used for
logging, ie: insert only tables supporting occasional queries used for
audit or event logs.

These tables will keep growing and there is no need to keep them that
large so what is the best strategy in managing the data in them.
>
>
>
I was thinking of going with MyIsam tables because I don't need
transactions n the table data is self contained and portable. I would
change my application to insert into tables which are named with a
datestamp component (ie: have the apps determine the name of the table
based on current time before doing an insert) and then have a cron job
to create new tables as needed and to also backup and remove older
tables as they are no longer being used.
>
>
>
Any thoughts on this ?
Well, just a few thoughts
- with MyISAM, delayed insert and REPLACE proved to be very useful for me
- for a very intensive logging application (1000 hits per second) I found
it better to keep the hits in textfiles (on ramdisk), and periodically (in
every minute or so) process them and feed them to MySQL, using a bunch of
speedup techniques

TH I am talking about "preprocessed" logfiles. Storing logs "as-is" in a
database seems an overkill for me. I would just use standard textfiles
with regular bzipping for that.
- Fagzal

2/6/07, Yong Lee <ylee (AT) eqo (DOT) comwrote:

Hi all,
>
>
>
Just wondering how people are dealing with tables that are used for
logging, ie: insert only tables supporting occasional queries used for audit
or event logs.

These tables will keep growing and there is no need to keep them that
large so what is the best strategy in managing the data in them.

I was thinking of going with MyIsam tables because I don't need
transactions n the table data is self contained and portable. I would
change my application to insert into tables which are named with a datestamp
component (ie: have the apps determine the name of the table based on
current time before doing an insert) and then have a cron job to create new
tables as needed and to also backup and remove older tables as they are no
longer being used.

Any thoughts on this ?

Use of the ARCHIVE engine in conjunction with a partitioning scheme works
wonders for logging.