2

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5

We have always(I am sure)

innodb_file_per_table = ON 

option in our mysql config. So I drop big table with confidence that file that represent this table will be deleted and disk space will be freed immediately, seems to be this file was deleted(this file not exist in DB directory in datadir, all other DB tables files exist), but in munin(disk usage) graph I don't see that disk space get free(my bad that I didn't notice disk usage before dropping table). Also ibdata1 seems to be is quite big for this situation(~ 200 GB).

  1. There is way to get some additional info about old table (that was dropped) ?
  2. There is some way to reduce disks usage without dumping and then restoring DB, maybe create new table with old_name(that was dropped) and try to make ALTERING ?

UPDATE:

1) ~100-110GB, or 20% of total disk space(ssd disk)

2) files_ | 124151382016

3) 10 tables, but only 1 is big table

4) Prehistory: we had big table(busy, inserts/updates/reads) that continue growing up, but some data in this table became old, and must be deleted, so we run delete queries in infinity loop( DELETE FROM files WHERE condition LIMIT 1000-100000, I don’t know how much rows were deleted with this method, but Innodb_rows_deleted was approx. ~600kk, and Innodb_rows_inserted nearly 1 billion or so) we deleting at night when load is very low — after we calculate that deleting takes too much time, so we use persona pt-archiver to move(only data that we need) from old table to new table(files_), and then DROP old table.

Yes, old one table had key constraints to separate table — “ON DELETE CASCADE ON UPDATE CASCADE”(in new table we have not constraints), also was in old table(and in current table)UNIQUE KEY on some table field. I am also now making backup with xtrabackup of current db to check what takes much space in ibdata1 file with innodb_space app. Seems to be only 1 possible way — to make logical backup, and then import, sql file is 40GB, so it would be not fast despite that server pretty powerful(128GB ram).

Thank you!

  • 1
    Have you by any chance enabled innodb_file_per_table after you created the table in question? – tombom Jun 1 '15 at 11:07
  • No, it always was in my config file and it was before creating table. (also tables that were created at same time represented in table-files). – user2746626 Jun 1 '15 at 12:21
  • I Have 4 Questions: 1) How much free space is on the disk right now ? 2) Run SELECT table_name,(data_length+index_length) table_size FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length) DESC LIMIT 1;. Based on this, what is the size of your biggest table ? 3) Run SELECT COUNT(1) FROM information_schema.tables WHERE engine='InnoDB'; How many InnoDB Tables are there ? 4) Do any tables have foreign key constraints ? – RolandoMySQLDBA Jun 1 '15 at 15:41
  • Supplemental Question: Did you run DROP TABLE mytable; only or did you run DELETE FROM mytable; first, then DROP TABLE mytable; ? – RolandoMySQLDBA Jun 1 '15 at 15:46
  • Hi @RolandoMySQLDBA! I was waiting for your reply, I read many you answers. Question was updated with details. – user2746626 Jun 1 '15 at 16:15
0

I would recommend you to run the following

mysqlcheck -u ROOTUSERNAME -p --auto-repair --optimize DBNAME_HERE

This will prompt for MySQL Root Password, type in and it will reclaim the disk space. Time taken for this process will depend on the size of the database. You may try this first in your test env and then proceed.

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.