본문 바로가기
MySQL/MySQL Admin

percona toolkit - pt-online-schema-change dry-run and print

notemusic 2015. 11. 26. 13:55

또 다시 percona toolkit 중에 pt-online-schema-change에 대해 설명하려합니다.

이번엔 실제 사례 중 과정을 보기 위해 dry-run과 print를 주어서 볼 건데요.

총 세가지가 섞여 있습니다.

pt-online-schema-change의 기능구현이 되어 있는 perl과 mysql 실제로 들어가는 쿼리, 그리고 툴을 돌렸을 때의 로그. 세가지인데

perl

 

Query

 

굵은 글씨는 Shell Log라고 보시면 됩니다.

그리고 테스트 할 테이블은 제가 벤치마킹하느라 sysbench로 만들어 놓은 테이블인데 1000만건이 들어있는 테이블입니다.

 

[root@MONDB mysql]# pt-online-schema-change --alter "drop index k" D=chung,t=sbtest \

--no-drop-old-table --no-drop-new-table \

--chunk-size=500 \

--chunk-size-limit=600 \

--host=127.0.0.1 \

--port=3306 \

--user=root \

--ask-pass \

--progress=time,1 \

--max-load="Threads_running=20" \

--chunk-index=PRIMARY \

--charset="UTF8" \

--dry-run --print

 

0. 기본설정 확인

Enter MySQL password:

Operation, tries, wait:

  analyze_table, 10, 1

  copy_rows, 10, 0.25

  create_triggers, 10, 1

  drop_triggers, 10, 1

  swap_tables, 10, 1

  update_foreign_keys, 10, 1

Starting a dry run.  `chung`.`sbtest` will not be altered.  Specify --execute instead of --dry-run to alter the table.

 

1. 신규테이블생성

Creating new table...

CREATE TABLE `chung`.`_sbtest_new` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=euckr

 

Created new table chung._sbtest_new OK.

 

2. Alert 적용된 테이블 생성

Altering new table...

ALTER TABLE `chung`.`_sbtest_new` drop index k

-       여기에 해당하는 pt-online-schema-change의 perl 부분입니다.

8947 ####################################################################

8948    # Step 2: Alter the new, empty table.  This should be very quick,

8949    # or die if the user specified a bad alter statement.

8950 #################################################################

8951

8952    # --plugin hook

8953    if ( $plugin && $plugin->can('before_alter_new_table') ) {

8954       $plugin->before_alter_new_table(

8955          new_tbl => $new_tbl,

8956       );

8957    }

8958

8959    if ( my $alter = $o->get('alter') ) {

8960       print "Altering new table...\n";

8961       my $sql = "ALTER TABLE $new_tbl->{name} $alter";

8962       print $sql, "\n" if $o->get('print');

8963       PTDEBUG && _d($sql);

8964       eval {

8965          $cxn->dbh()->do($sql);

8966       };

8967       if ( $EVAL_ERROR ) {

8968          die "Error altering new table $new_tbl->{name}: $EVAL_ERROR\n"

8969       }

8970       print "Altered $new_tbl->{name} OK.\n";

8971    }

 

-       실질적으로 alert가 적용된 테이블 스키마입니다.

CREATE TABLE `_sbtest_new` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT '0',

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=euckr

 

 

Altered `chung`.`_sbtest_new` OK.

 

3. 트리거 생성

Not creating triggers because this is a dry run.

10532    # To be safe, the delete trigger must specify all the columns of the

10533    # primary key/unique index.  We use null-safe equals, because unique

10534    # unique indexes can be nullable.  Cols are from the new table and

10535    # they may have been renamed

10536    my %old_col_for    = map { $_->{new} => $_->{old} } @$cols;

10537    my $tbl_struct     = $del_tbl->{tbl_struct};

10538    my $del_index      = $del_tbl->{del_index};

