Gravity.db optimieren

Muss die gravity.db optimiert werden? Ich kenne es nur von diversen MySQL Datenbanken - "optimize Table".

Wenn ja, welche Befehle sind dazu nötig?

Was willst du da optimieren? Ich sehe da eigentlich keine Notwendigkeit für....

Im Prinzip läuft dann einfach der vacuum Befehl drüber

Befehl ist
pihole -g -o

Hier gibt es ein bisschen eine Diskussion darüber

1 Like

Hab es gerade probiert:

 [i] Optimizing domains database...
  [✗] Unable to optimize gravity database /etc/pihole/gravity.db
  Error: database is locked

Probier es nochmal. Vielleicht hast pihole gerade anderweitig auf die Datenbank zugegriffen

Dreimal probiert in der letzten Minuten, immer der gleiche Fehler =(

=( - how to unlock - must i really delete the locked DB?

Thanks for the tip, didn't know that.

Do you have a link handy where the locking is discussed? Maybe It can be fixed easily...

Thanks for the link. (I even participated in the topic :see_no_evil:)

I just tried it myself and the database was locked after pihole -g -o

[2020-09-06 03:38:17.224 18528M] Reloading DNS cache
[2020-09-06 03:38:17.225 18528M] Blocking status is enabled
[2020-09-06 03:38:17.233 18528M] SQLite3 message: file unlinked while open: /etc/pihole/gravity.db (28)
[2020-09-06 03:38:17.356 18528M] Compiled 2 whitelist and 26 blacklist regex filters for 11 clients in 20.7 msec
[2020-09-06 09:18:18.551 18528M] Received: Real-time signal 0 (34 -> 0)
[2020-09-06 09:18:18.583 18528M] Compiled 2 whitelist and 26 blacklist regex filters for 11 clients in 18.8 msec
[2020-09-06 14:47:15.120 18528M] Resizing "/FTL-dns-cache" from 69632 to 73728
[2020-09-06 14:51:37.623 18528M] Resizing "/FTL-strings" from 77824 to 81920
[2020-09-06 20:49:42.701 18528M] Resizing "/FTL-dns-cache" from 73728 to 77824
[2020-09-06 20:55:43.280 18528M] Resizing "/FTL-strings" from 81920 to 86016
[2020-09-06 21:21:23.077 18528M] Reloading DNS cache
[2020-09-06 21:21:23.078 18528M] Blocking status is enabled
[2020-09-06 21:21:23.087 18528M] SQLite3 message: file unlinked while open: /etc/pihole/gravity.db (28)
[2020-09-06 21:21:23.156 18528M] SQLite3 message: database is locked in "SELECT EXISTS(SELECT domain, CASE WHEN substr(domain, 1, 1) = '*' THEN '*' || substr(:input, - length(domain) + 1) ELSE :input END matcher FROM domain_audit WHERE matcher = domain);" (5)
[2020-09-06 21:21:23.156 18528M] gravityDB_open("SELECT EXISTS(... domain_audit ...)") - SQL error prepare: database is locked
[2020-09-06 21:21:23.157 18528M] SQLite3 message: database is locked in "SELECT EXISTS(SELECT domain, CASE WHEN substr(domain, 1, 1) = '*' THEN '*' || substr(:input, - length(domain) + 1) ELSE :input END matcher FROM domain_audit WHERE matcher = domain);" (5)
[2020-09-06 21:21:23.157 18528M] gravityDB_open("SELECT EXISTS(... domain_audit ...)") - SQL error prepare: database is locked
[2020-09-06 21:21:23.157 18528M] gravityDB_count(0): Gravity database not available
[2020-09-06 21:21:23.158 18528M] SQLite3 message: database is locked in "SELECT EXISTS(SELECT domain, CASE WHEN substr(domain, 1, 1) = '*' THEN '*' || substr(:input, - length(domain) + 1) ELSE :input END matcher FROM domain_audit WHERE matcher = domain);" (5)
[2020-09-06 21:21:23.158 18528M] gravityDB_open("SELECT EXISTS(... domain_audit ...)") - SQL error prepare: database is locked
[2020-09-06 21:21:23.158 18528M] gravityDB_count(3): Gravity database not available
[2020-09-06 21:21:23.158 18528M] WARN: Database query failed, assuming there are no blacklist regex entries
[2020-09-06 21:21:23.158 18528M] SQLite3 message: database is locked in "SELECT EXISTS(SELECT domain, CASE WHEN substr(domain, 1, 1) = '*' THEN '*' || substr(:input, - length(domain) + 1) ELSE :input END matcher FROM domain_audit WHERE matcher = domain);" (5)
[2020-09-06 21:21:23.158 18528M] gravityDB_open("SELECT EXISTS(... domain_audit ...)") - SQL error prepare: database is locked
[2020-09-06 21:21:23.159 18528M] gravityDB_count(4): Gravity database not available
[2020-09-06 21:21:23.159 18528M] WARN: Database query failed, assuming there are no whitelist regex entries
[2020-09-06 21:21:23.159 18528M] Compiled 0 whitelist and 0 blacklist regex filters for 11 clients in 1.1 msec

However, I could not find a process locking it (checked with lsof and fuser).

Running pihole restartdns and the database was unlocked.

Add

Running manually

sudo sqlite3 /etc/pihole/gravity.db "Vacuum"

Locked the database as well

[2020-09-06 21:31:43.869 7565M] domain_in_list("chat-pa.clients6.google.com", 0xaaaae4b1c8b8, whitelist): Database is busy, assuming domain is NOT on list
[2020-09-06 21:31:43.870 7565M] domain_in_list("chat-pa.clients6.google.com", 0xaaaae4b15ce8, blacklist): Database is busy, assuming domain is NOT on list
[2020-09-06 21:31:43.870 7565M] domain_in_list("chat-pa.clients6.google.com", 0xaaaae4b17458, gravity): Database is busy, assuming domain is NOT on list
[2020-09-06 21:33:12.354 7565M] SQLite3 message: statement aborts at 46: [SELECT EXISTS(SELECT domain from vw_whitelist WHERE domain = ? AND group_id IN (0));] database schema has changed (17)
[2020-09-06 21:33:12.359 7565M] SQLite3 message: statement aborts at 46: [SELECT EXISTS(SELECT domain from vw_blacklist WHERE domain = ? AND group_id IN (0));] database schema has changed (17)
[2020-09-06 21:33:12.361 7565M] SQLite3 message: statement aborts at 48: [SELECT EXISTS(SELECT domain from vw_gravity WHERE domain = ? AND group_id IN (0));] database schema has changed (17)

I think the reason is, that gravity doesn't restart pihole-FTL if it is already running. But after vacuum it might be necessary to do so.

I read this for the first time. When I added the -o flag, it definitely did what I wanted it to do. I just tried it again, and it worked just fine (this is not saying there cannot be a bug somewhere).

Looking at the gravity code, I have an idea what might be happening: FTL is asked to reload the database and optimizing is started at almost the same time. FTL is trying to access the database when the optimization has already started (the database is locked due to the running VACUUM process) and nothing is asking FTL to re-reload after the optimization is done. Proposal: Delay reloading the gravity database until after the optimization is done (if requested at all).

Please try

pihole checkout core fix/optimize_gravity

This branch is based on master so no forced update to development features in here.
(this may also mean that it may not work if you are currently running development)

Tried it a few times and works without locking the database

1 Like

Okay, thanks for testing, I will open a PR about this minor change. Coming back to the original question,

I don't think much will change because the gravity database is already built "fresh" on every run of pihole -g. I have not done any performance measurements before and after but there are indices on everything that is performance-critical so there should be no difference.

edit Opened PR

You don't, but that's not a problem. We create a new and empty gravity database on each run. We copy all list domains from the old into the new database and then add the adlist (gravity) domains. This copying already skips all holes. Swapping is happening in the end where the databases are really replaced. The old database (may containing holes) is discarded and lost in the process.

I don't know. It was never needed. I think we added it because it isn't much work and it was explicitly requested somewhere. It may still be beneficial, because the indices are completely regenerated, I don't just don't know because nobody proved or dismissed this with proper measurements, so far.

I'm not going to remove it because of speculations.


To see how efficiently the content of a table is stored on disk, compute the amount of space used to hold actual content divided by the total amount of disk space used. The closer this number is to 100%, the more efficient the packing.

After running pihole -g:

SELECT sum(pgsize-unused)*100.0/sum(pgsize) FROM dbstat WHERE name='gravity';

The result is: 99.3528683980306

There is no change to this value after running pihole -g -o.


Modern filesystems operate faster when disk accesses are sequential. Hence, SQLite will run faster if the content of the database file is on sequential pages. To find out what percentage of the pages in a database are sequential (and thus obtain a measurement that might be useful in determining when to VACUUM), we run:

CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path;
SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*) FROM s AS s1, s AS s2 WHERE s1.rowid+1=s2.rowid;
DROP TABLE s;

The result is: 67.1310956301457

Again, no change after running VACUUM.


Even deeper database inspection shows us:

Page size in bytes................................ 4096      
Pages in the whole file (measured)................ 1580      
Pages in the whole file (calculated).............. 1580      
Pages that store data............................. 1580
Pages on the freelist (per header)................ 0 
Pages on the freelist (calculated)................ 0 
Pages of auto-vacuum overhead..................... 0 
Number of tables in the database.................. 12        
Number of indices................................. 10        
Number of defined indices......................... 1         
Number of implied indices......................... 9         
Size of the file in bytes......................... 6471680   
Bytes of user payload stored...................... 2477899

Again, no change after running the optimization...


The answer is: Optimizing is not necessary. The standard pihole -g run already gives the best possible result.

No point in keeping it in this case.

I think they wrote this while the research was in progress. I assume it will get removed.