mysql資料庫複製延時分析（mysql 延時函數）

本文目錄一覽：

• 1、數據包延時問題？
• 2、MySQL資料庫伺服器逐漸變慢分析與解決方法分享
• 3、現在我在學習MySQL，問問怎麼複製粘貼資料庫
• 4、如何解決mysql主從複製帶來的數據延遲問題
• 5、怎樣解決MySQL資料庫主從複製延遲的問題

數據包延時問題？

談到MySQL資料庫主從同步延遲原理，得從mysql的資料庫主從複製原理說起，mysql的主從複製都是單線程的操作，主庫對所有DDL和 DML產生binlog，binlog是順序寫，所以效率很高，slave的Slave_IO_Running線程到主庫取日誌，效率很比較高，下一步， 問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順 序的，成本高很多，還可能可slave上的其他查詢產生lock爭用，由於Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要 執行10分鐘，那麼所有之後的DDL會等待這個DDL執行完才會繼續執行，這就導致了延時。有朋友會問：「主庫上那個相同的DDL也需要執行10分，為什 么slave會延時？」，答案是master可以並發，Slave_SQL_Running線程卻不可以。

2. MySQL資料庫主從同步延遲是怎麼產生的。

答：當主庫的TPS並發較高時，產生的DDL數量超過slave一個sql線程所能承受的範圍，那麼延時就產生了，當然還有就是可能與slave的大型query語句產生了鎖等待。

3. MySQL資料庫主從同步延遲解決方案

答：最簡單的減少slave同步延時的方案就是在架構上做優化，盡量讓主庫的DDL快速執行。還有就是主庫是寫，對數據安全性較高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設置，而slave則不需要這麼高的數據安全，完全可以講sync_binlog設置為0或者關閉binlog，innodb_flushlog也 可以設置為0來提高sql的執行效率。另外就是使用比主庫更好的硬體設備作為slave。

mysql-5.6.3已經支持了多線程的主從複製。原理和丁奇的類似，丁奇的是以表做多線程，Oracle使用的是以資料庫(schema)為單位做多線程，不同的庫可以使用不同的複製線程。

基於區域網的master/slave機制在通常情況下已經可以滿足’實時’備份的要求了。如果延遲比較大，就先確認以下幾個因素：

網路延遲

master負載

slave負載

一般的做法是，使用多台slave來分攤讀請求，再從這些slave中取一台專用的伺服器，只作為備份用，不進行其他任何操作，就能相對最大限度地達到’實時’的要求了

slave_net_timeout單位為秒 默認設置為 3600秒 參數含義：當slave從主資料庫讀取log數據失敗後，等待多久重新建立連接並獲取數據 master-connect-retry單位為秒 默認設置為 60秒 參數含義：當重新建立主從連接時，如果連接建立失敗，間隔多久後重試。

通常配置以上2個參數可以減少網路問題導致的主從數據同步延遲

MySQL資料庫伺服器逐漸變慢分析與解決方法分享

一、檢查系統的狀態

通過操作系統的一些工具檢查系統的狀態，比如CPU、內存、交換、磁碟的利用率，根據經驗或與系統正常時的狀態相比對，有時系統表面上看起來看空閑，這也可能不是一個正常的狀態，因為cpu可能正等待IO的完成。除此之外，還應觀注那些佔用系統資源(cpu、內存)的進程。

1.使用sar來檢查操作系統是否存在IO問題

#sar-u210—

即每隔2秒檢察一次，共執行20次。

結果示例：

註：在redhat下，%system就是所謂的%wio。

Linux2.4.21-20.ELsmp

(YY075)05/19/2005

10:36:07AMCPU%user%nice%system%idle

10:36:09AMall0.000.000.1399.87

10:36:11AMall0.000.000.00100.00

10:36:13AMall0.250.000.2599.49

10:36:15AMall0.130.000.1399.75

10:36:17AMall0.000.000.00100.00

其中：

%usr指的是用戶進程使用的cpu資源的百分比;

%sys指的是系統資源使用cpu資源的百分比;

%wio指的是等待io完成的百分比，這是值得觀注的一項;

%idle即空閑的百分比。

