"Database is locked" when using podman on AWS EBS

[sluongng]

We use podman as one of our options for CI worker container orchestration.
It works very well.

However, we have received reports from users who run our CI worker on some AWS configurations that "Database is locked" error often happens.
We have checked and the Podman version was v4.9.2 and does include this fix #20850

After digging deeper, I think the situation is as follows:

• Our CI worker, written in Go, will handle multiple "CI tasks" concurrently using multiple goroutines.
• Each goroutine would then shell out to podman executable to create/exec/remove containers
• Podman process, each will spin up their own sqlite3 connection pool, with transaction exclusive lock and force fsync() pragma.
• This effectively forces an exclusive lock between each process. Because the SQLite database is located on an EBS mount, the write latency could be high, leading to slower fsync, leading to each podman process waiting for the lock for longer.
• With this, eventually, the 100 seconds timeout in the fix above is reached and users start to see "database is locked" error.

---

If my observation above is accurate, I think the steps to fix this are as follows:

• Make use of "podman system service" and podman remote to funnel all requests to a single podman daemon with a single connection pool.

• Update libpod/sqlite_state.go so that the pragma is configurable. (Optional)

• Create 2 separate Read-Only and Read-Write connection pools. [Advice Needed] What is a proper way to query concurrently? mattn/go-sqlite3#1022 (comment). This effectively uses a daemon-side lock, allows us to switch transaction lock to immediate and thus, improves concurrency of read-only workload.

• Expose configuration to enable mmap pragma to help speed up the database transaction.

[Luap99]

Make use of "podman system service" and podman remote to funnel all requests to a single podman daemon with a single connection pool.

If you do a lot of calls then you really should be calling into the REST API directly and not shell out for every single command.

Update libpod/sqlite_state.go so that the pragma is configurable. (Optional)

I like to avoid this unless absolutely necessary, making it configurable will make supporting it much harder as we now need to consider which sqlite options were used.

Create 2 separate Read-Only and Read-Write connection pools. mattn/go-sqlite3#1022 (comment). This effectively uses a daemon-side lock, allows us to switch transaction lock to immediate and thus, improves concurrency of read-only workload.

I don't know how much this would benifit podman, we still have a lot of writers.

Expose configuration to enable mmap pragma to help speed up the database transaction.

How would this help when the underlying storage is still slow? If it doesn't fsync in this case then it is much more likely to corrupt the database so I don't see how this helps. Although I don't really know much about sqlite options so maybe I am missing something?

[sluongng]

How would this help when the underlying storage is still slow? If it doesn't fsync in this case then it is much more likely to corrupt the database so I don't see how this helps. Although I don't really know much about sqlite options so maybe I am missing something?

https://www.sqlite.org/mmap.html mmap mostly benefits read workload and not much for writes. From your comment, I guess podman workload is write-heavy and this might now help that much? 🤔

I think for some workloads such as CI builds, the data corruption is not too high of a concern. We run podman inside a kubernetes pod so if the data corruption could be detected, we can replace the pod by failing a healthcheck. This is why I am advocating for configurable SQLite pragmas, especially around the timeout duration, transaction lock, and synchronicity would be nice to experiment with. Right now, with these values hardcoded, we would need to rebuild podman from source to iterate and find the right tradeoffs.

[sluongng]

I think for ephemeral workloads, especially ones with --transient-store enabled, the SQLite pragma should be more lenient to avoid locking. Worth also considering that the Disk that houses the SQLite DB file is likely to be the same disk that houses all the container overlay storage. Given a set of high-load containers, the disk IOPS could be hammered and thus, affect the DB fsync performance.