Re: Performance bottlenecks with thousands of small databases

[Rick James]

> My biggest question is if I flip innodb_file_per_table to OFF, what is the migration strategy to convert all existing databases and tables?

With it OFF, any newly CREATEd TABLE will be put into common ibdata1 file. There will be only a .frm file on disk. (ON implies a .frm and a .ibd file.) This cuts back somewhat on the number of files.

However, no existing tables are changed by doing that SET. To change one table with a .frm and .ibd to OFF, this does the job (once you set OFF):
ALTER TABLE tablename ENGINE=InnoDB;
That is logically a no-op, but it will move the data and get rid of the .ibd. You have a lot of files; so this will take a lot of work. I suggest you write a SELECT CONCAT("ALTER TABLE ", TABLE_NAME, " ENGINE=InnoDB;\n")
FROM information_schema.TABLES
WHERE ...;
to generate all the needed ALTERs. Or it might be possible to do the SELECT plus the ALTERs in a stored procedure.

I would not convert the bigger tables tables (many megabytes). See the columns DATA_LENGTH + INDEX_LENGTH.

This conversion may also free up a lot of disk space.