sharing in memory db through threads

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

sharing in memory db through threads

Wasilios Goutas
Hi,


I try to use an in memory database which works as long as I use as db path ":memory:".


To speed up pre-processing and importing of data I would like to grand several threads access to the same in memory database and have found the documentation on https://www.sqlite.org/inmemorydb.html saying that this is possible by opening the DB with sqlite3_open("file::memory:?cache=shared", &db) by each thread.


Unfortunately instead of being in memory, this creates a file on my Ubuntu box named file::memory:?cache=shared.


-rw-r--r-- 1 wgo wgo 3072 Nov 5 04:00 file::memory:?cache=shared


I used the sqlite3 sources and also pre-build libraries to check if this might be a problem of an outdated version, but in both cases I get the same results.


To demonstrate what I'm doing I created a git repository containing a test program.


https://gitlab.com/laiki/sqlshared


You might need to adapt the CMakeLists.txt file to reflect your path to the sqlite lib.


Am I doing something wrong, or is this a known behavior?


Kind regards


Wasili
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] sharing in memory db through threads

Hick Gunter
Have you enabled URI filenames? See https://www.sqlite.org/uri.html for details

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Wasilios Goutas
Gesendet: Montag, 05. November 2018 16:28
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] sharing in memory db through threads

Hi,


I try to use an in memory database which works as long as I use as db path ":memory:".


To speed up pre-processing and importing of data I would like to grand several threads access to the same in memory database and have found the documentation on https://www.sqlite.org/inmemorydb.html saying that this is possible by opening the DB with sqlite3_open("file::memory:?cache=shared", &db) by each thread.


Unfortunately instead of being in memory, this creates a file on my Ubuntu box named file::memory:?cache=shared.


-rw-r--r-- 1 wgo wgo 3072 Nov 5 04:00 file::memory:?cache=shared


I used the sqlite3 sources and also pre-build libraries to check if this might be a problem of an outdated version, but in both cases I get the same results.


To demonstrate what I'm doing I created a git repository containing a test program.


https://gitlab.com/laiki/sqlshared


You might need to adapt the CMakeLists.txt file to reflect your path to the sqlite lib.


Am I doing something wrong, or is this a known behavior?


Kind regards


Wasili
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sharing in memory db through threads

Keith Medcalf
In reply to this post by Wasilios Goutas

Did you compile sqlite3.c with SQLITE_USE_URI defined?  Or use one of the methods that tells the library that you are using a URI filename?

https://sqlite.org/uri.html

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Wasilios Goutas
>Sent: Monday, 5 November, 2018 08:28
>To: [hidden email]
>Subject: [sqlite] sharing in memory db through threads
>
>Hi,
>
>
>I try to use an in memory database which works as long as I use as db
>path ":memory:".
>
>
>To speed up pre-processing and importing of data I would like to
>grand several threads access to the same in memory database and have
>found the documentation on https://www.sqlite.org/inmemorydb.html
>saying that this is possible by opening the DB with
>sqlite3_open("file::memory:?cache=shared", &db) by each thread.
>
>
>Unfortunately instead of being in memory, this creates a file on my
>Ubuntu box named file::memory:?cache=shared.
>
>
>-rw-r--r-- 1 wgo wgo 3072 Nov 5 04:00 file::memory:?cache=shared
>
>
>I used the sqlite3 sources and also pre-build libraries to check if
>this might be a problem of an outdated version, but in both cases I
>get the same results.
>
>
>To demonstrate what I'm doing I created a git repository containing a
>test program.
>
>
>https://gitlab.com/laiki/sqlshared
>
>
>You might need to adapt the CMakeLists.txt file to reflect your path
>to the sqlite lib.
>
>
>Am I doing something wrong, or is this a known behavior?
>
>
>Kind regards
>
>
>Wasili
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sharing in memory db through threads

Jens Alfke-2
In reply to this post by Wasilios Goutas


> On Nov 5, 2018, at 7:28 AM, Wasilios Goutas <[hidden email]> wrote:
>
> To speed up pre-processing and importing of data I would like to grand several threads access to the same in memory database

That may not help. SQLite only supports a single writer, so only one connection can open a transaction at a time. If multiple threads are trying to write to the database (and you use a connection per thread), they’ll end up doing a lot of inefficient busy-waiting.

