Information: Forum is in read-only mode
For details and other support options see https://www.adiscon.com/news/support-forum-set-to-read-only-mode/

rsyslog+loganalyzer+mysql and slow searching

General discussions here

Moderator: alorbach

Google Ads


rsyslog+loganalyzer+mysql and slow searching

Postby mateuszj » Mon Nov 25, 2013 12:12 pm

Hi,
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
64GB RAM

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.
Example:
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?
mateuszj
New
 
Posts: 1
Joined: Mon Nov 25, 2013 11:03 am

Urgent Question?

  • Pulling out your Hair?
  • Wasting Time and Money?
  • Deadline Approaching?

Re: rsyslog+loganalyzer+mysql and slow searching

Postby alorbach » Tue Dec 03, 2013 10:50 am

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:
http://wiki.rsyslog.com/index.php/PhpLo ... te_Records

best regards,
Andre Lorbach
alorbach
Site Admin
 
Posts: 1627
Joined: Thu Feb 13, 2003 11:55 am

Re: rsyslog+loganalyzer+mysql and slow searching

Postby 300cpilot » Wed Mar 25, 2015 8:13 pm

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.
300cpilot
New
 
Posts: 8
Joined: Wed Mar 25, 2015 7:53 pm

Google Ads



Return to General

Who is online

Users browsing this forum: No registered users and 1 guest

cron