WooCommerce backend depressingly slow even though front end is blazing fast? :(

v3
#1

Hey guys,

I am using EasyEngine and hosted on DigitalOcean. I followed all the guidelines on how to setup EasyEngine with Wordpress. I am also using W3TC Caching plugin and using all the settings mentioned over here https://easyengine.io/wordpress-nginx/tutorials/single-site/w3-total-cache/ except the “page cache” one at present.

Anyhow, my front end seems to be running quite good and pages load instantly but the backend is super slow. Sometimes I have to wait for like 5-10 minutes to open a single order page and it sometimes land with connection time out.

I have gone through a lot of posts on the forum, did a lot of research and still haven’t been able to find a proper solution. I found pointers like when Object Cache and Database Cache are set to memcache, the backend becomes slow but no proper solution on how to solve it.

What do I need to debug this? Any pointers would be highly appreciated!

Thank you.

Sumeet Chawla

#2

I’ve ran into this issue before and I have not been able to pinpoint why. One thing you could look into is using a plugin called “WP Query Monitor” which will show you which queries are running slow, and from which plugin. If you find out what the issues, I would like to know as well.

#3

Hey Thorr, thanks for the quick reply. I haven’t used WP Query Monitor yet but will try it out. I have been using New Relics though and as per that /wp-admin/admin-ajax.php has been the most time consuming with max time going upto 278000ms :pensive:

Also, on digging deeper, it shows that the following DB transaction takes the most time:

Screen Shot 2016-03-18 at 2.23.33 am.png758×111 16.2 KB

this is the backend I am talking about i.e. the Dashboard area. Mostly while browsing through the product listing page or orders.

How do I go ahead debugging why this might be happening? Any pointers would be highly appreciated :slight_smile:

#4

WooCommerce is just not good for performance, the slow AJAX get fragments is a longstanding issue with the software and why I refuse to ever use it.

If you are on a shared host then that could explain your especially slow query times.

You can use MySQL Tuner to see if you should increase RAM for MySQL

You can try using some object caching like memcached or redis.

Honestly though the best advice is to not use WooCommerce.

#5

While researching on why the wp_options table might be taking so much time, I stumbled upon this: http://www.remicorson.com/too-many-wc_sessions-in-woocommerce/

So WooCommerce has been storing session details in the wp_option tables with entries like _wc_session_xxx and _wc_session_expires_xxx entries. I am yet to confirm this in my case but this might be the case.

But the good news is that WooCommerce’s latest updated “Dashing Dolphin” is dedicated to improving the way sessions are handled:

First, we improved how WooCommerce knows who is on the site and what they have in the cart, known as a session handler. In the past, this was done with the wp_options table. This is fine for smaller stores, but it can be a bit slow for stores with thousands of concurrent users and so we overhauled the way we store temporary data. We’re now storing less temporary data in the database, which speeds up every page load. - https://www.woothemes.com/2016/01/woocommerce-25-dashing-dolphin/

I think I started having this problem with more number of visits and lesser bounce rates. Need to dig deeper!

@blindpet I have been hearing this ever since I started the store but to be really honest, compared to Magento and others, I find WooCommerce very comfortable. Specially, given that I am a WordPress developer at heart, it becomes easier for me to launch new features or build up custom ones quickly. The other advantage I see with WordPress is the SEO friendliness of the website compared to others. Other than WooCommerce, the only big players I see are Magento and Shopify. Which one would you have gone with?

Obviously, when I reach a stage where I am confident that WordPress won’t be able to support the traffic, one can always shift to a custom made platform based on NodeJS to provide the ultimate performance output.

I was on a shared host around 7 months back but when the traffic started to grown, I created a droplet on DigitalOcean and used EasyEngine to setup the server. On the shared host, my frontend use to crash a lot as my MySQL server crashed a lot.

But on DO with EasyEngine’s config, the front end experience has been quite satisfying for me. I am using Nginx, mariaDB, and memcache to cache the object and database. I am yet to use page cache as I was facing some issue with cart updates and yet to use CDN. Would love any other tips on how to improve performance further. :slight_smile:

#6

There are a few different places that talk about replacing w3tc object cache with apc object cache.

Guy here:

https://wordpress.org/support/topic/w3-total-cache-slowing-admin-section-with-object-cachephp

Another guy here:

https://ewan.im/900/10-million-hits-a-day-with-wordpress-using-a-15-server in the comments section.

I’d try disabling object cache altogether and checking again regardless.

Otherwise, I’d look at repairing the db.

To be clear, if you disable woocommerce (if that is even possible), the backend clears up? Do you have a test environment here?

#7

Hello guys,

I think I finally solved it after staying up all night :sleeping: I will just explain the problem in steps so that it might be helpful for others who are facing the same problem:

  1. If the backend/dashboard is very very slow, like excruciatingly slow, then there is some issue and you need to address it.
  2. The first way to focus on the problem is to see which request in the backend is taking the most time.
  3. For the above step, you can use a variety of tools but I used New Relic to figure out the most time consuming request. This lead me to the query that select wp_options was consuming hell lot of time.
  4. Now once I reached here, it was easy as one google search will give you the exact reason why this is happening.
  5. WooCommerce was storing all sessions in the wp_options table and supposedly even the search engine robots were creating sessions when they were hitting the “add_to_cart” links. Remi Corson’s blog post helped me a lot in figuring out this step. http://www.remicorson.com/too-many-wc_sessions-in-woocommerce/
  6. Now to check if wp_options is the culprit or not, just fire up your mysql prompt and write the query “select count(*) from wp_options;”. If the number returned is alarmingly huge, then you are facing the sessions problem. I had 1.5 million entries in that table :scream:
  7. This also means the cron to clean the woocommerce session variables is not working properly. Try to verify the cron and execute it to see if it helps in reducing the number of rows.
  8. If that also does not help, go to WooCommerce -> System Status -> Tools and click on “Clear All Sessions” next to “Customer Sessions”.
  9. On doing that, my number of rows reduced to 12 thousand from 1.5 million.

Ever since I cleared the left over session data (which was suppose to auto clean after 48 hours), my backend has been acting normally. I am still keeping this under observation for the next 24-48 hours to be completely sure if this solved the issue or not.

The other good thing is that from the latest version of WooCommerce, they have completely changed the way they handle sessions so hopefully this kind of issue won’t happen in the future.

During my whole research, I was also led to posts where object cache and database cache were mentioned as culprit but it did not turn out to be the case for me (yet).

I hope this helps others who are facing a similar problem.

@josiahtoppin You are correct about the two links you shared. Even I found links like those but I did not want to do such a big jump by shifting my caching system from W3TC cache to something else. I think I even read somewhere that Rahul Bansal was not to keen about W3TC either. But to be honest the front end was really smooth for me till now *touches wood* and I did not want to experiment further. The frontend staying fast matters the most I guess because that is where the sales come from :stuck_out_tongue:

Anyway, my next step/research would be why did the cron not work properly. Was it a server misconfiguration somewhere or something else? The Cron manager plugin did show that it ran successfully 12 hours ago. Interesting mystery :smiley: Let’s see what I get on that.

Would love to hear more from you guys on how to optimise further. :slight_smile:

Take care and good night. *sleeps on keyboard*

#8

@sumeetchawla

Generally, we always add MySQL INDEX on wp_options table.

Following is syntax.

ALTER TABLE `wp_options` ADD INDEX `autoload` (`autoload`);

Apart from this, as woocommerce session uses transient API, you would certainly benefit from using redis with wordpress object cache - https://easyengine.io/tutorials/wordpress/redis-wordpress-object-cache/

Can you please try above 2 things and let me know how it works for you?

By the way redis-based object cache automatically works when you use:

ee site create example.com --wpredis

OR

ee site update example.com --wpredis
3 Likes
#9

hello, so it is applicable if i’m using w3tc along with redis? because i am figure it out that there 900 expired transient on the database, thats only for the expired only. i dont now if its ok if i delete it.

i’m experiencing high consume memory of mysql on my DO 512mb. while my visitor is only few as 30UV/month.

mysqltuner said i have to minimum is 2gb memory ram, but i think its too much according my few visitors and my revenue.

#10

Hey @jayzou

with EE Redis, you don’t need W3TC anymore. EE configures Redis in such a way that it provides full page cache. It will also auto install the WP Redis plugin which is used to enable object cache. The entire caching option is done at NGINX level so you don’t need a separate plugin to enable caching from wordpress’ end.

As for traffic, you definitely don’t need a 2GB server for 30UV/month. If you configure everything properly, you can actually setup multiple sites on the same server with similar traffic.

#11

Hey @rahul286,

Apologies for the super late reply.

I did try the alter command and it did have an impact on the performance. Recently shifted to Redis too and the caching has been working superbly as well. Thank you.

Sumeet

1 Like
#12

Hi Sumeet,

I’m also keen to try Redis full page cache but I’m not sure how well it works with WooCommerce. I’m using WP Rocket right now and it has built in settings that play nicely with WooCommerce. Did you do anything especial to make them work together?

Thanks

#13

[quote=“rahul286, post:8, topic:6108”] Generally, we always add MySQL INDEX on wp_options table.

Following is syntax.

ALTER TABLE wp_options ADD INDEX autoload (autoload);

Apart from this, as woocommerce session uses transient API, you would certainly benefit from using redis with wordpress object cache[/quote]

As per Rahul’s response last year, just wanted to follow up and let other users know that this issue has been unsolved in WordPress Trac for many years (autoload indexing).

If anyone is interested, consider this plugin:

Also just want to say thanks to rtCamp for all their configuration advice.

We are currently running custom LEMP stack + FastCGI Cache + Redis object cache for our clients and it is working great, even on very high traffic WooCommerce.

1 Like
#14

@jessuppi Glad to know you find our suggestions helpful.

I would like to give you two more suggestions:

  1. Do not use that plugin for MySQL index. Better do it using MySQL query I shared above:
ALTER TABLE `wp_options` ADD INDEX `autoload` (`autoload`);

Once you run the query, it automatically takes care of indexing future data. As it’s one-time activity, avoid using a WordPress plugin for such things.

  1. https://easyengine.io/tutorials/wordpress/woocommerce-window-shopping-caching-technique/ has some specialized caching techniques. They may be bit outdated. So if you want to test them, test them on a staging/dev setup.
#15

Thanks as always Rahul. From my understanding, ALTER can have recurring benefits, even if the index has been previously added.

This is from the MySQL documentation:

ALTER TABLE … TABLESPACE operations always cause a full table rebuild, even if the TABLESPACE attribute has not changed from its previous value.

What do you think?

We also disable WooCommerce Status widget for some high-traffic shops:

#16

No. You need to run ALTER table only once to add an index. If you try to add index which already exists, mysql throws an error:

# first time
> ALTER TABLE `wp_options` ADD INDEX `autoload` (`autoload`);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

# second time
> ALTER TABLE `wp_options` ADD INDEX `autoload` (`autoload`);
ERROR 1061 (42000): Duplicate key name 'autoload'

We did not need to disable those widgets.

Make sure you are using object-cache. EasyEngine already configures it for all WordPress sites created through it.

#17

Hello Newton,

My apologies for the late reply again. Missed the notification and just logged in here after a long time. Redis full page cache has been working wonderfully for me with WooCommerce. You can follow the guide provided by EasyEngine to enable Redis Full page object cache. I just followed that and did not do anything extra :slight_smile:

#18