MySQL 5.1.4x (Windows) | Innodb
I recently purged data from a mySQL DB (a few hundred thousand rows) and I'm planning to use
OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;
to reduce the data space footprint the empty whitespace is taking up in the filesystem.
My concern is that once I start this command, I won't know how long it's going to take or where it is in the process. Is there anyway I can determine this information? As far as I know, there's no progress indicator.
Best Answer
There is nothing MySQL has that will indicate the progress of
OPTIMIZE TABLE;
.You may have to go the OS using Windows Explorer and look for a growing tmp table. Hint: Temp table are MyISAM tables that do not have a .
frm
file with the name#sql-9999.MYD
and#sql-9999.MYI
. While these two fle exist, the query would be considered in progress. To know how far, one would have to know how many rows times the average row length to estimate the percentage done or left to be done.MariaDB has some progress metering for the following:
ALTER TABLE
ADD INDEX
DROP INDEX
LOAD DATA INFILE
CHECK TABLE
REPAIR TABLE
ANALYZE TABLE
OPTIMIZE TABLE
I have discussed subject before in terms of MySQL operations
May 02, 2012
: How can I monitor the progress of an import of a large .sql file?Jan 17, 2012
: Adding an index very slow...is there a mysql cmd to get an ETA or show progress?If you are trying to reduce the size of ibdata1 for InnoDB, dropping tables simply leaves big gaps in ibdata1. You must perform a full Cleanup of InnoDB Infrastructure.
Apr 01, 2012
: Is innodb_file_per_table advisable?Mar 25, 2012
: Why does InnoDB store all databases in one file?Mar 29, 2011
: Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDBFeb 04, 2011
: MySQL InnoDB - innodb_file_per_table cons?Oct 29, 2010
: Howto: Clean a mysql InnoDB storage engine?UPDATE 2012-12-21 19:30 EDT
Let's say you want to run
Look at two scenarios involving innodb_file_per_table and this
OPTIMIZE TABLE
SCENARIO #1 : innodb_file_per_table disabled
Running
OPTIMIZE TABLE
will cause all the data and index pages formydb.mytable
to be appended contiguously to ibdata1. This will leave the space previously occupied to add further to the fragmentation madness.In this scenario, the temp table would exist as InnoDB, and it would materialize in ibdata1. Thus, there would be nothing to visually monitor.
SCENARIO #2 : innodb_file_per_table enabled
For each InnoDB table mydb.mytable you would have
The
.ibd
file contains the data and index pages for the table.Running
OPTIMIZE TABLE
will cause all the data and index pages formydb.mytable
to be written to an external.ibd
file that you can see in Windows Explorer.CAVEAT
Enabling innodb_file_per_table and running
OPTIMIZE TABLE
will never reclaim the space formerly occupied within ibdata1. You will have to carry out the InnoDB Cleanup to recreate ibdata1 in such a way that data and indexes never, ever again reside in ibdata1.CONCLUSION
As stated earlier, MariaDB can monitor
OPTIMIZE TABLE;
If you want something you can monitor and you have innodb_file_per_table enabled, you can replace
with the following mechanical equivalent:
Please keep in mind the OPTIMIZE TABLE is nothing but
ALTER TABLE ... ENGINE=InnoDB;
followed by ANALYZE TABLE.With these steps, you know in advance what the temp tables are
LOGTABLEFOO1_NEW.ibd
LOGTABLEFOO2_NEW.ibd
LOGTABLEFOO3_NEW.ibd
Simply Monitor Each File's Existence and Size Until it Disappears.