List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 27 2012 9:15pm
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 3:02 PM, Akshay Suryavanshi
<akshay.suryavanshi50@stripped> wrote:
> Hi,
>
> 22 indexes are simply too many, assuming they are not composite, which means
> you already have a very large table.

Yeah, I agree the table has too many columns and too many indexes. I
didn't design it, and I have no control over it. There's a huge app
that's built around it, and changing the table would require too many
code changes.

> 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.

I don't know anything about the machine. I don't have direct access to
it (it's at my client's client's site) so I'm doing everything by
proxy.

> 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 is what it is - there's no free lunch - dropping the indexes, doing
the alter, and re-adding the indexes would probably take just as long.

It would be very nice to speed up the alter, but what I was really
looking for here was a way to monitor its progress.


> On Fri, Sep 28, 2012 at 2:22 AM, Larry Martell <larry.martell@stripped>
> wrote:
>>
>> 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