MySQL 複製性能
我在兩台機器之間的 MySQL 5.5 複製性能存在嚴重問題，主要是具有基於語句複製的 myISAM 表。二進制日誌和 mysql 數據目錄都位於同一個 Fusion ioDrive 上。
最近，當我們需要暫停複製大約 3小時。在沒有其他負載的情況下再次趕上大約需要 10 個小時。
如何提高複制的性能？機器 B 基本上是空閒的（很少，IO，16 個核心中的 2 個已用完，大量可用 RAM），因為只有 1 個 mySQL 執行緒正在寫入數據。以下是我的一些想法：
- 切換到基於行的複制。在測試中，這只產生了 10-20% 的性能提升
- 使用多執行緒複製升級到 mySQL 5.6。我們可以輕鬆地將數據拆分到單獨的數據庫中，基準測試似乎表明這會有所幫助，但程式碼似乎還沒有準備好生產。
- 一些有助於加快複製的配置變數
主要問題是，如果在暫停 3 小時後需要 10 小時才能趕上，這意味著複製在 10 小時內寫入 13 小時的數據，或者能夠以 130% 的數據進入速度寫入。我正在尋找在不久的將來在 Master 機器上至少雙寫，所以迫切需要一種方法來提高複制性能。
機器A：
- 掌握
- 24GB 記憶體
- 1.2TB Fusion ioDrive2
- 2x E5620
- 千兆互連
my.cnf:[mysqld] server-id=71 datadir=/data_fio/mysqldata socket=/var/lib/mysql/mysql.sock tmpdir=/data_fio/mysqltmp log-error = /data/logs/mysql/error.log log-slow-queries = /data/logs/mysql/stats03-slowquery.log long_query_time = 2 port=3306 log-bin=/data_fio/mysqlbinlog/mysql-bin.log binlog-format=STATEMENT replicate-ignore-db=mysql log-slave-updates = true # Performance Tuning max_allowed_packet=16M max_connections=500 table_open_cache = 2048 max_connect_errors=1000 open-files-limit=5000 # mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections key_buffer=4G max_heap_table_size = 1G tmp_table_size = 4G myisam_sort_buffer_size = 256M sort_buffer_size=4M read_buffer_size=2M query_cache_size=16M query_cache_type=2 thread_concurrency=32 user=mysql symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock機器 B：
- 奴隸
- 36GB 記憶體
- 1.2TB Fusion ioDrive2
- 2x E5620
- 千兆互連
my.cnf:[mysqld] server-id=72 datadir=/data_fio/mysqldata socket=/var/lib/mysql/mysql.sock tmpdir=/data_fio/mysqltmp log-error = /data/logs/mysql/error.log log-slow-queries = /data/logs/mysql/stats03-slowquery.log long_query_time = 2 port=3306 # Performance Tuning max_allowed_packet=16M max_connections=500 table_open_cache = 2048 max_connect_errors=1000 open-files-limit=5000 # mem = key_buffer + ( sort_buffer_size + read_buffer_size ) * max_connections key_buffer=4G max_heap_table_size = 1G tmp_table_size = 4G myisam_sort_buffer_size = 256M sort_buffer_size=4M read_buffer_size=2M query_cache_size=16M query_cache_type=2 thread_concurrency=32 user=mysql symbolic-links=0 plugin-load=archive=ha_archive.so;blackhole=ha_blackhole.so [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock
哇，你有一些非常強大的硬體來解決這個問題。除了升級到 Sandy/Ivy Bridge CPU 以獲得 Btree 搜尋的 20-50% 更好的性能等之外，您在硬體方面沒有更多可以投入的東西。
請注意，我的強項是 Innodb，所以我要
- 忽略你是 myisam 並表現得好像它不會有所作為。
- 假設這個問題足以推動你升級。是的，這是升級。
Innodb 可以通過將這些頻繁訪問的行儲存在其緩衝池中來幫助充分利用所有記憶體。您可以將其調整為您想要的任意大小（例如 80% 的記憶體），並且新的讀取/寫入會保留在記憶體中，直到它需要將它們推送到磁碟以為最新訪問的數據騰出更多空間。在記憶體中比您的 FusionIO 快一個數量級。
還有更多 Innodb 功能，例如自適應雜湊、自動鎖定機制等，可以為您的環境帶來好處。但是，您比我更了解您的數據。
在 innodb 的世界裡，一個好的短期解決方案是優化你的從站——你真的需要你的從站上的每個索引嗎？索引是插入/更新/刪除的球和鏈，即使使用 Fusion IO 卡也是如此。IOPS 並不是這裡的全部。Sandy/Ivy bridge procs 具有更好的記憶體吞吐量和計算性能——它們可以對你現在擁有的 Westmeres 產生巨大的影響。（總體圖 20-50%）。刪除從站上不需要的所有索引！
其次，幾乎可以肯定只適用於 innodb，mk-prefetch 可以知道哪些更新以及從屬設備寫入它們之前。這允許 mk-prefetch 首先執行讀取查詢，從而在單個 repl 執行寫入查詢時強制數據在記憶體中。這意味著數據在記憶體中，而不是在 fusionIO 中，這是一個快速數量級的性能提升。這產生了巨大的差異，超出了人們的預期。許多公司將此作為永久解決方案。通過查看Percona Toolkit了解更多資訊。
第三，也是最重要的，一旦你升級到 Innodb，一定要檢查 Tokutek。這些傢伙有一些非常棒的東西，遠遠超過了 Innodb 的寫入/更新/刪除性能。他們將提高複制速度吹捧為主要優勢之一，您可以從他們的基準測試中看到為什麼 Fusions 瘋狂的 IOPS在 Btrees 的情況下仍然無法幫助您。（注意：未經我獨立驗證。）他們使用直接替換 btree 索引，雖然複雜得多，但改善了 btree 索引的許多算法速度限制。
我正在考慮採用 Tokutek。如果他們釋放瞭如此多的寫入速度，那我就可以添加更多索引。由於他們以如此出色的比率（他們引用的 25 倍）壓縮數據和索引，因此您甚至無需為增加的數據支付（性能、維護）價格。你付錢（ $ ) for their engine though, $ 每個預壓縮 GB、IIRC 2500/年。如果您複製了數據，他們有折扣，但您甚至可以在您的奴隸上安裝 Tokutek 並保持您的主人原樣。查看MIT 算法開放課件講座中的技術細節。或者，他們的部落格上有大量技術資料，並為那些沒有 1:20 觀看影片的人提供正常白皮書。我相信這個影片也給出了讀取速度的 Big-O 公式。我不得不假設讀取速度較慢（總是需要權衡！），但公式太複雜，我無法衡量多少。他們聲稱它大致相同，但我寧願理解數學（不太可能！）。你可能比我更容易發現這一點。
Ps 我不隸屬於 Tokutek，我從未執行過他們的產品，他們甚至不知道我在看他們。
更新：
我看到您在此頁面上還有其他問題，並認為我會加入：
首先，slave pre-fetch 幾乎肯定不會對 myisam 起作用，除非你有一個特殊的環境。這主要是因為預取將鎖定您打算寫入的表，或者從執行緒鎖定了預取守護程序所需的表。如果您的表非常適合複製並且正在以循環方式寫入不同的表，那麼這可能會起作用 - 但請記住，這是非常理論上的。《高性能Mysql》這本書在“複製問題”一節中有更多的資訊。
其次，假設您的從站負載為 1.0-1.5，如果您執行其他 proc 或查詢但基線為 1.0，則可能會更高。這意味著您可能會受到 CPU 限制，這可能與您的 FusionIO 板載有關。正如我之前提到的，桑迪/常春藤橋會給你更多的魅力，但可能不足以讓你以最小的延遲度過艱難的時期。如果這個slave上的負載主要是只寫的（即沒有很多讀取），你的CPU幾乎肯定會花時間計算btree插入/刪除的位置。這應該加強我上面關於刪除非關鍵索引的觀點——你以後總是可以重新添加它們。禁用超執行緒將不起作用，更多的 CPU 不是你的敵人。一旦你的記憶體超過 32GB，比如 64GB，你需要擔心記憶體分配，但即便如此，症狀也是不同的。
最後，也是最重要的（不要跳過這部分；）），我假設您現在正在執行 RBR（基於行的複制），因為您在切換它時提到了非平凡的性能提升。但是 - 可能有一種方法可以在這裡獲得更多性能。如果您要複製沒有主鍵的表，則Mysql 錯誤 53375可能會出現。從機基本上不夠聰明，只能使用主鍵，因此沒有主鍵會迫使複製執行緒對每次更新進行全表掃描. 修復只是添加一個良性的代理自動遞增主鍵。如果表很大（比如幾千行或更大），我只會這樣做。當然，這是以在表上擁有另一個索引為代價的，這會增加您在 CPU 中支付的價格。請注意，反對這一點的理論論據很少，因為如果您不這樣做，InnoDB 會在幕後添加一個。然而，幻象並不能有效防禦 53375。Tungsten 也可以克服這個問題，但是在使用 Tungsten 時，您需要確保您的編碼是正確的。上次我玩它時，當任何非 UTF8 字元串需要複製時，它會可怕地死掉。那是我放棄它的時間。