Extreme slowness after recent update

[repli2dev]

Can you reproduce this issue on default Wordpress theme (eg Storefront)?

No (tested on new site with fresh woocommerce)

Can you reproduce this issue when all other plugins are disabled except WooCommerce, Polylang and Hyyan WooCommerce Polylang Integration?

Probably yes

What product versions and settings are you using when this issue occurs?

• PHP: 7.1.7
• WordPress: 4.9.6
• WooCommerce: 3.4.3
• Polylang: 2.3.7
• Hyyan WooCommerce Polylang Integration: 1.2.0
• Browser: All

Steps to Reproduce

have to many coupons in the database.

What I Expected

No problems

What Happened Instead

Site is extreme slow (>= 10 sec) in network admin and main site containing the WooCommerce after around 2-4 weeks around. (Frontend works fine -- 2 sec)

The XDebug profilling showed that around 93 % of time was spent in Hyyan\WPI\Coupon->adminRegisterCouponStrings down to registerCouponForTranslation and probably to reading to database.

Comment

Instantiating 2900 coupons after the same amount of queries to the database one-by-one is just ineffective and also we don't need translated coupons strings.

Is it a bug or feature?

[Jon007]

You are welcome to turn off the Coupons module by unchecking Coupons sync under Settings, WooPoly, Features.
If you need to keep the coupon translated product rules and just turn off the coupon translation then you can comment out these lines in Coupon.php:

            add_filter('woocommerce_cart_totals_coupon_label',
                array($this, 'translateLabel'), 20, 2);
            add_filter('woocommerce_coupon_get_description',
                array($this, 'translateDescription'), 10, 2);

Do Note that this was improved since the first version of the coupons module, now the query is only done once and then cached in a transient coupons-lang which is fairly efficient, depending also on how you do your transient caching. (I recommend redis cache to take the load off the database, as woocommerce does everything with transients these days).

From your report - slow specifically in wp-admin - it sounds like the transient cache is not working in admin mode does that seem to be the case and what are you using for transient cache??

[repli2dev]

Thanks for your response, I have attempted to apply your hotfix in our production and it didn't make any difference (still 10 seconds for response). Which is not suprising for me, because the slowness is one line up add_action('wp_loaded', array($this, 'adminRegisterCouponStrings')); according to XDebug.

I have found the transient in wp_options table, they look fresh, I used "Query Monitor" plugin and I see loading cca 1400 posts on every request (via WP_POST::get_instance()) origination in the plugin.
However from the structure of coupon-cs_CZ I would quess that you are talking about different query... I see an array of coupon arrays, they are requested and then one-by-one obtained via WP_Post::get_instance()... which is very probably the performance problem. (Or do you cache whole objects?)

Sadly the Redis is out of options as it is not available at our hosting service.

Another question: why they are loaded also in network administration?

---

I have tried to comment the "one-line-up" and the slowness id away, the coupon sync of translated products still works, so for the time of being we have a hotfix, but this needs proper fix.

[Jon007]

But the load should only happen once or once per language max then it’ll be in the transient cache - without redis etc this is just in wp options table same as all other transients

Or are you saying this only occurs on network admin, you are doing your testing there?

Actually I have seen other issues with network admin, that should probably be a separate issue, to ensure the plugin does not activate in network admin

[repli2dev]

Hmm, what is the thing that should be in the transient cache? In order to solve what this function does I would expect there to be a PHP serialization of all coupons, but I was unable to find any of it, what I see is only:
_transient_coupons-cs_CZ a:1231:{i:0;i:7846;i:1;i:7897;i:2;i:7903;i:3;i:782...

(However I can't imagine all objects serialized that would exceed the limitations of column text... and if they would be stored per one in transient I can't really imagine how would that help performance, because the queries would still needed to be done.)

---

Lets ignore the network admin for now, I am not sure about it and will investigate later.

[Jon007]

just the coupon ids are stored in the transient because coupon details are stored in woocommerce transients already so overkill to store them again.. but yes if you have 2900 coupons and you think this is causing you a problem then just it off as I suggested.

Also if the business is big enough to have 2900 coupons and spending effort on debugging performance, then I would suggest the business should also be able to afford proper hosting with the capability to optimize the site further... I can't quite imagine why it would need 2900 coupons as yet, what's the use case here?
.. I mean, you wouldn't normally have a coupon for each user, as you can just add users to a coupon, for example, 2900 coupons is really a lot ..

[repli2dev]

Oh, thanks for clarifying I see that it is as expected.

I would slightly disagree about your statement of the big business, we are quite small, however use coupons excesively, for example to create a campaign on discount portals, so we easily create hundreds of tokens with lifetime of about 6 months (and moreover Woocommerce has no process for coupon deletion, manually I was able to reduce it to 1400).

If it is the way I see in Xdebug, then using Redis would not help, but maybe you don't see the problem due to using OPCache (we are not :-(), but that can be tested locally...

For now I will stick to the hotfix and will investigate if there is way to mitigate the issue or any aproach to solve it properly but it will take some time.

---

Hostings are tough matter, we tried different one but we had VERY BAD experience, so we backed off.

[Jon007]

For now I will stick to the hotfix
Firstly, you don't need to stick to anything, you can turn off the coupon features as previously advised.
Also it's not a hotfix, this is in the released versions.

1400 is still a lot, are you having a coupon per product with different discount levels per product?

Anyhow, apart from turning it off the Coupon functionality of this plugin as suggested, there are two more things we could do:

• in Settings, WooPoly, Features, add an option to turn off only the coupon translation and leave the coupon product/category elegibility switched on
• filter the translation in wp_admin: in the front end, coupon translation is loaded only when needed, but in the back end it is applied generally via:
  add_action('wp_loaded', array($this, 'adminRegisterCouponStrings'));
  Perhaps this is only needed on the string translation page itself, if we can work out how to detect that.

[repli2dev]

Good ideas! Will look into it probably in two or three weeks (due to holiday).

[repli2dev]

Tested and it works nicely! Thanks for your time :-)