Lots of "Database is locked" log lines

[stellarpower]

Hi,

Currently on version 0.8.1; I've seen "Database is locked" messages come up before - initially they were sporadic and usually went away, but now they seem to be persistent. My users are unable to log into their IMAP and the server is also rejecting incoming messages with a 50x, so we are losing quite a lot of traffic.

Generally I have the logging turned up high, but there isn't much in there to tell me what's going on without trying to attach a debugger. Please could you advise what log directives would be best to add to see if I can pinpoint what might be going on? I have done some source-level work with Maddy but none of it affecting the database, so as there ought to be multiple places/portions of the configs where it will be involving the database, thought you would have a much better idea of what logging to turn up to the max.

Thanks a lot

[foxcpp]

Is there any difference in traffic as of late? Do you use official release tarballs or build your own packages? If your own - did you use CGo (if not, you will see using transpiled SQLite (modernc.org/sqlite), this is experimental in logs).
"Database is locked" is mostly caused by having too much concurrent traffic for SQLite to handle - I would recommend considering migration to PostgreSQL if you start hitting issues there.

[foxcpp]

Seems like there was indeed a bug in go-imap-sql related to how SQLite handles transactions.

Can you try with 2f6a4cf?

[stellarpower]

Thanks a lot for the swift reply,

Don't think any major differences, I would have to examine the logs in detail. But we're a small company, so traffic (as well as spam and usual attack attempts) probably going up gradually over time, but not expecting any sudden massive jump. I do see repeated warnings about loading large mailboxes and performance limitations. Unfortunately my colleagues do have a habit of being email-first and all internal communication and file sharing that could happen elsewhere ends up being managed by just emailing attachments and in the process creating six redundant copies on the server, so there are definitely to the order of 100k messages in the database and on the filesystem.

I have built from source (but not modified anything with IMAP or the database), using docker's "official" debian-based Go images - so assume this is CGo, and have not seen that log message regarding SQLite (presumably in the application logs, or would it show up in build logs?).

To confirm, is there a critical region around SQLite within Maddy/the go-imap-sql package, where Maddy's threads all have to wait for a lock, or is it that SQLite can operate in parallel but up to some limits given its relative simplicity as a database engine, compared to Postgres etc.?

If I did migrate to Postgres, would it be as simple as recreating the structures by e.g. dumping the SQL commands and then running them into the new database, or is it much more involved than that?

I will try patching that bump in as soon as I can and see what happens. go.mod says currently:
github.com/foxcpp/go-imap-sql v0.5.1-0.20240214172211-ee5bc28d4278

Thanks again

[foxcpp]

To confirm, is there a critical region around SQLite within Maddy/the go-imap-sql package, where Maddy's threads all have to wait for a lock, or is it that SQLite can operate in parallel but up to some limits given its relative simplicity as a database engine, compared to Postgres etc.?

SQLite cannot process concurrent writes at all so there is an exclusive DB-wide lock each time something is written. Normally, libsqlite3 will wait up to busy_timeout before returning SQLITE_BUSY (aka database is locked), maddy uses 5 seconds by default. Turns out, there is an edge case in which libsqlite3 ignores busy_timeout and returns error without waiting. I fixed this one in 2f6a4cf?.

If I did migrate to Postgres, would it be as simple as recreating the structures by e.g. dumping the SQL commands and then running them into the new database, or is it much more involved than that?

You might need to adjust schema definition before importing data but data-wise there is no difference (same columns, same formats, etc). I will take a look at how complicated it is.

[foxcpp]

Came up with this for PostgreSQL migration: #837

[stellarpower]

Thank you, that was what I thought. In that case, I think migrating will be obligatory for us, I have enough users that parallel access is probably required.

I'll begin testing that guide this week and let you know there if any issues with migrations offline. And in the meantime I'll test that update to the sqlite package on the live system - I'd have thought 5 seconds would be quite a bit, but it may make the problem occur less frequently.

Appreciate your help with this!