10539    my $del_index_cols = join(" AND ", map {

10540       my $new_col  = $_;

10541       my $old_col  = $old_col_for{$new_col} || $new_col;

10542       my $new_qcol = $q->quote($new_col);

10543       my $old_qcol = $q->quote($old_col);

10544       "$new_tbl->{name}.$new_qcol <=> OLD.$old_qcol"

10545    } @{$tbl_struct->{keys}->{$del_index}->{cols}} );

10546

10547    my $delete_trigger

10548       = "CREATE TRIGGER `${prefix}_del` AFTER DELETE ON $orig_tbl->{name} "

10549       . "FOR EACH ROW "

10550       . "DELETE IGNORE FROM $new_tbl->{name} "

10551       . "WHERE $del_index_cols";

10552

10553    my $qcols    = join(', ', map { $q->quote($_->{new}) }        @$cols);

10554    my $new_vals = join(', ', map { "NEW.".$q->quote($_->{old}) } @$cols);

10555    my $insert_trigger

10556       = "CREATE TRIGGER `${prefix}_ins` AFTER INSERT ON $orig_tbl->{name} "

10557       . "FOR EACH ROW "

10558       . "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals)";

10559    my $update_trigger

10560       = "CREATE TRIGGER `${prefix}_upd` AFTER UPDATE ON $orig_tbl->{name} "

10561       . "FOR EACH ROW "

10562       . "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals)";

10563

10564    my @triggers = (

10565       ['del', $delete_trigger],

10566       ['upd', $update_trigger],

10567       ['ins', $insert_trigger],

10568    );

 

-       pt-online-schema-change가 만든 Delete Trigger

-       트리거에 IGNORE가 있으므로 삭제될 대상이 없더라도 무시

CREATE TRIGGER `pt_osc_chung_sbtest_del` AFTER DELETE ON `chung`.`sbtest` FOR EACH ROW

DELETE IGNORE FROM `chung`.`_sbtest_new` WHERE `chung`.`_sbtest_new`.`id` <=> OLD.`id`;

 

-       pt-online-schema-change가 만든 Update Trigger

-       REPLACE가 있으므로 이미 있는 데이터면 UPDATE 없는 데이터면 INSERT

CREATE TRIGGER `pt_osc_chung_sbtest_upd` AFTER UPDATE ON `chung`.`sbtest` FOR EACH ROW

REPLACE INTO `chung`.`_sbtest_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);

 

-       pt-online-schema-change가 만든 Insert Trigger

-       REPLACE가 있으므로 이미 있는 데이터면 UPDATE 없는 데이터면 INSERT

CREATE TRIGGER `pt_osc_chung_sbtest_ins` AFTER INSERT ON `chung`.`sbtest` FOR EACH ROW

REPLACE INTO `chung`.`_sbtest_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);

 

 

4. 데이터 복사 LOW PRIORITY IGNORE

LOW PRIORITY는 현재 접속된 client 들이 해당 테이블로 작업을 하고 있을 경우 그 작업이 끝난 후에 insert를 하겠다는 옵션을 줍니다. 그럼으로써 LOCK IN SHARE MODE는 select 후 트랜잭션이 끝날 때까지 해당 Row값이 변경되지 않음을 보장합니다.

 

IGNORE 문의 경우 에러가 났을 경우 그냥 pass하도록 합니다.

왜냐하면 trigger로 넣은 데이터가 최신 데이터이기 때문입니다. Trigger로 최신화 되고 있습니다.

 

Not copying rows because this is a dry run.

5342       my $nibble_sql

5343          = ($args{dml} ? "$args{dml} " : "SELECT ")

5344          . ($args{select} ? $args{select}

5345                           : join(', ', map { $q->quote($_) } @cols))

5346          . " FROM $tbl->{name}"

5347          . ($where ? " WHERE $where" : '')

5348          . ($args{lock_in_share_mode} ? " LOCK IN SHARE MODE" : "")

 

9424    # NibbleIterator combines these two statements and adds

