Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^6: Threads and DBD::SQLite?

by erix (Vicar)
on Dec 16, 2013 at 23:41 UTC ( #1067391=note: print w/ replies, xml ) Need Help??


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

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)


Comment on Re^6: Threads and DBD::SQLite?
Select or Download Code
Re^7: Threads and DBD::SQLite?
by BrowserUk (Pope) on Dec 17, 2013 at 06:40 UTC

    Thanks again erix. Those are some intriguing numbers.

    ISTM that it doesn't retrieve enough.

    Have you seen/disabled this from the posted code?:

    $Q->nq( $_ ) for (shuffle 1 .. $N)[ 0 .. $R ]; #.....................................^^^^^^^^^^^

    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.

      OK, so my numbers for "Read all 100000 records using 4 threads" were really

      " Read ", $R * 100 / $N, " % of all records using 4 threads"

      that means the test was doing 0.1 % of what the output-line was saying.

      Here is a run without that mistake:

      # The offending code line changed to: # $Q->nq( $_ ) for (shuffle 1 .. $N); SQLite - 5.90120077 s - Populate db with 100000 rows SQLite - 0.15266800 s - Create primary index SQLite - 0.53022408 s - Read all 100000 rows, unthreaded SQLite - 7.23427510 s - Read all 100000 rows, 4 threads Pg - 23.40106297 s - Populate db with 100000 rows Pg - 0.53076911 s - Create primary index Pg - 13.71553111 s - Read all 100000 rows, unthreaded Pg - 7.40499115 s - Read all 100000 rows, 4 threads

        so my numbers

        Not only your numbers, mine also. Another forgotten artifact from when I was testing this a couple of months ago.

        Your latest output makes for intriguing reading. The Pg 1 thread versus 4 threads is sort of what you'd expect.

        The sqlite numbers make no sense at all. It looks like it has to re-build the cache from scratch for the new connections?

        Those kind of numbers combined with my inability to get a memorydb combined with shared db via multiple connections tell me that this stuff isn't ready for prime time yet.

        Thanks for your help.


        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.
Re^7: Threads and DBD::SQLite?
by BrowserUk (Pope) on Dec 17, 2013 at 08:35 UTC

    According to this the syntax for using uri filenames has changed.

    Using 'dbi:SQLite:dbname=file:memdb2?mode=memory&cache=shared' still creates a file; although the file is empty and the cache=shared appears to work.

    The new syntax is apparently: 'dbi:SQLite:uri=file:memdb2?mode=memory&cache=shared'. However, now the shared cache no longer seems to work.

    I'm bored with fighting dumb reasoning, so I'm putting this back in the deep freeze.


    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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2014-12-19 01:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (70 votes), past polls