I was sent here by Automattic

[andy-jam]

Hi all,

I am interested in this project. A support rep from Automattic said that "This [project] was slowed down for a bit but I think is going to be picked up again."

Recent updates to woocommerce have greatly improved the front end response.

Back end creating new products or updating products slows with large numbers of products - for me.

I'm busy migrating a client's catalog from a bespoke system to woocommerce. I've got a test site up and running and imported part of the catalog (without images). Initially the rate was about 90 products per minute. Perfectly acceptable. I've got up to 150,000+ products and the rate has slowed down to about 15 per minute. Starting to become painful to manually add products. Hopefully the slowdown isn't exponential.

On the front end I'm just using the Shopfront theme and once I thinned out the front page, mostly losing the catalog block, it still refreshes in a couple of seconds and so do individual product pages and the search is reasonably quick. So all good. No fancy plugins, trying to keep it minimal.

Looking at the database the issue appears to be a combination of the size of the postmeta table (3,102,853 rows) and that _SKU is a value in meta_key. Or should I say: over 1500,000 values in meta_key.

A simple search to bring back a unique SKU:

SELECT * FROM wp_postmeta WHERE meta_key="_sku" AND meta_value ="Fujitsu-618270"

Showing rows 0 - 0 (1 total, Query took 2.3573 seconds.)

Same server, different Wordpress install with Woocommerce only 785 rows in postmeta

SELECT * FROM wp_postmeta WHERE meta_key="_wp_attached_file" AND meta_value ="2020/01/****-Antique-Shop-Tags-1.jpg"

Showing rows 0 - 0 (1 total, Query took 0.0014 seconds.)

I took a quick look at the proposed table taking the woocommerce values out of post meta (I think I saw about 19 values for each product in my database) is a fantastic idea. Could you add a key to the sku field? I believe it is supposed to be a unique value in woocommerce so looks like it gets checked when a product is added or edited.

And, of course, is there anything I can do to help?

Cheers,

Andy

[maciej-laskowski]

Hi Andy, your database should have a wc_product_meta_lookup table that you should use for such queries. This is a core WC functionality and it includes sku column. More details: https://github.com/woocommerce/woocommerce/pull/22718

If you need top performance there are a lot of things you can do mainly to minimalize the amount and complexity of db queries you run on every page type. I would suggest installing a profiler like Query Monitor to diagnose those. The biggest performance improvements I got was by enabling Redis, minimizing the amount of extra meta fields I introduced and writing my own filters and search functions but every small detail matters like forcing database charset using pre_get_table_charset and pre_get_col_charset filters to prevent Wordpress from asking database about the charset on every single page load :)

With all that I managed to get a very nice TTFB on 60k products without any extreme measures like full page caching or cache warming. I am not sure if I can post a link to the working store here but shoot me a PM if you would like to see the final result.

However, at 150k products your challenge is even greater and the best (and only AFAIK) solution would be to use a dedicated table for products and orders but those don't seem to be production-ready just yet :(

[andy-jam]

Hi Maciej,

Many thanks for the response.

I only gave those SQL responses as an example of performance degradation. I'm trying to avoid writing (too much) custom code.

60K products was no problem, importing was still going fine at this point. I understand that I am heading towards extremes with product count which is why I'm here.

The front end of the site that a customer would see is still fine without caching so I'm expecting with caching it will be extra-fine :) Just not switching on any caching or minification until development is done and data is loaded.

And as I'm actually importing new data I don't think caching is going to be that helpful. I do have opcahing switched on on the server.

I'm using woocommerce' own import to load up products. The process becomes too problematic to create custom code - checking and applying categories etc... So better for me to use something that is thoroughly tested.

Could you send me a link to this Redis of which you speak please?

Interested in the filters you mentioned: add_filter('pre_get_table_charset', function($charset, $table) {return 'utf8mb4'; }, 10, 2); add_filter('pre_get_col_charset', function($charset, $table, $column) { return 'utf8mb4'; }, 10, 2);

Would dropping these into the top of functions.php do the trick? It would miss some initial wordpress setup but I'm guessing it would be activated by the time products are being added.

Always interested in the work of others. Send me the link to your site.

Cheers,

Andy

[andy-jam]

Hi Maciej,

Guess not in functions.php. Fine on the frontend. Killed admin.

I can see wc_product_meta_lookup in the database but it is empty. Do I need to activate something here?

Cheers,

Andy

[andy-jam]

I've installed query monitor. Adding a new product (manually - not importing) shows 1 slow script.

SELECT DISTINCT meta_key FROM wp_postmeta WHERE metakey NOT BETWEEN '' AND '_z' HAVING metakey NOT LIKE '\%' ORDER BY meta_key LIMIT 30

Caller:

meta_form()
wp-admin/includes/template.php:696
post_custom_meta_box()
wp-admin/includes/meta-boxes.php:751
do_meta_boxes()
wp-admin/includes/template.php:1343

Time: 2.2341

Running the same query in phpmyadmin...

Showing rows 0 - 2 (3 total, Query took 0.0030 seconds.) [meta_key: ET_ENQUEUED_POST_FONTS... - WP-SMPRO-SMUSH-DATA...]

