Performance issue for the MySQLSchema doEmpty() call

[QuidamAzerty]

Which version and edition of Flyway are you using?

5.0.7.
But it's irrelevant as it's still in the last version of the file

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java via Maven plugin

Which database are you using (type & version)?

MySQL 5.6 and 5.7

Which operating system are you using?

Linux. I don't put more info on that because it's (a priori) not relevant here.

Usage context

We have 1086 schema. In these schema, ~1000 have the same structure with 845 tables and views.
So our information_schema is a little heavy.

What did you do?

flyway.migrate() on a non Flyway migrated yet db (and so with baselineOnMigrate to true).

What did you expect to see?

Relatively fast first migration.

What did you see instead?

Relatively slow first migration.

Why ?

The db has not been migrated with Flyway yet. So schemaHistory is empty.
And, by following the Flyway process from here, a schema.empty() is called, which call the doEmpty() of MySQLSchema (in our case).
But the doEmpty() is done via the information_schema. And takes approximately 1min to do this method in our case.

So ? What is the question ?

Why did you not use the SHOW methods ?
They are much more efficient than go through the information_schema.
And another question would be is this performance problem can also exist on the other Schema children ?

Thanks for the reading and have a great day !

[axelfontaine]

I don't believe using SHOW will improve performance as that will most likely use the information_schema behind the scenes anyway.

What definitely should be done though is replacing those SELECT COUNT(*) with either EXISTS or SELECT 1 ... LIMIT 1.

[QuidamAzerty]

Seems legit.

If you wish, I can try the query beforehand.
What takes time on the current query is the Select count(*) from information_schema.TRIGGERS Where TRIGGER_SCHEMA=?

[axelfontaine]

Yes, could you try:

Select 
(Select 1 from information_schema.TABLES Where TABLE_SCHEMA=? limit 1) + 
(Select 1 from information_schema.VIEWS Where TABLE_SCHEMA=? limit 1) + 
(Select 1 from information_schema.TABLE_CONSTRAINTS Where TABLE_SCHEMA=? limit 1) + 
(Select 1 from information_schema.EVENTS Where EVENT_SCHEMA=? limit 1) + 
(Select 1 from information_schema.TRIGGERS Where TRIGGER_SCHEMA=? limit 1) + 
(Select 1 from information_schema.ROUTINES Where ROUTINE_SCHEMA=? limit 1)

Don't forget to replace ? with your actual schema name.

[QuidamAzerty]

The limit does not change the performance.
The query stay still in a checking permissions state for a long time when I execute it in production environment with a readonly account.
In a local environment, I don't have consistent change between the two queries (with and without the limit 1).
EDIT: forgot word that changes sentence meaning: "not" --"

[axelfontaine]

This has now been fixed.

The new query is

Select SUM(found) from(
       (Select 1 as found from information_schema.TABLES Where TABLE_SCHEMA='flyway_db' limit 1) union all
       (Select 1 as found from information_schema.VIEWS Where TABLE_SCHEMA='flyway_db' limit 1) union all
       (Select 1 as found from information_schema.TABLE_CONSTRAINTS Where TABLE_SCHEMA='flyway_db' limit 1) union all
       (Select 1 as found from information_schema.EVENTS Where EVENT_SCHEMA='flyway_db' limit 1) union all
       (Select 1 as found from information_schema.TRIGGERS Where TRIGGER_SCHEMA='flyway_db' limit 1) union all
       (Select 1 as found from information_schema.ROUTINES Where ROUTINE_SCHEMA='flyway_db' limit 1)) as all_found;

[golbi]

@axelfontaine I hit this issue on MySQL 5.7 using a Flyway version that contains the above mentioned fix. I believe it is not really solving the problem due to one specific "feature" of information_schema in mysql 5.x. As you can see here https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html (see the table there) the problem is around TRIGGERS query. With where on TRIGGER_SCHEMA, it scans all databases (confirmed with EXPLAIN). E.g. in my case it takes 45s, while I have 0 triggers, but around 2k DBs.
Using EVENT_OBJECT_SCHEMA in where helps but I'm not sure if this is what you want to check here(?) SHOW TRIGGERS is also fast.