in reply to learning memcached

Your thinking made a huge leap between "I am trying to learn memcached.." (which is a fine objective) and I want SQLite queries to fun faster. Your benchmark results are certainly believable (it runs slower when trying to use memcached). I certainly would not anticipate any role at all for memcached in an SQLite application.

First SQLite has its own memory cache and it can be dynamically adjusted by the user. Default is 2MB. A dynamic increase of cache to 200MB would look like this: $dbh->do('PRAGMA cache_size = 200000'); #size in pages Certain operations like index creation can take a LOT of cpu and memory. In the app I'm currently working on, when creating the DB from a "scratch" situation, I run the cache size way up to decrease index creation from 60 to 30 seconds in my app. Big "bang" for one line of code!

The SQLite code is C/C++ and will do a far faster job of local cache management than some app, that is potentially on another machine. Also problems can arise when inserting cache between you and the DB (the stale data problem, if records are being updated).

The big hitter in your benchmark probably is the file open operation. Move that out of the loop to get a better idea of what the DB can do. With 20,000 strings, if avg size is 100 bytes, that is 2,000,000 bytes, 2MB. SQLite is already caching that much stuff without you doing anything at all. Making a cache of SQLite's cache is pointless.

Replies are listed 'Best First'.
Re^2: learning memcached
by punkish (Priest) on May 22, 2011 at 15:29 UTC
    I was using SQLite only because it is easy to use, and I use it just about for everything and everywhere I can. My intent was not to make SQLite queries run faster.

    That said, I actually posted my query on the memcache mailing list and discovered several things. The entire thread is at http://bit.ly/lnfiuv. The major takeaway was that Cache::Memcached was way to slow. Per "Dormando's" suggestion, I installed Cache::Memcached::libmemcached and memcached code actually became about 30% faster than pure SQLite code. I also modified my test to get_multi IDs, as I learned that network travel is just a lot slower than disk travel. Here is the result -- 

    Benchmark: timing 10000 iterations of query_dbh, query_mem...
 query_dbh:  6 wallclock secs ( 3.87 usr +  0.92 sys =  4.79 CPU) @ 20
+87.68/s (n=10000)
 query_mem:  6 wallclock secs ( 2.51 usr +  1.23 sys =  3.74 CPU) @ 26
+73.80/s (n=10000)

    [download]

    For the record, here is my complete test script, including the code for creating the test db. 

    #!/usr/local/bin/perl

use strict;
use Cache::Memcached::libmemcached;
use DBI qw(:sql_types);
use Benchmark qw(:all);

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=mem.sqlite","","", 
    {RaiseError => 1, AutoCommit => 0}
);

my $memd = new Cache::Memcached::libmemcached ({
    'servers' => [ "localhost:11212" ]
});

my $sql = "SELECT Ifnull(str, 'none') FROM t WHERE id = ?";
my @ids = map { int(rand(19_999)) || 1 } (0 .. 20);

timethese(10_000, {
    'query_mem' =>  sub {
        my $hashref = $memd->get_multi(@ids);
        
        my @res = ();
        while (my ($id, $str) = each %$hashref) {
            unless ($str) {
                my $sth = $dbh->prepare($sql);
                $sth->execute($id);
                ($str) = $sth->fetchrow_array;
                $memd->set($id, $str);
            }
            
            push @res, [$id, $str];
        }
        out(@res);
    },
    'query_dbh' =>  sub {
        my $sth = $dbh->prepare($sql);
        
        my @res = ();
        for (@ids) {
            $sth->execute($_);
            my ($str) = $sth->fetchrow_array;
            push @res, [$_, $str];
        }
        out(@res);
    },
});

sub out {
    my @res = @_;
    
    open F, ">", "foo.txt" or die $!;
    say F "id: " . $_->[0] . ", str: " . $_->[1] for (@res);
    close F;
}

sub create_db {
    my $sth = $dbh->prepare(
        "CREATE TABLE t (id INTEGER PRIMARY KEY, str TEXT"
    );
    
    $sth->execute;
    $dbh->commit;
}

sub pop_db {
    my $sth = $dbh->prepare("INSERT INTO t (str) VALUES (?)");
    
    $sth->execute(randstr(rand(100))) for (0 .. 20_000);
    $dbh->commit;
}

sub randstr {
    my $len = shift;

    my @chr = ('a'..'z','A'..'Z','0'..'9','_', ' ');
    return join "", map { $chr[rand @chr] } 1 .. $len;
}

    [download]


    when small people start casting long shadows, it is time to go to bed
[reply]
[d/l]
[select]
[reply]
[reply]

In Section Seekers of Perl Wisdom