淘宝MySQL十大经典案例_final.pdf

《淘宝MySQL十大经典案例_final.pdf》由会员分享，可在线阅读，更多相关《淘宝MySQL十大经典案例_final.pdf（61页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、淘宝MySQL十大经典案例 阿里集团阿里集团-淘宝网淘宝网DBA 杨德华杨德华Devin 2012-09 个人简介个人简介 负责淘宝用户中心从IOE迁移到MySQL集群的性能测试,高可用运维,DB可用率达到100%管理数据魔方,SNS,淘宝评价,webww,notify等MySQL集群 服务器优化,成本节省,把某个业务线的MySQL机器数量从100台减少到70台。SAS-SSD/FIO的MySQL服务器升级过程 负责MySQL5.1.45-5.1.48-Percona 5.5.18的工作 淘宝线淘宝线上十大上十大MySQL经典案例经典案例 数据库设计相关:(一)InnoDB表如何设计主键索引

2、SQL相关:(二)字符串索引隐式转换 (三)表数据被莫名清空 (四)InnoDB表更新锁问题 客户端相关:(五)客户端连接被中断 淘宝线淘宝线上十大上十大MySQL经典案例经典案例 “灵异事件”相关:(六)核心数据库被同时关闭 Slave相关:(七)Slave 事件Loop (八)Slave 更新操作找不到对应记录 (九)备库设置read_only被堵塞 Swap相关:(十)数据库服务器Swap CREATE TABLE a(id bigint(20)NOT NULL AUTO_INCREMENT,message_id int(11)NOT NULL,user_id int(11)NOT NU

3、LL,msg varchar(1024)DEFAULT NULL,gmt_create datetime NOT NULL,PRIMARY KEY(id),KEY user_id(user_id,message_id),KEY idx_gmt_create(gmt_create)ENGINE=InnoDB DEFAULT CHARSET=gbk;CREATE TABLE b(user_id int(11)NOT NULL,message_id int(11)NOT NULL,msg varchar(1024)DEFAULT NULL,gmt_create datetime NOT NULL,P

4、RIMARY KEY(user_id,message_id),KEY idx_gmt_create(gmt_create)ENGINE=InnoDB DEFAULT CHARSET=gbk;一一 InnoDB表如何设计主键索引表如何设计主键索引 CREATE TABLE a(id bigint(20)NOT NULL AUTO_INCREMENT,message_id int(11)NOT NULL,user_id int(11)NOT NULL,msg varchar(1024)DEFAULT NULL,gmt_create datetime NOT NULL,PRIMARY KEY(id)

5、,KEY user_id(user_id,message_id),KEY idx_gmt_create(gmt_create)ENGINE=InnoDB DEFAULT CHARSET=gbk;CREATE TABLE b(user_id int(11)NOT NULL,message_id int(11)NOT NULL,msg varchar(1024)DEFAULT NULL,gmt_create datetime NOT NULL,PRIMARY KEY(user_id,message_id),KEY idx_gmt_create(gmt_create)ENGINE=InnoDB DE

6、FAULT CHARSET=gbk;一一 InnoDB表如何设计主键索引表如何设计主键索引 大多大多数互联网业务数互联网业务(用户用户,消消息息)，都可以，都可以选择选择a或者或者b来满足业务需求，来满足业务需求，但但a表和表和b表有何区别？表有何区别？一一 InnoDB表如何设计主键索引表如何设计主键索引 记录记录 空间空间 优点优点 缺点缺点 A表 500万(顺序)509M 主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高?B表 500万(随机)361M？?一一 InnoDB表如何设计主键索引表如何设计主键索引 记录记录 空间空间 优点优点 缺点缺点 A表 500万(顺序)5

7、09M 主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高?B表 500万(随机)361M 1.物理空间相对减少 2.根据user_id查数据,直接走主键拿到数据，无需回表?一一 InnoDB表如何设计主键索引表如何设计主键索引 记录记录 空间空间 优点优点 缺点缺点 A表 500万 509M 主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高 物理空间相对较多 如果根据user_id来查记录,需要走两次IO B表 500万 361M 1.物理空间相对减少 2.根据user_id查数据,直接走主键拿到数据，无需回表?一一 InnoDB表如何设计主键索引表如何设计主键索引

8、 记录记录 空间空间 优点优点 缺点缺点 A表 500万 509M 主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高 物理空间相对较多 如果根据user_id来查记录,需要走两次IO B表 500万 361M 1.物理空间相对减少 2.根据user_id查数据,直接走主键拿到数据，无需回表 (user_id,message_id)为随机写入,Btree分裂成本高,写性能低 InnoDB表如何设计主键索引表如何设计主键索引 优点优点 适用适用 场景？场景？A表 主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高 写操作较多的场景 B表 1.物理空间相对减少 2.根据us

9、er_id查数据,直接走主键拿到数据，无需回表 InnoDB表如何设计主键索引表如何设计主键索引 优点优点 适用适用 场景？场景？A表 主键ID自增,在写入数据的时候,Btree分裂成本低,写性能高 写操作较多的场景 B表 1.物理空间相对减少 2.根据user_id查数据,直接走主键拿到数据，无需回表 写少读多的场景,例如从hadoop回流到MySQL的统计结果表，这种统计结果一般数据较多，但主要是读 SQL相关相关-案案例二字符串索例二字符串索引隐式转换引隐式转换 Create Table:CREATE TABLE index_str(id int(11)NOT NULL AUTO_INC

10、REMENT,user_id varchar(30)NOT NULL,name varchar(30)DEFAULT NULL,PRIMARY KEY(id),KEY idx_user_id(user_id)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk SQL相关相关-案案例二字符串索例二字符串索引隐式转换引隐式转换 Create Table:CREATE TABLE index_str(id int(11)NOT NULL AUTO_INCREMENT,user_id varchar(30)NOT NULL,name varchar(3

11、0)DEFAULT NULL,PRIMARY KEY(id),KEY idx_user_id(user_id)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk Id一般为数字，但这个一般为数字，但这个表把表把id定义为了定义为了varchar类型。类型。SQL相关相关-案案例二字符串索例二字符串索引隐式转换引隐式转换 roottest 11:39:38select*from index_str;+-+-+-+|id|user_id|name|+-+-+-+|1|1111|NULL|2|0001|NULL|3|1|NULL|+-+-+-+3 r

12、ows in set(0.00 sec)恩.有三条记录 SQL相关相关-案例二字符串索引隐式案例二字符串索引隐式转换转换 当user_id=1的时候,查询分析器表示没用到idx_user_id索引 SQL相关相关-案例二字符串索引隐式转换案例二字符串索引隐式转换 当user_id=1的时候,查询分析器用到了idx_user_id的索引 SQL相关相关案例案例二二 字符串索引隐式转字符串索引隐式转换换 原原因分析因分析 数字类型的0001等价于1 字符串的0001和1不等值 当字符串的列有对应的索引，而在where条件里面不指定为字符串，index无法确认最终的记录 SQL相关相关-案例二字符串

13、索引隐式转换案例二字符串索引隐式转换 user_id=0001的时候,MySQL返回两条记录(被转换成1)user_id=0001的时候,MySQL返回一条记录 SQL相关相关-案例二字符串索引隐式转换案例二字符串索引隐式转换 user_id=1的时候,MySQL返回两条记录(被转换成数字值)user_id=1的时候,MySQL返回两条记录 SQL相关相关-案例二字符串索引隐式转换案例二字符串索引隐式转换 被隐式转换后，会进行全表遍历 建表需要注意对应好字段类型 SQL相关相关 案例三案例三 表被莫名清空表被莫名清空 roottest 12:34:22select*from test_dele

14、te;+-+|id|+-+|1|2|3|4|5|+-+5 rows in set(0.00 sec)全表清理的普通写法 1.delete from test_delete;2.delete from test_delete where id in(1,2,3,4,5);3.truncate table test_delete;4.delete from test_delete where id 0;还有什么二逼语句可以删除全表数据?SQL相关相关 案例三案例三 表被莫名清空表被莫名清空 1.delete from test_delete where a=a;Query OK,5 rows af

15、fected(0.00 sec)2.delete from test_delete where id=1 or a=a;Query OK,5 rows affected(0.00 sec)3.delete from test_delete where id;Query OK,5 rows affected(0.00 sec)SQL相关相关 案例三案例三 表被莫名清空表被莫名清空 DELETE FROM test_delete WHERE EXISTS(SELECT*FROM (SELECT*FROM test_delete WHERE id=5)AS b);Query OK,5 rows af

16、fected(0.00 sec)SQL相关相关 案例三案例三 表被莫名清空表被莫名清空 DELETE FROM test_delete WHERE EXISTS(SELECT*FROM (SELECT*FROM test_delete WHERE id=5)AS b);Query OK,5 rows affected(0.00 sec)exists后面的子查询是有返回值,恒真.导致前面的delete语句被触发 SQL相关相关 案案例四例四 InnoDB表更新锁问题表更新锁问题 CREATE TABLE a(id bigint(20)NOT NULL AUTO_INCREMENT,message

17、_id int(11)NOT NULL,user_id int(11)NOT NULL,msg varchar(1024)DEFAULT NULL,gmt_create datetime NOT NULL,PRIMARY KEY(id),KEY user_id(user_id,message_id),KEY idx_gmt_create(gmt_create)ENGINE=InnoDB DEFAULT CHARSET=gbk;业业务需要订正数据务需要订正数据 tx_isolation=REPEATABLE-READ UPDATE message_auto SET gmt_create=Now(

18、)WHERE msg IS NOT NULL;msg is not null 用不到索引,也没有索引 对于其他insert,delete,update,select会有何影响?roottest 01:21:21update message_auto set gmt_create=now()where mtext is not null;Query OK,5000002 rows affected(1 min 21.88 sec)Rows matched:5000002 Changed:5000002 Warnings:0 roottest 01:21:23update message_auto

19、 set gmt_create=now()where id=1;Query OK,1 row affected(1 min 16.96 sec)Rows matched:1 Changed:1 Warnings:0 roottest 01:22:08insert into message_auto value(10000001,1,1,hello,hello,now();Query OK,1 row affected(0.00 sec)roottest 01:22:12update message_auto set gmt_create=now()where id=10000001;Query

20、 OK,1 row affected(0.01 sec)Rows matched:1 Changed:1 Warnings:0 roottest 01:21:21update message_auto set gmt_create=now()where mtext is not null;Query OK,5000002 rows affected(1 min 21.88 sec)Rows matched:5000002 Changed:5000002 Warnings:0 roottest 01:21:23update message_auto set gmt_create=now()whe

21、re id=1;Query OK,1 row affected(1 min 16.96 sec)Rows matched:1 Changed:1 Warnings:0 roottest 01:22:08insert into message_auto value(10000001,1,1,hello,hello,now();Query OK,1 row affected(0.00 sec)roottest 01:22:12update message_auto set gmt_create=now()where id=10000001;Query OK,1 row affected(0.01

22、sec)Rows matched:1 Changed:1 Warnings:0 id=1的记录update操作需要和update全表差不多的时间 insert新的记录和update最新的记录，时间非常短 SQL相关相关 案例四案例四 InnoDB表更新锁问题表更新锁问题 mysql tables in use 1,locked 1 649 lock struct(s),heap size 80312,146316 row lock(s),undo log entries 145669 MySQL thread id 2675649,query id 610234950 localhost ro

23、ot Updating update message_auto set gmt_create=now()where mtext is not null show engine innodb statusG 案例五案例五 客户端连接被中断客户端连接被中断 PHP Warning:mysql_connect()function.mysql-connect:Cant connect to MySQL server on 客户端IP地址(99)in/home/admin/cai/html/common/db_mysql.class.php on line 27 案例五案例五 客户端连接被中断客户端连接

24、被中断 PHP Warning:mysql_connect()function.mysql-connect:Cant connect to MySQL server on 客户端IP地址(99)in/home/admin/cai/html/common/db_mysql.class.php on line 27 perror 99 OS error code 99:Cannot assign requested address 案例五案例五 客户端连接被中断客户端连接被中断 客户端的TCP连接相关统计信息 80端口连接数(CurrentConnection):16 nginx进程数量:10 p

25、hp进程数量:130 TCP连接状态:TIME_WAIT 26258 FIN_WAIT1 1 FIN_WAIT2 6 ESTABLISHED 893 TIME_WAIT的数量非常多,也可以通过netstat ant|grep TIME_WAIT来排查 案例五案例五 客户端连接被中断客户端连接被中断 TIME_WAIT过多的原因 nginx使用了短连接方式，会造成大量处于TIME_WAIT状态的连接 案例五案例五 客户端连接被中断客户端连接被中断 解决办法 让TIME_WAIT状态可以重用，这样即使TIME_WAIT占满了所有端口，也不会拒绝新的请求造成障碍 net.ipv4.tcp_tw_re

26、cycle=1 net.ipv4.tcp_tw_reuse=1 让TIME_WAIT尽快回收 案例五案例五 客户端连接被中断客户端连接被中断 其他案例 Cause:org.jboss.util.NestedSQLException:No ManagedConnections available within configured blocking timeout(1000 ms);-nested throwable:(javax.resource.ResourceException:No ManagedConnections available within configured blocki

27、ng timeout(1000 ms)Java服务器频繁GC导致没有可用连接池 案例五案例五 客户端连接被中断客户端连接被中断 其他案例 2011-02-23 11:47:16 WARNING CONNECT_ERROR -“host”:“172.19.70.59”,“port”:3306,“user”:“USER”,“pass”:“3*2”,“error”:“Cant connect to MySQL server on 客户端IP(4)错误代码4是表示系统中断 PHP服务器文件句柄不足,连接被中断 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 昨天下午14点44分 两台核

28、心MySQL数据库在同时升级一个任务调度程序 120921 14:44:54 Note/u01/mysql/libexec/mysqld:Normal shutdown 120921 14:44:54 Note/u01/mysql/libexec/mysqld:Normal shutdown B服务器 A服务器 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 120921 14:44:54 Note/u01/mysql/libexec/mysqld:Normal shutdown 120921 14:44:54 Note/u01/mysql/libexec/mysqld:Nor

29、mal shutdown 共同点 都是同一个时间点开始Normal shutdown Kill-9 mysqld_pid mysqld_safe Number of processes running now:0 mysqld_safe mysqld restarted 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 可以让MySQL Normal Shutdown的命令 本地执行 mysqladmin uroot pxxx shutdown mysqladmin uroot pxxx sh 超级用户远程shutdown mysqladmin utest pxxx hxxxx

30、shutdown 单时候开着电脑的DBA不多，除了并发更新监控程序，再没有其他操作。调度程序里面也没有进行shutdown的操作 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 可以让MySQL Normal Shutdown的命令 本地执行 mysqladmin uroot pxxx shutdown mysqladmin uroot pxxx sh 超级用户远程shutdown mysqladmin utest pxxx hxxxx shutdown 当时候开着电脑的DBA不多，除了并发更新监控程序，再没有其他操作。监控程序里面也没有带有shutdown的操作 案案例六例六

31、 两台两台核核心数据库被同时关闭心数据库被同时关闭 kill-9 mysqld_pid 和kill mysqld_pid的区别？案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 kill-9 mysqld_pid 和kill mysqld_pid的区别？Kill-9 mysqld_pid mysqld_safe Number of processes running now:0 mysqld_safe mysqld restarted kill mysqld_pid InnoDB:Normal Shutdown.案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 调度

32、程序(agent)的启动和重启 启动agent的同时,把自己的pid记录到一个文件 重启agent的时候,获取文件里面的pid,调用os.kill(pid)杀掉agent进程 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 相关事件回放 9.13之前:agent和MySQL都存活,假设pid分别为a,m 9.13:机器硬件维护,MySQL正常关闭,机器重启，agent被异常关闭,但记录pid的文件没被删除 9.13:MySQL重新启动,OS分配了pid(a)给MySQL,但agent没被启动 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 相关事件回放 9.21

33、(昨天)升级agent程序,先将agent关闭,agent里面的代码直接执行了os.kill(a)由于a这个pid已经是属于MySQL的pid，当agent执行os.kill(a)的时候 MySQL日志打印Normal shutdown 案案例六例六 两台两台核核心数据库被同时关闭心数据库被同时关闭 总结 os.kill(pid)得判断这个pid是否属于自己,避免误杀 OS的pid分配,可能会分配其他已被关闭程序的pid 内核版本 2.6.18-164.el5 /proc/sys/kernel/pid_max 32768(signed short int)案例七案例七 Slave 事件事件Lo

34、op Seconds_Behind_Master的定义 每一个 Binlog事件,都存了当前事件的时间戳 IO_Thread执行当前事件的时间戳(A)SQL_Thread执行当前事件的时间戳(B)Seconds_Behind_Master=A-B 案例七案例七 Slave 事件事件Loop os.kill(pid)得判断这个pid是否属于自己,避免误杀 OS的pid分配,可能会分配其他已被关闭程序的pid 内核版本 2.6.18-164.el5 /proc/sys/kernel/pid_max 32768(signed short int)案例七 Slave 事件Loop 案例七 Slave

35、事件Loop 简单重现的办法 1.配置server1和server2为双主结构：server1(server_id=1)server2(server_id=1)2、在server1执行以下SQL命令：mysql create table test.t1(id int);mysql stop slave;insert into t1 values(1);mysql set global server_id=3;mysql start slave;3、在server1，server2上执行：mysql select count(*)from t1;案例七 Slave 事件Loop 解决办法 Cha

36、nge Master to.IGNORE_SERVER_IDS 停掉主库的写,在备库寻找最后一个正确的position,change master to 如果修改过server_id,把server_id重新改回来 更详细信息 T MySQL复制事件复制事件在主备之间来回传输检测在主备之间来回传输检测 案例八 Slave 找不到对应记录 ERROR Slave SQL:Could not execute Update_rows event on table tbtry.try_audit;Cant find record in xxx_table,Error_code:1032;handler

37、 error HA_ERR_KEY_NOT_FOUND;the events master log mysql-bin.002403,end_log_pos 67019815,Error_code:1032 案例八 Slave 找不到对应记录 ERROR Slave SQL:Could not execute Update_rows event on table tbtry.try_audit;Cant find record in xxx_table,Error_code:1032;handler error HA_ERR_KEY_NOT_FOUND;the events master lo

38、g mysql-bin.002403,end_log_pos 67019815,Error_code:1032 binlog_format=ROW,binlog里面记录每个字段的具体值,通过slave_error_handler进行检测回补 案例八 Slave 找不到对应记录 案例九 备库设置read_only被堵塞 *13.row*Id:328071 User:root Host:localhost db:NULL Command:Query Time:81 State:Waiting for table flush Info:set global read_only=1 Rows_sent

39、:0 Rows_examined:0 Rows_read:1 案例九 备库设置read_only被堵塞 *13.row*Id:328071 User:root Host:localhost db:NULL Command:Query Time:81 State:Waiting for table flush Info:set global read_only=1 Rows_sent:0 Rows_examined:0 Rows_read:1 备库正在执行mysqldump 前端请求响应变慢 DB 负载上升 free m 后发现 开始使用Swap 十十 线上数据库出现线上数据库出现swap的案例的案例 操作系统设置swap的目的 十十 线上数据库出现线上数据库出现swap的案例的案例 http:/www.linux- 十十 线上数据库出现线上数据库出现swap的案例的案例 Flashcache压测，Flashcache元数据本身需要内存 多实例的主备库混搭 mysqldump q(Dont buffer query,dump directly to stdout)/proc/sys/vm/swappiness和/etc/sysctl.conf不一致 table_open_cache 和table_definition_cache过大 谢谢大家谢谢大家 新浪微博杨德华Devin