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?
Best Answer
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.Seems like having a *.ibd file for every single table can actually be disadvantageous if your DB contains a lot of small tables.