Mailing List Archive

Quoting Barney Treadway (barney@ecomshare.com):
> Greetings all,
>
> We're looking at a mysql backed interchange with estimated 3-5 million
> product records in short order. There's a natural break up in products to
> allow us to create 50-100 product tables rather than a single massive table.
> This would facilitate quicker updating etc from those editing/adding
> products (which we do via php). Does anyone have any input (advice,
> concerns, suggestions, threats, etc) as to interchange's ability to do
> common loops and search regions on either 1) a large number of tables
> (50-100) or 2) tables with millions of records?
>
> Any responses would be greatly appreciated.
>

There is really no way to do this well. It would require 50 queries
instead of one. And 50-100 is a lot of table objects to maintain. It is
too many ProductFiles for performance to be maintained in most cases.

It would be much faster to index a large table on a field and display it that
way. This is a logical break as well -- MySQL can do very fast selects
on a field = 'string' indexed field.

One possibility is to place only sku, category/owner pointer, and
short description in the large table and break the subsidiary stuff
into separate tables that would then be selected via the category
pointer. This would be a fast table to update, while the separate
tables that contain the extended data could be broken into pieces.
Still, the more tables you have the more overhead. If you want to
place a lot of load on the catalog you would have to encode your
display routines in embedded Perl to get real performance.

--
Akopia, Inc., 131 Willow Lane, Floor 2, Oxford, OH 45056
phone +1.513.523.7621 fax 7501 <heins@akopia.com>

Be patient. God isn't finished with me yet. -- unknown

Thanks Mike,

So if I understand, Scenario 1 would be one instance of ic with the 50-100
product tables but searching and all operations needing to look at all
tables would instead be performed through a master indexed table. Once a sku
has been acquired from the indexed table, then we'd drop to the individual
product tables to perform presentation and purchase operations? The master
indexed table would contain the appropriate product table name where the sku
is located, allowing products to be called directly from the appropriate
product table itself rather than scanning all tables. That would keep
performance fine to that stage, but keeping ic from doing a call to the
product tables through the entire cart process seems a major alteration
though. Then I have to consider the corresponding pricing table that might
grow to cumbersome size as well.

Scenario 2 was running a running separate instance of ic for each of the
50 - 100 tables (keeping records there down to a few hundred thousand) and
using a master indexing and search outside of ic to then point the purchase
process into the ic corresponding to the product table. The fault here with
this seems to be when products are purchased from multiple tables, things
would fall apart? Unless..... the "on-fly" directive could be employed in
this case to prevent searching all product tables and rather drop in enough
data to allow checkout without a table access? Theoretically you could then
run checkout through any of the ic instances. Concerns would be passing
enough data to allow modifiers and pricing adjustment to be employed where
appropriate. One caveat would be ic's inherent ease of writing from all
instances to a single orderline and transaction table.

I don't quite follow the "category pointer reference"...

So finding a way to attach the product table name to the sku to facilitate
direct look up through the checkout and other processes with [data table
sku] would seem a good step.

Again, thanks for the insight. I'm going to push ahead with this in some
manner and I'll endeavor to keep folks informed as to load issues and
workarounds for catalogs of this size. I'd appreciate any feedback or words
of warning/encouragement. :-)

Barney

>
> Quoting Barney Treadway (barney@ecomshare.com):
> > Greetings all,
> >
> > We're looking at a mysql backed interchange with estimated 3-5 million
> > product records in short order. There's a natural break up in
> products to
> > allow us to create 50-100 product tables rather than a single
> massive table.
> > This would facilitate quicker updating etc from those editing/adding
> > products (which we do via php). Does anyone have any input (advice,
> > concerns, suggestions, threats, etc) as to interchange's ability to do
> > common loops and search regions on either 1) a large number of tables
> > (50-100) or 2) tables with millions of records?
> >
> > Any responses would be greatly appreciated.
> >
>
> There is really no way to do this well. It would require 50 queries
> instead of one. And 50-100 is a lot of table objects to maintain. It is
> too many ProductFiles for performance to be maintained in most cases.
>
> It would be much faster to index a large table on a field and
> display it that
> way. This is a logical break as well -- MySQL can do very fast selects
> on a field = 'string' indexed field.
>
> One possibility is to place only sku, category/owner pointer, and
> short description in the large table and break the subsidiary stuff
> into separate tables that would then be selected via the category
> pointer. This would be a fast table to update, while the separate
> tables that contain the extended data could be broken into pieces.
> Still, the more tables you have the more overhead. If you want to
> place a lot of load on the catalog you would have to encode your
> display routines in embedded Perl to get real performance.
>

