Tip: How reduce DB stat size

General Customizing Pi-hole
1

Some tip how reduce query stats in sqlite3 db. Good for the Pis. :wink:

First you should top pi-hole

sudo service pihole-FTL stop

Than open the pi-hole db:

sqlite3 /etc/pihole/pihole-FTL.db

And now you can delete entries older than 30 days.

sqlite> SELECT count(*) FROM queries;
13092920
sqlite> DELETE FROM queries WHERE timestamp <= strftime('%s', datetime('now', '-30 day'));
sqlite> VACUUM;

Now start pi-hole again

sudo service pihole-FTL start

DB size before cleanup:

-rw-r--r-- 1 pihole pihole 829186048 Dez 14 12:43 pihole-FTL.db

DB size after delete+vacuum:

-rw-r--r-- 1 pihole pihole 292651008 Dez 14 12:49 pihole-FTL.db

Delete and vacuum process can take long time on the pi. Depends on how many data you will delete.

1 Like
3

I didn't know this. Is this working?

4

There is a configuration option to limit the number of days retained in the long term database, and Pi-Hole will do this automatically.

https://docs.pi-hole.net/ftldns/configfile/

1 Like
5

@jfb having added MAXDBDAYS=90 to my /etc/pihole/pihole-FTL.conf how do I trigger pinhole to purge the older data?

Also, do you know what the default value is? i.e. how many days does it keep in the absence of this line in the conf file?

Thanks

1 Like
6

@procopotode default value for MAXDBDAYS ist 365 days.

I don't know how often (daily?) is Pi-Hole deleting the data from the DB.

7

Thank you for sharing!

8

The Pi-hole is deleting hourly if I'm not mistaken. However, to reduce stress on your SD card, they are merely markes as discarded with will be overwritten with new queries. Saves one cycle of writing (no zero overwriting during deletion). I would worry more about the log files. Disable logging if you don't need it.

9

Alas this no longer works.

sqlite> DELETE FROM queries WHERE timestamp <= strftime('%s', datetime('now', '-30 day'));
Error: cannot modify queries because it is a view

But this seems to work:

sqlite> DELETE FROM query_storage WHERE timestamp <= strftime('%s', datetime('now', '-30 day'));

EDIT: I didn't have enough space to run either the DELETE or the VACUUM SQL commands. Lots of time wasted, though. :slight_smile:

So I wound up tossing the old database and starting new. I changed pihole-FTL.conf to add MAXDBDAYS=180. Hopefully, that will prevent the database from growing so large next time (it was 11G, btw). The following log entry after restarting seems to confirm that:

[2023-08-12 22:15:02.734 2784M]    MAXDBDAYS: max age for stored queries is 180 days

(The default is 365 days.)

Then:

systemctl stop pihole-FTL.service
rm /etc/pihole/pihole-FTL.db
systemctl start pihole-FTL.service

I read in this thread that pihole-FTL recreates an empty database if you do this as described above.

The following log entry (and subsequent apparent happiness of the system) seems to confirm that:

[2023-08-12 22:15:02.744 2784M] No database file found, creating new (empty) database

My pihole is apparently back in business. I hope this helps someone else out there.

1 Like
10

@fbicknel thx for sharing!