Logonalayser with rsyslog and mysql not working.

Posted on 2014-04-02
Medium Priority
Last Modified: 2014-04-09
I have perviously (october 2013 actually with this guide http://www.unixmen.com/install-and-configure-rsyslog-in-centos-6-4-rhel-6-4/) set up Loganalyser with mysql and rsyslog to receive log information from vmware vsphere hosts and store them. This has been running for some months. When I enter the webpage at http;//hostname/loganalyser I get the information below;

No syslog records found - Error Details:
No syslog records found

Logstream Warning
While reading the logstream, the php script timeout forced me to abort at this point.

If you want to avoid this, please increase the LogAnalyzer script timeout in your config.php. If the user system is installed, you can do that in Admin center.
I then start by checking the configuration and various outher troubleshooting steps shown below:

# more /var/www/html/loganalyser/config.php
(pasting relevvant information)
$CFG['UserDBEnabled'] = true;
$CFG['UserDBServer'] = 'localhost';
$CFG['UserDBPort'] = 3306;
$CFG['UserDBName'] = 'rsysdb';
$CFG['UserDBPref'] = 'logcon_';
$CFG['UserDBUser'] = 'rsyslog';
$CFG['UserDBPass'] = 'password!';
$CFG['UserDBLoginRequired'] = true;

$CFG['DefaultSourceID'] = 'Source1';

$CFG['Sources']['Source1']['ID'] = 'Source1';
$CFG['Sources']['Source1']['Name'] = 'My Syslog Source';
$CFG['Sources']['Source1']['ViewID'] = 'SYSLOG';
$CFG['Sources']['Source1']['SourceType'] = SOURCE_DB;
$CFG['Sources']['Source1']['DBTableType'] = 'monitorware';
$CFG['Sources']['Source1']['DBType'] = DB_MYSQL;
$CFG['Sources']['Source1']['DBServer'] = 'localhost';
$CFG['Sources']['Source1']['DBName'] = 'rsysdb';
$CFG['Sources']['Source1']['DBUser'] = 'rsyslog';
$CFG['Sources']['Source1']['DBPassword'] = 'password';
$CFG['Sources']['Source1']['DBTableName'] = 'SystemEvents';
$CFG['Sources']['Source1']['DBEnableRowCounting'] = true;

# mysql -u rsyslog -p
Enter password:
(Connection was sussessful)
mysql> use rsysdb;

Database changed
mysql> select CustomerID, ReceivedAt, DeviceReportedTime, Facility, FromHost from SystemEvents;

# du -h /mnt/syslogs
28G     /mnt/syslogs/mysql/rsysdb
1000K   /mnt/syslogs/mysql/mysql
28G     /mnt/syslogs/mysql
16K     /mnt/syslogs/lost+found
28G     /mnt/syslogs

# mysql -u root -p
Enter password:
(Connection was sussessful)

mysql> use rsysdb;
mysql> delete from SystemEvents where ReceivedAt < subdate(curdate(), 93);  #Also, this command runs via cron every day
Query OK, 0 rows affected (12 min 27.65 sec)

mysql>  exit

# du -h /mnt/syslogs
28G     /mnt/syslogs/mysql/rsysdb
1000K   /mnt/syslogs/mysql/mysql
28G     /mnt/syslogs/mysql
16K     /mnt/syslogs/lost+found
28G     /mnt/syslogs

mysql> select CustomerID, ReceivedAt, DeviceReportedTime, Facility, FromHost from SystemEvents where ReceivedAt > DATE_SUB(CONCAT(CURDATE(), '13:29:00'), INTERVAL 1 WEEK);
Empty set, 65535 warnings (17 min 21.13 sec)

mysql> SELECT CONCAT(table_schema, '.', table_name),
    ->        CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
    ->        CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
    ->        CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
    ->        CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
    ->        ROUND(index_length / data_length, 2)                                           idxfrac
    -> FROM   information_schema.TABLES
    -> ORDER  BY data_length + index_length DESC
    -> LIMIT  10;
| CONCAT(table_schema, '.', table_name) | rows    | DATA   | idx   | total_size | idxfrac |
| rsysdb.SystemEvents                   | 133.78M | 25.82G | 1.28G | 27.11G     |    0.05 |
| mysql.help_topic                      | 0.00M   | 0.00G  | 0.00G | 0.00G      |    0.04 |
| mysql.help_keyword                    | 0.00M   | 0.00G  | 0.00G | 0.00G      |    0.18 |
| mysql.help_relation                   | 0.00M   | 0.00G  | 0.00G | 0.00G      |    1.92 |
| mysql.help_category                   | 0.00M   | 0.00G  | 0.00G | 0.00G      |    0.13 |
| mysql.db                              | 0.00M   | 0.00G  | 0.00G | 0.00G      |    1.94 |
| mysql.tables_priv                     | 0.00M   | 0.00G  | 0.00G | 0.00G      |    NULL |
| mysql.procs_priv                      | 0.00M   | 0.00G  | 0.00G | 0.00G      |    NULL |
| mysql.columns_priv                    | 0.00M   | 0.00G  | 0.00G | 0.00G      |    NULL |
| rsysdb.logcon_searches                | 0.00M   | 0.00G  | 0.00G | 0.00G      |    3.79 |
10 rows in set (0.32 sec)

mysql> select CustomerID, ReceivedAt, DeviceReportedTime, Facility, FromHost from SystemEvents;

# tail /var/log/messages
Apr  2 13:52:37 NOC-SYSLOG01 kernel: Out of memory: Kill process 7140 (mysql) score 932 or sacrifice child
Apr  2 13:52:37 NOC-SYSLOG01 kernel: Killed process 7140, UID 0, (mysql) total-vm:7960704kB, anon-rss:3681596kB, file-rss:104kB

# ./mysqltuner.pl

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.1.69
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 25G (Tables: 13)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 1

-------- Security Recommendations  -------------------------------------------
[!!] User 'mysql@localhost' has no password set.
[!!] User 'root@' has no password set.
[!!] User 'root@hostname' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 22h 16m 54s (12M q [16.532 qps], 77 conn, TX: 288B, RX: 4B)
[--] Reads / Writes: 0% / 100%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (11% of installed RAM)
[OK] Slow queries: 0% (34/12M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.3G
[OK] Key buffer hit rate: 99.9% (118M cached / 170K reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 1360% (952 temp sorts / 70 sorts)
[!!] Temporary tables created on disk: 37% (674 on disk / 1K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 19% (64 open / 332 opened)
[OK] Open file limit used: 9% (100/1K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)
[!!] Connections aborted: 7%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (>= 8M)
    sort_buffer_size (> 1M)
    read_rnd_buffer_size (> 256K)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
To sum things up:
* Every day I delete every record from rsysdb.SystemEvents older then 93 days with a cron job. rsysdb.SystemEvents is the main contributor to the size of rsysdb with 27.11GB.
* I have tried to manually delete from the database, the command executes, but the size of the database is still the same.
* I have tried selecting from rsysdb.SystemEvents data that has been entered within a month, and a week, but receive no result.
* I have tried selecting from rsysdb.SystemEvents data without select  limiting at all, and my who mysql connection gets killed.
* I tried running a mysqltuner script, but could'nt see it offered me any information I needed.

When checking /var/log/messages I can see log entries from the ESXi hosts(from the start of it):

Mar 30 14:20:03 hostname.vmware.host hostprofiletrace:  ^^^hostProfiles-15811-20140330-142003-cli-commands.trc^^^168^^^ Command: esxcli ['system', 'coredump', 'network', 'get'], status: (0), output: "{'Network Server IP'
: '', 'Host VNic': '', 'Enabled': False, 'Network Server Port': 0}"#012#000
But this information is not available to my any longer from the database as I can see.

I check /etc/rsyslog.conf:

#### MODULES ####

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
$ModLoad immark  # provides --MARK-- message capability

# Provides UDP syslog reception
$ModLoad imudp
$UDPServerAddress >hostIpAddressIsHere<
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

$ModLoad ommysql
*.* :ommysql:,rsysdb,rsyslog,>passwordIsHere<
$AllowedSender UDP,, x.x.x.0/24
$AllowedSender TCP,, x.x.x.0/24
This looks good to me, and it has worked before.

Trying a restart:

# service rsyslog restart
Shutting down system logger:                               [  OK  ]
Starting system logger:                                    [  OK  ]

What is the best way to handle this? I would like loganalyser to be up and running and data to be accessible, and also handeled correctly.
Question by:itnifl
  • 3
  • 2
LVL 39

Accepted Solution

Aaron Tomosky earned 2000 total points
ID: 39973931
Ever thought about elasticsearch? Rsyslog pushes right into it and then you can use kibana or whatever to view stuff...might solve your ram and MySQL size issues.

Author Comment

ID: 39988092
Where do I start?

Author Closing Comment

ID: 39988689
I went for that, configuring it right now.
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 39988734
I put my elasticsearch on centos, if you want any help let me know.

Author Comment

ID: 39988760

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Can you run Linux on a Windows system?  Yep.  Here's how.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question