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.
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)
- Add suitable indexes (with help of