Slow Database Queries – Product Updates

Running WooCommerce: 3.4.3
Running WordPress: 4.9.6
MySQL: 5.5.5-10.1.34-MariaDB
Total Database Size: 991.26MB
Database Data Size: 674.75MB
Database Index Size: 316.51MB
Total Transients: 4356
Expired Transients: 0

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 last week. Each item has between 700 – 800 product variation combinations due to various color and style options.

Since adding about 8 new products from the new line, when you go to view the products list in the WordPress Admin area, it takes forever to load (20 seconds) and sometimes results in a 500 Error.

If you quick edit 1 product and it to a second category and hit update, it is taking on average 50.2 – 60.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 than a “smaller” product is understandable.

I have reverted back to the 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 with an 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 increasing in RAM on the server, offloading the DB to its own server or what, but 50.2 seconds to update a product is unacceptable and I need to find a way to speed this up. I would like to think the current server is more than sufficient with 16GB of ram.

I appreciate any suggestions…

• This topic was modified 7 years, 11 months ago by Chad.
• This topic was modified 7 years, 11 months ago by Chad. Reason: Correcting wrong time amount