JM Products is very slow

I have found that after enabling "JM Products" module in order to show on the tabs like in the demo, the mySQL server is running at 100% for long periods of time (40-50+ seconds) or crashes all together.

Magento 1.7.0.2 (Bitnami VMWare image)
Installed all JL Lingerie modules by hand
Created all CMS static blocks and pages
2GB RAM, 2 CPU cores, Intel Core i7 2.3GHz
55,337 products
2,152 configurable mapped to
49,555 simple products
SMD Color Design swatches module (on 2,152 products)
memcached
Use Flat Catalog Category and Use Flat Catalog Product enabled with fresh indexes

Some of the suggestions from phpMyAdmin > Status > Advisor >

"There are lots of rows being sorted."
"The rate of reading the first index entry is high."

When watching with the monitor I can see about 4,000 queries per second when I load a page with the JM Products module enabled.

Load time with the module and Magento caches disabled is about 4 seconds.

Load time with the module only being referenced for one item in the menu enabled and with Magento caches disabled is about 50 seconds, Magento caches enabled is 42 seconds.

It’s kind of a big thing.

I was wondering if there was a way to limit the number of results or a more efficient query for gathering the product data from the database. If the process takes a few seconds, but could be cached, it wouldn’t be that big of a deal as the product data doesn’t change too often. But 50 seconds per page when only one item is being returned at the end of it all is a bit too long.

I’m decent with mySQL and PHP, however I don’t know much about how Magento works and haven’t looked into the code supplied with the module yet.

Sorry if this is a repost, but I couldn’t find anyone else with this issue.

19 answers

Profile photo of Sherlock 0.00 $tone July 26, 2013
Public

Hi mate,

i see that you having a big number of products, to avoid the mentioned issue I think you can create a special category for Jm product, assign those products that you want to load to that category and set this category id in the back-end of Jm product setting.
I think doing this would help

#1
Profile photo of Marvin Fife 0.00 $tone July 26, 2013
Public

It was slow even when I had assigned a category like you suggested when I was investigating the issue. It was late and I decided to disable the module. I will give it another try as a work around and then report back my findings.

If you know which file would need to be edited for implementing a result limit I’d be happy to investigate coding it myself. There are variables in the admin section for the number of items dosplayed so those could be repurposed for the limiter, I think.

#2
Profile photo of Sherlock 0.00 $tone July 29, 2013
Public

Hi mate,

In the back-end of this extension it has a setting field name "# Qty of products" that is for limit number of products loaded, Did you try to enter a number to this field ?
I think it should help !

#4
Profile photo of Marvin Fife 0.00 $tone July 29, 2013
Public

Yes. It’s set to 10 by default and I’ve even tried it at 1 for the fun of it. There is no change.

I really wish it did help. I’m thinking that variable should be tied to a limit on the SQL query. If that were the case, it would be extremely fast.

Are the files I should be looking at [magento_base]/app/code/local/JoomlArt/JmProducts/Block/Filter.php and List.php ?

-Marvin

#5
Profile photo of Sherlock 0.00 $tone July 30, 2013
Public

Hi mate,

if you set it already I think the query has been tied to a limit value, that’s strange, yeah you can check the query at those files of [magento_base]/app/code/local/JoomlArt/JmProducts/Block/Filter.php and List.php

#6
Profile photo of Marvin Fife 0.00 $tone July 30, 2013
Public

I looked through the code for the qty mapping (someone spelled ‘quantity’ as ‘quanlity’ but it’s misspelled everywhere, even on the admin form variable name, so that’s not the issue). I don’t know enough about the magento classes in use and did my best to read and re-read the limit. It looks like it should work, but it doesn’t. The references to where the qty is added to the magento block/product model looks correct. There’s a limit=NULL in the input overwriting what would be sent by calling it and there’s an if then to put the qty in there, but I don’t know about that style of php programming or if it’s even written properly for the magento block. I just don’t know enough about that class.

