Chinaunix首页 | 论坛 | 博客
  • 博客访问: 948294
  • 博文数量: 83
  • 博客积分: 32
  • 博客等级: 民兵
  • 技术积分: 2080
  • 用 户 组: 普通用户
  • 注册时间: 2010-08-12 09:45
个人简介

努力学习,努力分享,努力.....在努力..努力离开..

文章分类

全部博文(83)

文章存档

2017年(3)

2015年(1)

2014年(47)

2013年(32)

分类: Mysql/postgreSQL

2014-10-31 23:27:38

在MySQL5.6时代引入了Online DDL特性.其实在5.6之前在执行DDL的时候是很头疼的事情..如果数据量很大更是麻烦.在5.6之前加列、删列、修改列定义、建索引,MySQL的处理方式主要是下面几步:
1、创建一个与原表一样的临时表,然后将该表的结构修改成目标的表结构
2、锁定原表、只供查询不能DML
3、将原表数据复制到新表
4、将原表重命名,把临时表名称改成正式表,删除原表释放锁
当然Percona公司还提供PT工具专门来解决此问题..不过此工具也是在原表上建Trigger借助临时表,只不过减少DDL操作时对DML堵塞的时间.还有其它解决方案这里不在诉说.那么5.6的Online DDL又有哪些亮点呢?
Online DDL的指标主要有以下几种:
In-Place:表示该更改操作是否可以添加Inplace,如果可以则修改并直接在该表对象上执行.
Copy:表示该操作是否需要复制表
Allow Concurrent DML:表示该操作是否可以联机进行.
Allow Concurrent Queries:表示当执行DDL时,是否可以同时查询数据
那么我们执行DDL时候怎么判断ALTER TABLE是否使用Online DDL特性了呢?其实当用户执行的时候并不需要特殊指定,MySQL本身会自动选择判断..不过用户也可以明确指定参数来控制Online DDL的行为.那么先来看两个子句吧.
一个是LOCK,它主要是控制变更时读写的并发粒度,后面跟四个参数:
1、DEFAULT:默认等于不指定LOCK
2、NONE:不锁定,其它会话能读能写
3、SHARED:共享锁定,其它会话可读不可写
4、EXCLUSIVE:排他锁定,其它会话不可读写

另一个是ALGORITHM,它主要对于DDL操作时的性能和策略产生影响,后面也有三个参数:

1、DEFAULT:不解释了
2、INPLACE:是否可以在当前表直接修改,如果不支持那么就报错了.
3、COPY:不管是否可以INPLACE都采取复制数据的方式

至此理论部分讲解完毕,下面开始测试Online DDL(光讲理论还是很难理解^_^)


首先用SYSBENCH构造40W数据   --注:数据越多效果越明显


1、我们先测试索引及执行DML时的影响

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (2.06 sec)
数据文件
[mysql@localhost ~]$ du -sh /data/test/sbtest1.ibd 
109M    /data/test/sbtest1.ibd
我们试着用Inplace模式建、删索引看看情况

