[Date Prev][Date Next][Thread Prev][Thread Next][Interchange by date
][Interchange by thread
]
Re: [ic] Category listing performance problems with catalog of over 17,000 products
At 03:28 PM 3/11/2001 +1100, you wrote:
>Hello,
>
>We are running one Interchange catalog (containing just over 17,000
>products) on a 2 x 300Mhz Sun Solaris Ultra 2 with 1 GB of memory. This
>machine is shared with one other application but consistently runs with a
>load average of less than 1 and much of our testing has been done at off
>peak times. The database being used for this catalog is mysql and the set up
>of Interchange is as follows:
[... excerpted slow performance ...]
I have the same problem, but for a different reason (we generate custom
categories on a per-visitor basis). But you might find the following "Tips
and tricks" posting by Mike Heins very helpful: (I just discovered it
solves my "how to benchmark" question I just posted, 'cat egg > my/face').
From: interchange-users-admin@minivend.com on behalf of Mike Heins
[mikeh@minivend.com]
Sent: Tuesday, November 21, 2000 12:58 PM
To: Interchange User List
Subject: [ic] Tips and Tricks -- optimizing lists
Area: Core
Category: Templates
Item: List optimization
Interchange has powerful search capabilities that allow you to produce
lists of items for use in category lists, product lists, indexes, and other
navigation tools.
These are a two-edged sword, though. Lists of hundreds or thousands of entries
can be returned, and techniques that work well displaying only a few items may
slow to a crawl when a large list is returned.
In general, when you are returning one item (i.e. a flypage) or a small list
(i.e. a shopping cart) you can be pretty carefree in your use of [if ...] and
[calc] and [perl] tags. When there are hundreds of items, though, you cannot;
each complex test or embedded Perl snippet causes the Safe module to have to
evaluate code, and each ITL tag requires parsing and argument building.
The Safe module is pretty fast considering what it does, but it can only
generate
a few thousand instances per second even on a fast system. And the ITL tag
parser can likewise only parse thousands of tags per CPU second.
What to do? You want to provide complex conditional tests but you don't want
your system to slow to a crawl. Luckily, there are techniques which can speed
up complex lists by orders of magnitude.
------------
Benchmarking
------------
A non-precise benchmark of different iteration options can be done
with the following global UserTag. Place this in a file in the
usertag/ directory in the Interchange root:
UserTag benchmark Order start display
UserTag benchmark AddAttr
UserTag benchmark Routine <<EOR
my $bench_start;
my @bench_times;
sub {
my ($start, $display, $opt) = @_;
my @times = times();
if($start or ! defined $bench_start) {
$bench_start = 0;
@bench_times = @times;
for(@bench_times) {
$bench_start += $_;
}
}
my $current_total;
if($display or ! $start) {
for(@times) {
$current_total += $_;
}
unless ($start) {
$current_total = sprintf '%.3f', $current_total - $bench_start;
for(my $i = 0; $i < 4; $i++) {
$times[$i] = sprintf '%.3f', $times[$i] - $bench_times[$i];
}
}
return $current_total if ! $opt->{verbose};
return "total=$current_total user=$times[0] sys=$times[1]
cuser=$times[2] csys=$times[3]";
}
return;
}
EOR
Then at the beginning of the code to check, call
[benchmark start=1]
to start the measurement. At the end
[benchmark]
will display the time used. Bear in mind that it is not precise, and
that there may be variation due to system conditions. Also, the longer
the times and the bigger the list, the better the comparison.
To see the system/user breakdown, do:
[benchmark verbose=1]
In general, "user" time measures Interchange processing time and and
the rest are indicative of the database access overhead, which can vary
widely from database to database.
-------------
Optimizations
-------------
----------------------------------------------------
[PREFIX-tag] is faster than [parsed-tag]
[loop prefix=foo search="ra=yes"]
[foo-data products image]
is slightly faster than
[foo-field image]
which is MUCH faster than
[data products image [foo-code]]
which is faster than
[data table=products column=image key="[foo-code]"]
[/loop]
The loop tags are interpreted by means of fast regular expression
scans of the loop container text, and fetch an entire row of
data in one query. The [data ...] ITL tag interpretation is
delayed until after the loop is finished, whereby the ITL tag
parser must find the tag, build a parameter list, then fetch the
data with a separate query. If there are repeated references to
the same field in the loop, the speedup can be 10x or more.
----------------------------------------------------
Pre-fetch data with rf=field1,field2,field3 and access
with [PREFIX-param field1].
mv_return_fields (otherwise known as "rf" in one-click
terminology) sets the fields that are returned from a search.
Once they are returned, they can be accessed with [PREFIX-param
field].
They can also be referenced with [PREFIX-pos N], where N is a digit
representing the ordinal position (i.e. starting with 0) in the
list of fields.
The following are equivalent:
Benchmark loop-field list: [benchmark start=1]
<!-- [loop search="ra=yes/st=db"]
[loop-code] price: [loop-field price] [/loop] -->
TIME: [benchmark]
<P>
Benchmark loop-param list: [benchmark start=1]
<!-- [loop search="ra=yes/st=db/rf=sku,price"]
[loop-code] price: [loop-param price] [/loop] -->
TIME: [benchmark]
but the second is much, much faster.
----------------------------------------------------
[PREFIX-alternate N] is available for row counting and display.
A common need when building tables is to conditionally close the table
row or data containers. I see a lot of:
[loop search="ra=yes"]
[calc] return '<TR>' if [loop-increment] == 1; return[/calc]
[calc] return '' if [loop-increment] % 3; return '</TR>' [/calc]
[/loop]
Much faster, by a few orders of magnitude, is:
[loop search="ra=yes"]
[loop-change 1][condition]1[/condition]<TR>[/loop-change 1]
[loop-alternate 3]</TR>[/loop-alternate]
[/loop]
If you think you need to close the final row by checking the
final count, look at this:
[loop search="ra=yes"]
[on-match]
<TABLE>
<TR>
[/on-match]
[list]
<TD>[loop-code]</TD>
[loop-alternate 3]</TR><TR>[/loop-alternate]
[/list]
[on-match]
</TR>
</TABLE>
[/on-match]
[no-match]
No match, sorry.
[/no-match]
[/loop]
This is a hundred times faster than anything you can build with
multiple [calc] tags.
----------------------------------------------------
Use simple go/nogo comparisons in [if ...]
Consider these two snippets:
[if scratch|value|cgi key] THEN [/if]
and:
[if scratch|value|cgi key == '1'] THEN [/if]
The first one doesn't require Perl evaluation. It simply checks to see
if the value is blank or 0, and returns true if it is anything but.
Of course this requires setting your test values to blank or 0 instead
of "No" or " " or somesuch, but it is anywhere from 20-35% faster.
Try it on the construct demo:
---- begin test ---
Overhead:
[benchmark start=1]
<!-- [loop search="ra=yes"][set cert][loop-field
gift_cert][/set][/loop] -->
[benchmark]
<P>
if scratch compare:
[benchmark start=1]
<!--
[loop search="ra=yes"]
[set cert][loop-field gift_cert][/set]
[loop-code] [if scratch cert] YES [else] NO [/else][/if]
[loop-code] [if scratch cert] YES [else] NO [/else][/if]
[loop-code] [if scratch cert] YES [else] NO [/else][/if]
[loop-code] [if scratch cert] YES [else] NO [/else][/if]
[loop-code] [if scratch cert] YES [else] NO [/else][/if]
[/loop]
-->
[benchmark]
<P>
if scratch compare eq 1:
[benchmark start=1]
<!--
[loop search="ra=yes"]
[set cert][loop-field gift_cert][/set]
[loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
[loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
[loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
[loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
[loop-code] [if scratch cert == 1] YES [else] NO [/else][/if]
[/loop]
-->
[benchmark]
<P>
[page @@MV_PAGE@@]Again[/page]
---- end test ---
----------------------------------------------------
Use [PREFIX-calc] instead of [calc] or [perl]
(This facility is not yet documented, but will be soon.)
You can execute the same code as [calc] with [PREFIX-calc], which has
two benefits:
1. It doesn't require ITL parsing.
2. It is executed during the loop instead of after it.
The [PREFIX-calc] object has complete access to all normal
embedded Perl objects like $Values, $Carts, $Tag, and such. If
you want to make a data table (i.e. "products" or "pricing")
available for access inside of it, just do:
[perl tables="products pricing"] [/perl]
prior to list start. Now you can do something like:
[loop search="ra=yes"]
[loop-calc]
$desc = $Tag->data('products', 'description', '[loop-code]');
$link = $Tag->page('[loop-code]');
return "$link $desc </A>";
[/loop-calc] <BR>
[/loop]
----------------------------------------------------
ADVANCED: Precompile and execute with [PREFIX-sub] and [PREFIX-exec]
(This facility is not yet documented, but will be soon.)
For repetitive routines, you can achieve a considerable savings
in CPU by pre-compiling your embedded Perl code.
In the "Construct Something" demo, the bar_link() routine in
catalog_before.cfg is an example of compiling the subroutine once
at catalog configuration time.
You can also compile routines at the time of the list execution
with [item-sub routine] CODE [/item-sub]. This means only one
Safe evaluation is done -- every time the [loop-exec routine]
is called, it is done fast as a call to the routine. This can be
10 times or more faster than separate [calc] calls, or 5 times
faster than separate [PREFIX-calc] calls.
Example:
[benchmark start=1]
loop-calc:
<!--
[loop search="st=db/fi=country/ra=yes/ml=1000"]
[loop-calc]
my $code = q{[loop-code]};
return "code '$code' reversed is " . reverse($code);
[/loop-calc]
[/loop]
-->
[benchmark]
<P>
[benchmark start=1]
loop-sub and loop-exec:
<!--
[loop search="st=db/fi=country/ra=yes/ml=1000"]
[loop-sub country_compare]
my $code = shift;
return "code '$code' reversed is " . reverse($code);
[/loop-sub]
[loop-exec country_compare][loop-code][/loop-exec]
[/loop]
-->
[benchmark]
----------------------------------------------------
ADVANCED: Execute and save with [query ...], then use an embedded Perl
routine.
You can run [query arrayref=myref sql="query"], which saves the
results of the search/query in a Perl reference. It is then
available in $Tmp->{myref}. (Of course, "myref" can be any
arbitrary name.)
This is the fastest possible method to display a list.
Observe:
--- begin test code ---
[set waiting_for]os28004[/set]
[benchmark start=1] Embedded Perl
<!--
[query arrayref=myref sql="select sku, price, description from
products"]
<!-- make query, this container text is not used. -->
[/query]
[perl]
# Get the query results, has multiple fields
my $ary = $Tmp->{myref};
my $out = '';
foreach $line (@$ary) {
my ($sku, $price, $desc) = @$line;
if($sku eq $Scratch->{waiting_for}) {
$out .= "We were waiting for this one!!!!\n";
}
$out .= "sku: $sku price: $price description:
$desc\n";
}
return $out;
[/perl]
-->
TIME: [benchmark]
[benchmark start=1] All loop
<!--
[query list=1 sql="select sku, price, description from products"]
[if scratch waiting_for eq '[sql-code]']We were waiting
for this one!!!!
[/if] sku: [sql-code]price: [sql-param price] desc:
[sql-param description]
[/query]
-->
TIME: [benchmark]
--- end test code ---
----------------------------------------------------
Other things that help:
*** Avoid interpolate=1 when possible. A separate tag parser must be spawned
every time you do this. Many times people use this without needing it.
*** Avoid saving large values to Scratch, as these have to be written to
the users session. If you need them only for the current page, clear
at the end by using [tmp scratch_var] contents [/tmp], which is
the same as [seti scratch_var] contents [/seti] except clears the
value before the session is written. You can also use
[scratchd scratch_var] to return the contents and delete them from
the session at the same time.
*** Use the [more-list] facility to break up your large searches. You
can use them in [query ....] and [loop ...] searches as well -- see
the docs.
--
Akopia, Inc., 131 Willow Lane, Floor 2, Oxford, OH 45056
phone +1.513.523.7621 fax 7501 <heins@akopia.com>
Any man who is under 30, and is not liberal, has not heart; and any man
who is over 30, and is not a conservative, has not brains.
-- Winston Churchill
_______________________________________________
Interchange-users mailing list
Interchange-users@www.minivend.com
http://www.minivend.com/mailman/listinfo/interchange-users
>Interchange version 4.6.1
>Interchange PID 1666
>Interchange SUID User interchange (uid 207)
>Last time server restarted Sun Mar 11 14:10:43 2001
>Last time changes applied Sun Mar 11 14:10:42 2001 (apply now)
>Environment variables passed MOD_PERL
>Perl Version Perl 5.00503 (called with: /usr/local/bin/perl)
>Optional Module Information:
>LWP::Simple found (v1.32).
>MD5 found (v2.01).
>MIME::Base64 found (v2.11).
>SQL::Statement found (v0.1016).
>Safe::Hole found (v0.08).
>Storable found (v1.010).
>Tie::Watch not found. Minor: cannot set watch points in catalog.cfg.
>URI::URL found (v5.02).
>Safe operations untrapped ftfile sort rand
>Database Interfaces:
>No GDBM.
>Berkeley DB_File available (v1.65)
>DBI enabled (v1.11), available drivers:
>
>The site hasn't been launched yet and is still in beta but can be accessed
>at:
>
>URL: http://www.bidorbuyshop.com.au
>User: demo
>Pass: banana69
>
>The main problem we are facing preventing us from launching is the
>performance of the category pages. The homepage of the site is a static page
>but you will see as soon as you click on any category listing (the main
>categories on our site are different areas in Interchange - we are therefore
>talking about the sub-categories - the ones that list all the products). It
>seems that the speed of this is unacceptable however to access an individual
>product description there is no problem at all.
>
>With the way the category listings are handled in Interchange it doesn't
>seem as though any further indexing or tweaking on the mysql database is
>going to help. We have thought of two ways we might be able to solve this
>problem:
>
>1. Split the number of products into multiple catalogs so that we have only
>a couple of thousand products in each catalog.
>2. Have a background process which runs periodically generating static pages
>for all the category listings and change the links so that the end users
>always access these static pages rather than the dynamic ones. Of course
>with this option we give away some of the functionality we would have
>utilizing the user's session when they are logged in.
>
>Does any one have any experience or any other ideas on how else we might be
>able to improve the performance in our case specifically for the generating
>of a product listing from clicking on the categories.
>
>Any assistance would be very much appreciated.
>
>Regards,
>
>Andy Higgins
>Research and Development bidorbuy.com Inc
>
>
>
>_______________________________________________
>Interchange-users mailing list
>Interchange-users@lists.akopia.com
>http://lists.akopia.com/mailman/listinfo/interchange-users
Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com
_______________________________________________
Interchange-users mailing list
Interchange-users@lists.akopia.com
http://lists.akopia.com/mailman/listinfo/interchange-users
