Re: Threads and DBD::SQLite?

[BrowserUk]

1. How can I turn on the SQLite "Shared-Cache Mode" from DBI or DBD?

   Use a connect string of the form 'dbi:SQLite:dbname=file:memdb2?mode=memory&cache=shared'.

2. Does it work as expected, if I fork off a thread, which opens SQLite shared memory? (Have anyone done this craziness with Perl before?)

   What are you expecting?

   (Kinda, but there are some issues. At least on windows.)

   For a start, retrieving a given number of records from a single thread&connection is nearly 2 orders of magnitude quicker than retrieving those same records via a connection made via a single connection in a separate thread.

   Running multiple clients in 2 separate threads cuts the time almost in half; and with 4 almost by 4; but it is still much faster to use a single thread/connection.

   This is true despite having deployed every speed-up pragma and option I can find.

3. What is the recommended tcp/ip server module on CPAN for threaded programs?

   Personally, I don't use a module for tcp servers.

   Writing a threaded server that does what I need is pretty simple. Making someone else's implementation (if you can find one) do what I need it to do is always harder.

A few questions: What OS are you targeting? What does your data (schema) look like? What do your queries look like?

[BerntB]

Ah, thank you! The exact string "cache=shared" gave some Google hits. This was fixed for DBD::SQLite this summer, it seems.

For a start, retrieving a given number of records from a single thread&connection is nearly 2 orders of magnitude quicker than retrieving those same records via a connection made via a single connection in a separate thread.

Two orders of magnitude slower using threads?! Sigh. Does it help to set "read uncommitted" on? (You do that in the connect string too?)

A few questions: What OS are you targeting? What does your data (schema) look like? What do your queries look like?

Honking big, multiprocessor Linux servers with Oracle and more memory than you can shake a stick at. :-) I'd like to replace an old C hack using a few GBytes of RAM for querying some subsets of a few tables really quick (quite a lot of work is required for some queries). The queries are data structures easily converted to SQL.

[BrowserUk]

Does it help to set "read uncommitted" on? (You do that in the connect string too?)

Not in my experiments on windows. (No, it is a pragma. See below.)

I do not have any great faith that my tests on windows will be reflected on *nix; so here is my test harness:

<Reveal this spoiler or all in this thread>

I'd love to see the results from a few runs done' on other OSs.

You can get threads::Q (my own, efficient, self-limiting queue implementation) from here.

[erix]

UPDATE: numbers are partly wrong; see further discussion lower down in this thread

I experimented a bit with this, on Centos 6.5. I had only a threaded perl 5.19.7 handy.

Initially I got similar (disappointing) results, but then I found two problems with the test program:

1. It turns out that after the "Create primary index"; block, a $dbh->commit is needed to get the index to be used/committed.

2. A (much) higher rowcount $N gives more interesting results. From around N$ = 10_000 and upwards, the threaded version *is* (much) faster.

For instance, for rowcount $N = 100_000:

$ perl 1067204.pl
Using minimum thread stack size of 16384 at /home/aardvark/perl-5.19/l
+ib/5.19.7/x86_64-linux-thread-multi/threads.pm line 49.
Took 6.262891 seconds (for Populate db with 100000 records)
Took 0.121979 seconds (for Create primary index)
Took 0.905479 seconds (for Retrieve the whole lot)
100000

Took 0.099463 seconds (for Read all 100000 records using 4 threads)

So, it was a good approach after all.