9425    # "FROM $orig_table->{name} WHERE <nibble stuff>".

9426    my $dml = "INSERT LOW_PRIORITY IGNORE INTO $new_tbl->{name} "

9427            . "(" . join(', ', map { $q->quote($_->{new}) } @common_cols) . ") "

9428            . "SELECT";

9429    my $select = join(', ', map { $q->quote($_->{old}) } @common_cols);

 

 

INSERT LOW_PRIORITY IGNORE INTO `chung`.`_sbtest_new` (`id`, `k`, `c`, `pad`)

SELECT `id`, `k`, `c`, `pad`

FROM `chung`.`sbtest` FORCE INDEX(`PRIMARY`)

WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 2952 copy nibble*/

 

SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `chung`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

 

5. 테이블 변경

Not swapping tables because this is a dry run.

 

10086    if ( $o->get('dry-run') ) {

10087       print "Not swapping tables because this is a dry run.\n";

10088

10089       # A return value really isn't needed, but this trick allows

10090       # rebuild_constraints() to parse and show the sql statements

10091       # it would used.  Otherwise, this has no effect.

10092       return $orig_tbl;

10093    }

10094    elsif ( $o->get('execute') ) {

10095       # ANALYZE TABLE before renaming to update InnoDB optimizer statistics.

10096       # https://bugs.launchpad.net/percona-toolkit/+bug/1491261

10097       if ( $args{analyze_table} ) {

10098          print ts("Analyzing new table...\n");

10099          my $sql_analyze = "ANALYZE TABLE $new_tbl->{name} /* pt-online-schema-change */";

10100          osc_retry(

10101             Cxn     => $cxn,

10102             Retry   => $retry,

10103             tries   => $tries->{analyze_table},

10104             stats   => $stats,

10105             code    => sub {

10106                PTDEBUG && _d($sql_analyze);

10107                $cxn->dbh()->do($sql_analyze);

10108             },

10109          );

10110       }

 

10123          my $sql = "RENAME TABLE $orig_tbl->{name} "

10124                  . "TO " . $q->quote($orig_tbl->{db}, $table_name)

10125                  . ", $new_tbl->{name} TO $orig_tbl->{name}";

 

 

6. 원본테이블 삭제

Not dropping old table because --no-drop-old-table was specified.

 

7. 트리거 삭제

Not dropping triggers because this is a dry run.

DROP TRIGGER IF EXISTS `chung`.`pt_osc_chung_sbtest_del`;

 

DROP TRIGGER IF EXISTS `chung`.`pt_osc_chung_sbtest_upd`;

 

DROP TRIGGER IF EXISTS `chung`.`pt_osc_chung_sbtest_ins`;

 

 

8. 신규테이블 삭제

Not dropping the new table `chung`.`_sbtest_new` because --no-drop-new-table was specified.  To drop the new table, execute:

DROP TABLE IF EXISTS `chung`.`_sbtest_new`;

 

Dry run complete.  `chung`.`sbtest` was not altered.

여기까지가 Dry run와 print를 했을 때 원본테이블과 신규테이블에 일어나는 것을 정리한 것입니다. perl와 mysql 실제 쿼리, log가 모두 혼합되어 있기 때문에 조금 난잡해 보일 수도 있지만 저같이 궁금해 하실 분들을 위해서 이렇게..ㅎ 다음에는 실제로 dry-run과 print를 하지 않고 execute를 한 것을 올리도록 하겠습니다.

 

'MySQL > MySQL Admin' 카테고리의 다른 글

Mysql prompt 변경하기!  (0) 2015.12.04
Transaction이란?  (0) 2015.12.04
percona toolkit - pt-online-schema-change 옵션 정리  (0) 2015.11.26
percona toolkit 설치  (0) 2015.11.24
percona toolkit - pt-online-schema-change 기본 설명  (0) 2015.11.24

'MySQL/MySQL Admin' Related Articles

티스토리툴바