Using pt-online-schema-change

[Ran Margaliot]

Hello,

I want to use the 'pt-online-schema-change' on a big table (~100M
records) and have a few questions:

1. while the change is running will my application be able to insert
new data into the changing table
2. will the app be able to read that data from the table while the
change is running
3. does this process effects the performance of mysql while running

Thanks,

Ran

[Will Gunty]

1) Yes

2) Yes

and 3) Yes

The internals of this tool are really quite simple, but complex at the same time.  Essentially, what it does is creates triggers that on insert/update/delete on your current table, store the information.  It then creates a new table with the new table definition.  Then it migrates data in chunks over to the new table, and then "replays" the events that were triggered from your original table.  At the end, it does a brief lock on both tables, and swaps them.

You may want to play with the chunk size and sleep between chunks.  Yes, this will increase the amount of time that the schema change takes, however, it will help the server.  In my experience, this whole process can cause severe replication lag on even the fastest of servers.  That is where we saw the greatest performance hit.  We tuned the chunk size down and the sleep up and got to a point that we could actually do it without creating lag.  However, this did make the schema change take about 2-4 times as long as a normal alter.

Hope this helps.

-- 
Will Gunty
Sent with Sparrow

--

You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

To post to this group, send email to percona-d...@googlegroups.com.

To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

[Ran Margaliot]

Thanks Will, that help a lot :)

regarding the chunk size and sleep time, do you mind sharing the
values you worked with, just so i could get the "feeling" of the
numbers

another small question: while moving the data from the old table to
the new one, does the tool keeps the same private key (id) for the
record? what i mean is will the ids of the records in the new table
will be identical to the ones in the old table?

Cheers,

Ran

On Dec 8, 8:14 pm, Will Gunty <w...@gunty.net> wrote:
> 1) Yes
> 2) Yes
> and 3) Yes
>
> The internals of this tool are really quite simple, but complex at the same time.  Essentially, what it does is creates triggers that on insert/update/delete on your current table, store the information.  It then creates a new table with the new table definition.  Then it migrates data in chunks over to the new table, and then "replays" the events that were triggered from your original table.  At the end, it does a brief lock on both tables, and swaps them.
>
> You may want to play with the chunk size and sleep between chunks.  Yes, this will increase the amount of time that the schema change takes, however, it will help the server.  In my experience, this whole process can cause severe replication lag on even the fastest of servers.  That is where we saw the greatest performance hit.  We tuned the chunk size down and the sleep up and got to a point that we could actually do it without creating lag.  However, this did make the schema change take about 2-4 times as long as a normal alter.
>
> Hope this helps.
>
> --
> Will Gunty

> Sent with Sparrow (http://www.sparrowmailapp.com/?sig)

>
>
>
>
>
>
>
> On Thursday, December 8, 2011 at 6:03 AM, Ran Margaliot wrote:
> > Hello,
>
> > I want to use the 'pt-online-schema-change' on a big table (~100M
> > records) and have a few questions:
>
> > 1. while the change is running will my application be able to insert
> > new data into the changing table
> > 2. will the app be able to read that data from the table while the
> > change is running
> > 3. does this process effects the performance of mysql while running
>
> > Thanks,
>
> > Ran
>
> > --
> > You received this message because you are subscribed to the Google Groups "Percona Discussion" group.

> > To post to this group, send email to percona-d...@googlegroups.com (mailto:percona-d...@googlegroups.com).
> > To unsubscribe from this group, send email to percona-discuss...@googlegroups.com (mailto:percona-discuss...@googlegroups.com).

[Kenny Gryp]

On 19 Mar 2014, at 10:08, lanbo <land...@gmail.com> wrote:

Sorry to reply to such an old message.

If anyone is still subscribed, could you please tell me how you can force pt-online-schema-change to sleep a certain amount of ms after each chunk?

We have I/O problems and would like to make it stop for a while after each INSERT SELECT *...

Will how did you set the sleep param? There is not any option such as that one.

Thanks

It’s not completely there, but you can set —max-load to something else, for example the default is Threads_running=25.

You could lower that value and then there’s more likely to be more waiting if the load is too high (If you have IO problems, I expect Threads_running to increase).

You can also try to monitor on pending IO status variables like for example Innodb_data_pending_reads.

https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Innodb_data_pending_reads

That’s a good start.  But this can cause the pt-osc run to take much longer and potentially do nothing for quite a while (there will be progress reports), so it’s not exactly like a ‘sleep’.

You can also just edit the code to achieve a real sleep.

For example: http://bazaar.launchpad.net/~percona-toolkit-dev/percona-toolkit/2.2/view/head:/bin/pt-online-schema-change#L8954

Add some sleep there around line 8955. That part is executed after every nibble.

Kenny Gryp

-- 

Principal Consultant, Percona

Phone: +1 888 401 3401 Ext: 527
Skype: grypyrg
Gtalk: kenny...@percona.com

[Jose Luis Landabaso]

Great!

I experimented a bit and it looks like that a chunk-time of 0.1 combined with --max-load Threads_running=15 is fine (so far).

Problem is apache (on the same development machine) was getting slower. In fact, there are no users connected to devel and therefore I assume that a smaller chunk-time did the trick.

Thanks

--
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-discuss...@googlegroups.com.

To post to this group, send email to percona-d...@googlegroups.com.

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