HikaShop + MySql Performance

-- url of the page with the problem -- : drc144k.com/
-- HikaShop version -- : 2.Latest
-- Joomla version -- : 2.5.Latest
-- PHP version -- : 5.4.Latest
-- Browser(s) name and version -- : Latest versions of Chrome, Safari, & FireFox.
-- Error-message(debug-mod must be tuned on) -- : NONE. Just extremely slow.


Our MySql DB is up to +1GB in size. The Product and File tables each have about 1.8 million rows and the Variant table has 3.5 million rows. It's taking 30 seconds or more to serve 1 product. If more users hit the site at once, they may see a product with the additional Hika modules (random products, latest products, best sellers) blank or a completely blank page with no errors for PHP (error level set to max) or MySql in the logs.

Before debugging and trying tune MySql, do you have any recommendations for tuning the Hika tables? I see they are MyISam while Joomla tables are InnoDB. I think the slowdown is from the Hika side of things and not Joomla. If you have any recommendations, please let us know.

Server Specs for test & production: 8GB Ram, 8 processors, Intel Xeon 2.5Ghz. 600GB SATA Hard Drive

Thanks.

Yikes!! Joomla 3?

So can we install Joomla 3, install HikaShop on Joomla 3, and then import our tables for HikaShop from Joomla 2.5?

Or do we need to do a CSV import of our products from J25+Hika? And redo all our settings from J25+Hika in Joomla 3? Hopefully not, the CSV import is taking 12 hours to complete right now.

We did some mysql tuning using the my-huge example shipped with mysql. Performance is much better so far but still pretty slow with only 5 people hitting it at the same time.

When going to Joomla 3, do the tables still remain MyIsam for Hika?

We turn on debug and see the following for a disabled module:
Application 0.130 seconds (+0.059); 20.77 MB (+9.351) - beforeRenderModule mod_hikashop (Associated products listing for category listing menu 219)
Application 5.349 seconds (+5.219); 26.19 MB (+5.421) - afterRenderModule mod_hikashop (Associated products listing for category listing menu 219)

This module sometimes takes up to 50 seconds to run! Why does the module try to render although it's disabled? Is this a "secret" or "hidden" module that must run under the covers?

Thanks.

Thanks for the clarification. Makes sense and I did finally see it in your docs. And it needs to be unpublished or it will show up twice on the site. Good stuff.


Now, is there any way to speed this up for sites with lots of categories? We want to display links only (adding images to the categories listing makes loading even slower) and we only show the direct subelements. We purchased another development box with 16GB RAM and 8 3GHz+ processors ... still takes over 20 seconds for HikaShop pages to load. Normal joomla pages load in less than 2 seconds.

Thanks.

I attached the 3 queries which the slow query log has the most. Point us to where they come from and we will see if there is any optimization we can do to speed them up.

Thanks.

Ah, I did not hit the Insert button. Hopefully it attaches this time.

Thanks.

Looks like it still failed to attach!!