MYSQL consuming CPU

Help
1

We are having issues with our LibreNMS server consuming all of the CPU. A reboot of the server resolves it temporarily and then it grows back to consume the CPU. Running top shows that mysqld is the culprit.

If I run SHOW FULL PROCESSLIST, it starts off a dozen processes and grows from there. THe highest I have seen so far is 76 processes. A lot of them seem to be DELETE statements from the ipv4 mac table.

I checked the info on this table and it is estimated to be about 1.1 GB in size .
SELECT COUNT(*) FROM ipv4_mac; shows 12,107,446 rows and is growing each time I run the command.

Is there any reason that this table should be this big and is there a way to clean it up? Or am I looking in the wrong place for performance issues?

Monitoring:
82 systems
approx 1600 ports
approx 400 sensors (might have counted those wrong)

librenms@librenms:~$ ./validate.php

Component Version
LibreNMS 1.35-107-gbdf02f1
DB Schema 230
PHP 7.0.22-0ubuntu0.16.04.1
MySQL 10.0.33-MariaDB-0ubuntu0.16.04.1
RRDTool 1.5.5
SNMP NET-SNMP 5.7.3

====================================

[OK] Database connection successful
[OK] Database schema correct
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.

2

I have also found by sorting the contents of the ipv4_mac table that there are many duplicate rows.
There are also many rows with identical IP addresses and mac addresses but different device_id and port_id. Not sure if this helps or not.

Thank you for all of your help.

3

Get the device_id that is showing a lot of duplicated data and then run ./discovery.php -h DEVICEID -d -m arp-table and pastebin the output

4

I noticed that all of the “duplicates” have a context_name = null in the ipv4_mac table
Also the ipv4_address is not the IP address of the device ID (but this might be normal since I do not know what this data is used for)

I ran the command you posted on 4 different deviceIDs

deviceID 23 (pfsense firewall) - https://pastebin.com/j0pLRvPs
deviceID 58 (ipam server) - https://pastebin.com/5eQFQDNN
deviceID 6 (Sharp Color Copier) - https://pastebin.com/PiFS4hWA
deviceID 84 (Dell Switch Stack) - https://pastebin.com/r4e9TkEn

Thanks.

5

The ipv4_mac table is now up to 14,268,438 rows and 1.4 GB.

What would happen if I just drop the contents of this table and then let it repopulate with the next poll?

6

randy im seeing the same thing I wonder if we could have the daily.sh do maintenance on the ARP-table to help clear it out after so many days? @laf

7

So, would it be too bad to run

TRUNCATE ipv4_mac;

and start the table again from a clean state?

8

You can definitely truncate the table. There is a bug where it is always duplicating entries. I’ll try to get a fix in.

1 Like
9

Fix is merged: https://github.com/librenms/librenms/pull/8147

10

Thanks. You guys rock!

2 Likes