mySQL v5.6 複製的事務速度基準 - 似乎很慢

November 15, 2014

我正在嘗試為我們的新基礎架構選擇最佳配置，但對結果有點困惑。
我使用 sysbench v0.5 進行測試：
準備數據
sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
--oltp-test-mode=complex --oltp-table-size=1000000 \
--mysql-db=mydb --mysql-user=root --mysql-password=mypassword prepare
做測試
sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
--oltp-test-mode=complex --oltp-table-size=1000000 --oltp-read-only=off \
--num-threads=6 --max-time=60 --max-requests=0 \
--mysql-db=mydb --mysql-user=root --mysql-password=mypassword run
從下面的結果可以看出，主-主複製（percona 有 3 台機器）的性能最差，其次是 mySQL 主從（2 台機器）配置，最快的是 mySQL 作為單個獨立伺服器。
這是複制解決方案的正常情況嗎？它看起來太慢了，配置之間的 10 倍差異對我來說看起來很不正常。也許我遺漏了一些東西……我對 Percona Galera Cluster 完全失望，它以對 innodb 速度快而聞名。呸 :)
請檢查以下提供的資訊並提出建議，謝謝。
關於伺服器
硬體
英特爾® 至強® E5-1650 v2 六核
64 GB ECC 記憶體
2 x 240 GB 6 Gb/s SSD 數據中心版（軟體 RAID 1）
1 Gbit/s 頻寬
你
Debian Wheezy
所有軟體包更新/升級。
連接等
伺服器位於同一個數據中心，均與 Gbit 交換機連接，並具有第二個乙太網卡，均配置為它們之間的專用網路。
目前，伺服器上沒有負載。
磁碟性能
第一次測試
# hdparm -Tt /dev/sda

/dev/sda:
Timing cached reads:   27166 MB in  2.00 seconds = 13599.63 MB/sec
Timing buffered disk reads: 1488 MB in  3.00 seconds = 495.64 MB/sec
第二次測試
# dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/output
10240+0 records in
10240+0 records out
83886080 bytes (84 MB) copied, 0.0517404 s, 1.6 GB/s
/etc/my.cnf 配置
Percona 的嚮導用於初始配置。
我使用官方手冊/網站和其他可靠來源來學習和配置複製。
預設儲存引擎使用InnoDB（sysbench創建的測試表也是InnoDB）
Percona XtraDB v5.6 Galera Cluster：第一個節點的 my.cnf
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[sst]
streamfmt=xbstream

[xtrabackup]
# compress
# compact
parallel=8
compress-threads=8
rebuild-threads=8

[mysqld]
wsrep_node_name=db1
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL
wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6
# This changes how |InnoDB| autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.1.4
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_cluster
# Authentication for SST method
wsrep_sst_auth="replicateuser:replicateuserpassword"

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid

# Rolandomysqldba's recommendation
innodb_thread_concurrency       = 0
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64

# MyISAM #
key_buffer_size                = 32M
myisam_recover_options         = FORCE,BACKUP

# SAFETY #
max_allowed_packet              = 16M
max_connect_errors              = 1000000
skip_name_resolve
sysdate_is_now                  = 1
innodb                          = FORCE

# DATA STORAGE #
datadir                         = /var/lib/mysql/

# BINARY LOGGING #
log_bin                         = /var/lib/mysql/mysql-bin
expire_logs_days                = 14
sync_binlog                     = 1

# CACHES AND LIMITS #
tmp_table_size                  = 32M
max_heap_table_size             = 32M
query_cache_type                = 0
query_cache_size                = 0
max_connections                 = 500
thread_cache_size               = 50
open_files_limit                = 65535
table_definition_cache          = 4096
table_open_cache                = 8K

# INNODB #
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_flush_log_at_trx_commit  = 1
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 42G