Both produced 3 rows: meta_key et_enqueued_post_fonts spay_email wp-smpro-smush-data

I'm not sure why there should be such a huge difference in speed unless query monitor is measuring more than just the query.

Any ideas?

Cheers,

Andy

[andy-jam]

This helped: https://9seeds.com/wordpress-admin-post-editor-performance/

[maciej-laskowski]

Hi Andy, I am afraid that at this scale you will have to write a lot of custom code :) I wrote a custom CLI migration tool based on laravel-zero to import all the categories, products etc. refresh links and do a lot of other custom stuff.

Regarding the db charset I use:

add_filter('pre_get_table_charset', function($charset, $table) {return 'utf8mb4' }, 10, 2);
add_filter('pre_get_col_charset', function($charset, $table) {return 'utf8mb4' }, 10, 2);

In functions.php and it works fine. Of course make sure your actual database uses utfmb4 :)

If your wc_product_meta_lookup is empty (but I am pretty sure it shouldn't be if you are using native WC importer) go to your “WooCommerce > Status > Tools” page and “Regenerate” the product lookup tables.

Are you sure you are using the latest WP&WC? I believe the custom metabox is disabled by default in never versions.

BTW. We are getting a bit too offtopic for this project issue tracker.

[andy-jam]

Hi Maciej,

Thanks for your continued interest and help!

I wrote a custom CLI migration tool based on laravel-zero to import all the categories, products etc. refresh links and do a lot of other custom stuff.

I'm trying to keep custom work to a minimum so I don't have to revisit code when woocommerce evolves. But I will be managing prices updates in custom code.

Regarding the db charset I use:

add_filter('pre_get_table_charset', function($charset, $table) {return 'utf8mb4' }, 10, 2);
add_filter('pre_get_col_charset', function($charset, $table) {return 'utf8mb4' }, 10, 2);

In functions.php and it works fine. Of course make sure your actual database uses utfmb4 :)

I drop this into functions.php and WP falls apart.

> Uncaught Error: syntax error, unexpected '}', expecting ';' in /home/******/public_html/wp-content/themes/****/functions.php on line 3
> 
> require_once()
> wp-config.php:184
> require_once()
> wp-load.php:37
> require_once()
> wp-blog-header.php:13
> require()
> index.php:17
> 
> There has been a critical error on your website.

I'd love to get this working.

New install of WP and WooCommerce. Checked a couple of table in PHPMyAdmin... `

Table	Create Table
wp_postmeta	CREATE TABLE wp_postmeta (  meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,  post_id bigint(20) unsigned NOT NULL DEFAULT 0,  meta_key varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,  meta_value longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,  PRIMARY KEY (meta_id),  KEY post_id (post_id),  KEY meta_key (meta_key(191)) ) ENGINE=MyISAM AUTO_INCREMENT=3103201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

` Charsets all look fine.

If your wc_product_meta_lookup is empty (but I am pretty sure it shouldn't be if you are using native WC importer) go to your “WooCommerce > Status > Tools” page and “Regenerate” the product lookup tables.

My Stupidity. The table is fine. And nice and quick! Will definitely be using this.

Are you sure you are using the latest WP&WC? I believe the custom metabox is disabled by default in never versions.

Yes latest version. I probably didn't notice metabox was already gone and got a false positive in my mind.

BTW. We are getting a bit too offtopic for this project issue tracker.

Maybe. It's all about performance though. And Automattic sent me here. I don't want to spawn a load of threads all related to this.

I'm finding Query Monitor interesting. It seems to imply Wordpress or the server/MariaDB are inconsistent with certain queries. The server isn't being used by anything else but who knows what cpanel/WHM decide to do at the most inconvenient times.

SELECT term_id, meta_key, meta_value FROM wp_termmeta WHERE term_id IN (18,19,20,15,363,352,105,251,32,469,

---

Lots and lots of terms!!!

---

69,201,98,612,568,286,192,361,142,452,109,295,147,533,380,40,457,421,189,58,217,589,94,442,114) ORDER BY meta_id ASC

Is a bad one. I think I'll thin out the categories and see how that affects things.

Thanks for your insights, knowledge and help!

Cheers,

Andy

[maciej-laskowski]

Hi Andy, having a lot of categories can be a pain for certain queries. My store having almost 3000 of them required some of the queries logic to be rewritten in order to work. But in your case having several term_ids in a where clause is really fine.

Regarding functions.php there is a syntax error, missing semicolons, I have been writing too much typescript lately ;) Try this: add_filter('pre_get_table_charset', function($charset, $table) {return 'utf8mb4'; }, 10, 2); add_filter('pre_get_col_charset', function($charset, $table) {return 'utf8mb4'; }, 10, 2);

[andy-jam]

Hi Maceij,

Thanks for the functions. They worked fine.

:)

Thinning back the categories has helped. In the middle of a cleanse of their current data which will also help.

I'm loathe to rewrite woocommerce's logic. It is continuously improving. I'll limp along so long as the front end is quick enough not to drive off customers.

Thanks for your help!

Cheers,

Andy