0

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'

Sreedhar
  • 29,307
  • 34
  • 118
  • 188
  • Did you ANALYZE TABLE tbl_name; for each table restored on Azure MySQL 5.7? After ANALYZE all indexes will be current. – Wilson Hauck Dec 19 '19 at 21:01
  • Additional information request from Azure MySQL. RAM size, # cores, any SSD/NVME devices on MySQL Host server? Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) SHOW ENGINE INNODB STATUS; G) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server workload tuning analysis to provide suggestions. – Wilson Hauck Dec 19 '19 at 21:14
  • @Wilson: Will update with requested details soon. I found most of the time was being consumed my the query SHOW FULL TABLE FROM LIKE for execution it is checking permission and this is slow. – Sreedhar Dec 19 '19 at 21:43

1 Answers1

0

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.

Sreedhar
  • 29,307
  • 34
  • 118
  • 188