Yeah, that should have much the same affect as checking out that branch from a 5.0 system. If you want to be safe, go from 4.3.2 to 5.0, and then check out the gravity performance branch of core afterward. But it shouldn't make much difference!
You can always test a workflow on something like a digital ocean droplet / disposable VM instance of some kind before you do it for real 
Can you please just cache every list so if the download in the future fails it can be re-used? I believe you didn't think about server failures when removing this feature.
Also /var/cache is a thing btw, there is no need to keep it in /etc.
Asked and answered earlier:
Thank your for another enlightening explanation, @DL6ER 
I took a glance at the DDL from Templates/gravity.db.sql.
My understanding of your design is most certainly only superficial, but I share my thoughts anyhow.
I saw that you are not using indexes at all, other than those enforced automatically by primary key constraints (this renders my earlier comments quite useless indeed).
The gravity table stands out as it declares only one text field, which is also its primary key.
I suspect an artifical integer id to be lighter on index creation with regards to both memory consumption and comparison speed for insertions.
Would there be a chance to introduce such an artificial id for the gravity table? If needed, creating a second index on the text field only after bulk loading?
Of course, if the latter is needed, it might easily overcompensate any performance gain achieveable by using an artificial integer.
I also noticed some trigger definitions.
I suspect the performance penalty to be substantially less than those of an index, but still:
Any chances you could get away with disabling triggers during bulk insert?
Removed it from my lists for all tests.
I've typed this during the pihole -g wait periods, so forgive me, if some comments are no longer necessary or valid after this AMAZING result.
pihole 4.3.2 (pihole -g) approximately 2.5 minutes
start: Thu 23 Jan 09:40:40 CET 2020
finished: Thu 23 Jan 09:42:03 CET 2020
Number of unique domains trapped in the Event Horizon: 2.150.519
new SD card (etcher), pihole with v4.3.2 (fully configured with existing whitelist, regex and adlists.list - 75 entries)
echo "release/v5.0" | sudo tee /etc/pihole/ftlbranch
pihole checkout core release/v5.0
pihole checkout web release/v5.0
pihole beta5 (pihole -g / database v9) approximately 1 hour 46 minutes
start: Thu 23 Jan 11:10:41 CET 2020
time before the first list starts processing (Flushing gravity table): Thu 23 Jan 11:19:44 CET 2020 (9 minutes)
finished: Thu 23 Jan 12:56:16 CET 2020
Number of gravity domains: 3303054 (2.154.469 unique domains)
database size (gravity.db): 214.760KB
applied "Add gravity database 9->10 update script #3089"
pihole beta5 (pihole -g / database v10) approximately 1 hour 50 minutes
start: Thu 23 Jan 13:00:37 CET 2020
time before the first list starts processing (Flushing gravity table): Thu 23 Jan 13:10:56 CET 2020 (10 minutes)
finished: Thu 23 Jan 14:50:59 CET 2020
Number of gravity domains: 3303342 (2.154.728 unique domains)
database size (gravity.db): 214.868KB
new SD card (etcher), pihole with v4.3.2 (fully configured with existing whitelist, regex and adlists.list - 75 entries)
echo "release/v5.0" | sudo tee /etc/pihole/ftlbranch
pihole checkout core tweak/gravity_performance
pihole checkout web release/v5.0
pihole tweak/gravity_performance (pihole -g / database v9) approximately 2 minutes
start: Thu 23 Jan 15:04:22 CET 2020
finished: Thu 23 Jan 15:06:20 CET 2020
Number of gravity domains: 530379 (530.379 unique domains)
database size (gravity.db): 72.176KB
applied "Add gravity database 9->10 update script #3089"
pihole tweak/gravity_performance (pihole -g / database v10) approximately 2.1 minutes
start: Thu 23 Jan 15:09:02 CET 2020
finished: Thu 23 Jan 15:11:12 CET 2020
Number of gravity domains: 530379 (530.379 unique domains)
database size (gravity.db): 72.176KB
@DL6ER: THIS IS AMAZING...
however, there appears to be something wrong, look at the number of unique domains (difference between beta5 and gravity_performance - a small difference can be explained by changing lists, gravity_performance produces only 25% of the beta5 result).
never the less, GREAT IMPROVEMENT, find the last bug, and we are one step closer to an ideal world.
I checked the firewall logs, no abnormal messages (blocks or drops) from pfsense firewall or SURICATA.
Also checked the pihole log, no abnormal entries found.
Checked the system logs, no abnormal warnings or errors found.
I noticed during list processing, a file /etc/pihole/gravity.db-journal is created, size changes during processing. This file can be quite large, example 88.885KB
Flushing the database in beta5 takes a very long time, my earlier suggestion to build gravity in a temp table eliminates the need to flush it...
again a lot to ask but anyway, while your at it...
could you change:
[i] Target: https://hostfiles.frogeye.fr/multiparty-trackers-hosts.txt
[✓] Status: Retrieval successful
[✓] Adding adlist with ID 11 to database table
into
[i] time - Target: https://hostfiles.frogeye.fr/multiparty-trackers-hosts.txt
[✓] time - Status: Retrieval successful
[✓] time - Adding adlist with ID 11 to database table
[✓] time - Done adding adlist with ID 11 to database table
possible other suggestion, to increase performance:
Be warned, this file is not the sole source of truth. You'll also have to check the gravity migration upgrade scripts... This is something that is not very nice, however, it ensures that everyone at any level of the gravity database version can be migrated to the most recent status. I will update this template to version 10 (most recent).
This is not the most recent state. The gravity table currently has a 2D index: [domain, adlist_id].
The index on the domain field is ultimately needed (actually the 2D index as above is needed). This is the tree that is scanned by FTL to check whether a domain is in gravity or not. Without this index, it'd have to search the entire database table which is a big No-No.
There should be no triggers on the gravity table, did I miss something?
No, I suppose it's rather my understanding of your DB design that is lacking
I only saw a bunch of triggers on several of the list tables, none for gravity itself.
This is already implemented. You should already see
instead of
Also already done. There is only a rather lightweight sed call running after each download. I don't think one very large sed is any notable faster than a few smaller calls. It's not there there are thousands of calls to sed, the overhead should not really be visible.
With TEMP you mean a separate one (we already do this in the gravity_new table)? Or do you mean an in-memory one? The latter may become problematic once it grows up to 200MB or so. It doesn't seem to be a good idea.
This impact is actually smaller than you'd maybe expect as it roughly scales with log_2(n).
Instead, on the new branch, we collect everything in one big file and inject this at once instead of step by step. You may have noticed the absence of
in my new performance branch.
Measuring the time is quite a lot of work in bash. There are many places where it'd need to be done. I'm not going to do this right now, we can, however, add specific time measurements in the most critical parts. But not as a permanent solution.
I found a typo because of which only the last downloaded list was actually used. Fix pushed.
running pihole -up
[✓] Installing latest logrotate script
[i] Backing up /etc/dnsmasq.conf to /etc/dnsmasq.conf.old
[✓] man pages installed and database updated
[i] Testing if systemd-resolved is enabled
[i] Systemd-resolved is not enabled
[✓] Restarting lighttpd service...
[✓] Enabling lighttpd service to start on reboot...
[i] Restarting services...
[✓] Enabling pihole-FTL service to start on reboot...
[i] Restarting pihole-FTL service...
Unable to complete update, please contact Pi-hole Support
Have you checked sudo service pihole-FTL status ?
I haven't changed anything outside gravity.sh which is not active here.
pihole-FTL was NOT running (after restart by pihole -up)
sudo service pihole-FTL start -> OK
pihole version
Pi-hole version is v4.3.2-390-gc5713b6 (Latest: v4.3.2)
AdminLTE version is v4.3.2 (Latest: v4.3.2)
FTL version is vDev-021cd83 (Latest: v4.3.1)
correct?
after pihole -up and start pihole-FTL:
pihole tweak/gravity_performance (pihole -g / database v10) approximately 14 minutes
start: Thu 23 Jan 19:03:35 CET 2020
finished: Thu 23 Jan 19:17:18 CET 2020
Number of gravity domains: 3303704 (2.155.018 unique domains)
database size (gravity.db): 250.816KB
Apparently, you fixed it, pihole -g execution time is now acceptable, given the large number of lists and unique domains. the process (step) that is consuming the most time is Activating new gravity table
The cached list also have reappeared in /etc/pihole
When will this be available in beta5?
This is not the very latest revision (it does not include the timers), but there has been no functional change (= also no extra speed-up) since then. Go ahead and try with the timers included. It may shed some light on what is the slowest part for you.
I'm still wondering what @jfb measures, I think he wanted to set up a similar setup with similar lists than you. Just to rule out you have some strange factor like a slow SD card or whatsoever.
When we think we sqeezed the last drop out of it. And when we removed the extra time commands. There is not much going on besides this performance branch as we're speaking. The beta seems overall to really work amazingly well (even if not amazingly fast, but we're working on it ).
pihole -up
[i] Checking for updates...
[i] Pi-hole Core: update available
[i] Web Interface: up to date
[i] FTL: update available
after pihole -up:
pihole tweak/gravity_performance (pihole -g / database v10) approximately 20 minutes
start: Thu 23 Jan 19:32:58 CET 2020
finished: Thu 23 Jan 19:52:30 CET 2020
Number of gravity domains: 3303813 (2.155.118 unique domains)
database size (gravity.db): 429.556KB
[i] Storing downloaded domains in new gravity table...
real 7m11.525s
user 1m9.096s
sys 0m12.848s
[✓] Storing downloaded domains in new gravity table
[i] Activating new gravity table...
real 6m59.576s
user 0m1.414s
sys 0m6.253s
[✓] Activating new gravity table
[i] Number of gravity domains: 3303813 (2155118 unique domains)
[i] Number of exact blacklisted domains: 0
[i] Number of regex blacklist filters: 20
[i] Number of exact whitelisted domains: 32
[i] Number of regex whitelist filters: 0
[✓] Cleaning up stray matter
[✓] Flushing DNS cache
[✓] DNS service is running
[✓] Pi-hole blocking is Enabled
[i] The install log is located at: /etc/pihole/install.log
Update Complete!
Current Pi-hole version is v4.3.2-392-g0939c35
Current AdminLTE version is v4.3.2
Current FTL version is vDev-9889501
The database has almost doubled in size!
microSDHC card 32 GB Transcend 32GB CL10 MICRO SDHC CARD Class 10 (item no.: 416521)
Yes, this is expected and an unavoidable side effect by what you called the "TEMP" table. I'll shortly explain why:
gravity is already there (assume the database is 200MB in size)gravity_temp leaving gravity intact (so FTL can still access it), this adds yet another 200 MB --> 400 MB in totalgravity to gravity_old (no change)gravity_temp to gravity (no change)gravity_old (no change)Steps 3 & 4 are needed how they are so we do not have to drop and recreate the view.
One might expect that step 5 releases 200 MB, however, the developers of sqlite3 decided to have the database only ever grow to avoid fragmentation. It marks the extra space as unused. It can be used by later gravity runs.
You could shrink the database size when running VACUUM on the database (we have
pihole -g -o for this, -o for --optimize), however, be warned that this may take some VERY long extra time as the database itself may be restructured in the process. I'd say it's not worth it if you have the extra space. It is the unavoidable price to pay for having the gravity table effectively two times at some point (when they are swapped).
This is a bit concerning. What happens at this point is 3, 4 and 5 from above. I can confirm that this also takes a few seconds on my Raspberry Pi even though I'd expect this to be an instantaneous action. I confirmed that the renaming is really only a matter of milliseconds, however DROP TABLE gravity_old; took 2.5 seconds for me (which is the entire time measured for Activating new gravity table in my case. I'll keep looking.
I isolated this by analyzing what the SQLite3 byteengine does when we DROP the table. I will not go into any details (it actually generates a very complex code with 62 instructions). However, it is a long loop that has to go through and erase the records in the database file that deal with the table before it can be dropped.
Uncircumventable.
The only remaining questions I have:
gravity_old as the last step in the pihole -g process. Everything would than be up and running, long before the script has completed.I also noticed pihole_FTL did not start fast enough, according to pihole -g, a retry counter (120 seconds) ran on my screen. This appears to be a little excessive, I checked the status in another putty session, pihole-FTL was up and running long before the timer expired. Multiple short (30 seconds) retry test loops?
That's the max time to wait, the check is once per second.
I don't want to argue here, but I specifically noticed, using sudo service pihole-FTL status, pihole-FTL was reporting a proper (green) state after less than 60 seconds, however the counter ran to 0.
According to the code (if I'm smart enough to understand it - NOT), this would mean DNS resolution was unavailable for 120 seconds. Correct?