pt-online-schema-change on a large table

643 views
Skip to first unread message

Daniel Kasak

unread,
May 25, 2017, 2:38:57 AM5/25/17
to Percona Discussion
Hi all.

I've just had a failed attempt at adding a column to a large table, using pt-online-schema-change. Everything was running fine until the last step - drop-swap. The last log message we saw was:

Analyzing new table...

After this, the original table became locked, and we started seeing application errors shortly after ( though unfortunately I was testing my VPN connection and didn't catch the errors immediately ). The table was locked for over 5 minutes before we finally killed the process and restored functionality.

I'd like to understand what's happening here, and what our options are. Is pt-online-schema-change explicitly locking the original table while the analyze operation completes? After we'd killed the pt-online-schema-change process, I tried running 'analyze table' on the new table, and it returned immediately. Why would the previous analyze operation have run for so long? The docs mention that it's potentially not safe to skip the analyze operation:

        This circumvents a potentially serious issue related to InnoDB
        optimizer statistics. If the table being alerted is busy and the tool
        completes quickly, the new table will not have optimizer statistics
        after being swapped. This can cause fast, index-using queries to do
        full table scans until optimizer statistics are updated (usually after
        10 seconds). If the table is large and the server very busy, this can
        cause an outage.

This is certainly not our situation. The batch-copying of data to the new table took 2 hours, and the table isn't *too* busy - we might get an insert every 30-60 seconds, and maybe 5x the reads. I would assume based on this that we're relatively safe to try with --noanalyze-before-swap?

Dan

James Wang

unread,
May 25, 2017, 4:37:17 AM5/25/17
to Percona Discussion
Has the trigger(s)?

We wrote own online schema change and it works fine :)

Wagner Bianchi

unread,
May 25, 2017, 8:23:59 AM5/25/17
to percona-d...@googlegroups.com
Hey Daniel, you can re-execute the pt-osc adding the --noanalyze-before-swap as you're dealing with a large table. 


Try that and let us know how it goes, cheers!


--
Wagner Bianchi, +55.31.8654.9510
Oracle ACE Director, MySQL Certified Professional
Skype: wbianchijr

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-discussion@googlegroups.com.
Visit this group at https://groups.google.com/group/percona-discussion.
 To view this discussion on the web visit https://groups.google.com/d/msgid/percona-discussion/4c4c0ba8-644c-46c8-8e1c-47b5a83724ab%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Daniel Kasak

unread,
Jun 16, 2017, 12:32:47 AM6/16/17
to Percona Discussion
We finally tried this again, with the --noanalyze-before-swap option. It "worked", but we still had an outage of about 200 seconds. The pt-online-schema-change process' state was something related to permissions ( like fetching permissions or something ) for most of this time, which seems a bit strange. Other processes were blocked waiting for a metadata lock. All things considered, I guess this was a good outcome, even though we had some errors thrown from our applications ... it made something previously impossible "just barely possible".

Thanks for the assistance :)

Dan


On Thursday, 25 May 2017 22:23:59 UTC+10, Wagner Bianchi wrote:
Hey Daniel, you can re-execute the pt-osc adding the --noanalyze-before-swap as you're dealing with a large table. 


Try that and let us know how it goes, cheers!


--
Wagner Bianchi, +55.31.8654.9510
Oracle ACE Director, MySQL Certified Professional
Skype: wbianchijr

2017-05-25 5:37 GMT-03:00 James Wang <jwang...@gmail.com>:
Has the trigger(s)?

We wrote own online schema change and it works fine :)

On Thursday, 25 May 2017 07:38:57 UTC+1, Daniel Kasak wrote:
Hi all.

I've just had a failed attempt at adding a column to a large table, using pt-online-schema-change. Everything was running fine until the last step - drop-swap. The last log message we saw was:

Analyzing new table...


--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.

Wagner Bianchi

unread,
Jun 16, 2017, 1:30:46 PM6/16/17
to percona-d...@googlegroups.com
You're welcome!!


--
Wagner Bianchi, +55.31.8654.9510
Oracle ACE Director, Oracle Certified Expert (OCE/MySQL)
Skype: wbianchijr

2017-06-16 1:32 GMT-03:00 Daniel Kasak <d.j.ka...@gmail.com>:
We finally tried this again, with the --noanalyze-before-swap option. It "worked", but we still had an outage of about 200 seconds. The pt-online-schema-change process' state was something related to permissions ( like fetching permissions or something ) for most of this time, which seems a bit strange. Other processes were blocked waiting for a metadata lock. All things considered, I guess this was a good outcome, even though we had some errors thrown from our applications ... it made something previously impossible "just barely possible".

Thanks for the assistance :)

Dan

On Thursday, 25 May 2017 22:23:59 UTC+10, Wagner Bianchi wrote:
Hey Daniel, you can re-execute the pt-osc adding the --noanalyze-before-swap as you're dealing with a large table. 


Try that and let us know how it goes, cheers!


--
Wagner Bianchi, +55.31.8654.9510
Oracle ACE Director, MySQL Certified Professional
Skype: wbianchijr

2017-05-25 5:37 GMT-03:00 James Wang <jwang...@gmail.com>:
Has the trigger(s)?

We wrote own online schema change and it works fine :)

On Thursday, 25 May 2017 07:38:57 UTC+1, Daniel Kasak wrote:
Hi all.

I've just had a failed attempt at adding a column to a large table, using pt-online-schema-change. Everything was running fine until the last step - drop-swap. The last log message we saw was:

Analyzing new table...


--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsubscribe@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/percona-discussion.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-discussion@googlegroups.com.

Visit this group at https://groups.google.com/group/percona-discussion.
To view this discussion on the web visit https://groups.google.com/d/msgid/percona-discussion/1279d9b6-0337-45cb-b379-0c72cb92dc8d%40googlegroups.com.

franck....@wagwalking.com

unread,
Aug 1, 2018, 12:41:12 AM8/1/18
to Percona Discussion
I had the same issue tonight (and i've seen it in the past) where the swapping takes a while and lock tables

Analyze table is a fast process, should take less than few seconds.
(Optimize table is the command that rebuild the table)
The process status was "checking for permission" which does not make sense either.
I think there is something not working properly in pt-osc. 
You're welcome!!
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/percona-discussion.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/percona-discussion.


The information contained in this message is intended for the addressee only and may contain classified information. If you are not the addressee, please delete this message and notify the sender; you should not copy or distribute this message or disclose its contents to anyone.
Reply all
Reply to author
Forward
0 new messages