【Mysql】mysql主鍵的缺少導致備庫hang
- (1).現象slave:
- mysql> show slave status\G;
-
*************************** 1. row ***************************
-
Slave_IO_State: Waiting for master to send event
-
Master_Host: xxx.xx.xx.xx
-
Master_User: replicator
-
Master_Port: 3006
-
Connect_Retry: 60
-
Master_Log_File: mysql-bin.000006
-
Read_Master_Log_Pos: 47465657
-
Relay_Log_File: slave-relay.100383
-
Relay_Log_Pos: 251
-
Relay_Master_Log_File: mysql-bin.000006
-
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: 18057461
-
Relay_Log_Space: 29409335
-
Until_Condition: None
-
Until_Log_File:
-
Until_Log_Pos: 0
-
Master_SSL_Allowed: No
-
Master_SSL_CA_File:
-
Master_SSL_CA_Path:
-
Master_SSL_Cert:
-
Master_SSL_Cipher:
-
Master_SSL_Key:
-
Seconds_Behind_Master: 1339
-
Master_SSL_Verify_Server_Cert: No
-
Last_IO_Errno: 0
-
Last_IO_Error:
-
Last_SQL_Errno: 0
-
Last_SQL_Error:
- slave的Seconds_Behind_Master一直在增加,slave出現hang住。
(2).解析當前slave執行到的位置的binlog:
-
mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000006 –start-position=18057461 >/tmp/2.log
-
### UPDATE qianyi.dmpush_message_temp
-
### WHERE
-
### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
-
### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
-
### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
- ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
(3)分析:
-
模擬場景:
1.表中無主鍵,全表進行更新:
master:
表結構:
CREATE TABLE `dmpush_message_temp` (
`clientid` varchar(36) DEFAULT NULL,
`infoid` bigint(10) DEFAULT NULL,
`endtime` varchar(14) DEFAULT NULL,
`stat` varchar(8) DEFAULT NULL
) ENGINE=innodb DEFAULT CHARSET=utf8;mysql> update dmpush_message_temp set stat=1 ;
Query OK, 226651 rows affected (1.69 sec)
Rows matched: 226651 Changed: 226651 Warnings: 0
mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000007 >/tmp/test.log- binlog中第一個出現的update事務日誌:
-
2281772 ### UPDATE qianyi.dmpush_message_temp
2281773 ### WHERE
2281774 ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
2281775 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
2281776 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
2281777 ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
2281778 ### SET
2281779 ### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
2281780 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
2281781 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
2281782 ### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */ -
-
binlog中最後出現的update事務日誌:
5313201 ### UPDATE qianyi.dmpush_message_temp
5313202 ### WHERE
5313203 ### @1=’fffff4fc-0b72-4ba2-9749-0189658af6d5′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
5313204 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
5313205 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
5313206 ### @4=’0′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
5313207 ### SET
5313208 ### @1=’fffff4fc-0b72-4ba2-9749-0189658af6d5′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
5313209 ### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
5313210 ### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
5313211 ### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
注意這裡由於表中沒有主鍵,所以導致了每一個事務條目的更新都是全表掃描,如果表中很很多的資料,則備庫執行該更新的事務條目的時候,就會出現很多的全表掃描更新;
root@xxxxxxxxx # cat /tmp/test.log|grep ‘UPDATE qianyi.dmpush_message_temp’ -A 10 |wc -l
2521492
mysql> select 2521492/11;—-11為一個update事務條目佔用的行數
+————-+
| 2521492/11 |
+————-+
| 229226.5455 |
+————-+mysql> use qianyi
Database changed
mysql> select count(*) from dmpush_message_temp;
+———-+
| count(*) |
+———-+
| 226651 |
+———-+
可以看到,binlog的條目數和該表的資料量查不多是一致,也就是在全表更新的時候(在row模式下)更新多少行,就有多少事務的事務條目;
4.最佳化
-
為dmpush_message_temp表新增主鍵:
mysql> alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key(id);
Query OK, 226651 rows affected (1.09 sec)
Records: 226651 Duplicates: 0 Warnings: 0mysql> update dmpush_message_temp set stat=0 ;
Query OK, 226651 rows affected (1.69 sec)
Rows matched: 226651 Changed: 226651 Warnings: 0解析binlog中的事務條目:
root@xxxxxxxxx # mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000008 >/tmp/test1.log### UPDATE qianyi.dmpush_message_temp
### WHERE
### @1=’fb5c72c9-0ac2-4800-93b2-b94dc9e1dd54′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
### @5=1 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE qianyi.dmpush_message_temp
### WHERE
### @1=’fb5bdc4f-da8a-4f03-aa5e-27677d7c8ac3′ /* VARSTRING(108) meta=108 nullable=1 is_null=0 */
### @2=133 /* LONGINT meta=0 nullable=1 is_null=0 */
### @3=’20121012220000′ /* VARSTRING(42) meta=42 nullable=1 is_null=0 */
### @4=’1′ /* VARSTRING(24) meta=24 nullable=1 is_null=0 */
### @5=2 /* INT meta=0 nullable=0 is_null=0 */
可以看到這裡的事務條目中由於已經有了主鍵,也就是@5(第一個事務條目更新和第二個事務條目更新的@5是遞增的,即主鍵),這樣事務日誌就會根據主鍵來更新,備庫執行則不會卡住;
解決:
-
問題的原因已經找到了,由於表中沒有主鍵,ROW模式下,每刪一條資料都會做全表掃,也就是說一條delete,如果刪了10條,會做10次全表掃。。。。所以slave一直卡住;
-
1.slave:停止slave;
-
mysql> stop slave;
-
Ctrl-C — sending “KILL QUERY 59028” to server …
-
Ctrl-C — query aborted.
-
Ctrl-C — sending “KILL 59028” to server …
-
Ctrl-C — query aborted.
-
Ctrl-C —
- Aborted
-
2.這個時候,發現slave已經卡住,無法進行任何操作,這個時候只有強行kill掉mysql程式
root@xxxxxxxx.com # ps -ef|grep 3006
root 4456 1 0 Oct11 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe –defaults-file=/etc/my3006.cnf
mysql 6828 4456 0 Oct11 ? 00:39:27 /usr/sbin/mysqld –defaults-file=/etc/my3006.cnf –basedir=/ –datadir=/home/mysql/data3006/dbs3006 –user=mysql –log-error=/home/mysql/data3006/mysql/master-error.log –open-files-limit=8192 –pid-file=/home/mysql/data3006/dbs3006/xxxxxxxx.com.pid –socket=/home/mysql/data3006/tmp/mysql.sock –port=3006kill -9 4456 6828
由於我們的slave複製是在mysqld啟動的時候自動啟動,所以這裡我們需要將其關閉:
vi /etc/my3006.cnf中加入:skip-slave-start,在用mysqld_safe啟動; -
3.由於主庫的binlog已經傳入備庫,這個時候,slave執行沒有主鍵更新的事務日誌就會hang住,這個時候可以採取一種巧妙的方法來避過,就是將備庫中的這張表進行資料清空,slave在執行realy log的時候,就會報1032錯誤,我們寫一個指令碼進行掉這些錯誤,當備庫追趕上主庫後,我們在把主庫的表透過mysqldump,或者insert select 還原到備庫,這樣就可以讓slave正常的執行起來,然後在通知客戶進行主鍵的改造;
a。slave上執行以下命令:
slave:清空備庫上有問題的表
set sql_log_bin=off;
truncate table qianyi.dmpush_message_temp;
start slave;
跳過該表上的錯誤:
sh /tmp/skip.sh 3006 dmpush_message_temp
b.等備庫追上主庫後,執行以下命令:
master:
lock tables qianyi.dmpush_message_temp read;
create table a2 like qianyi.dmpush_message_temp ;
lock tables a2 write, qianyi.dmpush_message_temp read;
insert into a2 select * from qianyi.dmpush_message_temp ;
slave:
set sql_log_bin=off;
drop table qianyi.dmpush_message_temp;
create table qianyi.dmpush_message_temp like a2;
insert into qianyi.dmpush_message_temp select * from a2;
c.最後讓應用改造,新增上主鍵:
mysql> alter table dmpush_message_temp add column id int not null auto_increment,add PRIMARY Key(id);
3.當slave卡住的時候,可以透過解析binlog來看看,slave到底卡住在那裡,是那個事務,下面是一個簡單的方法,來看當前salve開啟的表:
mysql> show open tables;
+———-+———————+——–+————-+
| Database | Table | In_use | Name_locked |
+———-+———————+——–+————-+
| qianyi | dmpush_message_temp | 1 | 0 |
| qianyi | test | 0 | 0 |
| qianyi | anson | 0 | 0 |
| mysql | db | 0 | 0 |
| mysql | slow_log | 0 | 0 |
| mysql | event | 0 | 0 |
+———-+———————+——–+————-+
可以看到這裡dmpush_message_temp一直處於開啟狀態,這裡就可以直接定位問題的根源了;
總結:主鍵對於innodb來說,是非常重要的,每張表的設計的時候,都應該把主鍵預設的加上,不管你需不需要他,而且主鍵的設計最好選擇自增型的主鍵,這裡也可以略提一下自增主鍵的好處:
a.自增型主鍵以利於插入效能的提高;
b.自增型主鍵設計(int,bigint)可以降低二級索引的空間,提升二級索引的記憶體命中率;
c.自增型的主鍵可以減小page的碎片,提升空間和記憶體的使用。
與這篇文章很像
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29096438/viewspace-2057127/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql主鍵的缺少導致備庫hangMySql
- MySQL 缺少主鍵的表的效能下降的原因MySql
- Mysql 從庫如果有未提交的事務主庫ddl操作導致主從延遲MySql
- mysql主備庫資料不一致的原因和解決方案MySql
- MySQL 主備MySql
- MySQL 5.7 主庫崩潰切備庫MySql
- MySQL主鍵的理解MySql
- MySQL 主備庫切換記錄MySql
- Latch導致MySQL CrashMySql
- Mysql 資料庫主庫,備庫實時同步配置MySql資料庫
- Mysql主從熱備MySql
- MySQL主從備份MySql
- MySQL 中的自增主鍵MySql
- mysql主從和主備的區別MySql
- Mysql跨庫主從熱備失效問題MySql
- Mysql分庫分表的主鍵生成演算法MySql演算法
- MySQL備份與主備配置MySql
- MySQL Flush導致的等待問題MySql
- 【mysql】mysql的資料庫主從(一主一從)MySql資料庫
- 解讀MySQL雙主複製的主備資料一致性GPMySql
- 【Mysql】是什麼導致MySQL資料庫伺服器磁碟IO高?MySql資料庫伺服器
- 故障分析 | replace into 導致主備不一致
- Linux主機名修改後導致mysql重啟失敗LinuxMySql
- MySQL 資料庫自增主鍵生成的優缺點MySql資料庫
- MySQL 5.6因為OOM導致資料庫重啟MySqlOOM資料庫
- MySQL新增自增主鍵的坑MySql
- 中止程式導致系統HANG住
- Centos Mysql 主從備份CentOSMySql
- Mysql 會導致索引失效的情況MySql索引
- 【Mysql】JDB2導致磁碟io使用率高 導致mysql延遲過高MySqlDB2
- mysql的分庫備份MySql
- 一條主鍵索引SQL導致的CPU被打滿索引SQL
- oracle 序列值導致的主鍵衝突問題Oracle
- MySQL:一個奇怪的hang案例MySql
- mysql主從複製+主備切換MySql
- MySQL 主鍵自增也有坑?MySql
- oracle僵死會話鎖住buffer,導致資料庫hang住Oracle會話資料庫
- MySQL:MySQL客戶端快取結果導致OOMMySql客戶端快取OOM