乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

MYSQL教程逐步分析MySQL從庫com_insert無變化的原因

作者：VEPHP   時間 2017-09-24

《MYSQL教程逐步分析MySQL從庫com_insert無變化的原因》要點：
本文介紹了MYSQL教程逐步分析MySQL從庫com_insert無變化的原因，希望對您有用。如果有疑問，可以聯系我們。

大家都知道com_insert等com_xxx參數可以用來監控數據庫實例的訪問量,也就是我們常說的QPS.并且基于MySQL的復制原理,所有主庫執行的操作都會在從庫重放一遍保證數據一致,那么主庫的com_insert和從庫的com_insert理論上應該是相等的.
如下面顯示,第二列代表主庫,第三列代表從庫：MYSQL必讀

代碼如下:

com_select????????????? 22???????????????? 1138
com_update????????????? 36?????????????????? 37
com_insert???????????? 133????????????????? 135
com_delete?????????????? 0??????????????????? 0
qcache_hits????????????? 0??????????????????? 0
Com_replace????????????? 0??????????????????? 0
Connections???????????? 13?????????????????? 24

但是我們看另外一個業務：MYSQL必讀

代碼如下:

com_select?????????????? 0?????????????????? 95
com_update?????????????? 0??????????????????? 0
com_insert????????????? 92??????????????????? 0
com_delete????????????? 20??????????????????? 0
qcache_hits????????????? 0??????????????????? 6
Com_replace????????????? 0??????????????????? 0
Connections????????????? 0??????????????????? 6

我們可以很明顯的看出來,主庫有92個寫,但是從庫0個寫,這是為什么呢?MYSQL必讀

這2個業務唯一的區別就是binlog_format的設置不一樣.MYSQL必讀

代碼如下:

第一個業務
show global variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value???? |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

第二個業務
show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW?? |
+---------------+-------+
MYSQL必讀

我們來看下com_xxx的官方文檔定義：MYSQL必讀

The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and Com_update_multi are similar but apply to DELETE and UPDATE statements that use multiple-table syntax.

從上述文檔,我們只能看到com_xxx是如何運作的,但是并不能解釋為什么使用RBR之后com_insert就不變化了.MYSQL必讀

接下來我們結合下面這段文檔來一起看看.MYSQL必讀

You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.
However, you can see what data was changed using mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.

這2段話結合來看,原因應該是這樣的：MYSQL必讀

1、主庫上接收的是statement的語句,所以com_insert符合觸發條件,會隨著業務增加.MYSQL必讀

2、而從庫是拿到主庫的binlog后重放更新數據,但是主庫的日志格式是row format,這就導致了binlog中記錄的不是statement語句,而是data的變化記錄.MYSQL必讀

3、這樣從庫雖然依然能進行更新記錄,但是無法解析出來這些data變化是一條statement語句導致的還是多條statment語句導致,所以就不在更新com_insert這個statment counter了.MYSQL必讀

基本上推論符合現實情況,但是沒有code證明,比較遺憾.MYSQL必讀

另外,如果我們無法通過com_insert來監控從庫的寫入情況,那么我們應該監控那個status呢?MYSQL必讀

個人建議對于row格式的實例,通過監控innodb_rows_inserted來監控寫入情況.MYSQL必讀

代碼如下:

show global status like 'innodb_rows_inserted';
+----------------------+------------+
| Variable_name??????? | Value????? |
+----------------------+------------+
| Innodb_rows_inserted | 2666049650 |
+----------------------+------------+

附：(兩個文檔的官方文檔鏈接)MYSQL必讀

http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Com_xxxMYSQL必讀

http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.htmlMYSQL必讀

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/3665.html


    標簽：