The most efficient design is probably a producer-consumer pattern, with one consumer thread writing to the database, being fed data by any number of producer threads. (The producer threads can read from the database concurrently, as long as the db is in WAL mode.) The writer thread should leave one transaction open during the entire import process, to minimize the overhead of commits.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sharing in memory db through threads

Simon Slavin-3
In reply to this post by Wasilios Goutas
On 5 Nov 2018, at 3:28pm, Wasilios Goutas <[hidden email]> wrote:

> To speed up pre-processing and importing of data I would like to grand several threads access to the same in memory database

I did not look at your source code, but you may not get the results you want.  Using many threads/processes to import data into the same database does not speed things up very much, because each thread has to lock the database while it writes to it.

If all your data is available at once then, for speed, do multi-threaded pre-processing and write the resulting INSERT commands (or CSV lines) to a text file.  Once the text file is complete use a single thread to import this text file into your SQLite database as a single transaction (surround it with BEGIN ... END).

Obviously, I have to tested relative speeds with your data and your hardware.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sharing in memory db through threads

Wasilios Goutas
In reply to this post by Keith Medcalf
thanks for the hint to https://sqlite.org/uri.html 
I changed the open statement to make use of URI
 sqlite3_open_v2("file::memory:?cache=shared", &db_shared, SQLITE_OPEN_URI | SQLITE_OPEN_READWRITE, NULL )
and it works as expected :)


> Wasilios Goutas <[hidden email]> hat am 5. November 2018 um 21:25 geschrieben:
>
>
> Hi Keith,
> https://sqlite.org/uri.html 
>
> 1. I fixed the git repository. Now it does contain the source not the build results :)
>
>
> 2. to answer your questions, no I didn't compile the sqlite sources with SQLITE_USE_URI and I opened the DB using "file:memory:?cache=shared"
>
>
> Regards
>
>
> Wasili
>
>
> > Keith Medcalf < [hidden email]> hat am 5. November 2018 um 16:59 geschrieben:
> >
> >
> > Did you compile sqlite3.c with SQLITE_USE_URI defined? Or use one of the methods that tells the library that you are using a URI filename?
> >  
> >  https://sqlite.org/uri.html 
> >  
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
> >
> >
> > >-----Original Message-----
> > >From: sqlite-users [mailto:sqlite-users-
> >  [[hidden email]" data-mce-href="mailto:>[hidden email]">>[hidden email]](https://email.ionos.de/appsuite/mailto:)] On Behalf Of Wasilios Goutas
> > >Sent: Monday, 5 November, 2018 08:28
> > >To: [hidden email]
> > >Subject: [sqlite] sharing in memory db through threads
> > >
> > >Hi,
> > >
> > >
> > >I try to use an in memory database which works as long as I use as db
> > >path ":memory:".
> > >
> > >
> > >To speed up pre-processing and importing of data I would like to
> > >grand several threads access to the same in memory database and have
> > >found the documentation on https://www.sqlite.org/inmemorydb.html 
> > >saying that this is possible by opening the DB with
> > >sqlite3_open("file::memory:?cache=shared", &db) by each thread.
> > >
> > >
> > >Unfortunately instead of being in memory, this creates a file on my
> > >Ubuntu box named file::memory:?cache=shared.
> > >
> > >
> > >-rw-r--r-- 1 wgo wgo 3072 Nov 5 04:00 file::memory:?cache=shared
> > >
> > >
> > >I used the sqlite3 sources and also pre-build libraries to check if
> > >this might be a problem of an outdated version, but in both cases I
> > >get the same results.
> > >
> > >
> > >To demonstrate what I'm doing I created a git repository containing a
> > >test program.
> > >
> > >
> > > https://gitlab.com/laiki/sqlshared 
> > >
> > >
> > >You might need to adapt the CMakeLists.txt file to reflect your path
> > >to the sqlite lib.
> > >
> > >
> > >Am I doing something wrong, or is this a known behavior?
> > >
> > >
> > >Kind regards
> > >
> > >
> > >Wasili
> > >_______________________________________________
> > >sqlite-users mailing list
> >  [[hidden email]" data-mce-href="mailto:>[hidden email]">>[hidden email]](https://email.ionos.de/appsuite/mailto:)
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> >  [hidden email]
> >  http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users