Querying Information_Schema on MySQL (Azure) takes too long

Question

Issue is reading information schema from MySql ... too slow:

Steps:

1. MySql dump created manually by scripting all objects from existing
   MySql db which is on AWS. (MySql Version 5.7)
2. Restored dump file on to Azure MySql (Version 5.7)
3. Re-pointed Application (Appian) which sits on AWS to newly created MySql db on Azure.
4. When tried to validate Application (Appian) Objects (Data Stores), which reads `Information_Schema` takes too long or gets timed out.
5. But reading data from the tables are faster.

As per suggestions checked innodb_stats_on_metadata, this is set to OFF.

Still no luck! Any suggestions?

After bit of investigation found that the following query is the one which is slow.

SHOW FULL TABLES FROM `database` LIKE 'xyz'

Most of the time its doing is 'checking permissions'

Answer 1

Response from Microsoft:

Command show full tables on Azure MySQL 5.7 is slower than expected after confirmed with product engineering. Per test, it will take 800ms to complete when only 1 connection thread with 1500 tables, response time will be increased accordingly when more tables stored and parallel connections come.

Regarding to this issue, there are 2 possible ways we could try right now:

1. Keep using MySQL 5.7, next release (The end of Jan,2020)will mitigate this issue by reducing response time to 50ms with 1500 tables, 1 thread.

2. Switch to MySQL 8.0 version as 8.0 doesn’t have operation like “list all of directories” when running show full tables. Currently 8.0 is in publish preview, the tentative GA is the end of Jan, 2020 as well.