SQLite is not designed to be accessed from EFS (or a network share). This due to performance and data integrity. In this case, the used tools require sqlite and this cannot be changed in the near future.
This is what we have:
- 1..n dockers that read-only access the SQLite file
- SQLite file is 100GB with a few very large tables (>500M records)
- SQLite file will only be read. (Data does not change)
- Random access reading sqlite data
Problem with SQLite file on EFS and mounted to a machine:
- Starting the docker takes 500s versus 70s with local storage. (thus scaling gets more complicated)
- Read access for several calculations take up to 70s, compared to 0s with local storage.
Current solution:
- Spin up a machine with local storage
- Copy the large sqlite file to local storage
- Start the docker
The copy takes 700s which means that the startup takes a while. Also, every docker needs extra local (ephemeral) storage. The performance of all calculations when running is good.
Question:
It is not expected that EFS will be as fast as local storage, however since this set up only requires read only access, there might be several sqlite settings that could be set to increase performance. What sqlite settings should be set? Or is there no way to increase performance and is our current solution the only one?
Increasing the page_size is not yet tested, but might increase performance:
PRAGMA page_size = 65536;
Since this set up is read-only, the following settings do not have any effect, is that correct?:
PRAGMA SYNCHRONOUS = OFF;
PRAGMA journal_mode = PERSIST;