SYSLOG View does not populate All Programs tab

[mxmartins]

I am running into a Syslog problem and have not figured out the cause of it…

I am sending all my network devices syslogs to the LibreNMS server that is running on a Ubuntu 20.04 VM. Presently, I have around 100 million syslog records in the database.

All is working properly except that the All Programs tab does not populate any program names for me to select from. After the install of LibreNMS, this tab was being populated correctly, but something has changed and now that tab is not populating at all with any information. I have made no changes to LibreNMS since the install.

I’m attaching a copy of the syslog page screen shot, along with the results of the validate.

Looking at /opt/librenms/logs, I see nothing unusual…

[mxmartins]

Here is the validate screen shot

[mxmartins]

This problem appears to be related to the large size of the syslog table.

I decided to “truncate” the syslog table, and now the All Programs tab works fine, as well as the All Priorities tab.

At some point, as the size of the syslog table grows rather large, those two tabs stop working. Is there a particular setting that can be changed that might impact this functionality?

[mxmartins]

Looking at the query used to fill the tab, as reported by slow-query

Time: 211103 10:59:00
User@Host: librenms[librenms] @ localhost []
Thread_id: 10557 Schema: librenms QC_hit: No
Query_time: 20.449829 Lock_time: 0.000014 Rows_sent: 51 Rows_examined: 24151708
Rows_affected: 0 Bytes_sent: 1145
SET timestamp=1635958740;
select distinct program from syslog limit 51 offset 0;

Wouldn’t an index on the ‘program’ field yield faster results?

[mxmartins]

After digging into this problem, it turns out the default mysql/mariaDB settings were not appropriate for my situation, given the extremely large size of the syslog table.

I tweaked the mysql settings/configuration, and now all works as advertised.

[DBMandrake]

100 million syslog records seems an excessive amount to keep, how many months/years logs is that ?

I have mine set to prune syslog entries after 90 days in config.php:

$config['syslog_purge'] = 90;

Unless you have a good reason not to I would recommend setting up some syslog pruning threshold so the syslog entries don’t grow without bound.

After all, syslog agents themselves (like rsyslog, syslog-ng) regularly rotate log files and only keep a certain number of old log files by default for this same reason.

[mxmartins]

I have it configured to only keep 30 days of log entries. But still, it reached around 100 million records.

I have a number of machines all feeding syslog entries to LibreNMS. Right now, with the 30 day pruning, I have around 68 million log entries, but that is not a full 30 days of logs yet.

But with the tweaking of the DB settings, everything is working just fine.