Re: [sqlite] Concurrent reads for VTs with in-memory data structures
Thank you both for your answers. I am using the shared memory segment in application startup approach. It seems that the problem was with allocating memory for an iterator to my data structure each time on xOpen and freeing the memory on xClose. It seems that for one thread it was not a big issue, but with many threads it became a bottleneck. I now keep one instance and allocate memory at xConnect and just "resetting" that on xOpen, and the performance is very good. I am using different connections (and different table instances) for each thread. From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Hick Gunter <h...@scigames.at> Sent: Monday, July 24, 2017 9:05 AM To: 'SQLite mailing list' Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data structures We are using a shared memory segment (created during application startup) to contain the data records, but you could also use a memory mapped file. This will keep the static data identical across all connections and processes. When writing your VT module, consider giving it a readonly/readwrite switch parameter. This can be used to provide the transaction and update methods (or not) in the method table within xCreate/xConnect. Not providing an xUpdate method tells SQLite that the table is strictly readonly and this may improve concurrency. That way you can perform the inital load with SQL statements in the "bootstrap" program while the application itself can only read. Bootstrap: CREATE VIRTUAL TABLE my_table_rw USING my_module ( 'RW' ); INSERT INTO ... DROP TABLE my_table_rw; Applcation. CREATE VIRTUAL TABLE my_table USING my_module (); -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dimitris Bil Gesendet: Montag, 03. Juli 2017 19:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] Concurrent reads for VTs with in-memory data structures I have some virtual tables that keep in-memory data structures. Data loading is happening at table creation (xCreate and xConnect are the same) and after that, during querying, only read access is needed. Queries do not access any other tables. Is there a way to achieve concurrent execution without having to keep multiple copies of each data structure? ___ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ 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
Re: [sqlite] Concurrent reads for VTs with in-memory data structures
We are using a shared memory segment (created during application startup) to contain the data records, but you could also use a memory mapped file. This will keep the static data identical across all connections and processes. When writing your VT module, consider giving it a readonly/readwrite switch parameter. This can be used to provide the transaction and update methods (or not) in the method table within xCreate/xConnect. Not providing an xUpdate method tells SQLite that the table is strictly readonly and this may improve concurrency. That way you can perform the inital load with SQL statements in the "bootstrap" program while the application itself can only read. Bootstrap: CREATE VIRTUAL TABLE my_table_rw USING my_module ( 'RW' ); INSERT INTO ... DROP TABLE my_table_rw; Applcation. CREATE VIRTUAL TABLE my_table USING my_module (); -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dimitris Bil Gesendet: Montag, 03. Juli 2017 19:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] Concurrent reads for VTs with in-memory data structures I have some virtual tables that keep in-memory data structures. Data loading is happening at table creation (xCreate and xConnect are the same) and after that, during querying, only read access is needed. Queries do not access any other tables. Is there a way to achieve concurrent execution without having to keep multiple copies of each data structure? ___ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrent reads for VTs with in-memory data structures
I presume only the data structure pointer is static -- the vtab/cursors still need to be dynamic .. I do not know why access to multiple separate instances of a virtual table would be serialized ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Dimitris Bil > Sent: Tuesday, 4 July, 2017 09:19 > To: SQLite mailing list > Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data > structures > > Keith, thanks for your answer. > > > I tried to implement it using multiple connections (each connection from a > different thread) from a single process, but not only all access seems to > be serialized, but the time spent when using threads is worse than the > total time if all queries were executed sequentially. For example, I am > testing the same query on an 8-core machine after I have created the > static in-memory data structures. If I use one thread, the query takes > about 20ms, whereas with two threads the time is about 60ms and with six > threads about 500ms. I am getting the same behavior if I just try to > execute a read from a regular table and also if I use the same connection > for all threads. I have tried different SQLITE_THREADSAFE values, and also > different values for PRAGMA synchronous and PRAGMA journal_mode, as well > as trying to open the connections in read only mode, but nothing seems to > work. > > > I would appreciate any idea on how to make this work properly. > > > > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on > behalf of Keith Medcalf <kmedc...@dessus.com> > Sent: Monday, July 3, 2017 6:36 PM > To: SQLite mailing list > Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data > structures > > > If it is singleton data the I suppose you could keep a static pointer to > the data structure, a static "use" counter, and a static mutex. > > Then, for each connection (xConnect), lock the mutex, if the static use > counter is zero then build the data structure and increment the use > counter, then set the new connection to use the shared data structure, > then release the mutex. > > For each xDisconnect, lock the mutex, release that set of virtual table > resources, decrement the counter and if the counter is zero, release the > static data, then release the mutex. > > This assumes that the data needs "reloading" after nothing is using it. > If this is not the case you do exactly the same thing except to never > rebuild the data structure after the first time, all you need (inside the > mutex) on the xConnect it to build the static data structure if it does > not exist, and on the xDisconnect simply release the virtual table > resources (but never the shared static data structure). > > This would mean, of course, that the data would be "static" across all > connections in a process, but different processes would have their own > static data. If you need to release and rebuild the data structure this > will happen only when no connection in the process is using connected to > the data. > > By diddling with the data attributes (basically making them all extern > references to a specially constructed data segment) you could make the > static data structure a singleton across multiple processes. However this > is not the default because static data segments are "process local" not > "library local" ... > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Dimitris Bil > > Sent: Monday, 3 July, 2017 11:14 > > To: sqlite-users@mailinglists.sqlite.org > > Subject: [sqlite] Concurrent reads for VTs with in-memory data > structures > > > > I have some virtual tables that keep in-memory data structures. Data > > loading is happening at table creation (xCreate and xConnect are the > same) > > and after that, during querying, only read access is needed. Queries do > > not access any other tables. Is there a way to achieve concurrent > > execution without having to keep multiple copies of each data structure? > > > > ___ > > 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://
Re: [sqlite] Concurrent reads for VTs with in-memory data structures
Keith, thanks for your answer. I tried to implement it using multiple connections (each connection from a different thread) from a single process, but not only all access seems to be serialized, but the time spent when using threads is worse than the total time if all queries were executed sequentially. For example, I am testing the same query on an 8-core machine after I have created the static in-memory data structures. If I use one thread, the query takes about 20ms, whereas with two threads the time is about 60ms and with six threads about 500ms. I am getting the same behavior if I just try to execute a read from a regular table and also if I use the same connection for all threads. I have tried different SQLITE_THREADSAFE values, and also different values for PRAGMA synchronous and PRAGMA journal_mode, as well as trying to open the connections in read only mode, but nothing seems to work. I would appreciate any idea on how to make this work properly. From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Keith Medcalf <kmedc...@dessus.com> Sent: Monday, July 3, 2017 6:36 PM To: SQLite mailing list Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data structures If it is singleton data the I suppose you could keep a static pointer to the data structure, a static "use" counter, and a static mutex. Then, for each connection (xConnect), lock the mutex, if the static use counter is zero then build the data structure and increment the use counter, then set the new connection to use the shared data structure, then release the mutex. For each xDisconnect, lock the mutex, release that set of virtual table resources, decrement the counter and if the counter is zero, release the static data, then release the mutex. This assumes that the data needs "reloading" after nothing is using it. If this is not the case you do exactly the same thing except to never rebuild the data structure after the first time, all you need (inside the mutex) on the xConnect it to build the static data structure if it does not exist, and on the xDisconnect simply release the virtual table resources (but never the shared static data structure). This would mean, of course, that the data would be "static" across all connections in a process, but different processes would have their own static data. If you need to release and rebuild the data structure this will happen only when no connection in the process is using connected to the data. By diddling with the data attributes (basically making them all extern references to a specially constructed data segment) you could make the static data structure a singleton across multiple processes. However this is not the default because static data segments are "process local" not "library local" ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Dimitris Bil > Sent: Monday, 3 July, 2017 11:14 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Concurrent reads for VTs with in-memory data structures > > I have some virtual tables that keep in-memory data structures. Data > loading is happening at table creation (xCreate and xConnect are the same) > and after that, during querying, only read access is needed. Queries do > not access any other tables. Is there a way to achieve concurrent > execution without having to keep multiple copies of each data structure? > > ___ > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrent reads for VTs with in-memory data structures
If it is singleton data the I suppose you could keep a static pointer to the data structure, a static "use" counter, and a static mutex. Then, for each connection (xConnect), lock the mutex, if the static use counter is zero then build the data structure and increment the use counter, then set the new connection to use the shared data structure, then release the mutex. For each xDisconnect, lock the mutex, release that set of virtual table resources, decrement the counter and if the counter is zero, release the static data, then release the mutex. This assumes that the data needs "reloading" after nothing is using it. If this is not the case you do exactly the same thing except to never rebuild the data structure after the first time, all you need (inside the mutex) on the xConnect it to build the static data structure if it does not exist, and on the xDisconnect simply release the virtual table resources (but never the shared static data structure). This would mean, of course, that the data would be "static" across all connections in a process, but different processes would have their own static data. If you need to release and rebuild the data structure this will happen only when no connection in the process is using connected to the data. By diddling with the data attributes (basically making them all extern references to a specially constructed data segment) you could make the static data structure a singleton across multiple processes. However this is not the default because static data segments are "process local" not "library local" ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Dimitris Bil > Sent: Monday, 3 July, 2017 11:14 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Concurrent reads for VTs with in-memory data structures > > I have some virtual tables that keep in-memory data structures. Data > loading is happening at table creation (xCreate and xConnect are the same) > and after that, during querying, only read access is needed. Queries do > not access any other tables. Is there a way to achieve concurrent > execution without having to keep multiple copies of each data structure? > > ___ > 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
[sqlite] Concurrent reads for VTs with in-memory data structures
I have some virtual tables that keep in-memory data structures. Data loading is happening at table creation (xCreate and xConnect are the same) and after that, during querying, only read access is needed. Queries do not access any other tables. Is there a way to achieve concurrent execution without having to keep multiple copies of each data structure? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
