Page 1 of 1

rsyslog+loganalyzer+mysql and slow searching

PostPosted: Mon Nov 25, 2013 12:12 pm
by mateuszj
I have on my server rsyslog 5.8.10 with loganalyzer and mysql 5.1.66.
My server is:
24 cores of Intel Xeon X5650 @ 2.67GHz

My rsyslog database contains over 200,000,000 records (I have about 500 messages per second so it grows pretty fast).
The problem is slow performance while I'm trying to search something.
Probably it's not LogAnalyzer fault, because if I would do SELECT manually it's the same.
I try to search records where message contains string "esmtp xx.yy.zz." (ip address). It takes about 200 seconds until it's finished.
I've increased key_buffer_size to 1024MB, read_buffer_size and sort_buffer_size to 512 and 128MB and it doesn't help.
I've also tried either on InnoDB or MyISAM engine, there are no big difference.

Is the any way to solve this problem? It is something wrong in my configuration, or it's just working like that?

Re: rsyslog+loganalyzer+mysql and slow searching

PostPosted: Tue Dec 03, 2013 10:50 am
by alorbach
This is a basic problem with all database engines like mysql. At a certain amount of data records, search queries become slow.
One way to keep this problem under control is to delete old data regularly. A script to do this is already included in Loganalyzer.

In order to use it, take a look to this wiki entry here: ... te_Records

best regards,
Andre Lorbach

Re: rsyslog+loganalyzer+mysql and slow searching

PostPosted: Wed Mar 25, 2015 8:13 pm
by 300cpilot
I joined this forum today for a simular issue. We have to keep all logs for a year from our network gear. It writes about 110mb a day to the mariaDB server.

I have to regularly optimize the database, the mysqlcheck program will do it.
(On Linux Version)
mysqlcheck -uroot -p --optimize --databases myDatabase

It will take a while to run through and it should improve the speed when it is finished.