I even tried a fresh Ubuntu install with the quick start. It worked fine, until I added in my clothing line of approx 2,700 individual items. This resulted in the same 5 second home page loading as before when I hand installed and enabled JM_products. Demo page loaded in about a second and a half.

I even went so far as to enable memcached, APC, and varnish with the accompanying magento plugin for it. In all cases, the page loading times decreased, but only when JM_Products was disabled. If its enabled, it is never able to finish loading the data, even when the PHP script timeout is set to 300 seconds (5minutes).

#7
Profile photo of Marvin Fife 0.00 $tone July 30, 2013
Public

Would it be beneficial if I gave you a copy of a database with 55,000 products in it for testing so you can see I’m not completely out of my mind?

#8
Profile photo of Sherlock 0.00 $tone July 31, 2013
Public

Would it be beneficial if I gave you a copy of a database with 55,000 products in it for testing so you can see I’m not completely out of my mind?

I have tried to print out the SQL Query and i see the limit variable is there, not sure what would be the cause πŸ™

#9
Profile photo of Serhiy Korohoda 0.00 $tone August 1, 2013
Public

Hi Marvin,

Have you managed to find out what is causing slow pull of products from database yet?

I have the same issue although my store has no more then 100 items.

When i disable the JM Product, my store loads 2-3 seconds faster then with jm products module enabled.

Looking forward to some resolution and your help.

with thanks,

#10
Profile photo of Marvin Fife 0.00 $tone August 1, 2013
Public

2-3 seconds at 100 products sounds like you’re just running on some slower hardware. Magento is a resource hog in and of itself. The fact that the "->setPageSize($perPage);" part of the code doesn’t help the situation.

If you enable the caches and setup something like APC (memcahce doesn’t help in single server Magento installs from my experience while dealing with JmProducts) or, if you can, the "PageCache powered by Varnish" made by Phoenix Media, that’s really all you would need with your 100 products. The PageCache took my page loads from ~5 sec down to >900ms when I do not have JmProducts enabled. It never loads properly if JmProducts is enabled.

