Order of entries in gravity database / domainlist table

Help
1

I was looking at the query log and noticed:

  • the additional_info field is only used for regex blacklist entries (and cname data), all other (white and black) list entries never get additional info.
  • although I have several blacklist regexes, from lists such as mmotti, adguardteam, nextdns, ... ,very few entries appear in the additional_field (thus never triggered by a query).

Looking at the results (grafana):

image
image607×333 29.2 KB

same can be achieved with this query (It may not be wise to run this if you don't have a MAXDBDAYS setting, limiting the number of entries in the database, I use MAXDBDAYS=8):

pihole-FTL sqlite3 ".timeout = 5000" ".headers on" ".mode column" \
"ATTACH '/etc/pihole/pihole-FTL.db' AS pihole_FTL" \
"ATTACH '/etc/pihole/gravity.db' AS gravity" \
"SELECT count(pihole_FTL.queries.additional_info), gravity.domainlist.id, gravity.domainlist.domain FROM pihole_FTL.queries \
INNER JOIN gravity.domainlist \
ON pihole_FTL.queries.additional_info = gravity.domainlist.id \
WHERE typeof(additional_info) = 'integer' \
GROUP BY additional_info ORDER BY count(additional_info) DESC;"

I was wondering if it would make sense to ensure the regexes with the highest count appear first in the list (e.g. id 1 -> .*;querytype=!A , id 2 -> ^(.+\.)?(facebook|fb(cdn|sbx)?|tfbnw)\.[^.]+$ , ...), may be wrong here...

I assume pihole-FTL loads (compiles) the regexes, as read from the database (in that order) and looks for a match. once a regex matches, regex processing stops. Putting the most used regex first, might speed up FTL (to be confirmed by DL6ER?)

2

On first glance, I'd suspect that the computational cost for dynamically determining the rank based on max counts may already be higher than the potential gains by omittíng further regex for blocked domains (which would yet have to be confirmed or rejected).

It also should be noted that the true cost of regex evaluation lies with the overhead imposed on every query - i.e. including allowed domains, where every single regex has to be confirmed as non-matching.

There would virtually be no benefit in considering a regex evaluation rank for allowed domains, while they would potentially incur the full cost of the additional effort for dynamically computing ranks.

3

In addition to the answer Bucking_Horn gave, you need to consider an additional required step. If you change the id based on the number of hits, you would need to change them not only in the gravity.db but also in the entire pihole-ftl.db (additional_info). Otherwise the query log link to the domain table would not work anymore. I also don't feel comfortable to change ids (primary key autoincrement), kind of loosing integrity, to maybe gain a few miliseconds.

4

Both the arguments from yubiuser and bucking_horn make a lot of sense, reordening the entries in the domainlist table would only be useful / possible, when starting fresh:

  • an empty query_storage table
  • knowledge about the regexes that are most frequently used

assuming (still needs to be confirmed) pihole-FTL stops processing regexes, as soon as there is a match, having the entries with the most hits first would decrease processing time, even though this would only be a few miliseconds. There is a lot going on on my raspberry pi (not necessarily pihole related), I can use everything I can gain, but, agreed, overkill for the average implementation...
I can get this information for blacklist regexes (see above), for some reason, the additional_info field isn't populated for other matches, which prevents me from obtaining this info. Knowlegde is power...
Maybe populating the additional_info field for all domainlist matches is something that might be considered (as a feature)?

5

Confirmed.

Not so much confirmation here. Talking about milliseconds is likely way too large - unless you are running your Pi-hole on a robot vacuum cleaner or inside a smart light bulb. You could use DEBUG_REGEX to get some more insight into regex speed where the timestamps will still be only millisecond-accurate. Maybe your results will surprise us.

I do see the need to do this for regex but why would we need it for exact matches? That's why they are called exact in the first place. You can easily get this with a COUNT and GROUP BY from the raw data. This will even immediately give you the domain without having to do the id -> domain lookup, in addition.

6

you are correct that it is possible to get the domain using count and group by, however, I feel much more confident, using the id.

The idea is:

  • over the years, users add whitelist and blacklist entries as needed / desired, this to maintain a good browsing experience. However, webpages change, due to new partners the company affiliates with, or the page simply changes.
  • users add entries, is there really anybody that removes entries (looks back)?
  • by being able to report on used / unused exact and regex entries, automation is only one step away, e.g. disable entries that haven't been used for a certain period.
  • using the same query with different parameters for all situations makes life easier, but, OK, not really necessary...

I'm using MAXDBDAYS=8, the queries listed below execute without any problems.
I've added a timestamp limitation in the queries, but I'm not sure, cannot test, if this has the desired effect on larger databases (MAXDBDAYS=365 - default). It would be nice if somebody could test this and report back.
Until than, WARNING, It may not be wise to run this if you don't have a MAXDBDAYS setting , limiting the number of entries in the database.

query used to list exact blacklist entries, with id:

sqlite3 ".timeout = 5000" ".headers on" ".mode column" \
"ATTACH '/etc/pihole/pihole-FTL.db' AS pihole_FTL" \
"ATTACH '/etc/pihole/gravity.db' AS gravity" \
"SELECT count( pihole_FTL.queries.domain), gravity.domainlist.id, gravity.domainlist.domain FROM pihole_FTL.queries \
INNER JOIN gravity.domainlist \
ON pihole_FTL.queries.domain = gravity.domainlist.domain \
WHERE gravity.domainlist.type = 1 \
AND pihole_FTL.queries.timestamp > strftime('%s','now','-10 days') \
AND pihole_FTL.queries.status IN (1, 4, 5, 6, 7, 8, 9, 10, 11, 15, 16) \
GROUP BY pihole_FTL.queries.domain ORDER BY count( pihole_FTL.queries.domain) DESC;"

grafana result (with id), there are no unused blacklist entries, thus no data:

image
image2567×573 56.3 KB

query used to list exact whitelist entries, with id:

sqlite3 ".timeout = 5000" ".headers on" ".mode column" \
"ATTACH '/etc/pihole/pihole-FTL.db' AS pihole_FTL" \
"ATTACH '/etc/pihole/gravity.db' AS gravity" \
"SELECT count( pihole_FTL.queries.domain), gravity.domainlist.id, gravity.domainlist.domain FROM pihole_FTL.queries \
INNER JOIN gravity.domainlist \
ON pihole_FTL.queries.domain = gravity.domainlist.domain \
WHERE gravity.domainlist.type = 0 \
AND pihole_FTL.queries.timestamp > strftime('%s','now','-10 days') \
AND pihole_FTL.queries.status IN (0, 2, 3, 12, 13, 14) \
GROUP BY pihole_FTL.queries.domain ORDER BY count( pihole_FTL.queries.domain) DESC;"

grafana result, partial:

image
image2609×914 125 KB

thus already possible, except for regex whitelist entries. Wouldn't mind at all if this was added (additional_data).