知数堂资深MySQL讲师,前新媒传信首席DBA、MySQL中国用户组(ACMUG)主席吴炳锡老师在5月19日晚上20:30做了在线分享《如何针对业务做DB优化》,受到了很多朋友的肯定和支持。也要再次感谢KVM虚拟化实践社区&Ceph中国社区的鼎力支持。
1、分享资料下载
百度云盘链接:http://pan.baidu.com/s/1mhSgnlm 密码:2ycr ,欢迎转存并再次分享。
2、本次分享PPT
这次的PPT我也在slideshare上放了一份。
用LOAD DATA导入数据却一直提示主键冲突问题解决案例。
有位学生遇到数据导入时一直提示1022主键冲突问题,而导入的数据明明完全没有任何冲突,百思不得其解,请我帮忙协查。
下面是关于该问题现象描述:
1、表结构DDL
CREATE TABLE `wcp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`txcode` char(4) NOT NULL,
`notice_from` enum(‘page’,’server’) ,
`message` varchar(600) NOT NULL ,
`signature` varchar(260) NOT NULL ,
`payment_no` char(30) NOT NULL ,
`notice_time` int(10) unsigned NOT NULL COMMENT ,
`dealt_ok` tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT,
PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=117 DEFAULT CHARSET=utf8;
欲导入的数据有116条,可以非常肯定的是,主键值都是顺序增长的,完全没有冲突,所以感觉非常奇怪。
细心的同学,从上面我贴的表DDL或许能感觉出什么不对劲的味道(嗯,有点像狗狗嗅觉灵敏的意思,哈哈)
经我这么一提醒,再认真看一下,是不是真的发现了什么?嗯,很棒,答对了(从我女儿爱看的米奇妙妙屋里学到的语气词,嘿),该表的引擎是ARCHIVE,而不是我们常用的InnoDB或MyISAM,会不会和这个有关系呢?
ARCHIVE几乎没怎么被用过,非常冷门,它有什么特点呢。翻翻手册便知:
The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.
The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations, BLOB columns, and basically all but spatial data types (see Section 11.17.4.1, “MySQL Spatial Data Types”). The ARCHIVE engine uses row-level locking.
The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.
可以看到,ARCHIVE引擎和我们平时用的InnoDB、MyISAM区别还是很大的。它不支持DELETE、REPLACE、UPDATE等操作命令,只支持INSERT、SELECT、LOAD DATA等。ARCHIVE也支持自增列属性,但也必须是普通索引、唯一索引或主键索引(这个和其他引擎基本一致)。
接下来我们尝试解决数据导入主键冲突的问题。
在这个例子中,想要最终能导入数据的话,有几个方法:
- 删除主键定义,或者删除主键的自增属性;
- 修改id列的主键索引为唯一索引或者普通索引;
- 调整表DDL定义时指定的AUTO_INCREMENT值为0或1;
- 修改表引擎为InnoDB或MyISAM;
第3个解决方案也是我自己几次反复测试才发现的,手册中也未提及,比较奇葩,这也是这次的案例令人最为不解的地方。
我在很多场合强调过,InnoDB引擎已可适用95%以上的业务场景,完全没必要再使用其他引擎了,这次的奇葩案例也是一开始没注意到用ARCHIVE引擎而走了些弯路。
不听老叶言,吃亏在眼前,这话我看在理,嘿嘿~
关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)
最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达
这是上周末在第二节全球PHP开发者大会上分享的内容,本来打算周六晚上就发布的,但因为拖延症又发作了,直到现在才发布,嘿~
这次的PPT我也同时放到百度云盘里了,欢迎下载转存。百度云盘链接: http://pan.baidu.com/s/1pKX1qTt 密码: y7ut
我放了一份在slideshare上,看看有多少能翻墙的同学,哈哈。
在slave上,发现SQL thread长时间处于Opening tables状态
朋友的数据库,做了主从replication复制。在slave实例上,SQL thread的长时间处于Opening tables状态,复制进程异常。
mysql> show processlist;
+—-+————-+———–+——+———+——-+—————————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——-+—————————————-+——————+
| 1 | system user | | NULL | Connect | 554 | Queueing master event to the relay log | NULL |
| 2 | system user | | NULL | Connect | 59212 | Opening tables | NULL |
整个实例大概20个database,总共300G左右。
master是5.5版本,slave是5.6版本,master上执行xtrabackup全库备份后搭建的slave。
我的第一反应是table cache是不是太小了,导致open table比较慢,所以才长时间处于这个状态。无论如何,先一层层排查吧。
先看下slave status(部分无用信息我隐掉了):
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Log_File: master-bin.000618
Read_Master_Log_Pos: 614915856
Relay_Log_File: replicate.000008
Relay_Log_Pos: 2384117
Relay_Master_Log_File: master-bin.000617
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 252048331
Relay_Log_Space: 1438994074
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Seconds_Behind_Master: 59240
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /home/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Opening tables
Master_Retry_Count: 86400
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
看不出来有什么异常的。
再看下系统负载情况:
[root@localhost mysql]# vmstat -S m 1
procs ———–memory———- —swap– —–io—- –system– —–cpu—–
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 2 1 19591 458 230576 0 0 11 20 0 0 0 0 100 0 0
1 1 1 19587 458 230579 0 0 2032 2528 1645 584 1 1 93 4 0
1 1 1 19583 458 230582 0 0 1664 2712 1773 461 1 1 93 4 0
0 2 1 19578 458 230585 0 0 2080 3376 1810 660 1 1 93 4 0
2 0 1 19576 458 230587 0 0 2224 1804 1634 594 1 1 94 4 0
3 1 1 19569 458 230590 0 0 1968 3488 1693 566 1 1 93 4 0
1 1 1 19567 458 230593 0 0 2016 2632 1775 515 1 1 93 4 0
[root@localhost mysql]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (localhost.localdomain) 03/21/2015 _x86_64_ (24 CPU)
03:21:57 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
03:21:58 PM dev8-0 185.86 5753.54 64.65 31.30 1.20 6.48 5.08 94.44
03:21:58 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
03:21:59 PM dev8-0 197.03 6114.85 95.05 31.52 1.16 5.88 4.48 88.32
03:21:59 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
03:22:00 PM dev8-0 188.89 5882.83 88.89 31.61 1.14 6.03 5.09 96.16
03:22:00 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
03:22:01 PM dev8-0 166.00 5152.00 80.00 31.52 1.42 8.56 5.58 92.70
也看不出来异常,继续看看mysql的日志吧:
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’objects_summary_global_by_type’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Incorrect definition of table performance_schema.rwlock_instances: expected column ‘WRITE_LOCKED_BY_THREAD_ID’ at position 2 to have type bigint(20), found type int(11).
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’setup_actors’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’setup_objects’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’table_io_waits_summary_by_index_usage’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’table_io_waits_summary_by_table’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’table_lock_waits_summary_by_table’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Column count of mysql.threads is wrong. Expected 14, found 3. Created with MySQL 50524, now running 50623. Please use mysql_upgrade to fix this error.
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’events_stages_current’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’events_stages_history’ has the wrong structure
2015-04-21 14:51:04 18667 [ERROR] Native table ‘performance_schema’.’events_stages_history_long’ has the wrong structure
…
…
2015-04-21 15:05:17 7f5997fff700 InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.
2015-04-21 15:05:17 7f5997fff700 InnoDB: Error: Fetch of persistent statistics requested for table “db_anonymous_info_10”.”t_friend_info_3″ but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
2015-04-21 15:05:17 7f7beb0cd700 InnoDB: Error: Table “mysql”.”innodb_table_stats” not found.
2015-04-21 15:05:17 7f7beb0cd700 InnoDB: Error: Fetch of persistent statistics requested for table “db_user_trade_1”.”t_trade_15″ but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
可以看到,这个实例中,P_S(performance_schema)库的几个表结构都异常了。另外,mysql.innodb_table_stats表也异常了,导致InnoDB表数据收集异常,然后SQL thread也跟着异常了。
造成上面问题主要原因是,没有用正确的方式来部署slave实例。从master上用xtrabackup物理备份到slave,启动实例后,应该再执行 mysql_upgrade 升级相关表结构,确保P_S(performance_schema)、I_S(information_schema)以及 mysql 等几个系统库表结构都升级到最新版本。
关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)
最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达