I’ve done some mySQL monitoring and the thing it’s doing is a JOIN over and over again on ‘e’. It’s not a difficult thing to do, but when you have to do it for 55,000 products, for 3 tabs, and each tab has 10 products, even at ~4,000 queries per second it ends up taking a while to complete… Close to 400 seconds just to do a single JOIN for each product in the catalog, even with ‘mode’, ‘category’, and perPage limits (listed as ‘# Qty of Products’ in the Admin section).

I’ve spent way too much time tracking down the issue here and it’s really frustrating. I REALLY, REALLY like the theme, but since it relies on a ‘broken’ module, it’s essentially usable for my company at this time. And considering there are another 30,000 additional products to be added soon, it’s even more disheartening. (Magento treats options as different products, we sell wigs that come in multiple colors (2,500 wigs (configurable), 50,000 possible wig model/colors combos(simple)), clothing (1,200 items (config), 2,500 size/color combos (simple)). Next up is shoes which each style has a color and a size.

The module extends the core module that Magento uses to display items when you are looking at a category. When I look at a category it is nice and speedy because the perPage variable is being sent correctly and only x of y items are looked up at a time, not all of them.

#11
Profile photo of Serhiy Korohoda 0.00 $tone August 1, 2013
Public

Hi Marvin,

thank you for your time and recommendation.

I already have APC with fcgi php handler on my server. The site will load faster when i enable gzip, compiler and css/java compression.But your point about PageCache is good although i could never get it working on my server Installation went well but it never made any difference at all. I may think of a full page cache option.

It is strange as i have used other Joomlart Magento templates with JM products and those work just fine.

Marvin, by the way, could you recommend a good speed testing site where i can check the speed? I currently use http://gtmetrix.com/reports/ but is it accurate? You mention 2-3 seconds is very slow but i have my own server and it is quite powerful (other sites fly on it). So my question is whether it is really down to this particular theme…

#12
Profile photo of Marvin Fife 0.00 $tone August 1, 2013
Public

"PageCache powered by Varnish" made by Phoenix Media is a full page cache mechanism and is available through Magento Connect http://www.magentocommerce.com/magen…y-varnish.html (hopefully linking to products that joomlart doesn’t make nor makes the kind of plug in is OK) is what I used. I’m running on Ubuntu and installation of Varnish was as simple as following the steps on https://www.varnish-cache.org/installation/ubuntu (I don’t work for anyone, I just like helping out when I can).

I noticed that in the copy of JmProducts there were some mis-spelled vars, but like I mentioned before, they were misspelled everywhere. I am wondering if maybe there’s just an issue with the version being distributed with JmLingerie since you say you’ve used it before in other templates.

Maybe you could replace the JmProducts in your Lingerie theme for a copy from another one of your templates to see if this is true.

I don’t blame anyone for not catching the speed issue as the demo only has a few products and I would not expect them to test against a database of 55,000 products.

As far as speed testing, I do all my development on a VBox VM on my MacBook Pro Retina that has a solid state HD so my times are coming from when I open the network portion of FireBug in Firefox. When I test my live site, which is an Amazon EC2 instance, I use http://www.webpagetest.org so I can test it from multiple locations.

#13
Profile photo of Serhiy Korohoda 0.00 $tone August 1, 2013
Public

This is so nice of you, Marvin, i really appreciate your help. I shall try the Varnish cache again.

In the meantime, i will use JM Products module from other template with JM Lingerie theme and report my findings.

Thank you once again!

#14
Profile photo of Sherlock 0.00 $tone August 2, 2013
Public

Hi thedailyspank,

you can try to print out the SQL Query that generated by the jmproduct to see if the limit is there, if you can pm me your url, admin account as well as FTP info i will check it further for you

#15
Profile photo of Marvin Fife 0.00 $tone August 2, 2013
Public

I am investigating the SMD Color Design color swatch as being a possible issue.

I imported all of the simple products and it appears to be working normally. Next step will be importing the configurable products. If that still works, I’ll be setting up the color swatch to see what happens.

#16
Profile photo of Marvin Fife 0.00 $tone August 3, 2013
Public

I was able to import my large collection of wigs with simple and configurable products (2,500 configurabel, 50,000 simple) and JmProducts works as expected.

I believe the color swatch plug-in to the source of my trouble. The exact reason for this, I still don’t know.

I did see some code of interest to me, if someone could show me where exactly to put it, would be nice (maybe in the filter.php or list.php files?).

Code:

$collectionSimple = Mage::getResourceModel('catalog/product_collection')
                ->addAttributeToFilter('type_id', array('eq' => 'simple'));

This code will only show simple product types, which is fine as a work around for this issue as the color swatch plugin is basically a requirement.

#17
Profile photo of Sherlock 0.00 $tone August 5, 2013
Public

Hi mate,

you can open the file of app\code\local\JoomlArt\JmProducts\Block\list.php there around line number 350 you look for this code

PHP Code:

 $products $resource->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
                        ->
addMinimalPrice()
                        ->
addFinalPrice()
                        ->
addTaxPercents()
                        ->
setStoreId($storeId)
                        ->
addStoreFilter($storeId)
                        ->
setOrder($fieldorder$order);





change it to

PHP Code:

 $products $resource->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
                        ->
addMinimalPrice()
                        ->
addFinalPrice()
                        ->
addTaxPercents()
                        ->
setStoreId($storeId)
                        ->
addStoreFilter($storeId)
                        ->
setOrder($fieldorder$order)
                                                ->
addAttributeToFilter('type_id', array('eq' => 'simple'));





I hope this change help somehow

#18
Profile photo of Marvin Fife 0.00 $tone August 6, 2013
Public

I will give this a shot and let you know how it goes. Thank you for all of your help. I realize it’s a variable you couldn’t of accounted for, so no worries. πŸ™‚

#19

This question is now closed

Written By

Comments