mysql> alter table sbtest1 add index idx_pad(pad),algorithm=inplace;
Query OK, 0 rows affected (10.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \! du -sh /data/test/sbtest1.ibd 
129M    /data/test/sbtest1.ibd

mysql> alter table sbtest1 drop index idx_pad,algorithm=inplace;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

果然Inplace效率高啊,当然sbtest1数据文件尽管索引删除文件大小也不会释放这是InnoDB决定的.那么我们接着看Copy方式
mysql> alter table sbtest1 add index idx_pad(pad),algorithm=copy;
Query OK, 400000 rows affected (5 min 50.03 sec)
Records: 400000  Duplicates: 0  Warnings: 0
在执行过程中我们在另外一个回话执行如下操作:
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set autocommit=0;
Query OK, 0 rows affected (0.17 sec)
mysql> select * from sbtest1 limit 10;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 151849 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
|  2 | 198035 | 05879259692-02024081996-76022818191-82933803603-81845875017-31928300264-16934042125-67052432228-92123768050-95121478647 | 79362588344-09017007031-35411714211-52054317597-26283585383 |
|  3 | 201968 | 86109785327-21666363106-71483869798-96435839840-61691707322-34771340621-77881281887-34657680322-46195586522-19795644808 | 74925946566-05246863347-88207617885-76849666822-72200234338 |
|  4 | 201169 | 57481185690-89398636500-16888148413-67987678267-15604944838-94210794401-18107184012-91338377776-83386272438-09451188763 | 35227182905-15234265621-59793845249-15413569710-23749555118 |
|  5 | 200061 | 29279855805-99348203463-85191104223-39587263726-81794340135-73817557808-54578801760-64404111877-55434439976-37212880746 | 59222897263-22759023974-22020489960-93434521232-77981152534 |
|  6 | 201765 | 24267764271-42431022577-79399828403-34660685942-15614883401-01775912296-17834847270-24498656403-67162539148-21266176221 | 26472102213-44313108032-85929810653-63595461233-99754685588 |
|  7 | 164825 | 75769514803-27086227718-38612213700-37972984756-05033716175-01596446901-14887935702-82254196675-91092890141-99940009825 | 01920094826-30050572228-27293124892-55703762324-88111796380 |
|  8 | 212166 | 82571936845-31830426410-85662298479-28456275464-64339136268-26186841165-94168712814-56389105006-66969794071-60071049942 | 13152283289-69561545685-52868757241-04245213425-69280254356 |
|  9 | 201661 | 94556997174-32108982644-63004661483-42547508604-40987100663-82959520169-01960773852-23325192900-64841585484-09299809863 | 38130396901-31554193919-79854584773-97713622125-48090103407 |
| 10 | 201495 | 92229281843-40509455748-54180693333-69666735372-33631067191-52840688810-46742388152-62036963372-40370446940-14952664058 | 29251291459-26439838509-02439953981-87093993879-41189576069 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
10 rows in set (0.28 sec)
mysql> delete from sbtest1 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
这是用Copy方式由于时间很长实际上是表重建的过程,所以此操作期间执行删除肯定会堵塞报死锁,如果用Online DDL肯定会成功啦,这里就不演示了.

下面我们继续尝试修改列
mysql> alter table sbtest1 auto_increment=600000,algorithm=inplace;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 change k k int(20) unsigned,algorithm=inplace;
Query OK, 0 rows affected (43.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改自增列真是秒快啊,当然它不需要重建表,应该还是直接修改.FRM文件及更新内存中的值.这里我们不在演示Copy上面已经演示过这里不在重复.

前面讲完Algorithm我们继续Lock子句,Lock相比Algorithm的优先级更高.那么我们接着往下看
我们首先测试Lock=None看看结果
session1:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)

mysql> create index idx_sbt1 on sbtest1(pad)lock=none;
Query OK, 0 rows affected (11.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

session2:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sbtest1 limit 5;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 150385 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
|  2 | 150188 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 |
|  3 | 150193 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 |
|  4 | 150641 | 72200234338-75748118569-08939863650-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-68338627243 | 80945118876-33522718290-51523426562-15979384524-91541356971 |
|  5 | 148853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-75543443997 | 63721288074-65922289726-32275902397-42202048996-09343452123 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)

session3:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest1 set k='1234' where id='1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session4:
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                           |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
|  2 | root | localhost | test | Sleep   |   58 |                                 | NULL                                           |
|  3 | root | localhost | test | Sleep   |   54 |                                 | NULL                                           |
|  4 | root | localhost | test | Query   |    0 | init                            | show full processlist                          |
| 11 | root | localhost | test | Query   |   60 | Waiting for table metadata lock | create index idx_sbt1 on sbtest1(pad)lock=none |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

从结果看Lock=None不会堵塞查询和DML,不过session1正在等待元数据锁定,由于执行的速度很快但是我们一直没有提交session2和session3.所以session1才会等待,这也说明DDL不堵塞DML.

我们继续看Lock=Shared
session1:
mysql> create index idx_sbt1 on sbtest1(pad)lock=shared;
Query OK, 0 rows affected (11.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

session2:
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sbtest1 limit 5;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 150385 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
|  2 | 150188 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 |
|  3 | 150193 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 |
|  4 | 150641 | 72200234338-75748118569-08939863650-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-68338627243 | 80945118876-33522718290-51523426562-15979384524-91541356971 |
|  5 | 148853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-75543443997 | 63721288074-65922289726-32275902397-42202048996-09343452123 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)

session3:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest1 set k='1234' where id='1';
Query OK, 0 rows affected (7.26 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session4:
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                             |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------+
|  2 | root | localhost | test | Sleep   |    5 |                                 | NULL                                             |
|  3 | root | localhost | test | Query   |    3 | Waiting for table metadata lock | update sbtest1 set k='1234' where id='1'         |
|  4 | root | localhost | test | Query   |    0 | init                            | show full processlist                            |
| 11 | root | localhost | test | Query   |    7 | altering table                  | create index idx_sbt1 on sbtest1(pad)lock=shared |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  2 | root | localhost | test | Sleep   |   90 |       | NULL                  |
|  3 | root | localhost | test | Sleep   |   88 |       | NULL                  |
|  4 | root | localhost | test | Query   |    0 | init  | show full processlist |
| 11 | root | localhost | test | Sleep   |   92 |       | NULL                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
4 rows in set (0.00 sec)
从结果看Shared不堵塞读,但是DML语句还在等待元数据锁定,需要等session1完毕才能继续.

我们最后看Lock=Exclusive
session1:
mysql> create index idx_sbt1 on sbtest1(pad)lock=exclusive;
Query OK, 0 rows affected (11.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

session2:
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sbtest1 limit 5;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 150385 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
|  2 | 150188 | 95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805 | 09512147864-77936258834-40901700703-13541171421-15205431759 |
|  3 | 150193 | 26283585383-48610978532-72166636310-67148386979-89643583984-06169170732-23477134062-17788128188-73465768032-24619558652 | 21979564480-87492594656-60524686334-78820761788-57684966682 |
|  4 | 150641 | 72200234338-75748118569-08939863650-01688814841-36798767826-71560494483-89421079440-11810718401-29133837777-68338627243 | 80945118876-33522718290-51523426562-15979384524-91541356971 |
|  5 | 148853 | 23749555118-82927985580-59934820346-38519110422-33958726372-68179434013-57381755780-85457880176-06440411187-75543443997 | 63721288074-65922289726-32275902397-42202048996-09343452123 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
5 rows in set (8.14 sec)

session3:
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update sbtest1 set k='1234' where id='1';
Query OK, 0 rows affected (5.36 sec)
Rows matched: 1  Changed: 0  Warnings: 0

session4:
mysql> show full processlist;
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                                |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
|  2 | root | localhost | test | Query   |    7 | Waiting for table metadata lock | select * from sbtest1 limit 5                       |
|  3 | root | localhost | test | Query   |    4 | Waiting for table metadata lock | update sbtest1 set k='1234' where id='1'            |
|  4 | root | localhost | test | Query   |    0 | init                            | show full processlist                               |
| 11 | root | localhost | test | Query   |    9 | altering table                  | create index idx_sbt1 on sbtest1(pad)lock=exclusive |
+----+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  2 | root | localhost | test | Sleep   |   21 |       | NULL                  |
|  3 | root | localhost | test | Sleep   |   18 |       | NULL                  |
|  4 | root | localhost | test | Query   |    0 | init  | show full processlist |
| 11 | root | localhost | test | Sleep   |   23 |       | NULL                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
4 rows in set (0.00 sec)
从结果看查询和DML语全部都在堵塞.

这次用的版本MySQL5.6.20测试Online DDL还是很给力的,不过也不是每种情况都支持有些操作就会抛出异常,比如drop primary key的时候就会直接报lock=none is not support不支持online ddl同时还要关注innodb_online_alter_log_max_size的配置,此参数是在执行DDL时保存操作时间内的日志,比如在高负载的时候此参数设置不当很可能会出现Message:Create index'inx_test' required more than 'innodb_online_alter_log_max_size' bytes of modification log.please try again..我们同时也要根据自身的情况灵活的运用Online DDL实现更细粒度的控制.今天先到这里吧...^_^

阅读(2374) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

CU博客助理2014-11-25 16:43:26

专家点评:在文章开始简单对比说明了普通DDL和Online DDL的区别,及目前的常用方法,这种开篇不错,能够吸引读者继续向下看.
对于"1、DEFAULT:不解释了",建议解释一下,避免读者疑惑:是按照以前的方式加吗?
文章最后的问题,最好总结为FAQ,每种情况写明应对的解决办法