> (Tips for more CPUs would be useful too - I'm interested in understanding what the benefits of paying for larger EC2 instances are, in addition to maximizing performance against currently available memory.) The standard CPython does not "do" multiple simultaneous threads of execution in CPython bytecode, so although CPython bytecode threads of execution may be executed "interleaved", they are never executed concurrently. So adding CPUs (or fake CPUs aka Cores) will not do anything with the Pure Python code. No matter how many "threads" of python bytecode you think you are executing, only ONE will execute at a time and all told cannot consume more than 1 CPU. Once the Python Bytecode has "transitioned" into "machine space", the code executing on the machine (that is, SQLite3) may use multiple CPUs simultaneously if it is configured to do so (the default). However, except for exceptional circumstances, a single entry executes entirely only on one thread (subsorts may use additional CPU threads, but that is only when constructing an index or processing a GROUP BY or ORDER BY sort). If you have, for example, a dozen python threads, those will execute **AT THE PYTHON LEVEL** interleaved on a single CPU core. However, when one of those threads calls sqlite3, the sqlite3 processing will execute *concurrently* with other Python code threads that are "ready to run" dispatched in interleaved fashion. > I've seen PRAGMA cache_size in the documentation - is this the main lever I should be looking at or are there other options that might help here? Pragma cache_size increases the memory storage pool for pages read on the current connection. A page which is stored in the connection cache which has not been invalidated will be accessed from the cache without generating an interrupt to the OS to perform I/O. This will be faster than generating a kernel I/O request, even if that kernel I/O request can be serviced without performing a physical device I/O (as in serviced by the OS from an OS level cache). > My current plan is to set up a simple load test that exercises my Python + SQLite code across a number of different queries, then modify the cache_size and see what impact that has on requests/second and request durations. This is a good plan. > I'd thoroughly appreciate any advice on ways to scale SQLite up like this. There are other strictures in the sqlite3 library. For example, if you have MEMSTATUS tracking enabled, then only one "sqlite3 thing" at a time can allocate/deallocate memory. Turning this off can speed up sqlite3 even if you are only executing one sqlite3 operation at a time because there will no longer be a need to check to ensure the single admittance requirement. Each "connection" can also only be executing one thing in sqlite3 at a time. By default there are "safety features" which prevent you from violating this requirement by ensuring single entry on a connection. You can turn this checking off if you are certain that you are complying with single entrance per connection requirements (see <https://sqlite.org/threadsafe.html>) > Most of my workloads are read-only (I open the SQLite file with the ?mode=ro or ?immutable=1 flags) so I'm mainly interested in improving performance of SELECTs against read-only databases. There is a difference between mode=ro and immutable=1. The former (mode=ro) means that I will be only reading the database but that someone else may be concurrently updating, so perform all the checks necessary to ensure that the database is in a consistent status. The latter (immutable=1) means that NO ONE will be updating the database so NEVER perform any of the checks to ensure that the database is in a consistent state because it will never change during the period the database is open with this flag. This means that "more=ro" means that someone else can update the database, and that if they do so, then the current page cache will be invalidated and all page requests will generate an I/O request to the OS, although the OS may still be able to satisfy the request from the OS level cache without actually perroming a physical I/O operation, the CPU must still enter Supervisor mode to perform the I/O operation. However, with "immutable=1" no checks are done to see if the database is being updated by someone else and if someone else does update the database then shit will likely break loose and hit the oscillatory device, smearing everything in sight with excrement ... See <https://sqlite.org/c3ref/open.html>