We are in the process of migrating our databases from an MSSQL Server AWS RDS instance onto an AWS MySQL 8.0.17 RDS instance
Our web application uses ORM/hibernate for database interactions, with 1 application tied to 1 database
the database server currently contains 172 db's, with roughly 260 tables per db (44,479 tables in total), setup with 1 user with access to all db's (there are only 4 other system/aws users returned from "select * from mysql.user")
the process of starting up an application requires ORM to inspect the informaton_schema
unfortunately, this is currently taking over 10 minutes to start a single web application, as the MySQL database seems to be struggling with accessing the information_schema, usually stuck on the status "checking permissions" for upto 5 seconds and also seems to be performing hundreds of these lookups per database
on our staging server, the same web application started up in under a minute, due to only having 8 db's rather than 172
we have since recreated the same slowness issue by adding the additional 164 db's to the staging server, indicating that the issue lies with the number of db's/tables on the server
we've already applied the below settings but this hasn't improved the performance:
innodb_stats_on_metadata=0 innodb_stats_persistent=0
Does anyone have any ideas on how we can optimise MySQL further to get the desired performance.
Any help / advice to speed up our schema queries is greatly appreciated
---- More information ----
Thanks for your replies. As requested, Pastebin URL's are below for more information
Global Status - pastebin.com/Je40S48C Show Variables - pastebin.com/FaN66Zrn
In terms of RAM, the above is taken from a staging server, which is an RDS instance (db.t3.small) so only has 2Gb RAM and 2 vCPU. I am only trying to connect to 2 databases though and the rest are dummy to mimic a live number of tables. We initially noticed this on a db.r5.4xlarge which has 128 Gb RAM and 16 vCPU so i am confident memory or CPU is not an issue. Once our application is up and running