I have 3 node Percona XtraDB cluster ( with one node as arbitrator garb ). Our cluster was behaving slow for sometime and we came to know that there are several slow queries run related to information_schema which are taking time. We are trying to find out why these queries take place. Finally to sort it out, we stopped all nodes and then did the bootstrap from node1 and brought back remaining nodes. This sorted our the issue on the node1 that I bootstrapped but it seems it is still happening on other node. It seems like there is some stats or cache that gets refreshed related to information_schema database that does the trick. I want to understand its detail. innodb_stats_on_metadata is set to OFF already. Following is an example query from several queries that we start to see under load in our servers.
SELECT * FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = ‘db_name1’ AND kcu.TABLE_NAME = ‘table_name1’ AND rc.TABLE_NAME = ‘table_name2’;
What user generates this query ?
Googling around it looks like it may come from the framework you use:
Information Schema queries executed locally in Percona XtraDB Cluster - so it is same as in MySQL. Where Information Schema queries are known to be slow in MySQL 5.x especially with large number of tables. MySQL 8 and Percona XtraDB Cluster 8 are much faster in this regard
Thanks for checking.
I finally found the issue and it seems to be related to GRA_13_64138256.log files created due to failure of a query. I found that there are several thousand log files like that in my datadir and once I deleted them, the information_schema queries stopped to appear in slow query logs as well. It was not related to cluster configurations but individual MySQL operations as I had to remove these files from each node to make it respond faster. It seems that information_schema started to kept track of some kind on these log files and large number of these files kept on slowing down information_schema related operations. It would be great if you could add your experience on this.