[ic] Category listing performance problems with catalog of over 17,000 products

[Andy Higgins]

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:

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