[Catalyst] Caching SQL results for speed...?

[will trillich]

Mostly thinking out loud here... but we welcome feedback if we're off
track...

Okay, after some perl -D:NTYProf tester.pl with 200 iterations:
70% of the time is taken up in five modules:
1) SQL::Abstract
2) DBIx::Class::ResultSet
3) Class::Accessor::Grouped
4) DBIx::Class::Storage::DBI
5) HTML::FormHandler::Field
...because in 200 iterations they were called millions of times each. (The
form requested has several select/option menus.)

Most of the data won't change rapidly -- e.g. client lists, countries,
employees just to populate a few popup menus -- so it seems like an
opportunity to benefit from caching the results, and only invalidating the
cache when a new record gets updated/deleted/added. Whole tables could be
cached without having to return to SQL/DBIC for every form-screen.

So we're looking for documentation on a best-of-breed approach for doing
this in a Catalyst environment...

Looking at Catalyst::TraitFor::Model::DBIC::Schema::Caching it looks like
you specify when the cache will expire:
$c->model('DB::Table')->search({ foo =3D> 'bar' }, { cache_for =3D> 18000 }=
);
whereas we would prefer being able to invalidate-cache-for-table-XYZ-now.

Aha, DBIx::Class::Cursor::Cached does include a
$rs->cursor->clear_cache;
method, we'll be tinkering with that, next!



On Tue, Apr 12, 2011 at 8:14 AM, will trillich
<will.trillich at serensoft.com>wrote:

> Thanks for the tips, Peter -- and for
> http://dragonstaff.blogspot.com/2009/05/testing-with-perl-catalyst.html!
> Got some cranking to do...
>
>
> On Tue, Apr 12, 2011 at 2:30 AM, Peter Edwards <peter at dragonstaff.co.uk>w=
rote:
>
>> On 12 April 2011 02:53, will trillich <will.trillich at serensoft.com>wrote:
>>
>>> Hi folks -- this may be more of a FormHandler question than a Catalyst
>>> question but I thought I'd check here to see if it's just us:
>>>
>>> We've been using HTML::FormHandler and are basically happy with it...
>>> until the performance issue mentioned below hit us. Any Catalystas runn=
ing
>>> into 50-second turnaround time with H:FH?
>>>
>>> [info] Request took 51.956100s (0.019/s)
>>>
>>> .------------------------------------------------------------+---------=
--.
>>> | Action                                                     | Time
>>>  |
>>>
>>> +------------------------------------------------------------+---------=
--+
>>> | /auto                                                      | 0.000181s
>>> |
>>> | /auth                                                      | 0.001857s
>>> |
>>> | /ticket/base                                               | 0.004652s
>>> |
>>> | /ticket/item                                               | 0.005865s
>>> |
>>> | /ticket/edit                                               | 51.88091s
>>> |
>>> |  Base:EDIT                                                 | 51.88050s
>>> |
>>> |   get FORM                                                 | 0.000078s
>>> |
>>> |   process FORM                                             | *
>>> 51.87286s* |
>>> | /end                                                       | 0.000290s
>>> |
>>>
>>> '------------------------------------------------------------+---------=
--'
>>>
>>> Turnaround time ranges from 6 seconds to 50+ seconds, with no discernab=
le
>>> pattern to the delay. (We can edit the same record multiple times and g=
et
>>> wildly differing lags.)
>>>
>>>
>>> Run your test server with perl -d:NYTProf myapp.pl and see which
>> routines use the time http://search.cpan.org/perldoc?Devel::NYTProf .
>> Maybe it is blocking on DNS network lookups.
>> Or if the time isused around the database calls, run with DBIC_TRACE=3D1
>> perl myapp.pl and watch and see which are the slow ones,  then run your
>> database query optimiser like  EXPLAIN SELECT foo;
>> http://dev.mysql.com/doc/refman/5.0/en/explain.html
>> http://www.postgresql.org/docs/8.3/static/sql-explain.html
>> Are other processes/users accessing the same database? If so check for
>> lock competition. Also disk space.
>>
>> Regards, Peter
>>
>>
>>
>> _______________________________________________
>> List: Catalyst at lists.scsys.co.uk
>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>> Searchable archive:
>> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
>> Dev site: http://dev.catalyst.perl.org/
>>
>>
>
>
> --
> 11 cheers for binary!
>



-- =

11 cheers for binary!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20110413/5fb0a=
1e6/attachment.htm