Performance tuning tips

Hi All

I am preparing a OC (1.4.8) site that will have around 3-4000 categories and around 12 million products... just wondering if anyone has a similarly sized site and what (if any) performance issues were encountered...?

FWIW I have already some issues with the categories (which are quite heavily nested) build/display on the catalog index page and I needed to add an index on category.parent_id to get it to perform...

So anyone running a similar sized OC site that has any tips?

TIA
rich

I think that will be by far the largest opencart install, so you will be very much in uncharted territory. But some performance issues that I know of. The catalog dropdown from the search causes problems sometimes. There are several tables that need to have indexes added to product_id (these are going to be added in 1.4.9). Beyond that who knows what you will run into with that large of a database. Just out of curiosity how are you going to get that many products into the store, and what in the world are you selling with that many products? I think amazon only has about 2 million products.

Whas there not a mod/topic somewhere that addressed the same question/issue?

This Is what I've found:
Caching of categories:

in catalog/controller/common/header.php
Find: Replace with:
in catalog/controller/product/search.php

Find: Replace with: After this you will find in system/cache/ a file called cache.categories.0

Xsecrets wrote:Just out of curiosity how are you going to get that many products into the store, and what in the world are you selling with that many products? I think amazon only has about 2 million products.

I was about to ask - are you Amazon, rich06? : ) I guess you won't be typing all the products in via the admin panel.
Good luck, and tell us how it goes.

lol that many products will take days to insert even automated

Too right! I feel like a yokel chewing a straw, but this has really piqued my curiosity. 12 million products..

Thanks for the tips everyone..! The site will be an online bookstore and the client has subscribed to a data feed service that catalogs every book title published in the US and UK -- I have prepared a multi threaded database load script when the supplier provided some sample data but the real data will be delivered to the client next week sometime so that will be the first time I will use my script in anger ... gulp!

The data suppliers advised me below...

>>The counts for last month’s load were as follows. You can expect a slight increase.

>>US=6,518,319
>>UK=4,372,571

Incredible numbers which I too find hard to believe but there you go...
Will keep you posted, but any other tips - apart from run away...! - will be gratefully received.

Rich

Just an update on this OC install if anyone's interested...

Once my data load process had finished the product/product_description tables have 7.1m records (a few million less than originally anticipated thankfully).

Naturally the code/database hit many brick walls with this volume of data and I have resolved many by adding indexes and denormalising where necessary and changing the database design with associated code changes.. still have loads to do I will need to integrate the OS Sphinx search engine (http://sphinxsearch.com/) for keyword searches as MySQL fulltext indexes can't really cope ...

Just want to say that OC code is very well organised and very easy to understand how it hangs together which has made my task a damned sight easier.

Fun times
Cheers
Rich

Hi Rich
Are there any news/updates about your performance optimizing stuff? Is it possible to see how you integrated sphinx (or available as extension?). Just curious.

bye
Marcel

Opencart is multi-store. This could be thought of as mulit-department which all come under one store with different templates for each (eg different colour for each department) with everything managed by a single admin.

It is an extra level of hierachy that I think could really help a very big shop with both product organisation and performance.

lotek wrote:Hi Rich
Are there any news/updates about your performance optimizing stuff? Is it possible to see how you integrated sphinx (or available as extension?). Just curious.

bye
Marcel

Hi Marcel

Well I downloaded and compiled Sphinx v0.9.9 http://sphinxsearch.com/downloads/release/ and I also installed the sphinx client PECL extension http://pecl.php.net/package/sphinx. Then it was a case of looking at what search options were to be made available to the site user -- as my site was an online book store the search options were

Author name
Title
Multiple ISBN numbers
Publisher
Categories

Then it is a question of defining the SQL queries to obtain the required results and putting them in the sphinx configuration file so it can build the indexes based on the queries. Below is an example:-
source getProductsByPublisher
{
type = mysql
sql_host = xxxxx
sql_user = xxxxx
sql_pass = xxxxx
sql_db = xxxxx
sql_port = 3306 # optional, default is 3306
sql_sock = /tmp/mysql.sock

sql_query = \
SELECT p.product_id, m.name \
FROM product AS p \
INNER JOIN manufacturer AS m ON (m.manufacturer_id = p.manufacturer_id)

sql_query_info = \
SELECT p.product_id, m.name \
FROM product AS p \
INNER JOIN manufacturer AS m ON (m.manufacturer_id = p.manufacturer_id) \
WHERE p.product_id = $id
}

In my case as the product data is loaded by a batch process I had to rebuild the sphinx indexes after each load process using the sphinx tool as follows --> <installdir>/bin/indexer --all. Also the sphinx listener daemon (searchd) needs to be running which passes data between Opencart and the sphinx engine however it must be shutdown when the re-indexing operation is running.

Next the Opencart product model code needs to be altered to use Sphinx rather than querying the MySQL database directly -- below is an example:-

$sphinx = new SphinxClient; // Instantiate PECL client
$sphinx->setServer('hostname',9999); // Hostname and port number that searchd is listening on

$mode = (strpos($keyword,'"') !== false ? SPH_MATCH_PHRASE : SPH_MATCH_ANY); // See if a phrase is being searched for

$sphinx->setMatchMode($mode);
$sphinx->setMaxQueryTime(100);
$sphinx->setLimits(0,2000,0,0);

// Get results from sphinx
$results = $sphinx->query($keyword,'getProductsByTitle');

$results['matches'] will contain an array of product ID values of all the matching products so when the Opencart query is issued the query's 'WHERE' clause should have added 'AND product.product_id IN (999,999,999) where 999 is the imploded list of IDs returned from Sphinx. There is also a command line tool (bin/search) supplied with Sphinx to test search queries.

In my case the products table is around 8.5 million rows now and sphinx performs really well so it is definitely worth using if you have a large product table.

Hope the above is of interest!
Cheers
Rich

Amazing!!

rich06 wrote:
In my case the products table is around 8.5 million rows now and sphinx performs really well so it is definitely worth using if you have a large product table.

Make that 12.5 million rows now....

Rich

rich06 wrote:

rich06 wrote:
In my case the products table is around 8.5 million rows now and sphinx performs really well so it is definitely worth using if you have a large product table.

Make that 12.5 million rows now....

Rich

Not a word I use frequently, but that is awesome.

As a side note - I'd love to see Sphinx integrated as part of Opencart some time, or maybe as an extension.

Maybe an extension, but you can't integrate something they you are guaranteed almost no host will ever have installed by default into the core.

Xsecrets wrote:Maybe an extension, but you can't integrate something they you are guaranteed almost no host will ever have installed by default into the core.

Ohh, point taken. So for technical reasons it's not a runner (as we say over here). I guess I've only read about what it can do, not how it's installed.

Am I the only one interested in actually seeing the site? URL please!!

Hi Rich,

Really interesting site...plz share the URL.

Regards,
Vivek

There is Sphinx Search module for Opencart FYI

http://www.opencart.com/index.php?route ... n_id=11734