1

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

3) How does max_tmp_table_size play into this?

carrieandrsn
  • 11
  • 1

2 Answers2

1

It sounds like you painted yourself into a corner by not running innodb_file_per_table from the start, so now you have a huge, unshrinkable ibdata1 file.

1) There isn't.

1.1) It might reduce the overall space usage by rebuilding the tables outside the ibdata1 file, then rebuild them again to inside ibdata1, reusing some of the unused space inside ibdata1

2) Yes:

SELECT TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES;

3) It doesn't. The tables you are seeing are probably tables being rebuilt for some reason (not sure why, I have to admit I haven't seen that happen from mysql_upgrade before). max_tmp_table_size is only for implicit (when a query plan says using temporary) and explicity (CREATE TEMPORARY TABLE ...) temporary tables, not for table rebuilds.

Gordan Bobić
  • 1,748
  • 13
  • 16
  • Thanks. The tables that are getting rebuilt have a lot of timestamps, 1-2 per line. – carrieandrsn May 12 '20 at 19:03
  • At some point, date formats and something else changed, thereby necessitating a rebuild of relevant tables. Also re-indexing may have been involved (and appear to be a "table rebuild"). – Rick James Jun 01 '20 at 20:27
0

The only(?) way to switch to file_per_table without the disk bloat is

  1. Dump the data.
  2. Get a fresh install (or otherwise get rid of ibdata1).
  3. Reload (with file_per_table on).
Rick James
  • 135,179
  • 13
  • 127
  • 222