[Koha-devel] DBIx::Class vs current Koha's DBI performance

[Fouts, Clay]

Hello, Dobrica.

Memoize and local caching is handy, and Koha has functions that benefit from
its use. However, because Koha's functions' return values usually depend on
database contents rather than just the parameters, you'd have to be very
clear about unmemoizing functions if the data on which they depends gets
changed. This is not always easy to determine since there's not a clear or
enforceable domain defined for which parts of the code can read and write
directly to various parts of the database contents.

Using a cache in such a way as to avoid these staleness problems typically
demands consistent use of a limited set of accessor and mutator functions
for the database contents, isolating the cache seeding and checking code
within those. That way, by defining those functions for, for example, the
'branches' table you can avoid caching the whole branch detail in one cache
and then the branch name in another. From within GetBranchName you'd simply
call GetBranchDetail (which in turn would call the private _get_branch_row
or something), then return only the name field.

I imagine you're already aware of much of this, and that this is a proof of
concept testbed you're publishing. I only bring it up because it's important
that others understand the subtlety of some of the issues that caching can
introduce, particularly when the code doesn't naturally support its use.

Caching DB contents has an even more pronounced impact when latency is
introduced by hosting the database on a different machine than the CGI
server. When you have 2k+ queries per script, even a few milliseconds RTT
adds up to a rather large number. Consequently, for many of Koha's smaller
tables, it often ends up being faster to seed the cache by fetching the
entire table on the first call rather than retrieving the rows as-needed. In
most Koha setups I've seen, bandwidth and memory are cheaper than latency +
MySQL query overhead. Over Unix sockets the 65kB datagram size allows the
contents of many tables to be delivered in a single packet. Ethernet is
smaller, of course, but the 1500 byte standard MTU is still large enough for
several tables to fit in a single packet, plus TCP windowing greatly reduces
the impact of subsequent packet latencies.

My experience is that the startup overhead introduced by DBIx::Class is not
sufficiently offset by its caching features in a CGI environment. Running
under Plack or something similar would easily recoup that overhead, of
course.

Cheers,
Clay


On Tue, Oct 26, 2010 at 10:17 AM, Dobrica Pavlinusic <dpavlin at rot13.org>wrote:

> I just figured out today that kohacon10 is in progress, and one of
> topics seems to be DBIx::Class migration.
>
> Let me start by saying that I'm all for it, but probably not for same
> resons as everybody else.
>
> I was trying to profile koha search page. On first run under
> Devel::NYTProf it showed 2220 invocations of DBI::st::execute.
>
> That's more than 2000 calls which wait for database to return answer.
> This might explain extremly high usage of mysql query cache which I'm
> seeing in production:
>
> http://mjesec.ffzg.hr/munin/koha/koha-mysql_queries.html
>
> So, I decided to experiment a bit. and few patches later I was able
> to reduce number of queries issued on each page load down to 876
>
> If you are interested in what I changed, my code is at:
>
> http://git.rot13.org/?p=koha.git;a=shortlog;h=refs/heads/nytprof-cache
>
> It's basically using either global hash to cache values per request of
> simple memoize around function.
>
> Which brings me back to DBIx::Class. If we can cache values using dbic,
> this will be huge WIN for Koha performance as opposed to loss :-)
>
> I never used dbic in that contex, but google seems to think it's
> possible, and I'm willing to take a try at it.
>
> --
> Dobrica Pavlinusic               2share!2flame
> dpavlin at rot13.org
> Unix addict. Internet consultant.
> http://www.rot13.org/~dpavlin
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha-community.org
> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-devel/attachments/20101026/fa299a58/attachment.htm>