Using SQLite in AWS EBS
(1) By Vijay (vijayst) on 2022-04-10 12:20:23 [link] [source]
I am using SQLite in an AWS EBS. It is some simulation data that can potentially run into multiple GBs. I have one such SQLite database that is 11GB. In AWS EC2 (linux) with 16GB RAM and EBS (SSD) attached, the query times are slow. It is 19.1 seconds. But if I download the DB to my Mac, the same query on the same file runs in 2.7 seconds. These are uncached numbers. The query is using the index as expected.
What could be the problem?
I tried my luck in StackOverflow but could not find other users who faced similar issues.
(2) By anonymous on 2022-04-10 13:22:10 in reply to 1 [link] [source]
AWS EBS drives are attached over the network. This means that fetching database pages requires a network trip to get them from a remote drive, that will certainly be a lot slower than fetching then directly from a local drive.
Depending on your filesystem cache, most visited pages should reside in your machine's memory, but if that cache is small then it might not be very effective.
In such situations my recommendation is usually to configure Pragma mmap_size to something generous that is still well under your system's memory limit to eliminate as many trips to the network drive as possible.
And make sure you have enough RAM
(3) By RandomCoder on 2022-04-10 15:28:20 in reply to 1 [source]
"AWS EBS" includes a wide variety of possible storage types with vastly different performance characteristics, you haven't said which one you're using. Assuming a gp2 volume at around 100gb big, with default mounting options, I'd expect burst performance of 3000 IOPS or so, which is a far cry from a MacBook's 300k (or more) IOPS for random reads a database is likely to do.
If speed is critical for your use case, you can try migrating your volumes to io2 with maxed out provisioned IOPS, though that will still probably be less efficient than your local SSD. For something approaching an apples-to-apples comparison (pun not intended), you'll need to move to an EC2 instance type with a dedicated instance NVMe drive, like a r5d.large type (though, there are many options here) and use it instead of EBS.
(4.1) By Vijay (vijayst) on 2022-04-10 16:36:22 edited from 4.0 in reply to 3 [link] [source]
Thanks. I moved the file to a local drive in EC2 instance. Still same poor query performance, atleast 6x slower than Mac. I understand the random access requirements of a database. I will look for a viable alternative.
(5) By Keith Medcalf (kmedcalf) on 2022-04-10 17:42:59 in reply to 4.1 [link] [source]
Have you increased the application cache size? (aka pragma cache_size)
(6) By Vijay (vijayst) on 2022-04-11 01:51:34 in reply to 5 [link] [source]
Yes, pragma cache_size=200000;