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 only takes a minute to sign up.
Sign up to join this community
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.
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 TABLEADD INDEXDROP INDEXLOAD DATA INFILECHECK TABLEREPAIR TABLEANALYZE TABLEOPTIMIZE TABLEI 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?Since this database uses innodb, will there be a growing temp table I can look for?
Let's say you want to run
OPTIMIZE TABLE mydb.mytable;
Look at two scenarios involving innodb_file_per_table and this OPTIMIZE TABLE
Running OPTIMIZE TABLE will cause all the data and index pages for mydb.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.
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 for mydb.mytable to be written to an external .ibd file that you can see in Windows Explorer.
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.
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
OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;
with the following mechanical equivalent:
CREATE TABLE LOGTABLEFOO1_NEW LIKE LOGTABLEFOO1;
INSERT INTO LOGTABLEFOO1_NEW SELECT * FROM LOGTABLEFOO1;
ALTER TABLE LOGTABLEFOO1 RENAME LOGTABLEFOO1_OLD;
ALTER TABLE LOGTABLEFOO1_NEW RENAME LOGTABLEFOO1;
DROP TABLE LOGTABLEFOO1_OLD;
ANALYZE TABLE LOGTABLEFOO1;
CREATE TABLE LOGTABLEFOO2_NEW LIKE LOGTABLEFOO2;
INSERT INTO LOGTABLEFOO2_NEW SELECT * FROM LOGTABLEFOO2;
ALTER TABLE LOGTABLEFOO2 RENAME LOGTABLEFOO2_OLD;
ALTER TABLE LOGTABLEFOO2_NEW RENAME LOGTABLEFOO2;
DROP TABLE LOGTABLEFOO2_OLD;
ANALYZE TABLE LOGTABLEFOO2;
CREATE TABLE LOGTABLEFOO3_NEW LIKE LOGTABLEFOO3;
INSERT INTO LOGTABLEFOO3_NEW SELECT * FROM LOGTABLEFOO3;
ALTER TABLE LOGTABLEFOO3 RENAME LOGTABLEFOO3_OLD;
ALTER TABLE LOGTABLEFOO3_NEW RENAME LOGTABLEFOO3;
DROP TABLE LOGTABLEFOO3_OLD;
ANALYZE TABLE LOGTABLEFOO3;
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.ibdLOGTABLEFOO2_NEW.ibdLOGTABLEFOO3_NEW.ibdSimply Monitor Each File's Existence and Size Until it Disappears.
There is a simpler way, if you have access to the file system.
MySQL generates a .TMD file; you can watch the size of the file growing; it should reach more or less the size of your existing MYD when it is done.
For instance, if you cd to your datadir, and simply watch "ls -lah|grep table_name" which will return something like this (on Linux).
In my case, the progress is 287 MB out of 427 MB (roughly).
-rw-rw----. 1 mysql mysql 427M Feb 19 23:45 ticket_change.MYD
-rw-rw----. 1 mysql mysql 541M Feb 20 01:10 ticket_change.MYI
-rw-rw----. 1 mysql mysql 287M Feb 20 01:45 ticket_change.TMD
Percona Toolkit's pt-online-schema-change does this for you.
Change mydb.LOGTABLEFOO1 to InnoDB, effectively performing OPTIMIZE TABLE in a non-blocking fashion because it is already an InnoDB table
pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=LOGTABLEFOO1
You can watch the size of the table by using this SQL from another unrelated MySQL docs:
SELECT NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.TABLES
WHERE NAME = 'scheme/table_name';
remember the value of fields FILE_SIZE and/or ALLOCATED_SIZE(they will vary in a large range when the table is using InnoDB Page Compression) in its output before starting to execute OPTIMIZE TABLE:
| NAME | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
|---|---|---|---|
| scheme/table_name | 4096 | 9776922624 | 4889554944 |
Then re-run this SQL regularly, you should notice these values have been reset to zero and then growing slowly until the OPTIMIZE is finished.
For approx. progress will be current size/original size before OPTIMIZE, note this will be less or larger than 100% when it's finished since the size of the rebuilt table might be larger or smaller than the original one.
