Mailbox search becomes very slow with many mails when using SQLite on a EBS volume (AWS)

[D-i-t-gh]

Hi,
we are facing performance issues when doing a mailbox search (about 50000 mails) using an AWS Amazon Linux EC2-instance with an EBS volume (gp3) and a persistent SQLite database. The web UI will freeze after submitting the search and eventually time out before a result is displayed. Also there doesn't seem to be a way to cancel the search, so the user is forced to wait. On the EC2 instance, I can see that volume is under full load, while the CPU is maybe at 10%. It looks like the search causes many random reads, and that is very slow on EBS volumes. I doubled the default values for IOPS and Throughput, however there was only little to no speed-up.

SQLite has an extension called FTS5 (https://sqlite.org/fts5.html), that should speed up a full-text search. I don't know - maybe Mailpit already uses this extension for the search? If not, do you think it would make sense and actually speed up the search? It would be nice to have a faster search without the need to use costlier hardware, if it is possible...

[axllent]

Hi @D-i-t-gh. This looks to me like a filesystem problem; Mailpit, or rather SQLite, is designed for a local filesystem, and from what I can tell EBS is a network filesystem mounted like NFS. Given how SQLite works, a search will need to read through the whole database (in an intelligent way of course), which would most likely explain why you are getting performance issues.

Just for the record, Mailpit doesn't use a fulletext search as such, it's using a custom search implementation which allows a lot more finetuning. I also don't think there is anything wrong with Mailpit's search performance either; I have a database with 145k messages (4.2GB) and a search takes around 2 seconds (and around 5 seconds from a "cold start"). It's on a local SSD however.

So in summary, whilst EBS appears to work with SQLite, there isn't unfortunately anything I can do to make the search quicker for you. If you try with a local copy of the database in your EC2 instance it should be much quicker to search.

[D-i-t-gh]

Thank you, I can confirm the issue is gone after migrating the SQLite database from the EBS volume to a directly attached NVMe SSD, using ext4 as filesystem. Now, the search will quickly finish. While the search is running, I can see with top that the CPU goes up to 100% while the io wait (wa) is about 0% (with EBS it was about 50%), so the system looks much healthier now.

[axllent]

I'm glad to hear your problems are resolved. Unfortunately there is no such thing as a network filesystem which can handle the same input/output as a local filesystem - no mater how fast they say it is. As you noted, SQLite works on EBS, but it's not performant.