[syslog-ng] syslog-ng + database performance

[Brian Candler]

On Sun, May 14, 2006 at 10:08:56PM +0100, Alexander Clouter wrote:
> Whilst INSERTing the rules you might want to consider some pre-processing.  
> If the log entry comes from a mailserver and its the SMTP daemon, flag that 
> entry in an ENUM column as being part of a SMTP daemon.  You are then 
> effectly creating an index based on the data contained in the log messages, 
> this column you can then index on.

In particular, beware that if you are doing queries like

   select * from logs where msg like '%mail%';

then they will almost certainly be unable to use an index, even if you have
one (unless your DB supports some very fancy full-text indexing). That is,
typically,

   like 'foo%';     -- fast, uses index
   like '%foo';     -- slow, won't use index, forces full table scan
   like '%foo%';    -- slow, won't use index, forces full table scan

So, it's a lot better to pre-parse the log lines into the fields of
interest, and put those fields into separate database columns suitably
indexed, if you intend searching on them.

Regards,

Brian.