Barney Treadway wrote:
> Again, thanks for the insight. I'm going to push ahead with this in some
> manner and I'll endeavor to keep folks informed as to load issues and
> workarounds for catalogs of this size. I'd appreciate any feedback or words
> of warning/encouragement. :-)
-----------------------------------------------------------------
I have run into many problems with large databases and IC. I HIGHLY
recommend the MySQLman application from the below website. Depending on
your system it may be tricky to setup but it will allow you to have an
excellent tool for managing these larger databases. I also use WebMin in
both site management and in DB management. Good Luck!

http://www.gossamer-threads.com
http://www.webmin.com
--
We specialize in multi-processor computing systems!
John Foster
AdVance-Computing Systems

Greetings John,

You mention "many problems" with large databases. Did you find a workaround
or did you go elsewhere for a mysql driven cart?

I use Webmin and phpmyadmin currently and couldn't live without either!
Mysqlman looks about identical to phpmyadmin.

Thanks,

Barney

>
>
> Barney Treadway wrote:
> > Again, thanks for the insight. I'm going to push ahead with this in some
> > manner and I'll endeavor to keep folks informed as to load issues and
> > workarounds for catalogs of this size. I'd appreciate any
> feedback or words
> > of warning/encouragement. :-)
> -----------------------------------------------------------------
> I have run into many problems with large databases and IC. I HIGHLY
> recommend the MySQLman application from the below website. Depending on
> your system it may be tricky to setup but it will allow you to have an
> excellent tool for managing these larger databases. I also use WebMin in
> both site management and in DB management. Good Luck!
>

Quoting John Foster (jfoster@augustmail.com):
> Barney Treadway wrote:
> > Again, thanks for the insight. I'm going to push ahead with this in some
> > manner and I'll endeavor to keep folks informed as to load issues and
> > workarounds for catalogs of this size. I'd appreciate any feedback or words
> > of warning/encouragement. :-)
> -----------------------------------------------------------------
> I have run into many problems with large databases and IC. I HIGHLY
> recommend the MySQLman application from the below website. Depending on
> your system it may be tricky to setup but it will allow you to have an
> excellent tool for managing these larger databases. I also use WebMin in
> both site management and in DB management. Good Luck!

It is easy to shoot yourself in the foot with any application and a large
database. I know, I have done it many times. Interchange's admin UI is
optimized for DBs that are 50,000 records or less. If you have that many
records, you need SQL to use the IC admin tool with any success. Even
then, you have to make sure that proper indexing is done on all fields
that are selected/sorted on.

I would be very interested to hear of success stories with other shop/cart
builders and very large databases. I haven't heard of many; in fact,
more than a few Interchange users are refugees from some commercial
application that hit the wall at 20,000 products.

The Interchange application itself will not repeal the laws of physics,
but if used in a reasonable manner it is capable of working with very
large database tables. There are sites which have as many as a million
items, and at least one that has 500,000 and uses GDBM. They don't try and
run the item editor on that, though! And they use external import/export
programs and don't try to do auto-import and export.

--
Akopia, Inc., 131 Willow Lane, Floor 2, Oxford, OH 45056
phone +1.513.523.7621 fax 7501 <heins@akopia.com>

Research is what I'm doing when I don't know what I'm doing.
-- Wernher Von Braun

Amen.

Actually in my case all admin, product entry/edit/delete is done through
separate php scripts with other functions also done my separate php apps. We
want ic/mv to do the cart operations only.

I'm a refugee from MS Commerce Server myself which costs bazillions to own,
and bazillions to operate, and is about as forgiving as the Spanish
Inquisition. Not to mention less powerful.... :-)

That's a kudos by the way.

Barney

