Massive performance issues

[apvlv]

I have a strange problem with your plugin. When I put an item to the cart, suddenly the page load times increase 10x, and from that moment on the performance is very slow (Woocommerce 2.5.5).

my webserver shows in this moments hundreds of same POST requests from this type:

[05/Apr/2017:08:52:27 +0200] "POST /wp-admin/admin-ajax.php?action=http_worker&nonce=c380f1cdbb HTTP/1.1" 499 0

however htop on the server shows no significant memory or cpu usage. There are no errors in the js console of the browser.

Strangely when I test from the admin account, with the plugin activated too, this doesn't happen, only from other customer accounts. Logged as admin i don't see this http_worker messages in the access log and the page load times are fast.

My mainly used plugins are WC Subscriptions, QTranslateX, Stripe Gateway, Revolution Slider, MegaMain Menu, Visual Composer.

At the moment, only deactivating your plugin does help.

Webserver Ubuntu 16.04/nginx/php7

Thank you for your help!

[ryanhungate]

Hey @pvlvsk thanks for letting us know, we'll be sure to look at it and report back.

[daviddarke]

+1 I'm getting this as well

[michaelrimbach]

Same here

[swearingdad]

We are also getting these errors.

[ryanhungate]

@pvlvsk @daviddarke @michaelrimbach @swearingdad

If you all don't mind, could you all provide :

1. The WooCommerce version that you're currently using?
2. The MailChimp plugin version that you're currently using
3. Verify that you're still seeing this problem after the latest update to the MailChimp plugin
4. @swearingdad you say 'errors'... did you just mean performance bottlenecks?
5. The type of traffic your site gets ( low, medium, high, crazy high ) etc.

The action=http_worker ajax call IS the queue that processes jobs and should not be impacting performance in the way you're describing... but of course we need to get to the bottom of this fast. We can't seem to simulate this scenario yet.

I can think of 2 possible solutions for you right off the top;

1. The queue can be run in command line mode, by using WP CLI... simply running "wp queue listen" in the background, but you need to make sure this process is always running to do that.
2. You could implement a cron script that pings your site every minute to initialize the queue process manually vs having your page views trigger it. ( this may be the easiest thing to do )

I'll be happy to go back and forth on this with everyone here to come up with a sensible solution. Please report back any time!

[swearingdad]

Hi

Wocommerce: 2.6.14 Mailchimp for Woocommerce Plugin: 2.0.0

Sorry, by "errors" I meant the same issue as other people, so a lot of performance hits. Or server company gave me this info as an example:

======== 2 - Requested Files (URLs) Total: 366/1600

 Hits Vis.     %   Bandwidth Mtd  Proto    Data
 ---- ---- ----- ----------- ---- -------- ----
 2768    1 9.49%    1.37 MiB POST HTTP/1.0 /wp-admin/admin-ajax.php?action=http_worker&nonce=9e604814de
 544    93 1.87%   66.66 MiB GET  HTTP/1.0 /
 441     2 1.51%  399.11 KiB POST HTTP/1.0 /wp-admin/admin-ajax.php?action=http_worker&nonce=860574902d
 274     2 0.94%  201.78 KiB POST HTTP/1.0 /wp-admin/admin-ajax.php?action=http_worker&nonce=e791aff4fd
 162    17 0.56%  168.12 KiB POST HTTP/1.0 /wp-admin/admin-ajax.php
 88     10 0.30%   44.73 KiB HEAD HTTP/1.0 /
 87      1 0.30%  209.25 KiB GET  HTTP/1.0 /wp-cron.php

========

This seems to result in large spikes in data traffic. See screen grab. Our site is generally relatively low traffic. If the data spikes are due to the Mailchimp plugin I don't know if they happen during the synch process.

We have tried to resynch data several times as the plugin does not seem to synch old order data fully, it gets to a certain point and then hangs.

Sorry if this is a bit 'vague' - I am not a developer!

Best

Simon

[ryanhungate]

Hey @swearingdad thanks for the info. It seems like something is a bit out of tune for sure. The way the queue works is by saving records to the database as the usage occurs... so think of 'adding something to the cart', or 'updating an order', or a 'new order' etc. each of these actions will generate a job/record that needs to be processed. When your site gets a page view, it will then ask the database whether or not there is a job to handle... if it's available it processes it and repeats that until there is nothing left to process ( which is why you're seeing spikes ).

What you're describing seems as if there are a bunch of jobs that are not meant to be there and it's processing thousands of them at a time to 'clean itself' if that makes any sense.

