0

There was noticed that Cloud SQL (MySQL) consumed to much CPU (~100%) during several days.

Firstly, I decided that we have many user online or background jobs. But it was regular workload, nothing special. Query Insights showed CPU under 20% for all users in DB and for all DBs in the MySQL instance.

Secondly, I decided that we have connections or memory leaks. Also, nothing special.

And, lastly, what I checked was output from SHOW FULL PROCESSLIST. I noticed some process which lasts too long (~8days) (see Time field) and State was 'statistics'. This workload was not shown in the CPU chart of Query Insights but belongs to my sql user. And, actually, this is strange for me.

The 'Info' field contains a 'SELECT ...' query. I was able to kill such processes. And the CPU came to normal levels.

enter image description here

enter image description here

UPDATED:

  1. https://bugs.mysql.com/bug.php?id=20932
  2. https://bugs.mysql.com/bug.php?id=26339
  3. https://bugs.mysql.com/bug.php?id=21153
  4. https://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/controlling-optimizer.html?

Description

Looks like, it was combination of two bugs (1) and (2). It seems that the current implementation of search query by tags, with dynamic (depends on amount of selected tags' categories) combination of 'IN ()' clauses where greater than 2 tags and 'INNER JOIN' clauses, leads to performance degradation of MySQL query optimizer.

Solution (in progress)

  1. Replace INNER JOIN with INTERSECT (MySQL 8.0.31).
  2. Replace IN (<values>) with UNION DISTINCT.
  3. Add suitable indexes (with help of EXPLAIN ANALYSE).
Improve this question
2
  • And the question is = why does it happen or how to improve it?
    – Rohit Gupta
    Feb 24 at 11:33
  • Show us the full SELECT and the CREATE TABLE for any table in that query.
    – Rick James
    Feb 25 at 1:19

1 Answer 1

Reset to default
0

A query that spends a long time is "statistics" may be, for example, a an accidental CROSS JOIN (a Join with no ON cluase). Or it may be a query that is being continually blocked by lots of other queries writing to the same table(s).

Check not only the slow query, but also all the other queries running at the same time.

If you don't have the slowlog turned on with a low value for long_query_time, do so. (However, the log won't show that long-running query until it finishes.)

Also provide SHOW VARIABLES LIKE '%timeout'

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.