DB grew massively, help!

Help
1

Dear community!

First of all, I’m an ex-Observium user and have been with LibreNMS for quite a while now. So far, I managed to solve my issues by applying common sense / browsing forums but this one time, as much as I hate to admit it, I feel I need to ask for advice.

My CentOS VM running LibreNMS has been up for prob around 2 years now and all was fine. I monitor around 40 hosts some of which push out syslog as well. My other MySQL VM has a 120GB /var slice and throughout that time, I never had an issue with disk space.

All of a sudden, the volume is 100% with the main culprit being

X@Y:/var/lib/mysql# du -sh * | sort -h
0 debian-5.5.flag
4.0K mysql_upgrade_info
212K performance_schema
216K phpmyadmin
1.1M mysql
1.4M librenms
5.0M ib_logfile0
5.0M ib_logfile1
111G ibdata1

Since the LibreNMS is the only db on this db host, I suspect that it’s fully responsible for the file size.

So with that, the question is, how do I do housekeeping, identifying records which can be purged, at the same time retaining the latest ~2 months worth of data?

Help is much appreciated!
M

2

Looks like you don’t have ‘innodb_file_per_table=1’ set in my.cnf for MySQL. The issue now is that changing it now has no effect unless you dump and re-import your database :frowning:

3

thanks a lot for the prompt response iaf!

i don’t mind dumping and re-importing as long as you can advise me on how to do that!

4

I’m only on my mobile so it’s difficult to do detailed replies. It’s just a standard MySQL thing though, try following https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-and-reset-a-root-password-in-mysql

5

Hi,

today morning i noticed the same problem. Over night the disk of librenms was full. about 50% of diskspace is taken by /var/lib/mysql/librenms
I do have set ‘innodb_file_per_table=1’ set in my.cnf for MySQL
Any other ideas?

Manfred

6

We store data in mysql so it can be expected to grow. You should be able to see which files in the librenms database dir are big to see where to start looking.

7

I did not check the sizes yesterday, but overnight something on my server took +20% of all disk space. ibdata1 alone is about 25% of all the disk!

user@noc-0:~$ sudo ls -l /var/lib/mysql
[sudo] password for user: 
insgesamt 36888
-rw-r--r-- 1 mysql mysql        0 Aug  7 16:23 debian-5.5.flag
-rw-rw---- 1 mysql mysql 27262976 Okt 31 14:20 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Okt 31 14:20 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Okt 31 14:20 ib_logfile1
drwx------ 2 mysql mysql    12288 Okt 29 00:15 librenms
drwx------ 2 mysql mysql     4096 Aug  7 16:23 mysql
-rw------- 1 mysql mysql        6 Aug  7 16:23 mysql_upgrade_info
drwx------ 2 mysql mysql     4096 Aug  7 16:23 performance_schema

according to https://docs.librenms.org/#Installation/Installation-Ubuntu-1404-Apache/ innodb_file_per_table is set to 1.

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 127.0.0.1
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days	= 10
max_binlog_size         = 100M
innodb_file_per_table=1
8

That only works if you set the option before the db was created. If the librenms folder doesn’t contain any large files then that doesn’t look like it’s the case. It also means the db will never shrink even after cleaning up.

Best you can do is dump the db, drop it and re-import. It will then be created as it should and show you a better idea of what’s taking up the space.