Syslog_purge, does it work?

Hi All,

Our LibreNMS install is configured to receive syslog from many devices. Disk usage under the database has continued to grow. The culprit is the syslog table. I’m attempting to find a purge interval that prevents the table from growing.

Initially we were running $config[‘syslog_purge’] = 90. Then we tried 30. Now I’m at 7 and I continue to lose space. “show table status like ‘syslog’” does not indicate an increase in “data_free”, which, I would guess, indicates we aren’t purging records within the table.

I’m not expecting the table to shrink, that’s not how MariaDB works, but I would expect new syslog to occupy previously emptied rows within the existing DB footprint.

Is there a way to confirm daily.sh is actually purging the syslog table of older entries? It’s running successfully from cron. I have also run it manually just to see the logging. I see other tables have entries cleared but there’s no mention of the syslog table. Below is the output from this morning’s cron run of daily.sh. Anybody have any ideas?

Caching PeeringDB data
Peering DB integration disabled
Returned: 0
Updating to latest release
HEAD is now at 00baac5... Bump version to 1.57
Returned: 0
Updating Composer packages
> LibreNMS\ComposerHelper::preInstall
Loading composer repositories with package information
Installing dependencies from lock file
Nothing to install or update
Generating optimized autoload files
> Illuminate\Foundation\ComposerScripts::postAutoloadDump
> @php artisan package:discover --ansi
Discovered Package: fideloper/proxy
Discovered Package: laravel/laravel
Discovered Package: laravel/tinker
Discovered Package: martinlindhe/laravel-vue-i18n-generator
Discovered Package: nesbot/carbon
Discovered Package: oriceon/toastr-5-laravel
Discovered Package: spatie/laravel-cors
Discovered Package: tightenco/ziggy
Package manifest generated successfully.
> LibreNMS\ComposerHelper::postInstall
> Illuminate\Foundation\ComposerScripts::postInstall
> @php artisan vue-i18n:generate
Returned: 0
Updating SQL-Schema
Nothing to migrate.
Returned: 0
Updating submodules

Returned: 0
Cleaning up DB
Refreshing alert rules queries
Clearing OS cache
Refreshing device group table relationships
Eventlog cleared for entries over 30 days
Perf times cleared for entries over 30 days
Device perf cleared for entries over 7 days
Ports fdb cleared for entries over 10 days
All deleted ports now purged
Returned: 0
Fetching notifications
[ Fri, 15 Nov 2019 02:13:39 -0500 ] http://www.librenms.org/notifications.rss (44)
[ Fri, 15 Nov 2019 02:13:40 -0500 ] misc/notifications.rss (55)
[ Fri, 15 Nov 2019 02:13:40 -0500 ] Updating DB  Done
Returned: 0
Caching PeeringDB data
Peering DB integration disabled
Returned: 0

Okay, it looks like the block that is responsible for this work is not being run on my installation.

In daily.php, line 64:
if ($options['f'] === 'syslog') {

I added some echo statements before the if and after (right before the try block). The first echo shows up in my terminal, the second does not.

It looks like it’s being run with the correct option:
[root@nms1 librenms]# ps axuww|grep daily.php
librenms 4909 0.1 0.1 387984 32376 pts/1 S+ 13:42 0:00 php /opt/librenms/daily.php -f syslog

Yep, sure enough. I just changed that if statement to “if (1) {” and it dropped into that block but did not run the dbDelete() and instead exited that block in the break.

I’ve determined that the only problem I’m seeing is that dbFetchRow() is returning an array (with a single value) and it looks like dbDelete() doesn’t want this as an argument. If I replace the returned array with its value, my syslog table is pruned:

diff -u daily.php.orig daily.php
--- daily.php.orig      2019-11-18 12:25:16.445269132 -0500
+++ daily.php   2019-11-19 11:30:34.322893355 -0500
@@ -72,6 +72,14 @@
             $rows = (int)dbFetchCell('SELECT MIN(seq) FROM syslog');
             while (true) {
                 $limit = dbFetchRow('SELECT seq FROM syslog WHERE seq >= ? ORDER BY seq LIMIT 1000,1', array($rows));
+
+                # If dbFetchRow hands us an array, we convert it to a number so dbDelete() works.
+                if (is_array($limit)) {
+                    foreach($limit as $value) {
+                        $limit = $value;
+                    }
+                }
+
                 if (empty($limit)) {
                     break;
                 }
1 Like

Submit a fix as Pull https://github.com/librenms/librenms/pulls

My gross hack?

Librenms depends on people giving back you think its not good enough but in all honesty everything helps.

You guys are alright. I’ll submit a pull request.

Here’s a smaller patch. This is a much simpler fix that was recommended by murrant. It’s in PR #10850.

diff -u daily.php.orig daily.php
--- daily.php.orig      2019-11-19 13:49:24.452085484 -0500
+++ daily.php   2019-11-19 13:18:28.986709226 -0500
@@ -71,7 +71,7 @@
         if (is_numeric($syslog_purge)) {
             $rows = (int)dbFetchCell('SELECT MIN(seq) FROM syslog');
             while (true) {
-                $limit = dbFetchRow('SELECT seq FROM syslog WHERE seq >= ? ORDER BY seq LIMIT 1000,1', array($rows));
+                $limit = dbFetchCell('SELECT seq FROM syslog WHERE seq >= ? ORDER BY seq LIMIT 1000,1', array($rows));
                 if (empty($limit)) {
                     break;
                 }

PR #10850 has been merged.