遇到诡异的问题 stop slave 无法停止

来源：互联网发布：南京未来网络小镇规划编辑：程序博客网时间：2024/06/01 09:07

在测试mysql transfer 性能的时候遇到问题，slave 在某个偏移位置点长时间的被卡主，只要一运行stop slave 就卡住，sql_thread线程执行出现问题，在master端的操作不能有效的传递到slave端。周五下午在master上面建的表，周六才传递到slave端。

查看偏移位置的操作发现很多这种 # at ，卡住的偏移在 # at 196484929 位置

[root@localhost test2]# mysqlbinlog -v -v -j 196484929 mysql-bin.000005 |less
# at 196484929
#130816 12:51:48 server id 2 end_log_pos 196484998 Query thread_id=14400043 exec_time=2 error_code=0
SET TIMESTAMP=1376628708/*!*/;
SET @@session.pseudo_thread_id=14400043/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 196484998
# at 196485054
# at 196486091
# at 196487128
# at 196488165
# at 196489202
# at 196490239
# at 196491276
# at 196492313
# at 196493350
# at 196494387
# at 196495424
# at 196496461
# at 196497498
# at 196498535
# at 196499572
# at 196500609
# at 196501646
# at 196502683
# at 196503720
# at 196504757
# at 196505794
# at 196506831
# at 196507868
# at 196508905
# at 196509942
# at 196510979
# at 196512016
# at 196513053
# at 196514090
# at 196515127
# at 196516164
# at 196517201
# at 196518238
# at 196519275
# at 196520312
# at 196521349
# at 196522386
# at 196523423
# at 196524460
# at 196525497
# at 196526534
# at 196527571
# at 196528608
# at 196529645
# at 196530682
# at 196531719
# at 196532756
# at 196533793
# at 196534830
# at 196535867
# at 196536904
# at 196537941
# at 196538978
# at 196540015
# at 196541052
# at 196542089
# at 196543126
# at 196544163
# at 196545200
# at 196546237
# at 196547274
# at 196548311
# at 196549348
# at 196550385
# at 196551422
# at 196552459

后面还有很多at

怀疑是不是这个事务的点操作太长了？

后来查找判定是sql_thread执行长事务导致，stop slave夯住