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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
