Huge Hikashop site / Incredibly slow

Hello to the HikaShop team.

I have created a website using your component and uploaded 151.000 products.
Each product has 3 options, totaling a number or 18 variants per product.

That rounds up to a teoretical 3 million products.

The DB has reached 1.4 GB, even if the uploaded csv lists total around 300-500MB.

The images are about 30GB in size (total), there are 2 images per product.

I believe that my website could also benefit you as a case study - see what happens when HikaShop is taken to the limit.

I've encountered a number of problems on my way to building this shop.
The biggest problem was creating the csv lists. Because the product is on 1 row and the variants have to be under that row as consecutive rows it was incredibly difficult to create that in an excel file.

For example, my website deals in paintings. The options deal with 1) type of product Painting/Poster 2) Size. There are 6 different possible sizes for a painting but not all of the paintings can be printed at all of those sizes. But because I could not make the csv otherwise i had to insert all the size variants for all the paintings and unpublish the not-necessary ones. Probably instead of 3 million variants I would've had half of that.

Another problem is that even if I specified the order for each product in each category in the CSV file, that didn't apply - it still has the order that was auto assigned.

Another problem is with the keywords - how do you sepparate them? If i sepparate them using comma when I import the csv and then export it again - only the first keyword is exported.

But the biggest problem of the moment is the incredibly slow speed the site is working on now.

The website is hosted on a virtual ubuntu, with 4 cores (8 threads) available and 2GB of ram in an HP server that's empty. The server is an HP blade - Intel Xeon E5620mhz with scsi raid 5.

I have about 30-40 categories with total of 151.000 individual products.

If i click on a category that has around 2000-5000 products inside, it takes between 10-30seconds to display the first 36 products.
If i click on a category that has around 40.000 products inside, it takes over 1-2 minutes.

If i click on a category that i've clicked previously it loads up ok - which means the cache is working, but even in that category, if I click on the next page of products (same category) it takes again the same amount of time.

Also if I click on a product it takes an age to load up.

I don't want to publish here publicly the address of the hosting server so please ask of me and I'll give you complete controll of the server.

I really need your help on this - I've got no options left, i managed to get around other shortcomings but I'm stuck now.

Thank you,

Lex.

Hi Lex,

About the speed problem, could you activate the debug mode and send us the report at the end of the page. There should be the time taken by the SQL query we'll be able to see what's taking so. But it sounds quite strange that even loading a product page is slow. You can PM me a link to your website if you want so we'll take a look by ourselves.

About the ordering, it depends of the category. It's the "category_ordering" column and I just tried it seems it working on my end.
About the keyword you indeed have to use comma but also a space after the comma. Like "one, two". Same thing here I just tried and it works.

By the way, do you have the last version?

Yes, it's the latest version. I gave you access by PM for everything you need.
By the way, on the main page of the website, scroll down and there's a menu on the left side with 40 different categories. The first ones will load fast - they are in the cache. Try the lower ones in the list - you'll see the problem.

I forgot to mention a problem - it might interest you.

The first import file I used had around 300-400 thousand records. After about 10 minutes it crashed and i kept making the file smaller and smaller until i saw that importing 2000 products with 18 variants worked.

So 36.000 products + variants is probably close to the maximum number the import can handle. I had to break my csv files in about 100 files to be able to import.

In the beginning it took around 5 minutes for each file to import. After about the 10'th, 15'th import file it took 15-18 minutes per import. Because mysql uses (i believe) 1 query per 1 thread, i disabled hyperthreading in my BIOS so 1 query would use one full core of the processor. That reduced the time back to about 13 minutes when it reched 20minutes/file.

Than I had an idea - start importing 2 files at once - I opened up to 2 administrator pages and started importing one file each - but it took twice as long - so no luck there.

I thought long and hard about it, but because I imported previously just the products and now i was importing the variants I realised (or hope so) that the DB wouldn't be scrambled.


Another problem is that my pictures all end in domain-name.xx.jpg (where xx is my domain) in the hikashop backend all pictures end in .xx no .xx.jpg - which is, i think, a bit weird. But the pictures work - they show up.

Another problem is that if I specify a certain image for one variant, then the rest of the variants MUST have specified images, otherwise it will show the hikashop default no-image. I have 18 variants plus main product. But i only use 2 pictures. Product + 6 variants use 1 image, 12 variants use the second one. So i had to mention, practicaly 17 times the path to a picture which was already specified to other 2 variants. 17 paths times 151.000 products = 2.6 million paths specified. This little detail probably ocuppies a lot of space in the DB - I don't know if this could be made otherwise but it's worth considering.

Another problem - this could be architectuarly-wise - It probably would be a good idea to be able to specify the default price for variants somwehere. I only have about 50 different prices in total - but i have almost 3 million prices in the DB because I had to specify a price for each variant for each product. Even if it's the same variant for all the products. This problem applies also to the name of the variant.

The way it is made now it's very user friendly to do anything, the learning curve it's steep but as i proved i could import many many products there should also be easy and effective ways of importing many many products.

lex.

Hello Nicolas,

Thank you very very much for your time and for the success .

A few things:

1) Can you tell me if the ordering is now done by the product_code ? that's what i'm seeing in the administrator side.

2) When i'll update hikashop, as new versions appear, and when i'll update from standard to business or other versions, will I lose any of the modifications you just did? Should I save myself some files, that you modified, sepparately?

3) Should I add indexes to all the tables everywhere? Will that do anything?

4) Also, I replied to a topic before and you required access to the site and I never gave it - www.hikashop.com/en/forum/3-bug-report/7...st-update.html#89649 - it's the issue with the variant name displayed after the product name in front-end even if I disabled it in the configuration. Can you please help me with that too?

Thank you!!!

Lex.

5) also, under each picture this error appears on the product page:

Notice: Undefined property: hikashopImageHelper::$image in /var/www/lumiere/administrator/components/com_hikashop/helpers/image.php on line 234 Notice: Trying to get property of non-object in /var/www/lumiere/administrator/components/com_hikashop/helpers/image.php on line 234

I also have the site locally and the error doesn't appear - I don't know if it's something you did or it's because of the server - but the virtual ubuntu is simply a clone, no differences...

Thank you, thank you, thank you!!!!!

It looks incredible now