On Thu, Sep 27, 2012 at 3:02 PM, Akshay Suryavanshi <akshay.suryavansh...@gmail.com> wrote: > 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.
It finally finished after 55 hours. > 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 >> >> >> > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
