1

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

mforsetti
  • 2,666
  • 2
  • 16
  • 20
Alex K.
  • 41
  • 6
  • On another note, the state of the executing queries that run forever is in the "Executing" state. – Alex K. Nov 16 '20 at 23:15
  • Please provide `SHOW CREATE TABLE g3hy2_usergroups`. Also, was anything else running when it took 'forever'? – Rick James Nov 16 '20 at 23:17
  • Hi @Rick. Thanks for the reply. The result of that query is this: https://justpaste.it/3kv35 – Alex K. Nov 16 '20 at 23:27
  • Yes, there are other queries running, many of them. It's possible that another query is problematic and it creates this issue with this simple one? – Alex K. Nov 16 '20 at 23:29
  • In particular, `ALTER TABLE g3hy2_usergroups` could block your very simple query. Provide the output from `SHOW PROCESSLIST` during the pile-up. – Rick James Nov 16 '20 at 23:33
  • Ok, I'll wait until this happens again, and I'll post the output of SHOW PROCESSLIST – Alex K. Nov 16 '20 at 23:34
  • hi @Rick. Here's the process list output during bottleneck CPU 100% https://justpaste.it/2yz8t – Alex K. Nov 18 '20 at 18:38
  • @WilsonHauck MySQL it currently uses 48GB of memory out of 55GB allocated to InnoDB – Alex K. Nov 18 '20 at 18:39
  • On this occasion, there's no SELECT count(id) FROM g3hy2_usergroups query, which actually tell us that the problem is not on that query, probably some other queries produce the problem. – Alex K. Nov 18 '20 at 18:42
  • 1
    Please show us the entire query that starts `SELECT c.id,c.title, p.* FROM (`, together with `SHOW CREATE TABLE` for the table(s) involved and `EXPLAIN SELECT...`. – Rick James Nov 18 '20 at 18:42
  • 1
    Also, if that statement is coming from an `EVENT`, please provide the definition of the `EVENT`. – Rick James Nov 18 '20 at 18:44
  • Here's that SELECT c.id....query: https://justpaste.it/276at – Alex K. Nov 18 '20 at 18:49
  • @RickJames sorry, what exactly do you mean by EVENT? – Alex K. Nov 18 '20 at 19:32
  • @AlexK. - `| 5 | event_scheduler | localhost | NULL | Daemon | 218063 | Waiting on empty queue | NULL ` -- This implies that the "Event Scheduler" is enabled. (It does say whether you have any `EVENTs`.) – Rick James Nov 18 '20 at 20:57
  • (`lft`,`rgt`) -- Do you have a hierarchy? The left-right is a clever trick, but it is terribly slow for large datasets. – Rick James Nov 23 '20 at 16:06

1 Answers1

1

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.)

Rick James
  • 2,463
  • 1
  • 6
  • 13