Re: SELECT cacheing
I don't know about Roger, but in my situation queries are called as
follows.
my $queryhandle=Query("select blah from blah where blah")
the Query routine can be overloaded with a timeout value (a default
capable of being set), with a timeout of 0 meaning that the select
should never be cached and should always be selected live from the
database. I'd assume Roger would need to have something similar in the
module he's developing.
regards,
P
On Fri, 8 Sep 2000, Drew Taylor wrote:
Roger Espel Llima wrote:
I've written a very small module to cache SELECT results from DBI
requests. The interface looks like:
use SelectCache;
my $db = whatever::get_a_handle();
my $st = qq{ select this, that ... };
my $rows = SelectCache::select($db, $st, 180);
this returns an arrayref of rows (like the selectall_arrayref function),
and caches the result in a file, which gets reused for 180 seconds
instead of asking the db again.
The names of the cache files are the md5's of the select statement,
using the last hex digit as a subdirectory name. There's no file
cleanup function; you can always do that from cron with find.
This is all very simple, but it's pretty useful in combination with
mod_perl, to speed up things like showing the "latest 10 posts", on
frequently accessed webpages.
The question now is: is there any interest in releasing this? I could
write some minimal docs and give it a 'proper' module name, if there's
interest.
I'm certainly interested. One question though - in the module do you
blindly use the cache? I ask because in my instance I display the
contents of a shopping cart on every page. And while only a few pages
change the cart contents, the cart listing does need to be current. How
do you handle this situation?
--
.-----.
| Peter SkipworthPh: 03 9897 1121 |
| Senior Programmer Mob: 0417 013 292 |
| realestate.com.au [EMAIL PROTECTED] |
`-'
Re: SELECT cacheing
Roger,
Sounds interesting - as a matter of fact, I'm doing it exactly the same
way on realestate.com.au, except I use the last 2 hex digits as the
directory name. Great minds think alike!
I don't know if many people realise this, but mySQL, for those of you
that use it, will soon include a select_cached function, for doing
exactly the same. Of course, a non-RDSM-specific solution would be great
as well on the Perl side. I'm using Storable to write the query result to
disk as an array of hashes. I've implemented most of DBI's query methods,
including fetchrow, fetchrow_hashref, fetchrow_arrayref, etc, and it works
quite well and is transparent to the programmer. Page generation speed
improved by up to 100% in a lot of cases.
I'd suggest that you include an (optionally
implementable) cache-cleaning routine, so that it's an 'all in one'
solution, as well as parameters such as 'maximum cache size' and
cache-timeout per store. Let me know if you'd like any help with getting
this completed, as I'd be happy to help.
regards,
Peter Skipworth
On Thu, 7 Sep
2000, Roger Espel Llima wrote:
I've written a very small module to cache SELECT results from DBI
requests. The interface looks like:
use SelectCache;
my $db = whatever::get_a_handle();
my $st = qq{ select this, that ... };
my $rows = SelectCache::select($db, $st, 180);
this returns an arrayref of rows (like the selectall_arrayref function),
and caches the result in a file, which gets reused for 180 seconds
instead of asking the db again.
The names of the cache files are the md5's of the select statement,
using the last hex digit as a subdirectory name. There's no file
cleanup function; you can always do that from cron with find.
This is all very simple, but it's pretty useful in combination with
mod_perl, to speed up things like showing the "latest 10 posts", on
frequently accessed webpages.
The question now is: is there any interest in releasing this? I could
write some minimal docs and give it a 'proper' module name, if there's
interest.
--
.-.
| Peter SkipworthPh: 03 9897 1121 |
| Senior Programmer Mob: 0417 013 292 |
| realestate.com.au [EMAIL PROTECTED] |
`-'
Fulltime employment opportunities in Melbourne, Oz.
The following two mod_perl-centric positions are now available at Australia's premier online realestate company, realestate.com.au. Please forward enquiries and applications to [EMAIL PROTECTED] Perl Programmer 1 Duties: The Perl Programmer is responsible for the: · Development of Perl code to ensure the timely completion of set tasks · Technical documentation of all code produced · Completion of individual projects · Completion of maintenance tasks · Development of unique solutions for novel requirements · Liaison with design teams and internal customers where required · Participation in team work and active communication with others in the team Required skill set: · Excellent internet knowledge · Excellent HTML skills · More than two years Perl 5 experience, preferably with object oriented experience · Excellent Unix skills · Client server development skills · XML experience an advantage · Oracle experience an advantage · Unix administration (web/ security) an advantage · Excellent communication skills, written and verbal · Concise clear technical written and verbal skills · Good team player · Excellent time management skills · Able to multitask · Testing experience · Common sense · Assertive · Work within guidelines · Creative Perl Programmer 2 Duties: The Perl Programmer is responsible for the: · Development of Perl code to ensure the timely completion of set tasks · Technical documentation of all code produced · Completion of individual projects · Completion of maintenance tasks · Development of unique solutions for novel requirements · Liaison with design teams and internal customers where required · Participation in team work and active communication with others in the team Required skill set: · Excellent internet knowledge · Excellent HTML skills · More than two years Perl 5 experience, preferably with object oriented experience · Oracle experience · Excellent Unix skills · Client server development skills an advantage · Unix administration (web/ security) an advantage · Excellent communication skills, written and verbal · Concise clear technical written and verbal skills · Good team player · Excellent time management skills · Able to multitask · Testing experience · Common sense · Assertive · Work within guidelines · Creative
Caching DB queries amongst multiple httpd child processes
Does anyone have any experience in using IPC shared memory or similar in caching data amongst multiple httpd daemons ? We run a large-ish database dependent site, with a mysql daemon serving many hundreds of requests a minute. While we are currently caching SQL query results on a per-process basis, it would be nice to share this ability across the server as a whole. I've played with IPC::Shareable and IPC::ShareLite, but both seem to be a little unreliable - unsurprising as both modules are currently still under development. Our platform is a combination of FreeBSD and Solaris servers - speaking of which, has anyone taken this one step further again and cached SQL results amongst multiple web servers ? Thanks in advance, Peter Skipworth -- .-. | Peter SkipworthPh: 03 9897 1121 | | Senior Programmer Mob: 0417 013 292 | | realestate.com.au [EMAIL PROTECTED] | `-'
