nyhotdogman

@olliejones wow, like magic! Order loading went from 15-20 seconds to under 4 seconds. Thanks a lot!

@olliejones just checking in to see if you possibly had time to investigate this further? I also sent you an email (to the address I found on plumislandmedia). Thanks in advance!

Unbelievable. Thanks a lot @olliejones for investigating this! Let me know if there is anything I can test for you. My server admin himself actually added indexes to our order tables a while ago (I think back using wooc 7 – we had huge slowness, after his indexes suddenly checkout times for clients went from 15 seconds to 4 seconds). I will then need to double check with him so using your Index WP MySQL For Speed?plugin does not cause a conflict. But I will test as soon as you have an update. Thanks again!

Hi @olliejones thanks for your reply and sorry for my late reponse – I wanted to wait until we update to woocommerce 9.0.2, which we did now. So I can see the order edit page load decreased quite a bit – but it is still at around 17 seconds.

1. See the trace:

MySQL wp_wc_orders_meta selectPerformanceAttributes

backtrace

in mysqli_query called at /var/www/html/wp-includes/class-wpdb.php (2349), in wpdb::_do_query called at /var/www/html/wp-includes/class-wpdb.php (2263), in wpdb::query called at /var/www/html/wp-includes/class-wpdb.php (3107), in wpdb::get_col called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/DataStores/CustomMetaDataStore.php (265), in Automattic\WooCommerce\Internal\DataStores...

duration
15871

name
Datastore/statement/MySQL/wp_wc_orders_meta/select

sql_obfuscated
SELECT DISTINCT meta_key FROM wp_wc_orders_meta WHERE meta_key !=? AND meta_key NOT BETWEEN ? AND ? AND meta_key NOT LIKE ? ORDER BY meta_key ASC LIMIT ?

timestamp
4537

2. Database query:

Total duration: 15871.00 ms | Call count: 1 | Database instance: ip-###-##-##-## Query

SELECT DISTINCT meta_key FROM wp_wc_orders_meta WHERE meta_key !=? AND meta_key NOT BETWEEN ? AND ? AND meta_key NOT LIKE ? ORDER BY meta_key ASC LIMIT ?Stack trace

                                                                                   in mysqli_query called at /var/www/html/wp-includes/class-wpdb.php (2349)

                                                                                in wpdb::_do_query called at /var/www/html/wp-includes/class-wpdb.php (2263)

                                                                                    in wpdb::query called at /var/www/html/wp-includes/class-wpdb.php (3107)

                              in wpdb::get_col called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/DataStores/CustomMetaDataStore.php (265)

…ores\CustomMetaDataStore::get_meta_keys called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (126)

…CustomMetaBox::order_meta_keys_autofill called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (146)

…taBoxes\CustomMetaBox::render_meta_form called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (75)

…\CustomMetaBox::render_custom_meta_form called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (60)

…erce\Internal\Admin\Orders\MetaBoxes\CustomMetaBox::output called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/Edit.php (372)

                  in Automattic\WooCommerce\Internal\Admin\Orders\Edit::render_custom_meta_box called at /var/www/html/wp-admin/includes/template.php (1456)

                                           in do_meta_boxes called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/Edit.php (496)

…\WooCommerce\Internal\Admin\Orders\Edit::render_meta_boxes called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/Edit.php (405)

…\WooCommerce\Internal\Admin\Orders\Edit::display called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/PageController.php (293)

                         in Automattic\WooCommerce\Internal\Admin\Orders\PageController::output called at /var/www/html/wp-includes/class-wp-hook.php (324)

                                                                      in WP_Hook::apply_filters called at /var/www/html/wp-includes/class-wp-hook.php (348)

                                                                                 in WP_Hook::do_action called at /var/www/html/wp-includes/plugin.php (517)

                                                                                              in do_action called at /var/www/html/wp-admin/admin.php (259)

 

3. Are you still interested in the output of the wp-cli commands you sent me? If yes, i will have my server admin send them to me shortly.

I should add we still have your faster order lookup plugin enabled ?? Thanks again and have a great day

Sebastian

• This reply was modified 5 months ago by nyhotdogman.
• This reply was modified 5 months ago by nyhotdogman.

@sandipmondal thanks a lot for your quick response; I missed that entry in the log, sorry! We will update and I will reply in case we still have issues. Thanks!

@olliejones I think I may have accidentally marked the issue as resolved, I adjusted it again now. I also saw woo 9.0 was released, but I do not see a fix for our issue in the change, despite woo support having accounced it in the other thread you commented on: https://github.com/woocommerce/woocommerce/blob/trunk/changelog.txt

Thanks again ?? Sebastian

