[Suggest]pt-online-schema-change pausible.

[Jung]

* Mysql5.6.25 Enterprise, pt-online-schema-change is lastest.

We report on this already to forum.
https://www.percona.com/forums/questions-discussions/percona-toolkit/46217-is-it-possible-to-pause-and-resume-data-copy-dynamically-on-pt-online-schema-change?view=thread

Why? this feature is required?
=> we have very big table and it takes 2 days more, so peak time is not evitable.
 , we have been troubled during peak time.
innodb_buffer_pool_pages_dirty was growing up, it hard to flush dirty page to disk due to heavy workload.

=> --sleep=0.5 --chunk-size=500 --max-lag=1 is helpful. but not enough.

So, we suggest pause function on peak time. we tested and worked fine.
please consider this and give us your opinon for this.

Below code is simple and works when I create pause_please.txt file.

package NibbleIterator;
 sub next()

 NIBBLE:
 while ( $self->{have_rows} || $self->_next_boundaries() ) {
 if ( !$self->{have_rows} ) {
 $self->{nibbleno}++;
 PTDEBUG && _d('Nibble:', $self->{nibble_sth}->{Statement}, 'params:',
 join(', ', (@{$self->{lower} || []}, @{$self->{upper} || []})));
 if ( my $callback = $self->{callbacks}->{exec_nibble} ) {
 $self->{have_rows} = $callback->(%callback_args);
 }
 else {
 $self->{nibble_sth}->execute(@{$self->{lower}}, @{$self->{upper}});
 $self->{have_rows} = $self->{nibble_sth}->rows();
 }
 PTDEBUG && _d($self->{have_rows}, 'rows in nibble', $self->{nibbleno});
 }

 if ( $self->{have_rows} ) {
 my $row = $self->{nibble_sth}->fetchrow_arrayref();
 if ( $row ) {
 $self->{rowno}++;
 PTDEBUG && _d('Row', $self->{rowno}, 'in nibble',$self->{nibbleno});
 return [ @$row ];
 }
 }

 PTDEBUG && _d('No rows in nibble or nibble skipped');
 if ( my $callback = $self->{callbacks}->{after_nibble} ) {
 $callback->(%callback_args);
 }
 $self->{rowno} = 0;
 $self->{have_rows} = 0;

 # ================<new code start>=============================
 while(-f "pause_please.txt") {
 print "Im sleeping\n";
 my $dbh = $self->{Cxn}->dbh();
 if ( !$dbh || !$dbh->ping() ) {
 eval { $dbh = $self->{Cxn}->connect() }; # connect or die trying
 if ( $EVAL_ERROR ) {
 #$oktorun = 0; # flag for cleanup tasks
 chomp $EVAL_ERROR;
 die "Lost connection to " . $self->{Cxn}->name() . " while waiting for "
 . "replica lag ($EVAL_ERROR)\n";
 }
 }
 $dbh->do("SELECT 'pt-online-schema-change keepalive'");
 sleep(60);
 }
 # ===============<new code end>=============================

 }

 PTDEBUG && _d('Done nibbling');
 if ( my $callback = $self->{callbacks}->{done} ) {
 $callback->(%callback_args);
 }

[Jung (ray0817)]

Is there any update?
please consider this case. we finished internal test also we found the keep alive neccesarily otherwise, it is terminated.

we think that we need your code review for double check.

please consider this scenario :
2016-08-17T08:55:03 Creating triggers...
2016-08-17T08:55:03 Created triggers OK.
2016-08-17T08:55:03 Copying approximately 125366439 rows...
Copying `store_service`.`big_mark_usr_dvce_ota`: 0% 2+08:27:46 remain

[Carlos Salguero (carlos-salguero)]

Could you create a pull request so I can reproduce this?
Is there a unit test case?

[Carlos Salguero (carlos-salguero)]

I am adding a new --pause-file param to specify a file name. If that file exists, the program will be paused.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-713