Use 1 connection for each thread. Sharing a connections between threads may cause transactions to be larger than each thread thinks.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shevek Gesendet: Donnerstag, 01. März 2018 09:10 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>; Simon Slavin <slav...@bigfraud.org> Betreff: [EXTERNAL] Re: [sqlite] High performance and concurrency On 02/28/2018 11:45 PM, Simon Slavin wrote: > > On 1 Mar 2018, at 7:24am, Shevek <sql...@anarres.org> wrote: > >> What I think is happening is that either a pthread mutex or a database lock >> is serializing the accesses, so each thread blocks the others. To be specific, I'm concerned about is the line sqlite3_mutex_enter(db->mutex) at the top of sqlite3_step(). Since my queries are spending all their time in sqliteVdbeExec(), which is reached through that path, I assume db->mutex is preventing concurrency. Our main hotspots in the query are sqliteVdbeExec() and updating the btree pointer to point to a new page (I forget the call name). We can't do much about the cost of execution; we've mmap'd everything to avoid the I/O, we're using covering indexes to help with locality, we've sorted our query keys to attempt to reduce index page seeks, and now we want to use concurrency and splitting the logic in our query to exploit memory bandwidth. Now I've traced this again, I'm looking warily at SQLITE_OPEN_NOMUTEX because we need thread-safety, as in, sqlite's internal data structures must be handled correctly in the presence of multiple threads or passing a connection between threads (safely in the JMM); we just don't need serialization of database reads and writes, because nothing we do has a serializable side-effect. Is SQLITE_OPEN_NOMUTEX the answer? > What journal mode are you using ? I'm fairly sure journal mode is NONE for our readonly database. Anyway, readonly shouldn't write to a journal. We have confirmed that the md5sum of the database file is unchanged during and after the execution of our application. S. > <https://sqlite.org/pragma.html#pragma_journal_mode> > > If it's not WAL, try WAL. If it's currently WAL, try DELETE. Once you've > changed it see if this changes how your program behaves. > > Simon. > _______________________________________________ > 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
