《MySQL主库xtrabackup备份导致应用异常》遗留问题(二)

案例重现
Session 1:跑procedure,主要是向表中insert记录

root@lx 10:55:17>call sp_myddl;

session 2: tps上升

                    -QPS- -TPS-
  ins   upd   del    sel   iud|
    0     0     0      0     0|
    0     1     0      1     1|
    0     0     0      1     0|
    0     0     0     19     0|
 1640     1     0      1  1641|
 2305     0     0      1  2305|
 2318     1     0      1  2319|

Session 3:跑长查询

root@lx 10:55:45>select count(*) from test_myddl a,test_myddl b;

session 4:FTWRL

root@lx 10:55:57>flush tables with read lock; --堵住

session 2:所以有tps都会堵住

  ins   upd   del    sel   iud|
    0     0     0      9     0|
    0     0     0     11     0|
    0     0     0      1     0|
    0     0     0      2     0|
    0     0     0      1     0|
    0     0     0      1     0|
    0     0     0      1     0|
    0     0     0      1     0|

Session 5:杀掉FTWRL mysql process id

root@(none) 10:56:58>kill 72863;
Query OK, 0 rows affected (0.00 sec)

Session 4:表现

root@lx 10:55:57>flush tables with read lock;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Session 2:insert test_myddl未恢复正常,但对其他表的DML操作恢复

  ins   upd   del    sel   iud|
    0     1     0      1     1|
    0     0     0      1     0|
    0     1     0      1     1|
    0     0     0      1     0|
    0     1     0      1     1|
    0     0     0     19     0|
    0     1     0      1     1|

Session 3:强制退出长查询

root@lx 10:55:45>select count(*) from test_myddl a,test_myddl b;
^CCtrl-C -- sending KILL QUERY 72826" to server ...

Session 2:insert写入恢复

  ins   upd   del    sel   iud|
 2345     0     0     19  2345|
 2346     1     0      1  2347|
 2319     0     0      1  2319|
 2341     1     0      4  2342|
 2377     0     0      1  2377|
 2401     1     0      1  2402|
 2393     0     0      1  2393|
 2410     1     0      1  2411|
 2320     0     0      1  2320|
 2332     1     0      1  2333|

Slow.log记录信息:

# Time: 121227 22:57:07
# User@Host: root[root] @ localhost []
# Thread_id: 72863  Schema: lx  Last_errno: 1160  Killed: 1053
# Query_time: 64.780099  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0  Rows_read: 0
# Bytes_sent: 0  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
SET timestamp=1356620227;
flush tables with read lock;
# Time: 121227 22:57:38
# User@Host: root[root] @ localhost []
# Thread_id: 72826  Schema: lx  Last_errno: 1160  Killed: 0
# Query_time: 106.972363  Lock_time: 0.000156  Rows_sent: 0  Rows_examined: 7126116  Rows_affected: 0  Rows_read: 0
# Bytes_sent: 0  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 3A3A27
SET timestamp=1356620258;
select count(*) from test_myddl a,test_myddl b;

小结:当FTWRL时,insert等待Waiting for global read lock,当kill掉FTWRL时insert(select也是)等待Waiting for table flush,其他表的写入没有影响。退出长查询,insert恢复正常。

新问题:test_myddl都没有closing table成功,为什么不能insert和select成功呢?

下次解释,写周报


Post a Comment