# information\_schema slow queries

**URL:** <https://forums.percona.com/t/information-schema-slow-queries/7932>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [August 20, 2020, 9:33am UTC](https://forums.percona.com/t/information-schema-slow-queries/7932 "2020-08-20T09:33:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![shahidbashir7861](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shahidbashir7861/32/4908_2.png) [@shahidbashir7861](https://forums.percona.com/u/shahidbashir7861)\
**Post date:** [August 20, 2020, 9:33am UTC](https://forums.percona.com/t/information-schema-slow-queries/7932/1 "2020-08-20T09:33:06Z")

</div>

Hello,&nbsp;  
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.&nbsp;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.&nbsp;  
  
  
SELECT \* FROM information\_schema.key\_column\_usage AS kcu  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INNER JOIN information\_schema.referential\_constraints AS rc  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ON (  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; kcu.CONSTRAINT\_NAME = rc.CONSTRAINT\_NAME  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND kcu.CONSTRAINT\_SCHEMA = rc.CONSTRAINT\_SCHEMA  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE kcu.TABLE\_SCHEMA = ‘db\_name1’ AND kcu.TABLE\_NAME = ‘table\_name1’ AND rc.TABLE\_NAME = ‘table\_name2’;  
  
  
  
  
  
Thanks.&nbsp;

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 26, 2020, 6:00am UTC](https://forums.percona.com/t/information-schema-slow-queries/7932/2 "2020-08-26T06:00:07Z")

</div>

What user generates this query ?&nbsp;  
Googling around it looks like it may come from the framework you use:  
[Craft CMS super slow every now and then; information\_schema problems? · Issue #4037 · craftcms/cms · GitHub](https://github.com/craftcms/cms/issues/4037)  
  
Information Schema queries executed locally in Percona XtraDB Cluster - so it is same as in MySQL.&nbsp; Where Information Schema queries are known to be slow in MySQL 5.x especially with large number of tables.&nbsp; &nbsp;MySQL 8 and Percona XtraDB Cluster 8 are much faster in this regard

---

<div class="post-metadata">

**Author:** ![shahidbashir7861](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shahidbashir7861/32/4908_2.png) [@shahidbashir7861](https://forums.percona.com/u/shahidbashir7861)\
**Post date:** [August 26, 2020, 11:35am UTC](https://forums.percona.com/t/information-schema-slow-queries/7932/3 "2020-08-26T11:35:12Z")

</div>

Hey Peter,&nbsp;  
  
Thanks for checking.&nbsp;  
  
I finally found the issue and it seems to be related to&nbsp;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.&nbsp;It seems that information\_schema&nbsp;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.&nbsp;  
  
  
  
Thanks.&nbsp;