# LOGGING #
long_query_time                 = 5
log_error                       = /var/log/mysql/error.log
log_queries_not_using_indexes   = 1
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M
Sysbench 測試結果
mySQL v5.6.21 (單)
transactions: 101001 (1683.29 per sec.)
mySQL v5.6.21 Replicated (Master + 1 Slave)
主從線上
transactions: 10501  (174.95 per sec.)
Slave 離線，binlog 同步關閉
transactions: 11280  (187.86 per sec.)
從站離線
transactions: 10779  (179.55 per sec.)
具有主從延遲（1 小時）
transactions: 10595  (176.48 per sec.)
MariaDB v5.5（單）
transactions: 73683  (1228.00 per sec.)
Percona XtraDB v5.6 Galera Cluster (3 Master-Master, xtrabackup-v2)
主節點（初始節點）
transactions: 703    (11.65 per sec.)
在另一個節點上測試
transactions: 643    (10.67 per sec.)
將複製方法更改為 rsync
transactions: 652    (10.80 per sec.)

你應該把一切都放在一個公平的競爭環境中。如何？
如果沒有適當的調整，舊版本的 MySQL 可能會跑贏新版本。
Sep 25, 2013:為什麼要使用 InnoDB 和 MySql 而不是 XtraDB 和 MariaDB？
Mar 26, 2012: Percona vs MySQL
Nov 24, 2011:為什麼 mysql 5.5 比 5.1 慢 (linux,using mysqlslap)
Oct 05, 2011:查詢在一些較新的 MySQL 版本中執行時間較長
Jun 19, 2011:如何正確執行 MySQL 烘烤？
在三種環境上執行 SysBench 之前
確保所有數據庫伺服器的所有 InnoDB 設置都相同
對於 Master/Slave，STOP SLAVE;在 Slave 上執行
對於 PXC（Percona XtraDB Cluster），關閉兩個 Master
比較單獨的 MySQL、Percona 和 MariaDB 的速度。
分析
如果 MySQL 是最好的（Percona 人，請不要向我扔爛蔬菜。這只是猜想），執行START SLAVE;. 在主/從上執行 SysBench。如果性能明顯變慢，您可能必須實現半同步複製。
如果 PXC 最好，您可能需要調整 wsrep 設置或網路本身。
如果 MariaDB 最好，您可以切換到MariaDB 集群（如果您有錢）或使用 MariaDB 設置主/從。執行 Sysbench。如果性能明顯變慢，您可能需要調整 wsrep 設置或網路本身。
為什麼要調整 wsrep 設置？請記住，Galera wsrep（WriteSet 複製）使用幾乎同步的送出和回滾。換句話說，要麼所有節點送出，要麼所有節點回滾。在這種情況下，最薄弱的環節必須是
節點之間的通信速度有多快（如果節點位於不同的數據中心，尤其如此）
如果任何一個節點的硬體設置配置不足
如果任何一個節點的通信速度比其他節點慢
旁注：您還應該確保為多個 CPU 調整 MySQL
Jun 01, 2012:我有 16GB 的記憶體，我應該如何配置 MySQL 伺服器？
May 07, 2012: MySQL 伺服器性能
Apr 26, 2012: CPU 性能與數據庫伺服器有關嗎？
Mar 16, 2012:在 Debian 上使用多個核心進行單個 MySQL 查詢
Oct 07, 2011:我應該使用 MyISAM 以外的儲存引擎來優化這些表還是應該獲得更好的磁碟？
Sep 20, 2011:多核和 MySQL 性能
Sep 12, 2011:可以讓 MySQL 使用多個核心嗎？
May 26, 2011:關於單執行緒與多執行緒數據庫的性能
更新 2014-11-04 21:06 EST
請記住，Percona XtraDB Cluster 一開始並沒有很好地編寫規模。請注意文件在其缺點（第二個缺點）下所說的內容：
這不能用作有效的寫入縮放解決方案。當您將寫入流量執行到 2 個節點而不是所有流量執行到 1 個節點時，寫入吞吐量可能會有所提高，但您不能期望太多。所有寫入仍然必須在所有節點上進行。
建議 #1
對於 PXC，關閉一個節點。針對兩個節點集群執行 SysBench。如果寫入性能優於三節點集群，那麼節點之間的通信顯然是瓶頸。
建議 #2
我注意到你有一個 42GB 的緩衝池，它是伺服器 RAM 的一半以上。您需要通過將 innodb_buffer_pool_instances設置為 2 或更多來對緩衝池進行分區。否則，您可以期待一些交換。
建議#3
您的innodb_log_buffer_size預設為 8M。嘗試將其設置為 256M 以提高日誌寫入性能。
建議 #4
你的innodb_log_file_size是 512M。嘗試將其設置為 2G 以提高日誌寫入性能。如果應用此設置，則將innodb_log_buffer_size設置為 512M。

