Extremely high CPU usage

To be very honest, I don’t remember installing this plugin, nor do I know what functionality it was providing, if any (I suspect one of our fulfillment staff installed it, but I’m clearly to blame if their user role was able to do so), so this is less of a support request and more of an issue report for the benefit of the dev. There’s also always the chance that I did install it, but so long ago that I don’t remember, in which case I’ll be keeping an eye out for side effects caused by deactivation.

Our store went down at 1:00pm today. MySQL’s process had pinned our CPU at 100%, causing a complete denial of service. I migrated to a server with more resources so I could get back up and running and properly assess the situation, but within 10 minutes, it was back up near 100% (not to the point of denial of service this time, but things were extremely slow). The answer lay in the slow execution logs. This query was frequently taken over 2 minutes to complete, and almost single-handedly causing 90+ percent of our CPU usage:

Query_time: 137.354499 Lock_time: 0.000372 Rows_sent: 2735 Rows_examined: 6654560
# Rows_affected: 0 Bytes_sent: 264369
SET timestamp=1738008229;
SELECT *
-- ,(SELECT GROUP_CONCAT(DISTINCT post_title) FROM wp_posts WHERE FIND_IN_SET(ID,product_ids)>0)product
FROM (
SELECT p.ID post_id,post_title coupon_code
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='product_ids' LIMIT 1)product_ids
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='exclude_product_ids')exc_prds
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='product_categories')cats
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='exclude_product_categories')exc_cats
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='individual_use' LIMIT 1)individual
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='coupon_amount' LIMIT 1)coupon_amount
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='customer_email' LIMIT 1)c_email
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_min_qty' LIMIT 1)min_qty
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_max_qty' LIMIT 1)max_qty
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_qty_ntf' LIMIT 1)qty_ntf
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_min_qty_ntf' LIMIT 1)min_qty_ntf
,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_max_qty_ntf' LIMIT 1)max_qty_ntf
,(SELECT meta_key FROM wp_postmeta WHERE post_id=p.ID AND meta_key LIKE '_wc_%_apply' AND meta_value='yes' ORDER BY meta_key LIMIT 1)apply
,DATE_FORMAT(FROM_UNIXTIME(x.meta_value),'%m-%d-%Y')exp_date
,CASE WHEN FROM_UNIXTIME(x.meta_value)<NOW() THEN 1 ELSE 0 END exp
FROM wp_posts p
LEFT JOIN wp_postmeta x ON x.post_id=p.ID AND x.meta_key='date_expires' AND LENGTH(x.meta_value)=10 AND x.meta_value REGEXP '[0-9]'
WHERE post_type='shop_coupon'
AND post_status='publish'
AND IFNULL(FROM_UNIXTIME(NULLIF(x.meta_value,'')),NOW())>=NOW()
)a
WHERE(individual='yes' OR apply IS NOT NULL OR c_email IS NOT NULL)
ORDER BY individual DESC,exp,CAST(coupon_amount AS SIGNED) DESC;

As you can see, our database is quite large. It’s a WooCommerce store with 2+ years of orders/customers, and a large line of 300+ individual products, so I suspect this may not cause issues for new/small stores, but it seems that at a certain database size, the scaling can get wildly out of hand. I do have quite a bit of custom functionality regarding coupons, so it’s more than possible that contributed to the problem, depending what woocommerce hooks this plugin is making use of.