Hi all,

I have a problem with concurrent insert into different SQLITE databases for each threads and don't understand how to solve it or if it is a limitation.

If i use only one thread, the process take 22 seconds (559512 for 1324 databases, ~36Mo)
If i use 10 threads, the process take 1 minutes 20 seconds ....
if i use 32 threads, the process take 3 minutes 43 seconds ....

With some debug it seems that the time is take when the pool is create.(~3 minutes for 32 threads)
What am i doing wrong?

The script is execute on a server with 128GO and 32 cores, no peaks for the cpu or the memory.

Thanks for your help
Best regards,
UPDATE: just forgot to mention that i work with memory tables, same problem with file:/dev/shm/xxx.sqlite or file:/dev/shm/xxx.sqlite?mode=memory
This is the part of the code who make the insert:
our $THREADS = 32;

sub worker {
    my $tid = threads->tid;
    my( $Qwork, $Qresults ) = @_;
    while( my $item = $Qwork->dequeue ) {
        my $result;
        my ($M,$m) = @{$item};
        my $dbfile_connect = qq{${dbfile}_${M}_${m}};
        my $sqlite_dbh = sqlite_connect($dbfile_connect,'');
        create_table_dpt(\$sqlite_dbh,\$table_dpt_sqlite,\@{$data_dpt}
+);
        create_tmp_table(\$sqlite_dbh,\$table_a_sqlite,\$table_d_sqlit
+e,\%{$uniq_values});
        insert_a_tmp_table(\$sqlite_dbh,\$table_a_sqlite,\$table_d_sql
+ite,\@{$data_ref->{$M}->{$m}},\%{$data_g_c},\%{$data_c});

        $sqlite_dbh->disconnect();
        $Qresults->enqueue( \%{$result} );
        print "$M $m finit \n";
    }
    $Qresults->enqueue( undef ); ## Signal this thread is finished
}

my $Qwork = new Thread::Queue;
my $Qresults = new Thread::Queue;

my @pool = map{
    threads->create( \&worker, $Qwork, $Qresults )
} 1 .. $THREADS;

foreach my $M_m (sort { $uniq_values->{M_id_top}{$b} <=> $uniq_values-
+>{M_id_top}{$a} } keys(%{$uniq_values->{M_id_top}}))
{

                my ($M,$m) = split('_',$M_m);
                $Qwork->enqueue([$M,$m]);
}

$Qwork->enqueue( (undef) x $THREADS );
$_->join for @pool;

[download]

In reply to Sqlite: Threads and inserts into a different database for each one. (1 thread fast, >1 slow) by ssc37

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.