Re: speeding up XS_DBI_dispatch()

On Tue, Jan 24, 2012 at 08:42:28PM +0000, Tim Bunce wrote:
> On Mon, Jan 23, 2012 at 12:51:12AM +0000, Dave Mitchell wrote:
> > In the particular case I've been investigating, the method lookup in
> > XS_DBI_dispatch() contributed a measurable part of the total loop time;
> > in particular, when as a test I added very crude and hacky caching for the
> > lookup of the inner fetch method, the execution time for the loop dropped
> > from 23.0s to 19.2s.
> 
> I wonder what that is as a % change in the cost of dispatch.

Using a localhost mysql server with a table with two int columns and 20M
rows, and the following code on a non-threaded, -O2  perl 5.15.6:

    ...
    my $cmd = 'select SQL_NO_CACHE id, val from t3';
    $dbh->{'mysql_use_result'}=1;
    my $sth = $dbh->prepare($cmd) or die;
    my ($id, $val);
    $sth->bind_columns(\$id, \$val);

    while ($sth->fetch) {
	$c++;
    }

then the CPU usage of the while loop broke down as follows:

    7.0% overhead of loop,              i.e. while() {$c++}
   19.2% handle the outer method call,  i.e. $sth->fetch()
         of which half is method lookup,
         half is assemble args ($sth) and pp_entersub
   12.7% XS_DBI_dispatch() doing DBI stuff
   20.5% XS_DBI_dispatch() doing method lookup and doing direct
         XS call to DBD::mysql::fetchrow_arrayref()
   40.6% DBD::mysql::fetchrow_arrayref() fetching a row

Given that my hacky cache for fetch() lookup reduced overall execution
time by 16.5%, that reduces overall XS_DBI_dispatch percentage from 33.2%
to 16.7%, i.e. nearly halves the dispatch time.

> The guts of the DBI are ancient and baroque with many dusty corners
> were dragons may, or may not, be lurking.

Oh dear :-)

Just out of curiosity (since I'm in the middle of trying to understand
the code in XS_DBI_dispatch), what is the main purpose of having all
functions share a big dispatch fn which calls the inner method, rather
than having a separate function for each?

> > * To what extent (if any) is DBI allowed to cheat when it comes to
> >   bypassing the perl API (in particular I'm thinking of the method
> >   invalidation stuff which I don't think has always been part of the API).
> 
> The DBI is always allowed to cheat in the name of performance!
> My simple but effective xsbypass hack doesn't seem to have caused
> problems in the years it's been there (though it's a pity threaded perls
> can't use it).

Just out of curiosity, why can't threaded perls use it?

PS - I'm specifically being paid only to fix a performance issue on
non-threaded builds, so I won't be looking at threaded issues. But
dPERINTERP looks like bad news on threaded builds.

-- 
Lady Nancy Astor: If you were my husband, I would flavour your coffee
with poison.
Churchill: Madam - if I were your husband, I would drink it.