1

Looking for some suggestions here. Have a client who has been on WooCommerce for about a year and a half now. We built the new site for them.

They started adding a new product line of furniture. Each item has between 700 - 800 product variation combinations.

Since adding about 8 new products from the line, when you go to view the products list in the admin, it takes forever to load.

If you quick edit 1 product and say add it to a second category and hit update, it is taking on average 10.2 - 10.8 seconds (according to Query Monitor) for the query to complete. Realizing that there are anywhere from 700-800 variations which need to iterate through, the potential for it take longer if understandable.

I have reverted back to Twenty Seventeen theme, disabled all plugins except for woocommerce and tested again. Query update time remained unchanged.

The site is on a dedicated server running CENT OS Intel Xeon E3-1270 v5 Quad-Core with 16GB RAM

Within wp-config.php I have set the following:

define('WP_MEMORY_LIMIT', '1024M');
define( 'WP_MAX_MEMORY_LIMIT', '2000M' );

Within PHP.ini configuration I have the following:

max_execution_time 500
max_input_time 60
max_input_vars 10000
memory_limit 2000M
post_max_size 512M

We are running MariaDB as a dropin replacement for MySQL

I am not sure if the way to speed things up is changing a setting in PHP.ini overall increase in RAM on the server, offloading the DB to its own server or what, but 10.2 seconds to update a product is unacceptable and I need to find a way to speed this up.

I appreciate any suggestions...

  • This question is rather difficult to answer because it's site-specific, but generally speaking the database is probably way too full. There are plugins available to optimize tables and do those sorts of things; checking to see if any tables are huge and then fixing them one at a time is the general route you should take. There are also plugins to show you which queries are taking so long - try those and you'll get a better idea of what exactly is slowing things down. Sometimes simple things like turning off revisions can help immensely. – WebElaine Jul 2 '18 at 16:17
  • @WebElaine I understand the uniqueness of this, but thought some general input maybe provided beyond what I have tried. I always utilize Query Monitor on all sites we develop. That is how I found the query times. I ensured all expired transients were deleted, I have turned off post revisions. I've looked at the database tables, but I am not sure what would be considered to be a "huge" table. Total Database Size: 991.26MB Database Data Size: 674.75MB Database Index Size: 316.51MB – Chad Warren Jul 2 '18 at 16:35
  • Start with cleaning up revisions, unused or unnecessary custom fields, meta and taxonomies. In short, relieve the database. This don't except other WordPress optimization actions. – Max Yudin Jul 2 '18 at 16:47
  • What routine maintenance are you regularly performing on the database? You are maintaining the database, aren't you? – Steve Jul 2 '18 at 16:52
  • @Steve I regularly optimize all database tables through PHPMyAdmin. I've set post revisions to be at 300 seconds instead of the normal 60, I regularly delete post revisions from the database. But none of these database query issues started until the new product line was added. We have several other products in the database which are 200 - 400 varations and have never had an issue with editing them (before or after all of the addition of the new products). – Chad Warren Jul 2 '18 at 17:17
0

While there are specific MySQL tuning options available, and of course the possibility of moving your database to a dedicated server; ultimately the issue is the way WooCommerce handles variations. WooCommerce itself warns about the impact of performance when creating variations, you even have to confirm your choice when creating variable products.

So first off I'd look at the variety of product customizers available for WooCommerce. Most of them do not actually create variations in the true WooCommerce sense, so there typically is far less MySQL overhead for the product.

Secondly, I'd review the product variations with an eye to data normalization. Can you present the products in a better structure that will reduce the number of variations required for a given product?

Typically I see that store admins tend to over complicate the product. If they are selling a T-shirt in both men's and women's cuts, multiple sizes and multiple colors they tend to treat it as a single product which creates far too many variations.

For example a single T-shirt available in (Men's or Women's), size (Small, Medium, Large, X-Large), color (Red, Blue, Green) creates a single product with 24 permutations/variations. Displaying, updating, or creating the product involves 25 different records; the parent record and each of the various records.

Creating two different products (i.e. Men's and Women's) reduces the total overhead to only 13 records for each product. Greatly limiting the overhead for each product.

Obviously, this is a very simplified example but it does demonstrate the principle and the impact it has on performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.