Hi, 22 indexes are simply too many, assuming they are not composite, which means you already have a very large table. Secondly the most important bottleneck is shown by the inserts/sec, only 405 inserts is very very slow. This could take ages to complete. And the 405 inserts/sec are averages calculated over some small period of time mostly under a minute, not from the uptime, you can see that at the top (initial lines) of the Show engine innodb status \G output.
Depending upon the machine footprint, inserts/sec should atleast be more than 10000 inserts/sec even on a busy server. Indexes are slowing this down. your calculation of 79 hours should be correct, only if there are no unique indexes, otherwise this will slow down more as the data increases. Regards, Akshay Surayavanshi On Fri, Sep 28, 2012 at 2:22 AM, Larry Martell <larry.mart...@gmail.com>wrote: > 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 > >> > > >
