You might want to try enabling mmap mode: pragma mmap_size = 4294967296; or something like that. Try to make it larger than your databases. I'd expect that if you're running with that many cores, you're _probably_ running in a 64-bit address space, so it'll probably work.
-scott On Fri, Mar 3, 2017 at 5:22 PM, Andrew Brown < andrew.br...@economicmodeling.com> wrote: > Yes, each thread has its own connection. > > On Mar 3, 2017 4:45 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > Does each thread have its own connection? > > > -----Original Message----- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Andrew Brown > > Sent: Friday, 3 March, 2017 13:14 > > To: sqlite-users@mailinglists.sqlite.org > > Subject: [sqlite] Massively multithreaded SQLite queries > > > > Hello, > > > > Based on my reading of the documentation it seems like SQLite is a great > > candidate for cases where you have a large number of threads that only > > need to read a database simultaneously, in our case for fast data lookup, > > aggregation, etc. I've been able to generate SQL queries that do this, > but > > once we start running them on a large server with a lot of multithreading > > going on, I find that we spend a lot of time in __raw_spin_lock - perhaps > > 75%+ of the time (In one case, 87% of 350 seconds x 72 cores was spent in > > __raw_spin_lock). This is being run on 64 and 72 core machines, and the > > more cores I run it on, the slower it ends up going. > > > > To give a bit more detail, I'm working with dotnet core, have written a > > custom sqlite wrapper (since the dotnet core one lacks the ability to set > > connection flags beyond readonly, and doesn't have sqlite_prepare_v2() > > implemented), and I'm running on linux against a bunch of SQLite files in > > the 2gb-400gb size range. Individual queries are wicked fast, but once I > > start spreading the load over all the cores by running simultaneous > > queries I lose the performance advantage and it actually becomes > > significantly slower. > > > > Have tried setting SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX. Tried > > shared cache, read uncommitted. Tried without shared cache, read > > uncommitted. Tried WAL. If I write a less efficient query, I spend less > > time in __raw_spin_lock, but of course then it takes longer for the > > queries themselves to return. > > > > Any tips to handle massively multithreaded side by side chunked queries > on > > the same database? > > > > Thank you in advance, > > Andrew Brown > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > https://na01.safelinks.protection.outlook.com/?url= > http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin% > 2Fmailman%2Flistinfo%2Fsqlite-users&data=01%7C01%7Candrew. > brown%40economicmodeling.com%7Cc0018bf7b8cd49bb588608d46297c70b% > 7C7cc1677566a34e8b80fd5b1f1db15061%7C0&sdata= > gad9Y8BC67c4ZAC05GckCghNHgKmm8i5piwgjM2MXgw%3D&reserved=0 > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://na01.safelinks.protection.outlook.com/?url= > http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin% > 2Fmailman%2Flistinfo%2Fsqlite-users&data=01%7C01%7Candrew. > brown%40economicmodeling.com%7Cc0018bf7b8cd49bb588608d46297c70b% > 7C7cc1677566a34e8b80fd5b1f1db15061%7C0&sdata= > gad9Y8BC67c4ZAC05GckCghNHgKmm8i5piwgjM2MXgw%3D&reserved=0 > > _______________________________________________ > 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
