On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi <akshay.suryavansh...@gmail.com> wrote: > Hi, > > The alter taking such a long time, could be due to composite indexes on the > table.
There are 22 indexes on the table, but none are composites. > 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. Ok, I will look for a temp file. > 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. The ROW OPERATIONS section has this: 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 7913, id 140206844888832, state: sleeping Number of rows inserted 75910241, updated 15602, deleted 70, read 9130481311 405.80 inserts/s, 0.00 updates/s, 0.00 deletes/s, 405.80 reads/s This appears to be statistics over the life of the invocation of the server, correct? But using 405.80 inserts/s give that the alter will take almost 79 hours. > > 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 >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
