Update to MySQL 5.7 and high disk space consumption Using: MySQL 5.6 on Debian 9, total DB size is around 450GB

Updated to 5.7, ran mysql_upgrade, noticed that around 150 GB has been taken up. 2 tables are really large and they stayed in 'copying to tmp table' for a couple of hours

Noticed innodb_file_per_table was on and created large ibd files that weren't there previously.

Restored from a snapshot, disabled the file_per_table, ran mysql_upgrade again. 100GB gone, which is almost 1/4 of my total DB.

In the first case, it pulled the data from ibdata and put it into a separate file but ibdata never shrinks so taken space almost doubled.

What happens in the second case? Does the temp table get created within the ibdata file that never shrinks so even when table is not used anymore - space is still gone?

Another thing I noticed is that space consumption doesn't start until query has been in copying to tmp table status for like an hour or so.

1) Is there any way to avoid/minimize space increase?

Would running update with file_per_table on, then disabling it and running alter table engine innodb free up the space?

2) Any way to predict how much space will be occupied? At least per table
ibdata1 never shrinks.
.ibd files never shrink.
When a table is in ibdata1 and is ALTERed, space in that file is temporarily consumed, thereby possible stretching the file.
With .ibd, an ALTER will probably create a new copy, then toss the old one, thereby temporarily using diskspace, then releasing it.
There are too many variables to make any predictions.  But a fairly safe rule is to never fill up more than half the disk.


The best thing going forward is rather painful:


Dump all the tables
Toss ibdata1 (or make a fresh install)
Turn on innodb_file_per_table
Reload the dump file.


That will 


leave a small ibdata1 file, which is necessary for meta data, etc.
Create each table in its own .ibd file.
Allow future ALTERs to only temporarily consume extra disk space.


Another thing to note:  innodb_file_per_table (ON or OFF) applies only to tables CREATEd or ALTERed after that.  All existing tables say either in ibdata1 or .ibd until such an action is taken.  Correlary:  It is possible to have some tables in ibdata1 and some in separate .ibd files.  It may be beneficial to put small tables in ibdata1 (OFF) and large ones in .ibd files (ON).