Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
+1 for 'like to avoid shutting down or running out of space to reclaim space' you idealistic mysql dreamer ! I'm no Mysql expert, but have you run the OPTIMIZE table ? this is from the MYSQL manual. Use OPTIMIZE TABLE in these cases, depending on the type of table: After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system. –  DamagedGoods May 20 '13 at 16:01

2 Answers 2

up vote 0 down vote accepted
+50

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:

share|improve this answer
    
I was able to get the disk space back from my partitions by using DROP PARTITION, however my ibdata1 file remains the same size. Is it possible to lower the disk space usage of that file without having to stop Mysql? –  sworded May 20 '13 at 21:35
    
Updated my answer... –  RolandoMySQLDBA May 20 '13 at 22:44
    
Thank you for accepting my answer. Do I get the bounty as well ??? –  RolandoMySQLDBA May 22 '13 at 16:16

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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