淘宝 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 表如何设计主键索引 SQL 相关 : ( 二 ) 字符串索引隐式转换 ( 三 ) 表数据被莫名清空 ( 四 ) InnoDB 表更新锁问题 客户端相关 : ( 五 ) 客户端连接被中断
淘宝线上十大 MySQL 经典案例 灵异事件 相关 : ( 六 ) 核心数据库被同时关闭 Slave 相关 : ( 七 ) Slave 事件 Loop ( 八 ) Slave 更新操作找不到对应记录 ( 九 ) 备库设置 read_only 被堵塞 Swap 相关 : ( 十 ) 数据库服务器 Swap
一 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`), 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 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`), 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 DEFAULT CHARSET=gbk; 大多数互联网业务 ( 用户, 消息 ), 都可以选择 a 或者 b 来满足业务需求, 但 a 表和 b 表有何区别?
一 InnoDB 表如何设计主键索引 记录空间优点缺点 A 表 500 万 509M 主键 ID 自增, 在写入数据? ( 顺序 ) 的时候,Btree 分裂成本低, 写性能高 B 表 500 万 ( 随机 ) 361M??
一 InnoDB 表如何设计主键索引 记录空间优点缺点 A 表 500 万 509M 主键 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 表如何设计主键索引 记录 空间 优点 缺点 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. 根据 user_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_INCREMENT, `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(30) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) Id 一般为数字, 但这个表把 id 定义为了 varchar 类型 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk
SQL 相关 - 案例二字符串索引隐式转换 root@test 11:39:38>select * from index_str; +----+---------+------+ id user_id name +----+---------+------+ 1 1111 NULL 2 0001 NULL 3 1 NULL +----+---------+------+ 3 rows 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 相关 - 案例二字符串索引隐式转换 user_id=0001 的时候,MySQL 返回两条记录 ( 被转换成 1) user_id= 0001 的时候,MySQL 返回一条记录
SQL 相关 - 案例二字符串索引隐式转换 user_id=1 的时候,MySQL 返回两条记录 ( 被转换成数字值 ) user_id= 1 的时候,MySQL 返回两条记录
SQL 相关 - 案例二字符串索引隐式转换 被隐式转换后, 会进行全表遍历 建表需要注意对应好字段类型
SQL 相关案例三表被莫名清空 root@test 12:34:22>select * from test_delete; +------+ 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 affected (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 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 affected (0.00 sec) exists 后面的子查询是有返回值, 恒真. 导致前面的 delete 语句被触发
SQL 相关案例四 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`), 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() WHERE msg IS NOT NULL; msg is not null 用不到索引, 也没有索引对于其他 insert,delete,update,select 会有何影响?
root@test 01:21:21>update 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 root@test 01:21:23>update message_auto set gmt_create=now() where id=1; Query OK, 1 row affected (1 min 16.96 sec)rows matched: 1 Changed: 1 Warnings: 0 root@test 01:22:08>insert into message_auto value(10000001,1,1,'hello','hello',now()); Query OK, 1 row affected (0.00 sec) root@test 01:22:12>update message_auto set gmt_create=now() where id=10000001; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@test 01:21:21>update 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 root@test 01:21:23>update message_auto set gmt_create=now() where id=1; Query OK, 1 row affected (1 min 16.96 sec)rows matched: 1 Changed: 1 Warnings: 0 id=1 的记录 update 操作需要和 update 全表差不多的时间 root@test 01:22:08>insert into message_auto value(10000001,1,1,'hello','hello',now()); Query OK, 1 row affected (0.00 sec) root@test 01:22:12>update message_auto set gmt_create=now() where id=10000001; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 insert 新的记录和 update 最新的记录, 时间非常短
SQL 相关案例四 InnoDB 表更新锁问题 show engine innodb status\g 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 root Updating update message_auto set gmt_create=now() where mtext is not null
案例五客户端连接被中断 PHP Warning: mysql_connect() [<a href= function.mysql-connect >function.mysqlconnect</a>]: Can t connect to MySQL server on 客户端 IP 地址 ' (99) in /home/admin/cai/html/common/db_mysql.clas s.php on line 27
案例五客户端连接被中断 PHP Warning: mysql_connect() [<a href= function.mysql-connect >function.mysqlconnect</a>]: Can t connect to MySQL server on 客户端 IP 地址 ' (99) in /home/admin/cai/html/common/db_mysql.clas s.php on line 27 perror 99 OS error code 99: Cannot assign requested address
案例五客户端连接被中断 客户端的 TCP 连接相关统计信息 80 端口连接数 (CurrentConnection): 16 nginx 进程数量 : 10 php 进程数量 : 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_recycle = 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 blocking timeout ( 1000 [ms] )) Java 服务器频繁 GC 导致没有可用连接池
其他案例 案例五客户端连接被中断 2011-02-23 11:47:16 WARNING CONNECT_ERROR - { host : 172.19.70.59, port :3306, user : USE R, pass : 3*******2, error : Can t connect to MySQL server on 客户端 IP' (4)"} 错误代码 4 是表示系统中断 PHP 服务器文件句柄不足, 连接被中断
案例六两台核心数据库被同时关闭 昨天下午 14 点 44 分 两台核心 MySQL 数据库在同时升级一个任务调度程序 120921 14:44:54 [Note] /u01/mysql/libexec/mysqld: Normal shutdown A 服务器 120921 14:44:54 [Note] /u01/mysql/libexec/mysqld: Normal shutdown B 服务器
案例六两台核心数据库被同时关闭 120921 14:44:54 [Note] /u01/mysql/libexec/mysqld: Normal shutdown 共同点都是同一个时间点开始 Normal shutdown 120921 14:44:54 [Note] /u01/mysql/libexec/mysqld: 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 shutdown 单时候开着电脑的 DBA 不多, 除了并发更新监控程序, 再没有其他操作 调度程序里面也没有进行 shutdown 的操作
案例六两台核心数据库被同时关闭 可以让 MySQL Normal Shutdown 的命令本地执行 mysqladmin uroot pxxx shutdown mysqladmin uroot pxxx sh 超级用户远程 shutdown mysqladmin utest pxxx hxxxx shutdown 当时候开着电脑的 DBA 不多, 除了并发更新监控程序, 再没有其他操作 监控程序里面也没有带有 shutdown 的操作
案例六两台核心数据库被同时关闭 kill -9 mysqld_pid 和 kill mysqld_pid 的区别?
案例六两台核心数据库被同时关闭 Kill -9 mysqld_pid mysqld_safe Number of processes running now: 0 mysqld_safe mysqld restarted kill -9 mysqld_pid 和 kill mysqld_pid 的区别? kill mysqld_pid InnoDB: Normal Shutdown...
案例六两台核心数据库被同时关闭 调度程序 (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( 昨天 ) 升级 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 事件 Loop 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 事件 Loop 1. 配置 server1 和 server2 为双主结构 : server1(server_id=1) < (MM) > 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 Change Master to...ignore_server_ids 停掉主库的写, 在备库寻找最后一个正确的 position,change master to 如果修改过 server_id, 把 server_id 重新改回来 更详细信息 Taobaodba.com MySQL 复制事件在主备之间来回传输检测
案例八 Slave 找不到对应记录 [ERROR] Slave SQL: Could not execute Update_rows event on table tbtry.try_audit; Can't find record in xxx_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's 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; Can't find record in xxx_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log 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: 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
十线上数据库出现 swap 的案例 前端请求响应变慢 DB 负载上升 free m 后发现开始使用 Swap
十线上数据库出现 swap 的案例 操作系统设置 swap 的目的
http://www.linux-mag.com/id/7589/ http://www.cs.cornell.edu/projects/ladis2009/talks/de an-keynote-ladis2009.pdf
十线上数据库出现 swap 的案例 Flashcache 压测,Flashcache 元数据本身需要内存 多实例的主备库混搭 mysqldump q (Don t buffer query, dump directly to stdout) /proc/sys/vm/swappiness 和 /etc/sysctl.conf 不一致 table_open_cache 和 table_definition_cache 过大
谢谢大家 新浪微博 @ 杨德华 Devin