故障案例:slave延遲很大,故障案例slave延遲

來源:互聯網
上載者:User

故障案例:slave延遲很大,故障案例slave延遲

案例1:lvm機型,從庫建立完成後,主庫qps 2w,從庫6k多。從某個時間點開始延遲在緩慢增加,一直漲到7w多秒才發現去處理;從庫io的util很高

故障原因:查看配置發現這個從庫開啟了log_slave_updates,一直在產生binlog,當把這個參數禁用或者設定sync_binlog=0以後,util立馬就降下來了,同步延遲也就慢慢變小了直到為0,此前已經發現多次使用lvm邏輯卷管理或者SSD的機器的udb只要開啟了這個sync_binlog=1,好像磁碟util都很高,也沒有深究為什麼。


案例2:slave延遲一直在增大,從庫和主庫的qps很低,io都很低,從庫cpu 100%

故障原因:因為從庫執行SQL是單線程的,所以只能利用一個CPU的資源,當cpu使用率到100%,整個庫都卡住了,不管什麼操作都很慢;發現從庫上只執行一個簡單的主鍵更新操作,所以很奇怪為什麼主鍵更新還這麼慢

     ID: 6933
   USER: system user
   HOST: 
     DB: gangao
COMMAND: Connect
   TIME: 457156
  STATE: Updating
   INFO: UPDATE`STK_DAILYQUOTEFA`SET`ID`=0x3631323538393739303330,`SECUCODE`=0x3237353233,`TRADINGDAY`='2003-03-18 00:00:00',`TRADINGSTATE`=0x31,`PREVCLOSINGPRICEBA`=0x312E363238,`OPENINGPRICEBA`=0x312E36323633,`HIGHESTPRICEBA`=0x312E36333332,`LOWESTPRICEBA`=0x312E36303734,`CLOSINGPRICEBA`=0x312E363136,`ENTRYTIME`='2015-07-24 15:41:13',`UPDATETIME`='2015-07-24 15:41:13',`GROUNDTIME`='2015-07-24 15:41:13',`UPDATEID`=0x3631323538393739303330,`RESOURCEID`=0x43616C63,`RECORDID`=NULL WHERE`ID`=0x3631323538393739303330


後來發現這裡的ID是0x3631323538393739303330十六機製表示的字串


普通10進位int值時的查詢計劃

mysql> explain select * from gangao.STK_DAILYQUOTEFA where id = 60737669922;
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | STK_DAILYQUOTEFA | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (2.84 sec)

注意這裡轉過去其實是字元,而問題就是出在這裡
mysql> select hex('60737669922');
+------------------------+
| hex('60737669922')     |
+------------------------+
| 3630373337363639393232 |
+------------------------+
1 row in set (0.00 sec)

mysql> select 0x3630373337363639393232;
+--------------------------+
| 0x3630373337363639393232 |
+--------------------------+
| 60737669922              |
+--------------------------+
1 row in set (0.00 sec)

這裡再解析執行計畫,發現找不到該記錄
mysql> explain select * from gangao.STK_DAILYQUOTEFA where id = 0x3630373337363639393232;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (3.10 sec)

實際上記錄是存在的
mysql> select * from gangao.STK_DAILYQUOTEFA where id = 60737669922;
+-------------+----------+---------------------+--------------+--------------------+----------------+----------------+---------------+----------------+---------------------+---------------------+---------------------+-------------+------------+----------+
| ID          | SECUCODE | TRADINGDAY          | TRADINGSTATE | PREVCLOSINGPRICEBA | OPENINGPRICEBA | HIGHESTPRICEBA | LOWESTPRICEBA | CLOSINGPRICEBA | ENTRYTIME           | UPDATETIME          | GROUNDTIME          | UPDATEID    | RESOURCEID | RECORDID |
+-------------+----------+---------------------+--------------+--------------------+----------------+----------------+---------------+----------------+---------------------+---------------------+---------------------+-------------+------------+----------+
| 60737669922 |    20041 | 2009-06-05 00:00:00 | 1            |            33.9315 |        33.8687 |        35.8897 |       33.7851 |        35.2277 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 60737669922 | Calc       | NULL     |
+-------------+----------+---------------------+--------------+--------------------+----------------+----------------+---------------+----------------+---------------------+---------------------+---------------------+-------------+------------+----------+
1 row in set (1.94 sec)
但是轉成這16進位表示的字元形式就出不來結果了

mysql> select * from gangao.STK_DAILYQUOTEFA where id = 0x3630373337363639393232;
Empty set (0.40 sec)


而這個數值真正的16進位數值應該是

mysql> select hex(60737669922);
+------------------+
| hex(60737669922) |
+------------------+
| E243F4B22        |
+------------------+
1 row in set (0.00 sec)

這裡需要加上0才行

mysql> select 0xE243F4B22+0;
+---------------+
| 0xE243F4B22+0 |
+---------------+
|   60737669922 |
+---------------+
1 row in set (0.00 sec)

而這時再解析

mysql> explain select * from gangao.STK_DAILYQUOTEFA where id = 0xE243F4B22+0;
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | STK_DAILYQUOTEFA | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (2.83 sec)

mysql> select * from gangao.STK_DAILYQUOTEFA where id = 0xE243F4B22+0;
+-------------+----------+---------------------+--------------+--------------------+----------------+----------------+---------------+----------------+---------------------+---------------------+---------------------+-------------+------------+----------+
| ID          | SECUCODE | TRADINGDAY          | TRADINGSTATE | PREVCLOSINGPRICEBA | OPENINGPRICEBA | HIGHESTPRICEBA | LOWESTPRICEBA | CLOSINGPRICEBA | ENTRYTIME           | UPDATETIME          | GROUNDTIME          | UPDATEID    | RESOURCEID | RECORDID |
+-------------+----------+---------------------+--------------+--------------------+----------------+----------------+---------------+----------------+---------------------+---------------------+---------------------+-------------+------------+----------+
| 60737669922 |    20041 | 2009-06-05 00:00:00 | 1            |            33.9315 |        33.8687 |        35.8897 |       33.7851 |        35.2277 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 2015-07-13 15:40:01 | 60737669922 | Calc       | NULL     |
+-------------+----------+---------------------+--------------+--------------------+----------------+----------------+---------------+----------------+---------------------+---------------------+---------------------+-------------+------------+----------+
1 row in set (4.25 sec)

所以問題就出在客戶將10進位整數123456轉成字串'123456'的16進位形式處理了,導致了索引根本沒用上,查詢緩慢,甚至一直在做無用功,根本不存在這些值。


案例3:主庫上只有write操作,主庫的io比較低,從庫io很高,主庫不存在峰值。

原因分析:後來對比主從的配置,發現主庫的innodb_flush_log_at_trx_commit設定為2,而從庫上innodb_flush_log_at_trx_commit是1,後來將從庫也調為2或者0就沒問題,這個參數具體的含義不多說,是沒調整前,主從的io對比




著作權聲明:本文為博主原創文章,未經博主允許不得轉載。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.