Hi, The alter taking such a long time, could be due to composite indexes on the table. we understand the table is big but not so big to take such a long time. Also we can get a hold of the process looking at the disk space consumed. Usually a tmp table created in data directory would also give a good understanding of the process, remember sizes need not be exact since there might be some defragmentation at file level.
Next you can check inserts/sec in Show engine innodb status \G and calculate the time it should take for the number of rows in the table. Usually, you carry this operation by adding the secondary indexes after the data import or such alters are complete. Regards, Akshay Suryavanshi On Fri, Sep 28, 2012 at 1:56 AM, Rick James <rja...@yahoo-inc.com> wrote: > Isn't ALTER a DDL, not DML? So I don't think you would find anything in > undo logs. > > > -----Original Message----- > > From: Larry Martell [mailto:larry.mart...@gmail.com] > > Sent: Thursday, September 27, 2012 1:20 PM > > To: Rick James > > Cc: mysql mailing list > > Subject: checking progress of alter table on an InnoDB table (Was: Re: > > checking progress of alter table on a MyISAM table) > > > > So we changed the table from MyISAM to InnoDB. I read that the 'undo > > log entries' shown in 'show engine innodb status' would correspond to > > the number of rows that have been operated on throughout the process of > > the ALTER. The table we're altering has 115,096,205 rows, and the > > alter's been running for 28 hours, and the undo log entries is 9309. > > Also that number seems to go up and down. So clearly, it's not what I > > think. > > > > So anyone know a way to monitor the status of the alter now that it's > > an InnoDB table? > > > > > > On Wed, Sep 26, 2012 at 10:31 AM, Rick James <rja...@yahoo-inc.com> > > wrote: > > > Not really. > > > You could look at the .TYD and .TYI file sizes and compare to the > > .MYD and .MYI, but that can be deceptive. If the table is really big, > > and has lots of indexes, the generation of the indexes might go slower > > and slower -- hence any math on the sizes would be optimistic. > > > > > >> -----Original Message----- > > >> From: Larry Martell [mailto:larry.mart...@gmail.com] > > >> Sent: Wednesday, September 26, 2012 8:52 AM > > >> To: mysql mailing list > > >> Subject: checking progress of alter table on a MyISAM table > > >> > > >> Is there any way to check on the progress of a long running alter > > >> table on a MyISAM table? I know it can be done with an InnoDB table, > > >> but I haven't found a way to do it on with a MyISAM table. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >
