MonitorWare Knowledge Base

I have been using a rsyslog/Log Analyzer/apache/Php/MariaDB on Minimal install of Centos 6.6 for a year now. It is a VM running on a IBM blade server, all storage is iscsi@4 gig, 40 gig backplane. The server has been in use for a year and the database is at 12.6 gig in size. I upgraded to Log Analyzer 3.6.6 yesterday.

Yesterday after optimizing the database I noticed that it was still slow so I started looking. So here are the questions:

1. Is there a default my.cnf that is recommended to be used for this? MariaDB's default my.cnf is almost blank.
2. What should the thread count be and cache sizes?

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

Hi,

I would properly recommend to try the new 4.1.2 version of Loganalyzer to see if it changes anything with the performance issues:
http://loganalyzer.adiscon.com/downloads/

In your Sources options, make sure "Enable Row Counting" is DISABLED. This feature costs a lot of performance on large databases.

best regards,
Andre

The question is if you really need the data to be present inside the database. You may consider exporting them to text files if it is just for archival and very uncommon to access them. Or alternatively move older data to a separate table.

This all started when I asked the powers that be if I could purge the old data. I was then given the orders to retain everything for one year. :(

I did try the beta version on this and it performed the same, but I might reload it because of the following changes I made.

Right now we have about 7 million entries from our firewalls in the message table alone. They are busy, some days over 200 hits a second. The issue is that now they want to add all the switches, servers, xenservers etc. It has required a rethink of the whole setup. It just isn't fast enough and had some issues if I was quering the db, entries would be dropped. In the default rsyslog will only retry once, then it drops the updated packet. So I enabled rsyslog que

Rsyslog was changed to allow caching if the Mariadb is off line. Add the following lines to the rsyslog.conf and create the folder listed, with proper access.
From http://www.rsyslog.com/doc/rsyslog_high ... _rate.html

$WorkDirectory /rsyslog/work # default location for work (spool) files
$MainMsgQueueFileName mainq # set file name, also enables disk mode
$ActionResumeRetryCount -1 # infinite retries on insert failure
$ActionQueueType LinkedList # use asynchronous processing
$ActionQueueFileName dbq # set file name, also enables disk mode

The create DB script puts the message column in the SystemEvents table in as text, and this makes all searches slooow. It may do this for other columns but I haven't look into it yet.

I modify the Database as follows:
Idea From http://stackoverflow.com/questions/6148 ... type-alter

From Mysql client on linux server:
alter table t1 modify column name varchar(255);

However I used phpmyadmin to modify the rsyslogdb, table SystemEvents, Column Message. Clicked change, then selected varchar and 255 characters. hit ok and let it sit and sit and sit, took about 20 minutes.

Now searches for * take 38 seconds with Enable Row Counting checked to finish. Before it would take almost 5 minutes. It does take about 20 seconds to go to the next page though you wouldn't really want to.

Next step is to add an additional rsyslog server to funnel log transactions to the db server. Then before it gets to large ad a slave db server

Now if I have done anything stupid please let me know quickly! :)

The reason we could not export the old into an excel or something was because they need to search from one location when they do an audit. Makes it difficult for a non-techie to run it. We tried.

Do NOT enable "Enable Row Counting" on large databases! This setting will slowdown perfomance as MYSQL goes through the whole database before it returns data to Loganalyzer.

best regards,
Andre Lorbach