Is layered block always slow? Even on VPS / dedicated?

[Mister Denial]

Hey everyone,

 

I noticed that even on a rather performance strong VPS, block layered considerably slows down the category pages.

 

The main page on the default bootstrap theme loads in under 2 seconds, with 49 objects, almost 1MB data, and with a TTFB of about 250-300ms. Quite fast.

 

But on a category page with 15 items (+8 from the new products block), TTFB jumps up to 1500-2000ms when layered block module is enabled. As soon as I disable, it goes down to 500-700ms. Debug mode tells me my server should better be a toaster. How embarrassing!

 

So considering all of this, I am wondering, is it at all possible to get performance out of a page that uses the layered block module?

 

Or is THAT the precise reason so many people don't use that module?

 

What's your opinion on this?

 

Dan

[benjamin utterback]

Hey everyone,

 

I noticed that even on a rather performance strong VPS, block layered considerably slows down the category pages.

 

The main page on the default bootstrap theme loads in under 2 seconds, with 49 objects, almost 1MB data, and with a TTFB of about 250-300ms. Quite fast.

 

But on a category page with 15 items (+8 from the new products block), TTFB jumps up to 1500-2000ms when layered block module is enabled. As soon as I disable, it goes down to 500-700ms. Debug mode tells me my server should better be a toaster. How embarrassing!

 

So considering all of this, I am wondering, is it at all possible to get performance out of a page that uses the layered block module?

 

Or is THAT the precise reason so many people don't use that module?

 

What's your opinion on this?

 

Dan

 

Hi, well the layered navigation module can take up more resources, especially if there are many products and especially categories. How many do you have? Do you have a link to the store? What version are you using?

[Mister Denial]

Hi, well the layered navigation module can take up more resources, especially if there are many products and especially categories. How many do you have? Do you have a link to the store? What version are you using?

 

Hi Benjamin,

 

the reason I was asking this question is because I noticed that in almost all performance threads, layered navigation is an issue, and also that many of the performance pros don't use the module.

 

Myself, I am using 1.6.0.8 with all modules up-to-date. I have about 200 products in the respective categories, and am on a VPS with over 2Gb dedicated memory and more than enough CPU to go around. Most of the time, I use only 10-15% of available CPU / RAM. I also use PHP 5.4

 

The thing is, I think that globally 1.6 is much faster than my previous shop version (upgraded from 1.4.9 last week), considering the amount of data loaded on the index page, I am really impressed. But the category pages, as already said, TTFB really jumps up, and depending on traffic, TTFB goes up to 1500-2000ms. Without the module enabled, immediate TTFB drop down to 500-700ms.

 

I have made a lot of tests today with both Gtmetrix, Pingdom and Webpagetest, always the same result. TTFB goes up by a factor of x2 or even x3 as soon as the module is enabled. I contacted my host to check if there's an error on the server, or if they can see anything unusual, they couldn't.

 

I will try upgrading to 1.6.0.9 tomorrow, see if that changes anything.

 

I really prefer the layered navigation over the classic categories, it looks more "pro" and it offers the customer a better experience, but unfortunately pageload is a major factor too, so it's a difficult decision.

 

In case you want to have a look, my website is here: http://www.the-black-angel.com

You'll see, first page fast, categories, not so much.

 

That being said, my compliments to the entire Prestashop team, the new version is really quite awesome, I love most of it.

[benjamin utterback]

Hi Benjamin,

 

the reason I was asking this question is because I noticed that in almost all performance threads, layered navigation is an issue, and also that many of the performance pros don't use the module.

 

Myself, I am using 1.6.0.8 with all modules up-to-date. I have about 200 products in the respective categories, and am on a VPS with over 2Gb dedicated memory and more than enough CPU to go around. Most of the time, I use only 10-15% of available CPU / RAM. I also use PHP 5.4

 

The thing is, I think that globally 1.6 is much faster than my previous shop version (upgraded from 1.4.9 last week), considering the amount of data loaded on the index page, I am really impressed. But the category pages, as already said, TTFB really jumps up, and depending on traffic, TTFB goes up to 1500-2000ms. Without the module enabled, immediate TTFB drop down to 500-700ms.

 

I have made a lot of tests today with both Gtmetrix, Pingdom and Webpagetest, always the same result. TTFB goes up by a factor of x2 or even x3 as soon as the module is enabled. I contacted my host to check if there's an error on the server, or if they can see anything unusual, they couldn't.

 

I will try upgrading to 1.6.0.9 tomorrow, see if that changes anything.

 

I really prefer the layered navigation over the classic categories, it looks more "pro" and it offers the customer a better experience, but unfortunately pageload is a major factor too, so it's a difficult decision.

 

In case you want to have a look, my website is here: http://www.the-black-angel.com

You'll see, first page fast, categories, not so much.

 

That being said, my compliments to the entire Prestashop team, the new version is really quite awesome, I love most of it.

 

Hey, I tested your website. I like it. Aside from that, I personally had no issues with speed, on any page.

 

It's taking 1-1.5 seconds to load and I am on a regular cable connection in the United States. Maybe somebody else can test it and let Mister Denial know your feedback.

 

For me, as a hypothetical visitor to your store, I had no issues with speed whatsoever. 

[dioniz]

I tested from southern europe and it works very fast for me.

[Mister Denial]

