[syslog-ng] syslog-ng + database performance

Brian Candler B.Candler at pobox.com
Mon May 15 11:58:39 CEST 2006


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.


More information about the syslog-ng mailing list