Mysql – Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

drop-tableinnodbMySQLmysql-5.5

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

There is way to get some additional info about old table (that was dropped) ?
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!

Best Answer

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.

REMOVE ANY PARTITION YOU WANT

ALTER TABLE tbl_name DROP PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name DROP PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
ALTER TABLE tbl_name2 DROP PARTITION partition-name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

DROP ALL DATA FROM PARTITION BUT KEEP PARTITION

ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

CREATE A BLANK TABLE

CREATE TABLE tbl_name2 LIKE tbl_name;
DROP TABLE tbl_name;
ALTER TABLE tbl_name2 RENAME tbl_name;

Give it a Try !!!

UPDATE 2013-05-20 18:41 EDT

I am glad you were able to drop the partition.

I have some distressing news: It is not possible to shrink ibdata1 without dumping all the data, deleting ibdata1, restarting mysql, and reloading.

Here are my past posts on how to do that safely:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

Mysql – DB disk space requirements comparision between MySQL versions

One thing must be noted that you probably never realized: The setting of the option innodb_file_per_table will determine how much space is inside each database folder.

First, take a look at the InnoDB Architecture

InnoDB Architecture

Note the system tablespace (which was know is named ibdata1 in the OS).

In MySQL 5.0, the default value for innodb_file_per_table is 0 or OFF
In MySQL 5.6, the default value for innodb_file_per_table is 1 or ON
With innodb_file_per_table disabled (default in 5.0), the data and index pages for all InnoDB tables are stored inside ibdata1. When you create the table mydb.mytable, you get the following:
- /var/lib/mysql/mydb/mytables.frm
- Data and index pages for mydb.mytable are inside ibdata1
With innodb_file_per_table enabled (default in 5.6), the data and index pages for all InnoDB tables are outside ibdata1. When you create the table mydb.mytable, you get the following:
- /var/lib/mysql/mydb/mytables.frm
- Data and index pages for mydb.mytable are stored in /var/lib/mysql/mydb/mytables.ibd

SUMMARY

Without setting innodb_file_per_table and just storing data

Databases under MySQL 5.0 will have nothing but .frm files for its InnoDB tables. Thus, you only have kilobytes of files in each database.
Databases under MySQL 5.6 will have .frm and .ibd files for its InnoDB tables. Thus, you will have megabytes or gigabytes of files in each database.