MySQL DBs taking up way too much disk space

Question

I'm on Ubuntu Server 18.04 running MySQL server 5.7.27 with InnoDB. I confirmed that InnoDB file-per-table is On. I have a mysqldump containing all of my DBs that takes up about 1.3 GB disk space.

After I imported the backup with sudo mysql -u root < myfile.sql, I noticed that there's significantly less space left on my drive. I checked the disk space usage of the newly created DBs in /var/lib/mysql and their combined size is about 14 GB.

I ran sudo mysqlcheck -o --all-databases -u root to optimize the tables, but it only reduced the size to 11 GB:

root@ip-x-x-x-x:/var/lib/mysql# du -sm * | sort -nr
1468    dbvwgcayak57wx
1468    dbmuj7j8scnzt6
1468    dbmmqtf5sss9pk
1468    dbf9ynyscjzw83
1468    dbdr5hk7kvsh73
1468    db3mj8b7b5ezuw
1468    db22z4jcfbf9yn
544 db7b5ezuwb8ca4
76  ibdata1
48  ib_logfile1
48  ib_logfile0
35  dbbz9e6hcmqbtb
23  db688zwgk7uvdh
19  mysql
12  ibtmp1
3   phpmyadmin
2   performance_schema
1   sys
1   ib_buffer_pool
1   dbzqcfb9scuv3h
1   dbscjzw83rv253
1   auto.cnf
0   debian-5.7.flag

All my large DBs contain several hundred tables that are small in size (less than 2 MB according to phpmyadmin), but they take up a lot more actual disk space (about 8 MB).

What else can I do to reduce disk space?

Answer 1

I solved this issue by setting innodb_file_per_table = 0 in my MySQL config (/etc/mysql/mysql.conf.d/mysqld.cnf) and re-importing the mysqldump after dropping all databases. The ibdata1 file that contains all DB data is just about 2.4 GB.

root@ip-x-x-x-x:/var/lib/mysql# du -sm * | sort -nr | head -15
2445    ibdata1
48  ib_logfile1
48  ib_logfile0
12  ibtmp1
11  dbvwgcayak57wx
11  dbmuj7j8scnzt6
11  dbmmqtf5sss9pk
11  dbf9ynyscjzw83
11  dbdr5hk7kvsh73
11  db3mj8b7b5ezuw
11  db22z4jcfbf9yn
4   db7b5ezuwb8ca4
1   phpmyadmin
1   mysql
1   ib_buffer_pool

Seems like having a *.ibd file for every single table can actually be disadvantageous if your DB contains a lot of small tables.

Answer 2

It may take less space to put small tables in ibdata1 by having innodb_file_per_table = OFF. Try this on one of the databases:

SET GLOBAL innodb_file_per_table = OFF;
logout and login
SHOW VARIABLES LIKE 'innodb_file_per_table';  -- to verify
check the size of ibdata1
USE db;
ALTER TABLE table1 ENGINE=InnoDB;
ALTER TABLE table2 ENGINE=InnoDB;
...
compare the size of ibdata1
`du` on the db directory

The 8MB is due to the way that space is pre-allocated. The table is right in the boundary from small-and-compact versus big-so-preallocation-does-not-matter-percentagewise.

You might consider having a tablespace-per-database. See "general tablespaces". The migration code would be similar to the above.

Please provide SHOW CREATE TABLE for a typical table; there may be other techinques to apply. ALso, if most of the tables are 'identical', I would strongly suggest using a single table rather than spreading the data across identical tables. Again, the SHOW will help in discussing this further.

Oops, now I see your answer wherein you did effectively what I am suggesting. Still...

• Do the largest few tables with file_per_table. This will facilitate admin tasks.
• Consider having a tablespace per database, especially if you will be eventually dropping a database (since they smell identical).