Sudden CPU idle alarm caused by shardingshpere-proxy

[xiaoyuge]

Question

Today I encountered a CPU idle alarm issue caused by shardingshpere-proxy. I suddenly received a CPU monitoring alarm in the production environment, and the CPU usage rate exceeded the threshold. So, I logged into the production environment machine and used the top command to view the results, which are as follows:

From the graph, it can be seen that the CPU utilization rate of shardingshper proxy has reached 790.7%.
So, I further examined which threads had higher CPU usage, as shown in the following figure:

Then, based on this information, I further examined the thread stack information of these high CPU occupied threads, as follows:

From the thread stack information, it seems that some kind of SQL optimization is being done. May I ask, what are these threads specifically doing? How to avoid high CPU usage caused by this type of thread?

ShardingShpere-proxy version is 5.3.2
Mysql version is 5.7.37

[strongduanmu]

@xiaoyuge Did you use the experimental SQL Federation feature?

[xiaoyuge]

@strongduanmu I have checked my configuration files,i didn't use the SQL Federation feature.Is there any place to enable this feature?

[strongduanmu]

SQL Federation is disabled by default. From your description, you did not enable this feature. In addition, SQL queries for system tables will be executed through SQL Federation by default. Please check whether there are too many system table queries.

SELECT * FROM informartion_schema.tables;

[xiaoyuge]

Based on the information you provided, I searched for the logs of shardingshpere-proxy using the keyword "information_schema" and found the following abnormal log information:

The start time of this abnormal log is "2024-07-11 09:42:47.420" and the end time is "2024-07-11 10:32:51.998". The reason why it ended is because I restarted shardingshpere-proxy。

This time period coincides very well with the start and end time periods of CPU alarms, as shown in the following figure:

So, it can be basically confirmed that the CPU alarm is related to this abnormal log. So, please help to see what caused these abnormal logs to be generated?

[strongduanmu]

ShardingSphere does not fully support the information_schema system table, so there are some situations that cannot be supported. Usually, information_schema queries are low-frequency, so I suggest you check which business is frequently querying the system table.

[xiaoyuge]

There is no place in my business application to query the information_schema table.
However, during the development process, our developers will use the Navicat client to connect to Shardingsphere proxy for queries.
I have observed the logs of Shardingsphere-proxy, and when using the Navicat client to double-click the table name for querying, the following log information will be output:

I executed the same table query statement 'show table status like gk_order_local_life' in the query window of navicat, and even encountered an error, as shown below:

If I execute the query statement 'SELECT * FROM gk_order_local_life LIMITED 0,1000' in navicat, it will also report an error, as follows:
exe-sql-error-log.md
It seems that using navicat to query the proxy will result in frequent queries for information_schema and various unknown exceptions?