Database size reaches 3.9GB and FTL fails to start

[Sem]

Expected Behaviour
I'd expect the DB to automatically self-heal, rotate or similar.
OS: DietPi
Hardware: Raspberry Pi 4 2GB, 32GB MicroSD card.

Actual Behaviour:
FTL service falls over and fails to start when the DB reaches 3.9GB in size. Renaming and moving the DB to a different location allows everything to start again but obviously with no historic data.I used the guidance here: Query database - Pi-hole documentation to rename and move the existing DB.

Debug Token:
https://tricorder.pi-hole.net/bf2fpj45ge

[Bucking_Horn]

You didn't specify the affected database file.

Are you positive this is due to database file size?
Are there any log statements that would put the blame on file size limits?

To my knowledge, neither Raspbian's ext4 file system nor sqlite3 would impose a near 4GiB file size limit by default. At a minimum, you should be able to grow files to sizes of about 16TiB, and sqlite3 using its standard 4096 page size would allow for a file size of just under 16 GiB TiB (EDIT: my own RPi Zero, an ARMv6 CPU, returns that 4096 default).

You could verify the current page size for your db file as follows (assuming you were referring to pihole-FTL.db):

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db "pragma page_size;"

Maxium sqlite file size in bytes can be calculated as roughly 2³² * page_size.

[MichaIng]

Probably not the file size is the issue, but the free disk space? Although, as long as you did not move the existing database to a different disk, free space is obviously not the issue. Not sure if the Raspbian SQLite3 build (targeting armv6hf) has some changed limits, but I doubt it.
You use the 32-bit image, or is it the 64-bit image (which uses the regular Debian arm64 repository)?

Indeed, 4 GiB file size should not be an issue on this RPi system with official Raspberry Pi Foundation kernel and ext4 file system. But for a micro SD card, a frequently written 4 GiB file won't be of joy for long, in terms of SD card wear, assuming that there is some disk write overhead involved when doing even small changes to large SQLite databases.

From the docs: Query database - Pi-hole documentation

Another way of controlling the size of the long-term database is by setting a maximum age for log queries to keep using the config parameter MAXDBDAYS . It defaults to 365 days, i.e. queries that are older than one year get periodically removed to limit the growth of the long-term database file.

In case you don't need access logs for one year, you should be able to reduce the database size dramatically by reducing the max age by adding/setting e.g. MAXDBDAYS=7 in /etc/pihole/pihole-FTL.conf to have logs for one week only.

If you require long-term logs, moving the database to an external drive (no SD card) is probably the best solution, e.g.: DBFILE=/mnt/external/pihole-FTL.db

OOT: In addition to the max age setting, a max size setting could be actually nice, in cases where keeping the database size below a limit is more important than a consistent log age. Has such a request been done already? Can find it here or on GitHub at least .

[DL6ER]

@MichaIng

assuming that there is some disk write overhead involved when doing even small changes to large SQLite databases

SQLite3 organizes the database in pages, the entire file can never be rewritten (unless, of course, you explicitly request this, e.g., though VACUUM). When changing a few bytes of data, only a few bytes of data is written. There is no auto-cleaning or reorganization in place here. This would also be awfully slow with the queries table holding dozens of millions of entries in a database of this size. Also amending items to the tree is a very lightweight operation as the tree never needs to be rebuild from additions (existing leafs are just split simply if there is no more space).

Pages are numbered beginning with 1. The maximum page number is 4294967294 (2^32 - 2). The minimum size SQLite database is a single 512-byte page. The maximum size database would be 2147483646 pages at 65536 bytes per page or 281,474,976,579,584 bytes (about 281 terabytes). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.

https://www.sqlite.org/fileformat2.html

This upper limit is a bit fishy because one typically doesn't use such an extreme page size. When using the standard page size of 4096 the practical upper limit is rather something like 16.4 TB - which is still a lot larger than 4 GB

@Sem

FTL service falls over and fails to start when the DB reaches 3.9GB in size

Can you be a bit more specific here?
What do the log files /var/log/pihole-FTL.log and /var/log/pihole.log say?
FTL is built to works just fine without the database. It even works fine when the database is completely corrupted. So, I concur with the others in here that the file size is unlikely the issue you're facing here.

[MichaIng]

@DL6ER

SQLite3 organizes the database in pages, the entire file can never be rewritten (unless, of course, you explicitly request this, e.g., though VACUUM ). When changing a few bytes of data, only a few bytes of data is written. There is no auto-cleaning or reorganization in place here. This would also be awfully slow with the queries table holding dozens of millions of entries in a database of this size. Also amending items to the tree is a very lightweight operation as the tree never needs to be rebuild from additions (existing leafs are just split simply if there is no more space).

Thanks for clarifying, I was never really sure about this. So then it depends on how fast the database grew to that size, respectively how large it would become in one year, as it could still be a good reason to limit it or move it via config to an external drive when running a high load Pi-hole instances on an SBC with system on a smaller SD card.

16 TiB is btw the file size limit on ext4, when using the default 4 KiB block size, which is the case for the Raspberry Pi OS (based) images: Comparison of file systems - Wikipedia

[Coro]

@MichaIng

16 TiB is btw the file size limit on ext4, when using the default 4 KiB block size

They both have the exact same reason for their limit. It's both times a 32bit integer that is counting pages.

@MichaIng

it could still be a good reason to limit it

Oh yes. On extreme amounts of queries, every database request (like the long-term pages on the webpage) will take forever and eat up all your remaining RAM.

Seeing a Pi-hole with such an immensely large database makes me think that there must be > 200,000 queries a day. This may be just too much for a Raspberry Pi as server. Consider upgrading to some beefier hardware. Pi-hole is very very good in performance and they try hard to make it fit on low-power devices but, at some point, the natural limit of things is reached.

[MichaIng]

@Coro

They both have the exact same reason for their limit. It's both times a 32bit integer that is counting pages.

4 KiB × 2^32 = 16 TiB

@Coro

there must be > 200,000 queries a day. This may be just too much for a Raspberry Pi as server.

First I thought the same, but with assumed 100 bytes per query (not sure how much it is approx with meta data?) 1 query per second accumulates to 3 GiB in a year already, and from CPU/throughput side this is way below from what an RPi (even Zero models) is capable of, isn't it? If such database size slows down queries notably and hence raises CPU effort for each request for logging only and then might even lead to an overload/crash (?), then limiting it's size seems like a reasonable step for SBCs. For long-term logs a backup/rotation script can be used, so for most use cases RPi's will still do perfectly fine.

Memory usage is another thing (of course tied to CPU usage/speed). Adding a log entry/new row should not lead to high memory usage only because the whole database is large, should it? But when using the web UI to search and filter logs, I guess it can. Would be another reason for a limit that rules out that all physical memory even can be used by searching though the logs.