List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 27 2012 8:52pm
Subject:Re: checking progress of alter table on an InnoDB table (Was: Re:
checking progress of alter table on a MyISAM table)
View as plain text  
On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi
<akshay.suryavanshi50@stripped> 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 <rjames@stripped> 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.martell@stripped]
>> > 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 <rjames@stripped>
>> > 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.martell@stripped]
>> > >> 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
>>
>
Thread
checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Larry Martell27 Sep
  • RE: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Rick James27 Sep
    • Re: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Larry Martell27 Sep
    • Re: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Akshay Suryavanshi27 Sep
      • Re: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Larry Martell27 Sep
        • Re: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Akshay Suryavanshi27 Sep
          • Re: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Larry Martell27 Sep
            • Re: checking progress of alter table on a MyISAM tableReindl Harald27 Sep
          • Re: checking progress of alter table on an InnoDB table (Was: Re:checking progress of alter table on a MyISAM table)Larry Martell28 Sep