Partitioning and freeing disk space

Question

I am running MySql innodb with innodb_file_per_table.

As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need to be run to delete the old partitions to free up the disk space? I would like to avoid having a system that runs out of disk space because of the database disk usage. I would also like to avoid having to shut down Mysql.

Edit: I have read that discarding the tablespace should delete the .ibd files that take up most of the space.

ALTER TABLE tbl_name DISCARD TABLESPACE;

However there is a known bug using this command on partitioned tables.

Answer 1

The status of the bug is labeled as Won't fix and I can understand why in this context.

The right way would have been to run ALTER TABLE tbl_name DROP PARTITION; because the MySQL Documentation on ALTER TABLE Partition Operations says:

DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded.

ALTER TABLE t1 DROP PARTITION p0, p1;

Once you ran ALTER TABLE tbl_name DISCARD TABLESPACE;, the data dictionary should no longer be aware of the physical presence of the orphaned .ibd files. However, since the bug does not reclaim the diskspace, perhaps the data dictionary entries for your tables is intact. Let's find out.

WARNING : You should run service mysql stop and do a physical backup of /var/lib/mysql

First do this: SHOW CREATE TABLE tbl_name\G

Then, run SELECT * FROM tbl_name WHERE (stuff from partition #x) LIMIT 10;

to test the table's usability of each partition.

Once you are confortable with the table usability, you can any of the following:

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?

Answer 2

I am not sure there is a 'smart' online space reclaim capability. I believe you will need to take a mysqldump of your database, and other items like procedures, then delete the database, stop Mysql, delete the data and logfiles for your database, start the database, and then restore from dump.