ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MySQL Online DDL--pt-online-change-schema测试

MySQL Online DDL--pt-online-change-schema测试

原创 Linux操作系统 作者:84223932 时间:2013-10-15 17:18:09 0 删除 编辑


欢迎转载,请注明作者、出处。
作者:张正
blog:http://space.itpub.net/26355921 
QQ:176036317
如有疑问,欢迎联系。

 因为MySQL5.55.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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论
我是一名DBA,也是一名自由潜水员,爱生活。

注册时间:2013-08-26

  • 博文量
    27
  • 访问量
    1213873