Daily db not cleaning database huge

Help
1

[root@DIT-LibreNMS librenms]# ./validate.php ====================================

Component Version
LibreNMS 1.52-70-gf3ba894
DB Schema 2019_05_30_225937_device_groups_rewrite (135)
PHP 7.2.22
MySQL 5.5.64-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

I noticed my database has grown and not shrinking.
For some reason my ibdata1 is huge. I tried running the daily.sh and it hands on cleaning DB. It errors out after about 12 hours saying mysql error.
ibdata1 = 763.8 GiB

Refreshing device group table relationships
Could not connect to database, check logs/librenms.log.
Could not connect to database, check logs/librenms.log.
Could not connect to database, check logs/librenms.log.

./daily.sh
Re-running /opt/librenms/daily.sh as librenms user
Updating SQL-Schema OK
Cleaning up DB

I have put this in my.cnf
innodb_file_per_table=1

2

I was able to get it to finish running but database size did not shrink at all.

3

Yes mysql does not shrink files. You can dump to sql and re-import.

4

I reimported and found it took more space. It now gives this error
Sep 22 10:44:49 LibreNMS postfix/cleanup[679]: E633F20A12C7: message-id=20190922204449.E633F20A12C7@LibreNMS
Sep 22 10:44:49 LibreNMS postfix/cleanup[679]: warning: E633F20A12C7: write queue file: No space left on device
Sep 22 10:44:49 LibreNMS postfix/pickup[16320]: warning: maildrop/29EC320A040D: error writing E633F20A12C7: queue file write error
Sep 22 10:44:50 LibreNMS postfix/pickup[16320]: E6B1E20A12C7: uid=0 from=
Sep 22 10:44:50 LibreNMS postfix/cleanup[679]: E6B1E20A12C7: message-id=20190922204450.E6B1E20A12C7@LibreNMS
Sep 22 10:44:50 LibreNMS postfix/cleanup[679]: warning: E6B1E20A12C7: write queue file: No space left on device
Sep 22 10:44:50 LibreNMS postfix/pickup[16320]: warning: maildrop/BA46C20A12A1: error writing E6B1E20A12C7: queue file write error
Sep 22 10:44:51 LibreNMS postfix/pickup[16320]: E734A20A12E8: uid=0 from=
Sep 22 10:44:51 LibreNMS postfix/cleanup[679]: E734A20A12E8: message-id=20190922204451.E734A20A12E8@LibreNMS
Sep 22 10:44:51 LibreNMS postfix/cleanup[679]: warning: E734A20A12E8: write queue file: No space left on device
Sep 22 10:44:51 LibreNMS postfix/pickup[16320]: warning: maildrop/02C7420A126E: error writing E734A20A12E8: queue file write error
Sep 22 10:44:52 LibreNMS postfix/pickup[16320]: E7BF620A12E8: uid=0 from=
Sep 22 10:44:52 LibreNMS postfix/cleanup[679]: E7BF620A12E8: message-id=20190922204452.E7BF620A12E8@LibreNMS
Sep 22 10:44:52 LibreNMS postfix/cleanup[679]: warning: E7BF620A12E8: write queue file: No space left on device
Sep 22 10:44:52 LibreNMS postfix/pickup[16320]: warning: maildrop/B5B7D20A10DD: error writing E7BF620A12E8: queue file write error

5

Ok so I reimported it. It shrank by half. Do I have to keep doing this when it grows or will it start to shrink now? I noticed its not kept in ibdata1 anymore.

6

if you have the daily cleanup running it should keep it at a reasonable size.

That is because you set innodb_file_per_table=1

7

what should innodb_file_per_table=1 be set too?

8

Whatever you want, LibreNMS suggest it is set because it makes some management things easier, such as shrinking the db files.

1 Like