Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Re^2: learning memcached

by punkish (Priest)
on May 22, 2011 at 15:29 UTC ( #906177=note: print w/ replies, xml ) Need Help??


in reply to Re: learning memcached
in thread learning memcached

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)

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; }


when small people start casting long shadows, it is time to go to bed


Comment on Re^2: learning memcached
Select or Download Code
Re^3: learning memcached
by steve (Deacon) on May 24, 2011 at 15:42 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2015-07-04 17:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls