Performance problem with syslog plugin

[randypunter]

Hi There,

I have recently setup a new cacti server, using the prebuilt packages,

I have setup the syslog plugin, and configured rsyslog to accept messages from my network, everything is working ok, but the performance of the syslog.php script is appalling, sometimes taking many minutes to display the messages.

My rsyslog.conf is here

$ModLoad imudp
$UDPServerRun 514
$ModLoad ommysql

$template cacti_syslog,"INSERT INTO syslog_incoming(facility_id, priority_id, program, date, time, host, message) \
values (%syslogfacility%, %syslogpriority%, '%programname%', '%timereported:::date-mysql%', '%timereported:::date-mysql%', '%HOSTNAME%', TRIM('%msg%'))", SQL

*.* >localhost,syslog,cacti,xxxx;cacti_syslog


my config.php is here

$use_cacti_db = false;

if (!$use_cacti_db) {
$syslogdb_type = 'mysql';
$syslogdb_default = 'syslog';
$syslogdb_hostname = 'localhost';
$syslogdb_username = 'cacti';
$syslogdb_password = 'xxxx';
$syslogdb_port = 3306;
}else{
$syslogdb_type = 'mysql';
$syslogdb_default = 'syslog';
$syslogdb_hostname = 'localhost';
$syslogdb_username = 'cacti';
$syslogdb_password = 'xxxx';
$syslogdb_port = 3306;
}

/* field in the incomming table */
$syslog_incoming_config['dateField'] = 'date';
$syslog_incoming_config['timeField'] = 'time';
$syslog_incoming_config['priorityField'] = 'priority_id';
$syslog_incoming_config['facilityField'] = 'facility_id';
$syslog_incoming_config['hostField'] = 'host_id';
$syslog_incoming_config['textField'] = 'message';
$syslog_incoming_config['id'] = 'seq';


if I run php /usr/share/cacti/plugins/syslog/syslog_process.php --debug

SYSLOG: Syslog Table is NOT Partitioned
SYSLOG: Deleted 0, Syslog Message(s) (older than 2017-03-27)
SYSLOG: Unique ID = 14
SYSLOG: Found 49, New Message(s) to process
SYSLOG: Stats 34, Record(s) to the 'syslog_statistics' table
SYSLOG: Found 1, Removal Rule(s) to process
SYSLOG: Deleted 6, Messages for removal rule 'Remove Inline power'
SYSLOG: Found 0, Alert Rules to process
SYSLOG: Moved 43, Message(s) to the 'syslog' table
SYSLOG: Deleted 43, Already Processed Message(s) from incoming
SYSLOG: Deleted 0, Syslog Statistics Record(s)
SYSLOG: Deleted 0, Syslog alarm log Record(s)
SYSLOG: Deleted 0, Syslog Host Record(s)
SYSLOG: Deleted 0, Syslog Host/Facility Record(s)
SYSLOG: Processing Reports...
SYSLOG: We have 0 Reports in the database
SYSLOG: Finished processing Reports...
2017-04-26 20:59:38 - SYSTEM SYSLOG STATS:Time:0.15 Deletes:0 Incoming:49 Removes:6 XFers:0 Alerts:0 Alarms:0 Reports:0


However the performance is still taking over 2 minutes to load any pages in the syslog tab.

Please help

[Osiris]

How many devices? How many rows in syslog table?

[randypunter]

approximately 80 devices and the attached picture shows the table status

Show table status from syslog DB

Screen Shot 2017-05-11 at 08.30.17.png (145.91 KiB) Viewed 1243 times

[Osiris]

Maybe it's better to used a partitioned table? You should move that table to InnoDB and look at the recommendations in Cacti's installer too.