• This reply was modified 5 months, 1 week ago by nyhotdogman.

@sandipmondal Hi there – has this issue been fixed in release 9.0? As I cannot see anything related to it inside the changelog, or am I missing it?: https://github.com/woocommerce/woocommerce/blob/trunk/changelog.txt

We have a shop with 400k+ orders and it is taking 30+ seconds for an order to load with wooc ver 8.9.3 – opening 5 order pages at once causes EC2 CPU to go from 10 to 60%. I cannot express how urgent this issue is and unfortunately the fix supplied here by Ollie is not working for us using 8.9.3. Thanks in advance, I really appreciate it, Sebastian

@olliejones thanks a lot for your quick reply and the fix! I appreciate it a lot! Unfortunately it did not solve the slowness in the order page loading, still 30+ sec. We have NewRelic setup, I attached you a few details from one of the transactions, maybe it helps:

1. Database queries: https://drive.google.com/open?id=1LOU8MofC11LNby3NulUleE48rwNe9D81&usp=drive_fs

2) MySQL wp_wc_orders_meta select / 32.37 s

MySQL wp_wc_orders_meta select

backtrace

in mysqli_query called at /var/www/html/wp-includes/class-wpdb.php (2349), in wpdb::_do_query called at /var/www/html/wp-includes/class-wpdb.php (2263), in wpdb::query called at /var/www/html/wp-includes/class-wpdb.php (3107), in wpdb::get_col called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/DataStores/CustomMetaDataStore.php (265), in Automattic\WooCommerce\Internal\DataStores\CustomMetaDataStore::get_meta_keys called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (126), in Automattic\WooCommerce\Internal\Admin\Orders\MetaBoxes\CustomMetaBox::order_meta_keys_autofill called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (146), in Automattic\WooCommerce\Internal\Admin\Orders\MetaBoxes\CustomMetaBox::render_meta_form called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (75), in Automattic\WooCommerce\Internal\Admin\Orders\MetaBoxes\CustomMetaBox::render_custom_meta_form called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/MetaBoxes/CustomMetaBox.php (60), in Automattic\WooCommerce\Internal\Admin\Orders\MetaBoxes\CustomMetaBox::output called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/Edit.php (372), in Automattic\WooCommerce\Internal\Admin\Orders\Edit::render_custom_meta_box called at /var/www/html/wp-admin/includes/template.php (1456), in do_meta_boxes called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/Edit.php (496), in Automattic\WooCommerce\Internal\Admin\Orders\Edit::render_meta_boxes called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/Edit.php (405), in Automattic\WooCommerce\Internal\Admin\Orders\Edit::display called at /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/Orders/PageController.php (293), in Automattic\WooCommerce\Internal\Admin\Orders\PageController::output called at /var/www/html/wp-includes/class-wp-hook.php (324), in WP_Hook::apply_filters called at /var/www/html/wp-includes/class-wp-hook.php (348), in WP_Hook::do_action called at /var/www/html/wp-includes/plugin.php (517), in do_action called at /var/www/html/wp-admin/admin.php (259)

duration
32373

name
Datastore/statement/MySQL/wp_wc_orders_meta/select

port_path_or_id
unknown

sql_obfuscated
SELECT DISTINCT meta_key FROM wp_wc_orders_meta WHERE meta_key != ? AND meta_key NOT LIKE ? ORDER BY meta_key ASC LIMIT ? 

timestamp
1621

I can also supply you any other information if you are interested. Once again, thanks a lot!

@pagarme muito bom, estamos aguardando

Thanks a lot!

Thanks a lot for the super quick answer Claudio! We will update the plugin and add our credentials.

Have a great day, Sebastian

Perfeito, muito obrigado!

Hi Thomas, not sure if you received the email – but any chance you can test the checkout again using a log in? As this is when the error still shows…feel free to also just create an account on the site: [moderated — see original post]

Thanks
Sebastian

• This reply was modified 2 years, 8 months ago by Steven Stern (sterndata).
• This reply was modified 2 years, 8 months ago by Steven Stern (sterndata).

Ah okay, sorry for that. Thomas, I have sent you an email ??

Hallo Thomas,

thanks a lot for the answer and also for taking the time to check on our website. The error is still there, but it only shows in the checkout for logged in users. Also, we use autotpmize for caching, but you will see the error is still there (in the cached file). If you’d like: bit.ly/3JIF5vA

You can use this test user to see the issue:

[redacted]

Thanks a lot und noch einen sch?nen Mittwoch Abend
Sebastian

• This reply was modified 2 years, 9 months ago by nyhotdogman.
• This reply was modified 2 years, 9 months ago by Steven Stern (sterndata).