Is there a progress indicator for OPTIMIZE TABLE progress?












3















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.










share|improve this question





























    3















    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.










    share|improve this question



























      3












      3








      3


      3






      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.










      share|improve this question
















      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.







      mysql innodb windows mysql-5.1






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 22 '12 at 1:06









      RolandoMySQLDBA

      142k24223379




      142k24223379










      asked Dec 21 '12 at 18:28









      Mike BMike B

      2422515




      2422515






















          3 Answers
          3






          active

          oldest

          votes


















          9














          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 InnoDB


          • Feb 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




          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



          SCENARIO #1 : innodb_file_per_table disabled



          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.



          SCENARIO #2 : innodb_file_per_table enabled



          For each InnoDB table mydb.mytable you would have




          • /var/lib/mysql/mydb/mytable.frm

          • /var/lib/mysql/mydb/mytable.ibd


          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.



          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



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

          • LOGTABLEFOO2_NEW.ibd

          • LOGTABLEFOO3_NEW.ibd


          Simply Monitor Each File's Existence and Size Until it Disappears.






          share|improve this answer


























          • Since this database uses innodb, will there be a growing tmp table I can look for?

            – Mike B
            Dec 21 '12 at 20:21



















          0














          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





          share|improve this answer

































            0














            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 retun something like this (on Linux).
            In my case, the progress is 287 MB out of 490 MB (roughly).
            watching optimize table






            share|improve this answer








            New contributor




            Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
            Check out our Code of Conduct.




















              Your Answer








              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "182"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: false,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f30922%2fis-there-a-progress-indicator-for-optimize-table-progress%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              3 Answers
              3






              active

              oldest

              votes








              3 Answers
              3






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              9














              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 InnoDB


              • Feb 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




              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



              SCENARIO #1 : innodb_file_per_table disabled



              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.



              SCENARIO #2 : innodb_file_per_table enabled



              For each InnoDB table mydb.mytable you would have




              • /var/lib/mysql/mydb/mytable.frm

              • /var/lib/mysql/mydb/mytable.ibd


              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.



              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



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

              • LOGTABLEFOO2_NEW.ibd

              • LOGTABLEFOO3_NEW.ibd


              Simply Monitor Each File's Existence and Size Until it Disappears.






              share|improve this answer


























              • Since this database uses innodb, will there be a growing tmp table I can look for?

                – Mike B
                Dec 21 '12 at 20:21
















              9














              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 InnoDB


              • Feb 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




              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



              SCENARIO #1 : innodb_file_per_table disabled



              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.



              SCENARIO #2 : innodb_file_per_table enabled



              For each InnoDB table mydb.mytable you would have




              • /var/lib/mysql/mydb/mytable.frm

              • /var/lib/mysql/mydb/mytable.ibd


              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.



              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



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

              • LOGTABLEFOO2_NEW.ibd

              • LOGTABLEFOO3_NEW.ibd


              Simply Monitor Each File's Existence and Size Until it Disappears.






              share|improve this answer


























              • Since this database uses innodb, will there be a growing tmp table I can look for?

                – Mike B
                Dec 21 '12 at 20:21














              9












              9








              9







              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 InnoDB


              • Feb 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




              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



              SCENARIO #1 : innodb_file_per_table disabled



              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.



              SCENARIO #2 : innodb_file_per_table enabled



              For each InnoDB table mydb.mytable you would have




              • /var/lib/mysql/mydb/mytable.frm

              • /var/lib/mysql/mydb/mytable.ibd


              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.



              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



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

              • LOGTABLEFOO2_NEW.ibd

              • LOGTABLEFOO3_NEW.ibd


              Simply Monitor Each File's Existence and Size Until it Disappears.






              share|improve this 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 InnoDB


              • Feb 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




              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



              SCENARIO #1 : innodb_file_per_table disabled



              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.



              SCENARIO #2 : innodb_file_per_table enabled



              For each InnoDB table mydb.mytable you would have




              • /var/lib/mysql/mydb/mytable.frm

              • /var/lib/mysql/mydb/mytable.ibd


              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.



              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



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

              • LOGTABLEFOO2_NEW.ibd

              • LOGTABLEFOO3_NEW.ibd


              Simply Monitor Each File's Existence and Size Until it Disappears.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited May 23 '17 at 12:40









              Community

              1




              1










              answered Dec 21 '12 at 19:03









              RolandoMySQLDBARolandoMySQLDBA

              142k24223379




              142k24223379













              • Since this database uses innodb, will there be a growing tmp table I can look for?

                – Mike B
                Dec 21 '12 at 20:21



















              • Since this database uses innodb, will there be a growing tmp table I can look for?

                – Mike B
                Dec 21 '12 at 20:21

















              Since this database uses innodb, will there be a growing tmp table I can look for?

              – Mike B
              Dec 21 '12 at 20:21





              Since this database uses innodb, will there be a growing tmp table I can look for?

              – Mike B
              Dec 21 '12 at 20:21













              0














              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





              share|improve this answer






























                0














                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





                share|improve this answer




























                  0












                  0








                  0







                  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





                  share|improve this answer















                  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






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Mar 7 '17 at 7:51









                  Marco

                  3,73231624




                  3,73231624










                  answered Mar 7 '17 at 5:14









                  cornernotecornernote

                  1011




                  1011























                      0














                      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 retun something like this (on Linux).
                      In my case, the progress is 287 MB out of 490 MB (roughly).
                      watching optimize table






                      share|improve this answer








                      New contributor




                      Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                      Check out our Code of Conduct.

























                        0














                        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 retun something like this (on Linux).
                        In my case, the progress is 287 MB out of 490 MB (roughly).
                        watching optimize table






                        share|improve this answer








                        New contributor




                        Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                        Check out our Code of Conduct.























                          0












                          0








                          0







                          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 retun something like this (on Linux).
                          In my case, the progress is 287 MB out of 490 MB (roughly).
                          watching optimize table






                          share|improve this answer








                          New contributor




                          Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.










                          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 retun something like this (on Linux).
                          In my case, the progress is 287 MB out of 490 MB (roughly).
                          watching optimize table







                          share|improve this answer








                          New contributor




                          Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.









                          share|improve this answer



                          share|improve this answer






                          New contributor




                          Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.









                          answered 10 mins ago









                          Pierre-Olivier BenoitPierre-Olivier Benoit

                          1




                          1




                          New contributor




                          Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.





                          New contributor





                          Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.






                          Pierre-Olivier Benoit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Database Administrators Stack Exchange!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f30922%2fis-there-a-progress-indicator-for-optimize-table-progress%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Liste der Baudenkmale in Friedland (Mecklenburg)

                              Single-Malt-Whisky

                              Czorneboh