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.

Does https://docs.pi-hole.net/ftldns/configfile/#maxdbdays not already?

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