Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re^3: Threads and DBD::SQLite?

by BrowserUk (Pope)
on Dec 15, 2013 at 16:53 UTC ( #1067246=note: print w/ replies, xml ) Need Help??


in reply to Re^2: Threads and DBD::SQLite?
in thread Threads and DBD::SQLite?

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:

#! perl -slw use strict; use threads ( stack_size => 4096 ); use threads::shared; use threads::Q; use List::Util qw[ shuffle min ]; use Time::HiRes qw[ time ]; use DBI; use constant { CONNECT=> 'dbi:SQLite:dbname=file:memdb2?mode=memory&cache=shared' +, # CONNECT=> 'dbi:SQLite:dbname=mydb', CREATE => 'create table if not exists DB ( ID integer(8),' . join(',', map "F$_ text(15)", 1..9) . ')', INSERT => 'insert into DB ( ID , ' . join( ',', map "F$_", 1..9 ) . ') values (' . '?,' x 9 . '?) +', INDEX => 'create index if not exists I1 on DB ( ID )', QUERY => 'select * from DB where ID = ?', }; sub timeit (&@) { my $code = shift; my $start = time; $code->(); sprintf "Took %f seconds %s", time()-$start, @_ ? "(for @_)" : ''; } sub thread { my $tid = threads->tid; my( $Q ) = @_; my $dbh = DBI->connect( CONNECT, '', '' ) or die DBI::errstr; my $sth = $dbh->prepare( QUERY ) or die DBI->errstr; while( my $id = $Q->dq ) { $sth->execute( $id ) or die DBI::errstr; my $r = $sth->fetch or warn( "No data for $id" ) and next; ## do something with record. #printf "[$tid] %5u %s %s %s %s %s %s %s %s %s\n", @{ $r }; } $sth->finish; $dbh->disconnect; } my @chars = ( 'a'..'z' ); sub dummy { my $n = shift; join '', @chars[ map int( rand @chars ), 1 .. $n ]; } our $T //= 4; our $N //= 100; our $R //= min( 1000, $N ); our $Qsize //= 10; my $dbh = DBI->connect( CONNECT, '', '', { AutoCommit =>0 } ) or die D +BI::errstr; $dbh->do( 'PRAGMA synchronous = off' ); $dbh->do( 'PRAGMA cache_size = 1073741824' ); $dbh->do( 'PRAGMA read_uncommitted = on' ); $dbh->do( CREATE ) or die DBI::errstr; print timeit { my $ins = $dbh->prepare( INSERT ) or die DBI->errstr; for my $n ( 1 .. $N ) { my @fields = ( $n, map dummy( 15 ), 1 .. 9 ); $ins->execute( @fields )or die $ins->errstr; # $n % 10 or $dbh->commit } $ins->finish; $dbh->commit; } "Populate DB with $N records"; print timeit { $dbh->do( INDEX ) or die DBI::errstr; } "Create primary index"; print timeit { my $sth = $dbh->prepare( QUERY ) or die DBI->errstr; for my $id ( 1 .. $N ) { $sth->execute( $id ) or die DBI::errstr; my $r = $sth->fetch() or warn( "No data for $id" ) and next; ## do something with record. } $sth->finish; } "Retrieve the whole lot"; print $dbh->selectrow_array( 'SELECT count(*) from DB' ); $dbh->disconnect; print timeit { my $Q = threads::Q->new( $Qsize ); my @threads = map threads->create( \&thread, $Q ), 1 .. $T; $Q->nq( $_ ) for (shuffle 1 .. $N)[ 0 .. $R ]; $Q->nq( (undef) x $T ); $_->join for @threads; $dbh->disconnect; } "Read all $N records using $T threads"; unlink 'file'; ## Only needed on windows?

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.


With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.


Comment on Re^3: Threads and DBD::SQLite?
Select or Download Code
Re^4: Threads and DBD::SQLite?
by erix (Vicar) on Dec 16, 2013 at 00:39 UTC

    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.

      erix, I can't thank you enough for do that.

      1. Ouch! I swear I had it in there at one point, but looking back to where this code originated, it was missing or in the wrong place back then.

        Position and frequency of commits is one of the many variables I've been playing with trying to get a handle on how they affect the overall performance. For example, if you uncomment the line:

        # $n % 10 or $dbh->commit

        The insertion takes 4 times as long.

      2. $N is a command line parameter.

        My typical test runs consist of the command: for /L %t in (1,1,4) do @1045292 -N=1e6 -T=%t -Q=100 which is probably self explanatory.

      From around N$ = 10_000 and upwards, the threaded version *is* (much) faster.

      Weird thing is, varying the number of threads (-T=n) makes no difference to the time required to process all the records:

      C:\test>1049236 -N=1e6 -T=1 -Qsize=1000 Took 86.039947 seconds (for Populate DB with 1e6 records) Took 2.232394 seconds (for Create primary index) Took 16.897088 seconds (for Retrieve the whole lot) 1000000 Took 0.268509 seconds (for Read all 1e6 records using 1 threads) C:\test>1049236 -N=1e6 -T=4 -Qsize=1000 Took 85.957917 seconds (for Populate DB with 1e6 records) Took 3.164024 seconds (for Create primary index) Took 16.881054 seconds (for Retrieve the whole lot) 1000000 Took 0.251258 seconds (for Read all 1e6 records using 4 threads)

      Leastwise, not when reading the records for the second time. Comment out the "Retrieve the whole lot" block and then things get muddy again:

      C:\test>1049236 -N=1e6 -T=1 -Qsize=1000 Took 86.307525 seconds (for Populate DB with 1e6 records) Took 2.363253 seconds (for Create primary index) 1000000 Took 0.978504 seconds (for Read all 1e6 records using 1 threads) C:\test>1049236 -N=1e6 -T=4 -Qsize=1000 Took 86.182529 seconds (for Populate DB with 1e6 records) Took 3.017540 seconds (for Create primary index) 1000000 Took 1.925757 seconds (for Read all 1e6 records using 4 threads)

      Once the single-threaded, first pass through the data is removed, it takes twice as long for 4 threads to read all the records as it does for 1 thread.

      But then, why does it take the single-threaded, read-all 16 seconds to do so; when it only takes 1 second to start an entirely new thread, make a completely new connection, and then read-all the records?

      Many questions and little consistency from which to derive answers at this point. If you can see any other obvious cock-ups I'd be grateful to hear of them.

      (Ps. Any feel for how Pg would compare in similar a application?)


      With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
      Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
      "Science is about questioning the status quo. Questioning authority".
      In the absence of evidence, opinion is indistinguishable from prejudice.
      blockquote

        Very interesting: I find that, with threads, postgres is not even so much slower. Like you, I am a bit suspicious of the results, but I see no faults in the code.

        (All this is with fsync = off (in postgresql.conf), but that (as expected) only seems to influence the writing, not the reading)

        So, comparison is again between a SQLite memory table, and a postgresql normal table.

        UPDATE I believe there is an oversight in the threading block. ISTM that it doesn't retrieve enough. But I have to admit that I do not quite understand it at the moment. And I'll have to leave it at that for now (bedtime), more later, I hope...

        SQLite - 5.96526885 s Populate db with 100000 records SQLite - 0.14969087 s Create primary index SQLite - 0.54066706 s Retrieve the whole lot SQLite - 0.06327486 s Read all 100000 records using 2 threads SQLite - 6.06698895 s Populate db with 100000 records SQLite - 0.14992595 s Create primary index SQLite - 0.53164196 s Retrieve the whole lot SQLite - 0.12085700 s Read all 100000 records using 4 threads SQLite - 6.14582801 s Populate db with 100000 records SQLite - 0.15506721 s Create primary index SQLite - 0.55244803 s Retrieve the whole lot SQLite - 0.17814016 s Read all 100000 records using 8 threads Pg - 29.35742998 s Populate db with 100000 records Pg - 0.53696704 s Create primary index Pg - 13.75696707 s Retrieve the whole lot Pg - 0.14590406 s Read all 100000 records using 2 threads Pg - 22.90790606 s Populate db with 100000 records Pg - 0.56523991 s Create primary index Pg - 13.84565902 s Retrieve the whole lot Pg - 0.13429594 s Read all 100000 records using 4 threads Pg - 19.43180919 s Populate db with 100000 records Pg - 0.53746796 s Create primary index Pg - 13.00171399 s Retrieve the whole lot Pg - 0.19895601 s Read all 100000 records using 8 threads

        (This is PostgreSQL 9.4devel, btw)

        I must thank you again BrowserUK, the question really was found by the right person! If you see me around, I'll buy you a beer/coffee/tea. :-) On one hand, these questions taught me to recommend the MySQL MEMORY storage engine at work. On the other hand, I'm intrigued and will look seriously at Perl threading sometime.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1067246]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2014-09-03 00:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (34 votes), past polls