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