Strange behaviour of MySQL on Windows (CPU 100% with simple query)

We run MySQL 8 on Windows 2019 server (Dell R420, 32 cores, 64GB, SSDs on Raid10). Every now and then, our website goes completely offline, as the CPU on the MySQL server is completely bottlenecked (CPU 100%).

After a bit of investigation, we found out that these queries pile up and take forever to return results:

SELECT count(id)
FROM g3hy2_usergroups

This is a very simple query. It just returns the number of user groups on that table. That table is InnoDB, and its table size is only 80kB.

When we run this query manually, it takes 0.037 seconds to return the results, but after we run it for (for example) the 40th time, we hit the "bug" and it takes forever to return results (it actually runs forever, I suspect until it hits the MySQL timeout).

I don't know if this is a MySQL bug and/or there's something wrong with our database or something else we can't think of.

But we would like for someone with deeper knowledge to give us a hint so we can look in the right direction, and we get one step close to solve this issue.

Btw it's not a hardware issue, as we have tried different servers with the same results.

Thanks Alex

Some composite indexes that will probably help:

g3hy2_social_stream_item: INDEX(target_id, context_type, d.created)
g3hy2_social_stream_item: INDEX(target_id, created)
g3hy2_social_stream_item: INDEX(verb, context_type, created, context_id)
g3hy2_content:  INDEX(cat_id, id)

This seems to be useless; get rid if it if you can:

JOIN  g3hy2_jreviews_content as jc  ON c.id = jc.content

Try to move this into the derived table with the UNION:

    GROUP BY  p.listing_id
    ORDER BY  p.created DESC
    LIMIT  20;

That is, try to do the LIMIT before Joining to c and jc. This should decrease the cost of those Joins.

If you succeed in the above task, then move those 3 lines into each SELECT of the UNION. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or

About the stall at 40 copies... It may be that you have 40 copies of this query running simultaneously and they are stumbling over each other -- sharing 32 cores. My comments above hope to make the query run enough faster to prevent the freeway pileup.

(EXPLAIN might help in analysis.)