I'm going to try to duplicate this on our side and see if that could possibly be it. Stay tuned!

Ryan

[swearingdad]

Hi Ryan

To be clear, I think the spikes are happening when I hit the "Synch" or "Re-synch" buttons in the admin area. So it might be trying to send all the old orders to Mailchimp in one go? I don't think the spikes are occurring with day-to-day usage.

However when synching, the plugin seems to get so far, and then just give up. It has never managed to synch all the old order and all the old products. If it cannot do that then it is perhaps not the plugin for us - though that is a separate issue!

Best

Simon

[ryanhungate]

@swearingdad do you happen to have access to your error logs?

[swearingdad]

I would have thought so. What date range do you need?

[ryanhungate]

well to start, let's go with the most recent sync time... you can send as much as you think is appropriate for us to look at.

[swearingdad]

How can I send them to you directly and securely? I don't think they are suitable for public viewing :-)

Thanks!

[ryanhungate]

@swearingdad ryan@vextras.com :)

[swearingdad]

Sent!

[adam-sandor]

I think I just ran into this one after updating the plugin to version 2.0.2. My access log is full of POST requests like this: 181.224.152.147 - - [14/Oct/2017:19:44:29 +0200] "POST /wp-admin/admin-ajax.php?action=http_worker&nonce=c3674d9586 HTTP/1.0" 504 247 "https://www.thestorytellingjeweller.com/wp-admin/admin-ajax.php?action=http_worker&nonce=c3674d9586" "WordPress/4.8.2; https://www.thestorytellingjeweller.com"

The IP is always the IP of the server where the site is running.

[yuk75]

I've run into this issue too, it crashed my site and resulted in me being suspended. PLEASE FIX THIS!!!

WC version: 3.2.1 WP version: 4.8.2 WP memory limit: 1 GB Server info: Apache PHP version: 5.6.31 WC database version: 3.2.1 MailChimp for WooCommerce by MailChimp – 2.0.2

I am happy to provide any other info if needed. My log is filled with the same requests so I didn't bother adding. I did not click any sync buttons I was just trying to do things in my admin area (uploaded a product, couldn't even add images it slowed my site down so much).

WITH YOUR PLUGIN DISABLED MY WEBSITE ACTUALLY LOADS. Before disabling it takes minutes to load a single click, be it in the backend or front end, THIS DISABLED MY WHOLE WEBSITE. Not cool!

[austingray]

Just wanting to chime in that I experienced this issue on a customer's site. It only happened when a user was logged in, but once a user logs in, the site became completely unusable with nothing in the error log, only the http_worker spam in the access log. Deactivating the Mailchimp plugin fixed it instantly.

[khungate-zz]

@adam-sandor @yuk75 @austingray , we're closing this one out. @swearingdad's issue was something different that should've been noted. check out #158 for more info. regarding some of the possible performance tweaks.

[lloydud]

We are getting the same issues, we also had to disable the plugin else the site just crashes and is inaccessible.

From what I can see the mysql process list is being flooded by sleep commands. Think this could be caused by some sort of infinite loop??? Every time a http_worker call is being made these issues crop up in the log:

[Mon Oct 23 13:53:31.830629 2017] [:error] [pid 3993] [client 127.0.0.1:45953] product_type was called incorrectly. Product properties should not be accessed directly. Backtrace: do_action('wp_ajax_http_worker'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Http_Worker->maybe_handle, WP_Worker->process_next_job, MailChimp_WooCommerce_Cart_Update->handle, MailChimp_WooCommerce_Cart_Update->process, MailChimp_WooCommerce_Cart_Update->transformLineItem, WC_Product->__construct, WC_Data_Store->read, WC_Product_Data_Store_CPT->read, WC_Product_Data_Store_CPT->read_product_data, WC_Product->is_type, WC_Product->get_type, WC_Abstract_Legacy_Product->__get, wc_doing_it_wrong. This message was added in version 3.0., referer: http://server.dev/wp/wp-admin/admin-ajax.php?action=http_worker&nonce=8c925c3303 [Mon Oct 23 13:53:31.830690 2017] [:error] [pid 3993] [client 127.0.0.1:45953] PHP Notice: Undefined property: WC_Product::$product_type in /var/www/web/app/plugins/woocommerce/includes/abstracts/abstract-wc-product.php on line 139, referer: http://server.dev/wp/wp-admin/admin-ajax.php?action=http_worker&nonce=8c925c3303

AND

[Mon Oct 23 10:57:24.400347 2017] [proxy_fcgi:error] [pid 12480] [client 127.0.0.1:33706] AH01071: Got error 'PHP message: PHP Warning: Error while sending QUERY packet. PID=2175 in /home/server.dev/jfettvxhps/public_html/web/wp/wp-includes/wp-db.php on line 1887\nPHP message: product_type was called incorrectly. Product properties should not be accessed directly. Backtrace: do_action('wp_ajax_http_worker'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Http_Worker->maybe_handle, WP_Worker->process_next_job, MailChimp_WooCommerce_Cart_Update->handle, MailChimp_WooCommerce_Cart_Update->process, MailChimp_WooCommerce_Cart_Update->transformLineItem, WC_Product->__construct, WC_Product->get_type, WC_Abstract_Legacy_Product->__get, wc_doing_it_wrong. This message was added in version 3.0.\nPHP message: product_type was called incorrectly. Product properties should not be accessed directly. Backtrace: do_action('wp_ajax_http_worker'), WP_Hook->do_action, WP_Hook->apply_filters, WP_Http_Worker->maybe_handle, WP_Worker->process_next_job, MailChimp_WooCommerce_Cart_Update->handle, MailChimp_WooCommerce_Cart_Update->process, MailChimp_WooCommerce_Cart_Update->transformLineItem, WC_Product->__construct, WC_Data_Store->read, WC_Product_Data_Store_CPT->read, WC_Product_Data_Store_CPT->read_product_data, WC_Product->is_type, WC_Product->get_type, WC_Abstract_Legacy_Product->__get, wc_doing_it_wrong. This message was added in version 3.0.\n', referer: http://server.dev/wp/wp-admin/admin-ajax.php?action=http_worker&nonce=395726f017

[yuk75]

I don't understand how this can be a new bug when I'm getting the same error everyone else reported. I am getting the error listed here in this bug, I don't think it's related to #158 or #159?

As @austingray said this error only happens when someone's logged in and it's crippled my site ending in the server crashing. The load went so high it couldn't even load my site or FTP I couldn't do anything and then it crashed, which is really bad for me because I'm on a shared server not my own.

Can you please help me understand @khungate? I'm not reenabling this until I am sure this issue has been fixed because I will lose my hosting and my customers who tried to access my site for a sale which flopped thanks to being down for 2 days because of your plugin.

[mattyl]

I have these issue on many sites and also on different platform - eg magento I have spent hours with mailchimp help - which inevitably means deleting stores and starting again. The issues occur when something doesn't sync right. I appreciate its difficult balancing the masively hungry need of mailchimp for data and the capabilities of most users servers (often shared) to cope. What seems to be happening is that the actions are not being correctly accepted, so processes just build up and up until the servers crash. The only solution seems to be to clear all data and start again. This indicates a deeper issue. There needs to be some better method of dropping processes when they are missed or fail more than x times.

[mattyl]

same call 819 times in 19 minutes

[ryanhungate]

@mattyl Let me see if I can help out. The calls to the ?action=http_worker are normal - that is how the queue works whenever jobs exist in the queue table. Have you checked that table for an excess of records? If so you may want to delete everything in that queue and just let it start over again. Is this still happening say after an hour or so ( or there is not other data in the queue table )?

My guess is that you just have a lot going on at the moment, whether it be current activity or possibly the recent plugin update may have gotten your site out of a 'stuck state' and is just processing a backlog right now. Have you enabled your MailChimp logs at all to see if it's just simply processing data that may have been previously stuck? Is this a busy site? Have you updated the plugin to the most recent 2.1.5?

One other thing to note is that for busier stores, we recommend using the CLI version which can be set up a few ways:

1. on a cron schedule ( not WP_CRON ). This may be the easiest way to get things started. It's doing something similar as a process manager would to make sure it's always running - and no overlapping.

2. using a process manager like MONIT or SUPERVISORD to assure that the call of wp queue listen is always running.

If you do that, the HTTP worker will not be called at all - and will simply be handled in a background process. There are instructions on how to do that in the readme.txt file - but if you have questions on it, just let us know and we can help get you all squared away.

[mattyl]

Hi thanks for the help By getting my client to uninstall and reinstall the plugin , we resolved the issue - i imagine that process cleared the backlog in the table. My point was generic - the code should handle this scenario better as its a critical error. Over the past 18 months with the woocommerce and magento plugins i have seen this issue 12 times on different sites, on different servers. Because it creates a DDOS like effect, it is traumatic for the clients as the server loads are so high its difficult to recover. Presently when a client presents with poor server performance, we ask - mailchimp plugin? - and hey inevitably the solution is disable it and problem solved. This cannot be a coincidence. There needs to be better error handling so that jobs dont rack up. Or at least a way that users can throttle the calls so backlogs can be managed in sensible way - perhaps information - like “hey you have 1000 jobs waiting” My clients love the mailchimp product and are keen to integrate. My servers hate the plugins!

On 15 Mar 2018, at 15:35, Ryan Hungate notifications@github.com wrote:

@mattyl https://github.com/mattyl Let me see if I can help out. The calls to the ?action=http_worker are normal - that is how the queue works whenever jobs exist in the queue table. Have you checked that table for an excess of records? If so you may want to delete everything in that queue and just let it start over again. Is this still happening say after an hour or so ( or there is not other data in the queue table )?

My guess is that you just have a lot going on at the moment, whether it be current activity or possibly the recent plugin update may have gotten your site out of a 'stuck state' and is just processing a backlog right now. Have you enabled your MailChimp logs at all to see if it's just simply processing data that may have been previously stuck? Is this a busy site? Have you updated the plugin to the most recent 2.1.5?

One other thing to note is that for busier stores, we recommend using the CLI version which can be set up a few ways:

on a cron schedule ( not WP_CRON ). This may be the easiest way to get things started. It's doing something similar as a process manager would to make sure it's always running - and no overlapping.

using a process manager like MONIT or SUPERVISORD to assure that the call of wp queue listen is always running.

If you do that, the HTTP worker will not be called at all - and will simply be handled in a background process. There are instructions on how to do that in the readme.txt file - but if you have questions on it, just let us know and we can help get you all squared away.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/mailchimp/mc-woocommerce/issues/100#issuecomment-373397353, or mute the thread https://github.com/notifications/unsubscribe-auth/AAbLA6HWxcjuQ-yTO4LSvUJPgZknGSLuks5tenw7gaJpZM4Mz3Y3.

[ryanhungate]

Hey @mattyl thanks for the follow up. We're always very happy to get feedback from someone like yourself managing multiple WP installs. It can be very insightful at times. We completely understand your point and are trying very hard to get this right.

The latest Woo plugin should address all of the concerns you had about the excessive calls - but since you mentioned 'your servers' it sounds as if you're managing the clients sites, right? If so, and you continue to see any sort of excess at all, maybe the right thing to do is to use the CLI version. You can also specify a 'sleep between jobs' as well. If you do it this way as an agency you could limit the worker process to 1 per client and remove the http calls altogether.

wp queue listen --sleep_processing=10

We are going to consider this topic closed for now, but if you feel we need to revisit this again, just ping us back.

[adam-sandor]

I'm not happy with that answer. Reinstalling the plugin to clear the queue is not a proper solution especially if the site going down is the effect. I'm not comfortable enabling the plugin again until this is resolved properly in the code.

On Thu, Mar 15, 2018 at 5:21 PM Ryan Hungate notifications@github.com wrote:

Hey @mattyl https://github.com/mattyl thanks for the follow up. We're always very happy to get feedback from someone like yourself managing multiple WP installs. It can be very insightful at times. We completely understand your point and are trying very hard to get this right.

The latest Woo plugin should address all of the concerns you had about the excessive calls - but since you mentioned 'your servers' it sounds as if you're managing the clients sites, right? If so, and you continue to see any sort of excess at all, maybe the right thing to do is to use the CLI version. You can also specify a 'sleep between jobs' as well. If you do it this way as an agency you could limit the worker process to 1 per client and remove the http calls altogether.

wp queue listen --sleep_processing=10

We are going to consider this topic closed for now, but if you feel we need to revisit this again, just ping us back.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/mailchimp/mc-woocommerce/issues/100#issuecomment-373435726, or mute the thread https://github.com/notifications/unsubscribe-auth/AKLmT0vy7mcQasBjRLiSjfSrFdl9plhKks5tepTvgaJpZM4Mz3Y3 .

[ryanhungate]

@adam-sandor We consider this plugin to be working properly right now. Do you have any sort of issue for anything recent that would give us something to look into?

[mattyl]

I tend to agree This is a plugin for an ecommerce system and so by its nature, when it brings down sites (and it does frequently) it means users lose money. My clients tend to be small merchants taking 5 -> 15 orders a day, this sort of thing hurts them hard. The mailchimp attitude seems very much to be a shrug of the shoulders and it all been fixed in the latest release - this is an ongoing issue.

The product works fine until there is a hitch and then it spirals out of control. At which point the poor merchant is stuffed.

This speaks to a need for better handling of “bad” processes

I expect we’ll wait for the next person who’s suffered a disaster to find the thread on google and report the same issue, only to be told at that time the latest version has resolved all the issues.

On 15 Mar 2018, at 17:25, Ádám Sándor notifications@github.com wrote:

I'm not happy with that answer. Reinstalling the plugin to clear the queue is not a proper solution especially if the site going down is the effect. I'm not comfortable enabling the plugin again until this is resolved properly in the code.

On Thu, Mar 15, 2018 at 5:21 PM Ryan Hungate notifications@github.com wrote:

Hey @mattyl https://github.com/mattyl thanks for the follow up. We're always very happy to get feedback from someone like yourself managing multiple WP installs. It can be very insightful at times. We completely understand your point and are trying very hard to get this right.

The latest Woo plugin should address all of the concerns you had about the excessive calls - but since you mentioned 'your servers' it sounds as if you're managing the clients sites, right? If so, and you continue to see any sort of excess at all, maybe the right thing to do is to use the CLI version. You can also specify a 'sleep between jobs' as well. If you do it this way as an agency you could limit the worker process to 1 per client and remove the http calls altogether.

wp queue listen --sleep_processing=10

We are going to consider this topic closed for now, but if you feel we need to revisit this again, just ping us back.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/mailchimp/mc-woocommerce/issues/100#issuecomment-373435726, or mute the thread https://github.com/notifications/unsubscribe-auth/AKLmT0vy7mcQasBjRLiSjfSrFdl9plhKks5tepTvgaJpZM4Mz3Y3 .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/mailchimp/mc-woocommerce/issues/100#issuecomment-373437032, or mute the thread https://github.com/notifications/unsubscribe-auth/AAbLAzjIHHLG8Ykmeeq2Msw2SiwlTzvuks5tepXjgaJpZM4Mz3Y3.

[Garconis]

Also note: https://wordpress.org/support/topic/overload-server-2/

[ijerkov]

@mattyl I agree with you, things like this can do some serious damage to small business.

I ended up with 20 seconds sleep setup queue listen --sleep_processing=20 That's the lowest number that still doesn't kill the website (VPS, a lot of traffic and large database) With that rate, I expect this sync to finish in 7 days!

@ryanhungate I checked mysql logs and found 2 requests that are executed very often and take too much time on our database ( As I already mentioned, we have a lot of data).

SELECT COUNT(*)
                                FROM wp_posts as posts
                                LEFT JOIN wp_postmeta AS meta ON posts.ID = meta.post_id
                                WHERE   meta.meta_key = '_customer_user'
                                AND     posts.post_type = 'shop_order'
                                AND     posts.post_status IN ( 'wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed' )
                                AND     meta_value = '30560';

3.5s just to get number of orders per user

and

SELECT SUM(meta2.meta_value)
                                FROM wp_posts as posts
                                LEFT JOIN wp_postmeta AS meta ON posts.ID = meta.post_id
                                LEFT JOIN wp_postmeta AS meta2 ON posts.ID = meta2.post_id
                                WHERE   meta.meta_key       = '_customer_user'
                                AND     meta.meta_value     = '30560'
                                AND     posts.post_type     = 'shop_order'
                                AND     posts.post_status   IN ( 'wc-processing','wc-completed' )
                                AND     meta2.meta_key      = '_order_total';

4s to get sum of processed and completed orders

In total, almost 8s per user for just these 2 values.

Instead of first request, you can use something like this:

SELECT ID, posts.post_status
       FROM wp_posts as posts
       WHERE ID in (
          SELECT post_id FROM wp_postmeta
          WHERE meta_key =  '_customer_user'
          AND  meta_value = '30560')
          AND     posts.post_type = 'shop_order'
          AND     posts.post_status IN ( 'wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed' )

This sql can use index on wp_postmeta and primary key in wp_posts so it returns a value in ~1.5s

Now you can use php to count the number of orders from results and to filter only 'wc-processing' and 'wc-completed' order ids.

And then use filtered list of ids in this select:

SELECT SUM(meta_value) FROM wp_postmeta AS meta
WHERE meta.meta_key='_order_total' 
AND post_id IN (29337)

This select can use index efficiently and takes almost no time at all (maybe a couple of ms).

I know this solution is not perfect, it's reading tables directly instead of using internal wp api but I also think that's a good compromise to improve performance and reliability.

[ryanhungate]

@ijerkov thanks for the info - we'll look into this in our next release cycle. Optimizations like this are always important to us - so i'll see if we can dedicate a little time to this particular issue. I'm sure it will help many folks.