如果wio列的值很大，如在35%以上，說明系統的IO存在瓶頸，CPU花費了很大的時間去等待I/O的完成。Idle很小說明系統CPU很忙。像以上的示例，可以看到wio平均值為11，說明I/O沒什麼特別的問題，而idle值為零，說明cpu已經滿負荷運行了。

2.使用vmstat監控內存

cpu資源

[root@mysql1

~]#

vmstat

procs

———–memory———-—swap–

—–io—-–system–

—–cpu——

r

b

swpd

free

buff

cache

si

so

bi

bo

in

cs

us

sy

id

wa

st

72

25428

54712672264

14

43

53

59

1

198

vmstat

的輸出那些信息值得關注?

io

bo:

磁碟寫的數據量稍大，如果是大文件的寫，10M以內基本不用擔心，如果是小文件寫2M以內基本正常

①

CPU問題

下面幾列需要被察看，以確定cpu是否有問題

Processesinthe

run

queue

(procs

r)

Usertime

(cpu

us)

System

time

(cpu

sy)

Idle

time

(cpu

id)

問題情況:

如果processes

in

run

queue

(procs

r)的數量遠大於系統中cpu的數量，將會使系統便慢。

如果這個數量是cpu的4倍的話，說明系統正面臨cpu能力短缺,這將使系統運行速度大幅度降低

如果cpu的idle時間經常為0的話，或者系統佔用時間(cpu

sy)是用戶佔用時間(cpu

us)兩輩的話，系統面臨缺少cpu資源

解決方案

:

解決這些情況，涉及到調整應用程序，使其能更有效的使用cpu，同時增加cpu的能力或數量

②內存問題

主要查看頁導入的數值(swap中的si)，如果該值比較大就要考慮內存，大概方法如下：

最簡單的，加大RAM

減少RAM的需求

3.磁碟IO問題

處理方式：做raid10提高性能

4.網路問題

telnet一下MySQL對外開放的埠，如果不通的話，看看防火牆是否正確設置了。另外，看看MySQL是不是開啟了skip-networking的選項，如果開啟請關閉。

現在我在學習MySQL，問問怎麼複製粘貼資料庫

這得看你的數據表是什麼存儲引擎，

新建的數據表默認是InnoDB

數據表的存儲引擎是可以更改的

隨便進入一張表，選擇操作，裡面有存儲引擎可以修改，你想複製表就可以將存儲引擎修改成

myisam，

然後找到資料庫的data目錄複製好後，存儲引擎再改成你需要的類型

phpmyadmin新建表時存儲引擎

phpmyadmin修改存儲引擎

如何解決mysql主從複製帶來的數據延遲問題

從DB2轉到MySQL，做過線上環境的配置，不過是先配置好，然後再把數據導入，前期測試好就行了，具體主主還是主備，還是主主備要看你們的需求了，網上都有配置過程。

怎樣解決MySQL資料庫主從複製延遲的問題

在老版本的MySQL 3.22中，MySQL的單表限大小為4GB，當時的MySQL的存儲引擎還是ISAM存儲引擎。但是，當出現MyISAM存儲引擎之後，也就是從MySQL 3.23開始，MySQL單表最大限制就已經擴大到了64PB了（官方文檔顯示）。也就是說，從目前的技術環境來看，MySQL資料庫的MyISAM存儲 引擎單表大小限制已經不是有MySQL資料庫本身來決定，而是由所在主機的OS上面的文件系統來決定了。 而MySQL另外一個最流行的存儲引擎之一Innodb存儲數據的策略是分為兩種的，一種是共享表空間存儲方式，還有一種是獨享表空間存儲方式。 當使用共享表空間存

儲方式的時候，Innodb的所有數據保存在一個單獨的表空間裡面，而這個表空間可以由很多個文件組成，一個表可以跨多個文件存在，所

以其大小限制不再是文件大小的限制，而是其自身的限制。從Innodb的官方文檔中可以看到，其表空間的最大限制為64TB，也就是說，Innodb的單

表限制基本上也在64TB左右了，當然這個大小是包括這個表的所有索引等其他相關數據。

而當使用獨享表空間來存放Innodb的表的時候，每個表的數據以一個單獨的文件來存放，這個時候的單表限制，又變成文件系統的大小限制了。