MySQL Online DDL--pt-online-change-schema測試

84223932發表於2013-10-15
MySql

歡迎轉載，請註明作者、出處。
作者：張正
blog：http://space.itpub.net/26355921 
QQ：176036317
如有疑問，歡迎聯絡。

 因為MySQL在5.5及5.5之前的版本中對Online DDL支援不是太好，可能會引發一些Bug。目前大多數場景中都是使用percona公司提供的pt-online-change-schema工具 進行Online DDL。

     pt-online-change-schema原理為：

1. 建立需要執行alter操作的原表的一個臨時表，然後在臨時表中更改表結構。

2. 在原表中建立觸發器（3個）三個觸發器分別對應insert,update,delete操作

3. 從原表複製資料到臨時表，複製過程中在原表進行的寫操作都會更新到新建的臨時表。

4. Rename 原表到old表中，在把臨時表Rename為原表，最後將原表刪除(可能不刪除)，將原表上所建立的觸發器刪除。


伺服器情況：
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1572864000 |
+-------------------------+------------+
1 row in set (0.00 sec)

表account和account2完全相同：
mysql> select count(AccountId) from account;
+------------------+
| count(AccountId) |
+------------------+
|          2021433 |
+------------------+
1 row in set (2.91 sec)

mysql> select count(AccountId) from account2;
+------------------+
| count(AccountId) |
+------------------+
|          2021433 |
+------------------+
1 row in set (1.63 sec)

mysql> checksum table account;
+---------------+------------+
| Table         | Checksum   |
+---------------+------------+
| test.account  | 3618473368 |
+---------------+------------+
1 row in set (12.30 sec)

mysql> checksum table account2;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| test.account2| 3618473368 |
+--------------+------------+
1 row in set (15.51 sec)


一。

對account表進行操作，無其他任何連線：
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 121 | root | localhost | test | Query   |    0 | NULL  | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

執行SQL：
mysql> update account set NickName='test result' where AccountId between 100000 and 100090;
Query OK, 91 rows affected (0.12 sec)
Rows matched: 91  Changed: 91  Warnings: 0

mysql> update account set address='test address result' where AccountId between 889917 and 889523;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set IpAddress='10.10.10.1' where AccountId between 1387652 and 1387930;
Query OK, 279 rows affected (0.50 sec)
Rows matched: 279  Changed: 279  Warnings: 0

mysql> update account set newNickName='test' where AccountId between 387652 and 387949;
Query OK, 298 rows affected (0.34 sec)
Rows matched: 298  Changed: 298  Warnings: 0

執行DDL：
mysql>alter table account add column uuid varchar(60) null default null after childrenInHS;
Query OK, 2021433 rows affected (10 min 6.80 sec)
Records: 2021433  Duplicates: 0  Warnings: 0
用時10分鐘。

檢查checksum：
mysql> checksum table account;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| test.account | 2787974151 |
+--------------+------------+
1 row in set (1 min 36.62 sec)


二。

account2表使用pt-online-change-schema：
/usr/bin/pt-online-schema-change --user=root --host=127.0.0.1 --port=3306 --charset=utf8 --nodrop-old-table --alter="add column uuid varchar(60) null default null after childrenInHS " D=test,t=account2 --exec


# A software update is available:
#   * The current version for MySQL Community Server (GPL) is 5.5.34.

Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`account2`...
Creating new table...
Created new table test._account2_new OK.
Altering new table...
Altered `test`.`_account2_new` OK.
2013-10-15T12:56:19 Creating triggers...
2013-10-15T12:56:20 Created triggers OK.
2013-10-15T12:56:20 Copying approximately 2019344 rows...
Copying `test`.`account2`:   2% 24:35 remain
Copying `test`.`account2`:   5% 17:14 remain
Copying `test`.`account2`:  10% 13:40 remain
Copying `test`.`account2`:  14% 11:32 remain
Copying `test`.`account2`:  18% 11:18 remain
Copying `test`.`account2`:  22% 10:38 remain
Copying `test`.`account2`:  26% 09:45 remain
Copying `test`.`account2`:  29% 09:36 remain
Copying `test`.`account2`:  32% 09:16 remain
Copying `test`.`account2`:  36% 08:52 remain
Copying `test`.`account2`:  39% 08:21 remain
Copying `test`.`account2`:  43% 07:53 remain
Copying `test`.`account2`:  46% 07:36 remain
Copying `test`.`account2`:  49% 07:16 remain
Copying `test`.`account2`:  51% 07:00 remain
Copying `test`.`account2`:  54% 06:50 remain
Copying `test`.`account2`:  56% 06:31 remain
Copying `test`.`account2`:  59% 06:09 remain
Copying `test`.`account2`:  65% 04:56 remain
Copying `test`.`account2`:  72% 03:53 remain
Copying `test`.`account2`:  80% 02:30 remain
Copying `test`.`account2`:  89% 01:14 remain
2013-10-15T13:07:54 Copied rows OK.
2013-10-15T13:07:54 Swapping tables...
2013-10-15T13:07:54 Swapped original and new tables OK.
2013-10-15T13:07:54 Dropping triggers...
2013-10-15T13:07:54 Dropped triggers OK.
Successfully altered `test`.`account2`.
用時12分鐘。

同時在另一個session中執行與上面相同的SQL，以模擬Online DML：
#!/bin/bash
mysql test -e "update account2 set NickName='test result' where AccountId between 100000 and 100090;"
sleep 3
mysql test -e "update account2 set address='test address result' where AccountId between 889917 and 889523;"
sleep 7
mysql test -e "update account2 set IpAddress='10.10.10.1' where AccountId between 1387652 and 1387930;"
sleep 5
mysql test -e "update account2 set newNickName='test' where AccountId between 387652 and 387949;"

檢視連線：
mysql> show processlist;
+-----+-----------+---------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id  | User      | Host                | db   | Command | Time | State             | Info                                                                                                 |
+-----+-----------+---------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 126 | root      | localhost:42757     | test | Query   |    2 | query end         | INSERT LOW_PRIORITY IGNORE INTO `test`.`_account2_new` (`accountid`, `swid`, `username`, `password`, |
| 127 | root      | localhost:42758     | test | Sleep   |  182 |                   | NULL                                                                                                 |
| 131 | root      | localhost           | NULL | Query   |    0 | NULL              | show processlist                                                                                     |
| 135 | moni_user | 192.168.0.144:51884 | NULL | Sleep   |    0 |                   | NULL                                                                                                 |
+-----+-----------+---------------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

在同步過程中，建立了臨時表 _account2_new ：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| _account2_new  |
| account        |
| account2       |
+----------------+

由於加了--nodrop-old-table引數，同步完成後_account2_old依然存在，其為原來的account2表：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| _account2_old  |
| account        |
| account2       |
+----------------+
3 rows in set (0.00 sec)

檢查checksum：
mysql> checksum table account2;
+---------------+------------+
| Table         | Checksum   |
+---------------+------------+
| test.account2 | 2787974151 |
+---------------+------------+
1 row in set (14.24 sec)
上面得到account表的checksum為2787974151
表account與account2完全相同。


三。
總結：
上述表明：表account在無其他連線下，直接進行加欄位 得到的結果 與 該表使用pt-online-change-schema加欄位得到的結果相同。





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26355921/viewspace-774421/，如需轉載，請註明出處，否則將追究法律責任。

相關文章