Solved

Logonalayser with rsyslog and mysql not working.

Posted on 2014-04-02
5
2,545 Views
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;
Killed


# 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;
Killed

# 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@127.0.0.1' 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:127.0.0.1,rsysdb,rsyslog,>passwordIsHere<
$AllowedSender UDP, 127.0.0.1, x.x.x.0/24
$AllowedSender TCP, 127.0.0.1, 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.
0
Comment
Question by:itnifl
  • 3
  • 2
5 Comments
 
LVL 38

Accepted Solution

by:
Aaron Tomosky earned 500 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.
0
 
LVL 2

Author Comment

by:itnifl
ID: 39988092
Where do I start?
0
 
LVL 2

Author Closing Comment

by:itnifl
ID: 39988689
I went for that, configuring it right now.
0
 
LVL 38

Expert Comment

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

Author Comment

by:itnifl
ID: 39988760
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 32
Multilanguage Database Design in MySQL 5 38
php image upload 3 27
html input clean up 3 32
SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now