Monitoring doesn’t work

Resolved niiicooo56
(@niiicooo56)

2 years, 6 months ago

Hello,

Speed has improved (thanks), but I think it’s still possible to do better by analyzing requests.

However, when I try to activate a monitor, it doesn’t intercept any Query and indicates a duration of 0s. Could you please help me ?

A monitor I call “monitor5both” :
monitor5both 21 novembre 2023 16h56―21 novembre 2023 16h56 (0μs) 0 queries captured.

Another question: do you have any index suggestions for the wp_woocommerce_order_itemmeta table? It’s my second-largest table (after wp_postmeta), with 10M entries.

Thank you,

Viewing 8 replies - 1 through 8 (of 8 total)

Plugin Author OllieJones
(@olliejones)

2 years, 6 months ago

Thanks for the report. Is it possible you started your monitor and did not give it time to finish before looking at it? That’s one reason you might see a zero duration.

As for WooCommerce’s so-called “high performance” order store, yes, the standard keys are stuck in the year 2015 with MySQL 5.6 along with the rest of WordPress. They use prefix keys on VARCHAR(255) columns. They can be improved. More to come.

Really, WooCommerce?

Thread Starter niiicooo56
(@niiicooo56)

2 years, 6 months ago

I’ve just restarted a monitor test for 1 min, and even if I look after 5min I still have 0s and 0 query.

Yes, and almost 10M wp_postmeta lines correspond to shop_order wp_posts. That’s a lot of lines for WooCommerce. I have about 150,000 orders and it’s growing every day.

So you don’t have any tips for WooCommerce tables?

pipdig
(@pipdig)

2 years, 6 months ago

Sorry to jump in. I just wanted to give my +1 for optimizing wp_woocommerce_order_itemmeta

Plugin Author OllieJones
(@olliejones)

2 years, 6 months ago

Sorry to say, I don’t know why your site doesn’t capture monitors. Puzzling. I would next look at wp-content/debug.log for clues.

I’ll take a look at the “HPOS” stuff. It would help to get some monitors showing its use.
Plugin Author OllieJones
(@olliejones)

2 years, 6 months ago
Here’s an update to the keys on that wp_woocommerce_order_itemmeta table. It creates the same suite of keys as this plugin does for wp_postmeta. It requires a modern (5.7+) version of MySQL or MariaDB. It replaces the clustered index (PK) with a more frequently-accessed one.
```
ALTER TABLE wp_woocommerce_order_itemmeta
  ADD UNIQUE KEY meta_id (meta_id),
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (order_item_id, meta_key, meta_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key,meta_value(32), order_item_id, meta_id),
  ADD KEY meta_value (meta_value(32), meta_id),
  DROP KEY order_item_id;
```
If you want to restore the WooCommerce standard keys, do this.
```
ALTER TABLE wp_woocommerce_order_itemmeta
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (meta_id),
  ADD KEY order_item_id (order_item_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key(32)),
  DROP KEY meta_id,
  DROP KEY meta_value;
```
- This reply was modified 2 years, 6 months ago by OllieJones.
pipdig
(@pipdig)

2 years, 6 months ago

That’s great, thanks @olliejones. Would you like me to upload a monitor before making the change and then again after? Or will uploading a single monitor afterwards suffice?

Plugin Author OllieJones
(@olliejones)

2 years, 6 months ago

Before-and-after monitors would be very useful! Thanks for offering to do that extra work.

Plugin Author OllieJones
(@olliejones)

2 years, 5 months ago

I haven’t heard back from you, so I’m marking this resolved. Please don’t hesitate to create a new support thread if I can help you further.