Database processes slowing server down

-- HikaShop version -- : 2.3.4
-- Joomla version -- : 3.2.4
-- PHP version -- : 5.3.3
-- Browser(s) name and version -- : Firefox
-- Error-message(debug-mod must be tuned on) -- : 504 Gateway Time-out
nginx

Hi
I keep getting 504 Gateway Time out errors while working on the site and this was the response I got from my Hosting Service Provider

There is currently a large amount of disk usage on the server being consumed by the MySQL service. There are currently
some huge processes trying to go through;

| 4238614 | xi_user | localhost | xi_db | Query | 686 | Sending data | INSERT IGNORE INTO xi_hikashop_product
(product_code, product_parent_id, product_created, product_modified, product_hit, product_last_seen_date, product_name,
product_description, product_quantity, product_published, product_sale_start, product_sale_end, product_delay_id,
product_tax_id, product_type, product_vendor_id, product_manufacturer_id, product_url, product_weight, product_keywords,
product_weight_unit, product_meta_description, product_dimension_unit, product_width, product_length, product_height,
product_max_per_order, product_access, product_group_after_purchase, product_min_per_order, product_contact,
product_sales, product_waitlist, product_layout, product_average_score, product_total_vote, product_page_title,
product_alias, product_price_percentage, product_canonical, product_msrp, product_display_quantity_field,
product_warehouse_id, product_quantity_layout, occupation) SELECT CONCAT(p.product_code, '_', c0.characteristic_id) AS
c_product_code, p.product_id, 1418829594, 1418829594, 0, 0, product_name, product_description, product_quantity,
product_published, product_sale_start, product_sale_end, product_delay_id, product_tax_id, product_type,
product_vendor_id, product_manufacturer_id, product_url, product_weight, product_keywords, product_weight_unit,
product_meta_description, product_dimension_unit, product_width, product_length, product_height, product_max_per_order,
product_access, product_group_after_purchase, product_min_per_order, product_contact, product_sales, product_waitlist,
product_layout, product_average_score, product_total_vote, product_page_title, product_alias, product_price_percentage,
product_canonical, product_msrp, product_display_quantity_field, product_warehouse_id, product_quantity_layout,
occupation FROM xi_hikashop_product AS p, xi_hikashop_characteristic AS c0 WHERE p.product_id IN
(928,927,926,10769,10770,10771,10772,10773,10774,10775,10776,10777,10778,10779,10780,10781,10782,10783,10784

I have cut this process short, as it is quite lengthy, but I hope this gives you some idea. As the VM you have only has
2G of ram, it will struggle to perform these tasks and will slow down and cause scripts to take longer to complete, ie
the exceeding of 90 seconds and causing the server to error.

I would advise to take a look at these processes and see if they can be optimised.

The process I was trying to acheive was to move a product into a different category and add it to a couple more. Not something I thought was out of the ordinary or used a vast amount of resources to process.

Are the time out errors being generated by hikashop or could there be another reason?

Thanks Jerome
I cannot find the product with id "928" in the backend - the nearest product id to it (923) had a charactersitic with "no values found" (see attachment).
Am just wondering as the site has been updated from Joomla 2.5 to Joomla 3 and the Hikashop has also been updated whether the database has not been populated correctly with the characteristics.
It has got to the point where the site is becoming unusable whenever I navigate to Hikashop in the backend.

Hi Jerome
I have looked in the database and there are 183,145 rows in the variant table. I'm presuming that this is the problem and why the server is timing out.
There are also 339 characteristics which the client has created so maybe I will need to educate him on how to do it properly!
Thanks for your help with this and for helping pinpoint the problem!

Hi Jerome

Just another problem related to the above.

The variants are not being generated automatically now so the site is operational however due to the fact that they were being generated automatically the size of the database has grown substantially (it is now 400MB with 2million plus products!!!!)

Was just wondering if there was an easy way to filter out the variants from the database and leave the master products and characteristics intact??

Hi

I am experiencing the exact same thing for a friend
He has 4 products and 2 characteristics (with 4 & 11 options). Only one characteristic is used pr product.

That gives him 147.000 rows in #__hikashop_variant and 414.000 rows in #__hikashop_products.
His shop is not live yet and he is experimenting with the characteristics, but this is with default settings.

As far as I can see, HS doesn't delete old variants when deleting them? I'd call that a bug.
HS needs some way to deal or enlighten us. Maybe the "check db function" can clean up/verify the integrity of the tables (or at least those tables)?

Edit: Forgot to say this is with HS Business version 2.3.5

Hi again

Sorry for being unclear. This should replicate it:
1. Create a set of characteristics.
2. Then attach it to a product from within the edit product page -> save
3. Change some characteristics from within the edit product page -> save
4. Go in to edit product page and delete the set -> save

Keep an eye on the db - The entries are still in #__hs_products as variants and still got the mapping...
We did quite some testing with that feature, but it blows my mind that it could generate THAT many entries that it did. (simple math says it should've been attached 39.000 times so I guess it's twofold; A bug in the generation of variants somewhere AND variants not being deleted).