Galera篇-与单点性能对比测试及配置优化

压测命令：

#准备数据结构
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-user=root --mysql-password=Password --mysql-db=testdb --mysql-host=127.0.0.1 --mysql-port=3307 --rand-type=uniform --oltp-tables-count=10 --oltp-table-size=1000000 prepare
sysbench --test=/usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=Password --mysql-db=testdb --mysql-host=127.0.0.1 --mysql-port=3307 --table-size=1000000 --tables=10 --threads=8 prepare
sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-user=root --mysql-password=Password --mysql-db=testdb --mysql-host=127.0.0.1 --mysql-port=3307 --table-size=1000000 --tables=10 --threads=8 prepare
 
#压测
# 1. oltp.lua
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-user=root --mysql-password=Password --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-db=testdb --oltp-read-only=off --rand-type=uniform --num-threads=8 --oltp-tables-count=10 --oltp-table-size=1000000 --report-interval=30 --max-time=1800 run
# 2. oltp_read_write.lua
sysbench --test=/usr/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=Password --mysql-db=testdb --mysql-host=127.0.0.1 --mysql-port=3307 --rand-type=uniform --threads=8 --tables=10 --table-size=1000000 --report-interval=30 --max-time=1800 run
# 3. oltp_write_only.lua
sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-user=root --mysql-password=Password --mysql-db=testdb --mysql-host=127.0.0.1 --mysql-port=3307 --oltp-read-only=off --rand-type=uniform --threads=8 --tables=10 --table-size=1000000 --report-interval=30 --max-time=1800 run
 
#清理
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-user=root --mysql-password=Password --mysql-db=testdb --mysql-host=127.0.0.1 --mysql-port=3307 --num-threads=8 --oltp-tables-count=10 --oltp-table-size=1000000 cleanup
 
#参数说明
--mysql-db=testdb               指定使用的数据库
--oltp-read-only=off            是否仅执行select测试，默认关闭
--rand-type=uniform             表示随机类型为固定模式，其他几个可选随机模式：uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
--num-threads=8                 表示发起 8 个并发连接
--oltp-tables-count=10          表示会生成 10 个测试表
--oltp-table-size=1000000       表示每个测试表填充数据量为 1000000
--report-interval=30            表示每10秒输出一次测试进度报告
--max-time=1800                 表示最大执行时长为1800秒
--oltp-test-mode=STRING         测试类型：simple(简单select测试),complex(事务测试),nontrx(非事务测试),sp(存储过程) ；默认complex
--mysql-table-engine=STRING     storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]

测试结果解读：

SQL statistics:
    queries performed:
        read:                            1663326 --读总数
        write:                           475233 -- 写总数 
        other:                           237617 -- 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等) 
        total:                           2376176 -- 全部总数
    transactions:                        118808 (989.85 per sec.) -- 总事务数(每秒事务数)
    queries:                             2376176 (19797.13 per sec.) --读写数（每秒读写次数）
    ignored errors:                      1      (0.01 per sec.) --总忽略错误总数(每秒忽略错误次数)
    reconnects:                          0      (0.00 per sec.) --重连总数(每秒重连次数)
 
General statistics: --常规统计
    total time:                          120.0244s --总耗时
    total number of events:              118808 --共发生多少事务数
 
Latency (ms):
         min:                                    6.08 --最小耗时
         avg:                                   10.10 --平均耗时
         max:                                   87.65 --最长耗时
         95th percentile:                       28.16 --95%请求的最大响应时间
         sum:                              1199522.76 --总耗时
 
Threads fairness: --并发统计
    events (avg/stddev):           11880.8000/273.15 --总处理事件数/标准偏差
    execution time (avg/stddev):   119.9523/0.00 --总执行时间/标准偏差

1 默认配置——单点 vs Galera

单点配置：

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Galera默认配置：

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/data/database/mysql
socket=/var/lib/mysql/mysql.sock
 
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
server-id=1
binlog_format=row
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
 
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://172.16.212.11,172.16.212.12,172.16.212.13"
wsrep_node_name="node2"
wsrep_node_address="172.16.212.12"
wsrep_sst_method=xtrabackup
wsrep_sst_auth="root:Password"

2 优化配置——单点 vs Galera

配置优化目标：

[mysqld]
 
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /data/database/mysql/data/mysql.sock
pid-file                       = /data/database/mysql/data/mysql.pid
explicit_defaults_for_timestamp = 1
transaction-isolation          = READ-COMMITTED
character-set-server           = utf8mb4
 
# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP
 
# SAFETY #
max-allowed-packet             = 500M
max-connect-errors             = 1000000
skip-name-resolve
#sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sql-mode               = STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now                 = 1
 
# DATA STORAGE #
datadir                        = /data/database/mysql/data/
 
# BINARY LOGGING #
log-bin                        = /data/database/mysql/master-bin/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1
 
# REPLICATION #
server-id                      = 1
skip-slave-start               = 1
log-slave-updates              = 1
relay-log                      = /data/database/mysql/relay-bin/relay-bin
slave-net-timeout              = 60
sync-master-info               = 1
sync-relay-log                 = 1
sync-relay-log-info            = 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         = 1024
table-open-cache               = 2048
 
# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           =256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2G
 
# LOGGING #
log-error                      = /data/database/mysql/logs/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /data/database/mysql/logs/mysql-slow.log

3 测试结果

结果表明：innodb-buffer-pool-size 值可以在一定程度提升性能，一般可设置为机器总内存的80%

配置优化结果：

key-buffer-size=32M
max-allowed-packet=500M
max-connect-errors=1000000
sysdate-is-now=1
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=1024
table-open-cache=2048
innodb-flush-method=O_DIRECT
innodb-log-files-in-group=2
innodb-log-file-size=256M
innodb-flush-log-at-trx-commit=1
innodb-file-per-table=1
innodb-buffer-pool-size=8G
log-queries-not-using-indexes=1
slow-query-log=1