SQLite databases are very large

smcclos1

For some unknown reason, I am running out of space. I was able to determine where it is, but not how to control it:

[2.6.0-RELEASE][root@pfSense-backup3.home.arpa]/usr/local/datastore/sqlite: ls -l
total 3134179
-rw-r--r-- 1 root wheel 45056 May 4 12:50 alert_all.sqlite
-rw-r--r-- 1 root wheel 4803751936 May 22 08:41 conn_all.sqlite
-rw-r--r-- 1 root wheel 1972715520 May 22 08:41 dns_all.sqlite
-rw-r--r-- 1 root wheel 214843392 May 22 08:40 http_all.sqlite
-rw-r--r-- 1 root wheel 8192 May 4 12:50 sip_all.sqlite
-rw-r--r-- 1 root wheel 907161600 May 22 08:40 tls_all.sqlite

johnpoz

@smcclos1 I don't even have that folder.. What packages are you running? Not sure what would be storing data in a sqlite db there.

smcclos1

@johnpoz Nothing fancy just iperf, ntopng, Open-VM-Tools, tftpd.

I thought it would be related to the logs, but I cannot get any documentation to support it.

johnpoz

@smcclos1 well the only package that makes any sense to be using any sort of db would be ntop.. I don't currently have that installed..

I would look to its logging or whatever to reduce the size..

smcclos1

@johnpoz I don't think that is it. I removed the package, yet the files are increasing in size.

[2.6.0-RELEASE][root@pfSense-backup3.home.arpa]/usr/local/datastore/sqlite: ls -l
total 3147315
-rw-r--r-- 1 root wheel 45056 May 4 12:50 alert_all.sqlite
-rw-r--r-- 1 root wheel 4823379968 May 22 09:33 conn_all.sqlite
-rw-r--r-- 1 root wheel 1981362176 May 22 09:33 dns_all.sqlite
-rw-r--r-- 1 root wheel 217030656 May 22 09:33 http_all.sqlite
-rw-r--r-- 1 root wheel 8192 May 4 12:50 sip_all.sqlite
-rw-r--r-- 1 root wheel 910487552 May 22 09:33 tls_all.sqlite

jimp

It probably is ntopng, it's possible the processes for both ntopng and redis are still running somehow. Check the process list (e.g. ps uxawwd)

I'm not seeing any specific reference to those filenames or that directory, though.

You could pkg install lsof and use that to find which process is holding open those files and trace it back from there.

smcclos1

Found it:

[2.6.0-RELEASE][root@pfSense-backup3.home.arpa]/usr/local/datastore/sqlite: lsof | grep sqlite
zenarmor- 42139 root 12u VREG 975122615,3200245394 45056 39910 /usr/local/datastore/sqlite/alert_all.sqlite
tcsh 51105 root cwd VDIR 975122615,3200245394 10 39950 /usr/local/datastore/sqlite
ipdrstrea 62585 root 11uw VREG 975122615,3200245394 4864839680 39953 /usr/local/datastore/sqlite/conn_all.sqlite
ipdrstrea 62585 root 14uw VREG 975122615,3200245394 4864839680 39953 /usr/local/datastore/sqlite/conn_all.sqlite
tcsh 78254 root cwd VDIR 975122615,3200245394 10 39950 /usr/local/datastore/sqlite
lsof 99407 root cwd VDIR 975122615,3200245394 10 39950 /usr/local/datastore/sqlite
grep 99620 root cwd VDIR 975122615,3200245394 10 39950 /usr/local/datastore/sqlite

I installed zenarmor (sunnyvalley.cloud) on the server. completely forgot about that. Will move the issue there

smcclos1

Resolved issue in Sunny Valley Forum.

Now back under control, and have new script to see how large these files are in compressed and uncompressed size

find . -type f -iname "*all.sqlite" -exec sh -c "echo -n '    de-compressed: '; du -Ah '{}'; echo -n 'compressed (used): '; du -h '{}'; echo ' '" \;
   de-compressed:  44K ./usr/local/datastore/sqlite/alert_all.sqlite
compressed (used): 4.5K ./usr/local/datastore/sqlite/alert_all.sqlite

  de-compressed: 8.0K ./usr/local/datastore/sqlite/sip_all.sqlite
compressed (used): 4.5K ./usr/local/datastore/sqlite/sip_all.sqlite

   de-compressed:  73M ./usr/local/datastore/sqlite/tls_all.sqlite
compressed (used):  30M ./usr/local/datastore/sqlite/tls_all.sqlite

   de-compressed: 443M ./usr/local/datastore/sqlite/conn_all.sqlite
compressed (used): 187M ./usr/local/datastore/sqlite/conn_all.sqlite

   de-compressed: 184M ./usr/local/datastore/sqlite/dns_all.sqlite
compressed (used):  78M ./usr/local/datastore/sqlite/dns_all.sqlite

   de-compressed:  58M ./usr/local/datastore/sqlite/http_all.sqlite
compressed (used):  12M ./usr/local/datastore/sqlite/http_all.sqlite