Thanks to the both of you for your feedback on the website, I am relieved to here it loads fast. Maybe I am too focused on the whole speed thing. Sometimes good has to be enough rather than chasing perfect.

 

Still, I have a really good VPS server running my shop, and to see that the layered module is capable of doubling the TTFB is impressive. I don't know if there's a way to cache it in the future, but that might be a much sought after feature, as this module is often mentioned in PS slow threads and by performance gurus.

 

Anyways, good job guys, other than that, I love the new Prestashop.

[parsifal]

Hi Mister Denial, I also visited your site and it feels quite snappy. Seems like total load time is just above 2sec, there is no way the TTFB would be as high as you previously mentioned. Have a look at my Firefox Network Console:

 

 

How optimized is the LAMP stack and PHP/SQL caching of your VPS? If you have not yet optimized it, you could gain some speedup by doing it.

 

Also, IIRC there was a fix introduced in 1.6.0.9 that mentioned faster loading of categories. That's something worth looking into.

Edited August 7, 2014 by parsifal (see edit history)

[Mister Denial]

Hi Parsifal,

 

thanks for your feedback. Strange, I have completely different results than you do (see screenshot 1).

 

I also used the debug mode to check what Prestashop runsm and which processes take forever, and it looks like actionProductlistOveride is the culprit - that one activated with the layered navigation.

 

I am no tech guy, but if I look at the right column, it looks like currently there is no SQL caching at all, or how should I understand these results?

 

I have a VPS with lots of CPU and RAM, but apparently I am not running it right.

[linaslp]

Hello, guys.

 

Yes layered navigation could be the problem. So any solutions how to make it work better? 

 

I have 4000 producs in one category. And it is killing CPU if i try to click lots of layered navigation filters CPU usage goes crazy.

 

I mean from 33% to 400%...

 

Need solutions to fix this. Maybe another module? Or tihis is just layered navigation bug? I cached everything in my shop even indexed Modules URL Prices ect.... 

[eSzeL]

I have 8000 products.. On a dedicated server blocklayered module fires this SQL, query time is crazy, site becomes slow..

# Query_time: 50.101005  Lock_time: 0.000416  Rows_sent: 124  Rows_examined: 38915861  Rows_affected: 0
# Bytes_sent: 6337
SET timestamp=1412339065;
SELECT COUNT(DISTINCT p.id_product) nbr, lpa.id_attribute_group,
                                        a.color, al.name attribute_name, agl.public_name attribute_group_name , lpa.id_attribute, ag.is_color_group,
                                        liagl.url_name name_url_name, liagl.meta_title name_meta_title, lial.url_name value_url_name, lial.meta_title value_meta_title, psi.price_min, psi.price_max

                                        FROM ps_layered_product_attribute lpa
                                        INNER JOIN ps_attribute a
                                        ON a.id_attribute = lpa.id_attribute
                                        INNER JOIN ps_attribute_lang al
                                        ON al.id_attribute = a.id_attribute
                                        AND al.id_lang = 1
                                        INNER JOIN ps_product as p
                                        ON p.id_product = lpa.id_product
                                        INNER JOIN ps_attribute_group ag
                                        ON ag.id_attribute_group = lpa.id_attribute_group
                                        INNER JOIN ps_attribute_group_lang agl
                                        ON agl.id_attribute_group = lpa.id_attribute_group
                                        AND agl.id_lang = 1
                                        LEFT JOIN ps_layered_indexable_attribute_group_lang_value liagl
                                        ON (liagl.id_attribute_group = lpa.id_attribute_group AND liagl.id_lang = 1)
                                        LEFT JOIN ps_layered_indexable_attribute_lang_value lial
                                        ON (lial.id_attribute = lpa.id_attribute AND lial.id_lang = 1)  INNER JOIN ps_product_shop product_shop
                ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)

                        INNER JOIN `ps_layered_price_index` psi
                        ON (psi.id_product = p.id_product AND psi.id_currency = 1 AND psi.id_shop=1)
WHERE a.id_attribute_group = 1 AND lpa.`id_shop` = 1 AND product_shop.active = 1 AND product_shop.`visibility` IN ("both", "catalog")
                                        AND p.id_product IN (
                                                SELECT id_product
                                                FROM ps_category_product cp
                                                INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
                                                c.nleft >= 153
                                                AND c.nright <= 248
                                                AND c.active = 1)
                                        )

                                        GROUP BY lpa.id_attribute
                                        ORDER BY ag.`position` ASC, a.`position` ASC;

Edited October 3, 2014 by eSzeL (see edit history)

[becici]

Hi HesZel,

 

The same for me.

If you try to delete

 

• AND p.id_product IN (
• SELECT id_product
• FROM ps_category_product cp
• INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
• c.nleft >= 153
• AND c.nright <= 248
• AND c.active = 1)
• )

 

It goes faster but not fast enought. The big problem is not the query, but that is not cached. Did you find out evidence that Presta is caching that query? or that is caching the layered in cache folder?

[peal]

Hi, I am fighting with this module too. I know there are alternatives, but paid as far as I know and we can't afford them at this moment.

So why debugging, I found out that blocklayered module IS cached by APC if enabled in BO. However pain in the *** is that besides this module also lot of other presta variables are cached and it cause high fragmentation soon. It would be good to force caching only for this module but I don't know how...