我將更新我的進度和我在下面發現的其他內容。

檢查 /etc/init.d/mysql

mySQL 以此開頭

su - mysql -s /bin/sh -c "/usr/bin/mysqld_safe &gt; /dev/null 2&gt;&1 &"

錯誤日誌顯示 65535 open_files_limit 指令不成功。簡單地說，mysql 使用者無法設置自己的限制。

為所有使用者提高 ulimit nofiles（啟用 open_files_limit）

檢查 Debian 上的目前津貼

su mysql -s /bin/sh -c "ulimit -a"

/etc/security/limits.conf

# Added these
* soft nofile 65535
* hard nofile 65535
* soft nproc 10240
* hard nproc 10240

/etc/pam.d/su

# Find and uncomment the following:
session    required   pam_limits.so

所有伺服器上的 Sysctl 調整

添加到 /etc/sysctl.conf 並做了 sysctl -p

#mo swap
vm.swappiness = 0
kernel.sysrq = 0

net.core.somaxconn = 65535
net.ipv4.conf.default.rp_filter=1

net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_congestion_control = cubic
net.ipv4.tcp_keepalive_time = 900
net.ipv4.tcp_keepalive_intvl = 60
net.ipv4.tcp_keepalive_probes = 15

# Increase system file descriptor limit
fs.file-max = 100000

# Discourage swap
vm.swappiness = 0

# Increase ephermeral IP ports
net.ipv4.ip_local_port_range = 1024 65535

# Increase Linux autotuning TCP buffer limits
# Set max to 16MB for 1GE and 32M (33554432) or 54M (56623104) for 10GE
# Don't set tcp_mem itself! Let the kernel scale it based on RAM.
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.rmem_default = 16777216
net.core.wmem_default = 16777216
net.core.optmem_max = 40960
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# Make room for more TIME_WAIT sockets due to more clients,
# and allow them to be reused if we run out of sockets
# Also increase the max packet backlog
net.core.netdev_max_backlog = 65535
net.ipv4.tcp_max_syn_backlog = 30000
net.ipv4.tcp_max_tw_buckets = 2000000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 10

# Disable TCP slow start on idle connections
net.ipv4.tcp_slow_start_after_idle = 0

# If your servers talk UDP, also up these limits
net.ipv4.udp_rmem_min = 8192
net.ipv4.udp_wmem_min = 8192

# Disable source routing and redirects
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.all.accept_source_route = 0

# Log packets with impossible addresses for security
net.ipv4.conf.all.log_martians = 1

目前狀態

transactions: 113132 (1885.45 per sec.)

引用自：https://dba.stackexchange.com/questions/81619

mySQL v5.6 複製的事務速度基準 - 似乎很慢

關於伺服器

硬體

你

連接等

磁碟性能

/etc/my.cnf 配置

Percona XtraDB v5.6 Galera Cluster：第一個節點的 my.cnf

Sysbench 測試結果

mySQL v5.6.21 (單)

mySQL v5.6.21 Replicated (Master + 1 Slave)

MariaDB v5.5（單）

Percona XtraDB v5.6 Galera Cluster (3 Master-Master, xtrabackup-v2)

分析

更新 2014-11-04 21:06 EST

建議 #1

建議 #2

建議#3

建議 #4

檢查 /etc/init.d/mysql

為所有使用者提高 ulimit nofiles（啟用 open_files_limit）

所有伺服器上的 Sysctl 調整

目前狀態

相關問答

為 32GB 混合 nginx/php/mysql 伺服器優化 Percona 5.7 my.cnf

MySQL 複製：從伺服器性能最重要的配置參數？

列不能從“int”類型轉換為“bigint(20) unsigned”類型？

如何避免複製滯後，以防所有在 master 上的寫入和在副本上的讀取？

MySQL V8 複製滯後等待處理程序送出

MySQL 複製從屬是否應該設置為只讀？