concurrent database access

[swills]

Versions

Pi-hole version is v5.18.3 (Latest: v5.18.4)
web version is v5.21 (Latest: v5.21)
FTL version is v5.25.2 (Latest: v5.25.2)

Platform

• OS and version: Debian 11

• Platform: Kubernetes via https://github.com/MoJo2600/pihole-kubernetes

Expected behavior

I'm using pi-hole in kubernetes with two copies accessing the same shared database, in order to support HA. I expected this to work OK, but I see issues.

Actual behavior / bug

Memory usages increases forever until the pod runs out of memory and is killed. The logs show:

[2025-02-06 05:45:02.346 264/T296] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1707284701;" failed: database is locked (SQLITE_BUSY)
[2025-02-06 05:46:00.168 264/T296] Encountered error while trying to store client in long-term database
[2025-02-06 05:46:00.169 264/T296] Statement finalization failed when trying to store queries to long-term database
[2025-02-06 05:46:00.169 264/T296] Error while trying to close database: database is locked
[2025-02-06 05:46:01.304 264/T296] ERROR: SQL query "DELETE FROM network_addresses WHERE lastSeen < 1707284760;" failed: database is locked (SQLITE_BUSY)
[2025-02-06 05:47:01.169 264/T296] ERROR: SQL query "END TRANSACTION" failed: database is locked (SQLITE_BUSY)
[2025-02-06 05:47:01.169 264/T296] END TRANSACTION failed when trying to store queries to long-term database
[2025-02-06 05:47:01.170 264/T296] Keeping queries in memory for later new attempt

Steps to reproduce

Steps to reproduce the behavior:

Install via k8s and helm, ensuring the persistentVolumeClaim is using a storageClass which supports accessModes set to ReadWriteMany and setting it to that, and then increase replicaCount to 2 or more.

Debug Token

• URL:

Screenshots

If applicable, add screenshots to help explain your problem.

Additional context

Add any other context about the problem here.

[DL6ER]

HA is and has never officially been supported. I don't think your observed log lines have anything to do with the database file locking. They just indicate that FTL doesn't manage to store queries in the database and keep them in its 24 hours rolling window memory (which it anyway has pre-allocated). Once queries could not be added within a full 24 hours window, they are lost and will not make their way into the database.

I have never tried this myself but writing to the database from two independent FTL processes is likely causing other defects, too, e.g., the uniqueness constraint of the query ID will be violated as both processes will think they are the single source of truth.

What are you trying to achieve with sharing the long-term query database? I would understand you want to share the gravity.db database (as to have the same blocking lists and manually added domains/regex) but doing what you are trying with the long-term query database is not having any net benefit, yet, it is not supported and causing issues like you have seen.

Bottom line: Do you still see increasing memory when you run your system exactly as before but just avoid sharing the database file? If so, there may be a memory leak in the database code handling the errors. With all that said, I shall not hide from you that we are close to releasing Pi-hole v6.0 shortly and it very likely already have a fix for what you are seeing. If you are deploying containers, anyway, you could try the :nightly tag on the official Pi.hole docker container. Please note that v5 -> v6 is strictly a one-way operation so I'd recommend taking a snapshot/copying the data before the migration.

[swills]

My only goal is to not have down time when re-deploying. With only one pod/container, there's a brief outage if I change config (to add a new custom DNS entry, for example) and re-deploy.

Other than the brief outage during re-deployment, there's no issue if I run only one pod, either with shared (ReadWriteMany, nfs) or non-shared (ReadWriteOnce, rbd) storage. Things work flawlessly in that case, and I'm vary grateful and happy and I thank you very much for all the effort and work on it!

I tried the nightly tag but ran into an issue with the readiness and liveness probes which probably need to be addressed in the helm chart. (I'm not worried about preserving stats or logs and all config is built outside the pod/container and added in so there's no issue there.)

I think what you're implying is that another option would be to run two instances with the same config and separate query database, is that right? And it would mean stats and logs would vary in the web interface. I'd be OK with that, but it would need to be setup in the helm chart I think.

[rdwebdesign]

another option would be to run two instances with the same config and separate query database, is that right?

I can't answer for DL6ER, but to avoid downtime most users (including myself and probably other Pi-hole members) configure 2 independent Pi-hole servers (no matter if they are docker, VMs, different computers or a combination of previous options), each Pi-hole with its own IP. Then we configure both IPs as DNS server in the DHCP settings of the router.

When one Pi-hole is offline, the devices will use the other. If both are online, each device will decide how they will send/distribute the DNS queries between both servers.

And it would mean stats and logs would vary in the web interface.

Yes.
Actually there will be 2 separate web interfaces, one for each Pi-hole installation.

If you want to show data from both Pi-holes in a single place, maybe you can try to use some service/script to aggregate the 2 individual databases into a single data source.

[DL6ER]

I think what you're implying is that another option would be to run two instances with the same config and separate query database, is that right?

Yes, I agree with what @rdwebdesign has said:

most users [...] configure 2 independent Pi-hole servers [...], each Pi-hole with its own IP. Then we configure both IPs as DNS server in the DHCP settings of the router.

it would need to be setup in the helm chart I think.

I have had no contact with Kubernetes myself, so I cannot comment on this at all.