Tip: How reduce DB stat size

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

I didn't know this. Is this working?

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

@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

@procopotode default value for MAXDBDAYS ist 365 days.

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

Thank you for sharing!

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.