Add index on huge table when using pt-online-schema-change

Hi everyone,

My environment:
AWS Aurora(mysql)

I use pt-online-schema-change to add index for a table with about 100 million rows. This table have insert and update frequently.
I set critical load =200. But it also failed because threads_running over 200.
I’m afraid to set critical load too high to cause server loading high.

Another question. Does this tool can execute in specific time range?
For example, our off-peek time is 12:00 a.m. to 6:00 a.m.
So this tool will pause when the time is 6:00 a.m. and it will restart on next 12:00 a.m.
Is this possible in this tool?

Does there have a good way if I want to add index in such a big table?
Can I use pt-online-schema-change to achieve this target?
Could anyone give me some help, thanks a lot!

1 Like

Hi KT_CHANG,

On the official doc you can find some flags that can be useful for you:

–pause-file = Execution will be paused while the file specified by this param exists.
–max-lag = Pause the data copy until all replicas’ lag is less than this value. After each data-copy query (each chunk), the tool looks at the replication lag of all replicas to which it connects, using Seconds_Behind_Master.

Regards

2 Likes