0

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?

  • This question has nothing to do with sw development, this is an admin question, therefore it is off topic here on SO. The DBA sister site of SO may offer more help. – Shadow Sep 5 at 5:36
  • Are there a lot of files like mysql-bin.000*? – tombom Sep 5 at 6:31
  • There are no mysql-bin.000* files. I updated my question to show the content of my /var/lib/mysql folder. The directories starting with db* represent my DBs and contain only a *.ibd and *.frm file for each table. – Rakantor Sep 5 at 7:15
0

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.

0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.