> Quoting John Foster (jfoster@augustmail.com):
> > Barney Treadway wrote:
> > > Again, thanks for the insight. I'm going to push ahead with
> this in some
> > > manner and I'll endeavor to keep folks informed as to load issues and
> > > workarounds for catalogs of this size. I'd appreciate any
> feedback or words
> > > of warning/encouragement. :-)
> > -----------------------------------------------------------------
> > I have run into many problems with large databases and IC. I HIGHLY
> > recommend the MySQLman application from the below website. Depending on
> > your system it may be tricky to setup but it will allow you to have an
> > excellent tool for managing these larger databases. I also use WebMin in
> > both site management and in DB management. Good Luck!
>
> I would be very interested to hear of success stories with other shop/cart
> builders and very large databases. I haven't heard of many; in fact,
> more than a few Interchange users are refugees from some commercial
> application that hit the wall at 20,000 products.
>
> The Interchange application itself will not repeal the laws of physics,
> but if used in a reasonable manner it is capable of working with very
> large database tables. There are sites which have as many as a million
> items, and at least one that has 500,000 and uses GDBM. They don't try and
> run the item editor on that, though! And they use external import/export
> programs and don't try to do auto-import and export.
>

Mike Heins wrote:
>
> Quoting John Foster (jfoster@augustmail.com):
> > Barney Treadway wrote:
> > > Again, thanks for the insight. I'm going to push ahead with this in some
> > > manner and I'll endeavor to keep folks informed as to load issues and
> > > workarounds for catalogs of this size. I'd appreciate any feedback or words
> > > of warning/encouragement. :-)
> > -----------------------------------------------------------------
> > I have run into many problems with large databases and IC. I HIGHLY
> > recommend the MySQLman application from the below website. Depending on
> > your system it may be tricky to setup but it will allow you to have an
> > excellent tool for managing these larger databases. I also use WebMin in
> > both site management and in DB management. Good Luck!
>
> It is easy to shoot yourself in the foot with any application and a large
> database. I know, I have done it many times. Interchange's admin UI is
> optimized for DBs that are 50,000 records or less. If you have that many
> records, you need SQL to use the IC admin tool with any success. Even
> then, you have to make sure that proper indexing is done on all fields
> that are selected/sorted on.
>
> I would be very interested to hear of success stories with other shop/cart
> builders and very large databases. I haven't heard of many; in fact,
> more than a few Interchange users are refugees from some commercial
> application that hit the wall at 20,000 products.
>
> The Interchange application itself will not repeal the laws of physics,
> but if used in a reasonable manner it is capable of working with very
> large database tables. There are sites which have as many as a million
> items, and at least one that has 500,000 and uses GDBM. They don't try and
> run the item editor on that, though! And they use external import/export
> programs and don't try to do auto-import and export.
>
> --
> Akopia, Inc., 131 Willow Lane, Floor 2, Oxford, OH 45056
> phone +1.513.523.7621 fax 7501 <heins@akopia.com>
>
> Research is what I'm doing when I don't know what I'm doing.
> -- Wernher Von Braun
>
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

--
We specialize in multi-processor computing systems!
John Foster
AdVance-Computing Systems

Barney Treadway wrote:
>
> Greetings John,
>
> You mention "many problems" with large databases. Did you find a workaround
> or did you go elsewhere for a mysql driven cart?
-------------------------------------------------------
ABSOLUTELY NOT! I use IC EXCLUSIVELY.. I just had a lot of learning to
do and someone (thanks Pugdog) recommended the use of these specialized
MySQL management tools for databases of the size I use. (268,000 items
with 23 fields in the products.txt table with a huge 30Mgb, searchable,
secondary catalog of reference info that corresponds to the items)

> I use Webmin and phpmyadmin currently and couldn't live without either!
> Mysqlman looks about identical to phpmyadmin.
------------------------------------------------------
I looked at phpadmin when it was an infant and rejected it for several
reasons, the main one being that it did not suit my personal tasts.
These others meet my current needs.

Mike and crew...IC is a great product & I expect huge successes for you
this year.

--
We specialize in multi-processor computing systems!
John Foster
AdVance-Computing Systems