Quote: Please don't try to parallelize a part of your program which exists mostly to read or write to a SQLite database. SQLite is not a client/server system. Access to the database file on disk is a bottleneck. If you have 8 threads which spend most of their time executing SQLite calls, all they will do is constantly block one-another.
I think this is true of writes. But I dont think it is true of reads. Given an immutable SQLite file (read only), this can be safely shared among different threads and processes. I think distributing reads among cores is in the class of problem called "embarrassingly parallel": https://en.wikipedia.org/wiki/Embarrassingly_parallel Quote: Access to the database file on disk is a bottleneck. https://www.amazon.com/Samsung-970-EVO-1TB-MZ-V7E1T0BW/dp/B07BN217QG/ This NVMe SSD disk has a read speed of 3GB/s. If I distribute readers across 8 CPU cores so they all compute concurrently (without blocking each other), and they all use a tiny portion of that disk read bandwidth, surely they would be 8x faster than using a single core? On Tue, Aug 6, 2019 at 1:42 PM Simon Slavin <slav...@bigfraud.org> wrote: > On 6 Aug 2019, at 11:34am, test user <example.com.use...@gmail.com> wrote: > > > - Does the second request just take longer to return from the FFI call > whilst waiting for the mutex? > > Yes. You do not need to build backoff-and-retry code into your own > software. SQLite does it for you. For every database connection you open, > set a timeout using this: > > <https://sqlite.org/c3ref/busy_timeout.html> > > A setting of perhaps 30 seconds would be appropriate. The SQLite API will > use the amount of time you set to decide how long to keep retrying access > before it gives up, assumes that the database is permanently locked, and > returns an error code of SQLITE_BUSY. > > > So for example, if I had: > > > > - 8 cores > > - 8 threads > > - 8 db connections, 1 per thread > > - 1 database file > > - x amount of read requests per second > > > > If I were to load balance x requests over each of the 8 threads, all the > reads would complete concurrently when in SERIALIZED mode, with WAL enabled? > > Please don't try to parallelize a part of your program which exists mostly > to read or write to a SQLite database. SQLite is not a client/server > system. Access to the database file on disk is a bottleneck. If you have > 8 threads which spend most of their time executing SQLite calls, all they > will do is constantly block one-another. You will get a far simpler > program, far simpler to debug, if you do all your access through one thread > using one connection. > > What you /might/ want to do is have one thread which reads data from the > database and keeps an in-memory buffer full of, say, the next thousand rows > of data. Then other simultaneous processing threads can read their data > directly from memory. > > However, once again this may not be appropriate. You may find that your > software executes at acceptable speed without any parallelization at all. > I suggest you try this before devoting your time to complicated programming. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
