一、主机环境
两台机配置相同:
[root@192 ~]# pt-summary
# Percona Toolkit System Summary Report ######################
Date | 2018-08-15 16:47:37 UTC (local TZ: EDT -0400)
Hostname | 192.168.237.128
Uptime | 4:41, 1 user, load average: 0.00, 0.01, 0.05
System | VMware, Inc.; VMware Virtual Platform; vNone (Other)
Service Tag | VMware-56 4d 17 8c b3 93 0a 4b-22 86 08 db fd 63 8d 28
Platform | Linux
Release | Red Hat Enterprise Linux Server release 7.5 (Maipo)
Kernel | 3.10.0-862.el7.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.17
SELinux | Disabled
Virtualized | VMWare
# Processor ##################################################
Processors | physical = 1, cores = 1, virtual = 1, hyperthreading = no
Speeds | 1x3408.002
Models | 1xIntel(R) Core(TM) i7-6700 CPU @ 3.40GHz
Caches | 1x8192 KB
# Memory #####################################################
Total | 974.6M
Free | 508.8M
Used | physical = 206.3M, swap allocated = 2.0G, swap used = 0.0, virtual = 206.3M
Buffers | 259.4M
Caches | 598.7M
Dirty | 416 kB
UsedRSS | 213.2M
Swappiness | 30
DirtyPolicy | 30, 10
DirtyStatus | 0, 0
二、主从库配置
数据库环境:
[root@192 ~]# pt-mysql-summary
# Percona Toolkit MySQL Summary Report #######################
System time | 2018-08-15 16:50:03 UTC (local TZ: EDT -0400)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
/var/lib/mysql 0 0 /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/libexec/mysqld
Has symbols | No
# Report On Port 3306 ########################################
User | @
Time | 2018-08-15 12:50:03 (EDT)
Hostname | 192.168.237.128
Version | 5.5.56-MariaDB MariaDB Server
Built On | Linux x86_64
Started | 2018-08-15 08:06 (up 0+04:43:15)
Databases | 5
Datadir | /var/lib/mysql/
主库:
[root@192 sysbench]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#skip-grant-tables
log_bin=mysql-bin
server_id=129
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
从库:
[root@192 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#skip-grant-tables
log_bin=mysql-bin
server_id=130
relay_log=/var/lib/mysql/mysql-relay-bin
log_slave_updates=1
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
三、测试脚本
主机执行:
mysqladmin create sbtest
sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password= \
--test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 --oltp-table-size=10000000 --rand-init=on prepare
sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password= \
--test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 \
--oltp-table-size=10000000 --num-threads=100 --oltp-read-only=off \
--report-interval=10 --rand-type=uniform --max-time=120 \
--max-requests=0 --percentile=99 run >> sysbench_oltpX_8_20180816.log
四、输出
主机监控(mysqladmin ext -i1 | awk '/Queries/{q=$4-qp;qp=$4}/Threads_connected/{tc=$4}/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}')输出:
2569 76 19
11826 102 11
11203 102 88
11817 102 91
11639 102 84
12545 102 11
12581 102 91
12848 102 46
12281 102 82
12769 102 9
11993 102 52
12807 102 15
12449 102 12
11901 102 13
12680 102 24
11498 102 27
12793 102 25
12612 102 18
12141 102 102
12471 102 24
10620 102 5
13148 102 27
12679 102 10
12609 102 71
12028 102 4
12912 102 14
12561 102 15
12803 102 13
11966 102 12
12493 102 74
11592 102 26
12194 102 25
12836 102 26
12345 102 8
12597 102 29
10947 102 14
12886 102 20
12934 102 17
10743 102 20
11795 102 17
12655 102 29
12825 102 4
12572 102 27
12587 102 25
12984 102 60
12945 102 17
11768 102 96
10871 102 50
12032 102 14
9072 102 60
11684 102 17
12717 102 30
10613 102 8
11733 102 30
12063 102 90
12941 102 32
12245 102 42
12221 102 66
12964 102 5
12556 102 25
11134 102 49
12215 102 13
11688 102 3
12414 102 14
12278 102 21
12800 102 12
11524 102 14
13264 102 102
12592 102 3
13047 102 55
11420 102 63
11926 102 7
12644 102 23
12954 102 44
12278 102 97
13449 102 12
12873 102 43
13150 102 86
13381 102 23
12662 102 22
12607 102 22
12732 102 15
12570 102 11
12568 102 20
13009 102 26
13234 102 28
12832 102 5
12640 102 39
11975 102 44
11693 102 22
12417 102 3
13128 102 22
13027 102 28
13157 102 18
12959 102 22
12825 102 79
13117 102 85
13012 102 3
13304 102 4
13121 102 60
12978 102 15
12992 102 90
11598 102 24
12915 102 12
11535 102 22
12652 102 36
12051 102 57
12661 102 10
12588 102 102
12562 102 102
12803 102 24
12404 102 2
13089 102 38
13101 102 59
11132 102 49
12903 102 10
13068 102 10
13077 102 25
12916 102 29
12722 102 102
5254 2 2
备机相同脚本输出:
6 1 1
216 1 1
1331 1 1
1651 1 1
2291 1 1
2356 1 1
2701 1 1
2611 1 1
2521 1 1
2946 1 1
2881 1 1
3401 1 1
3101 1 1
3016 1 1
3001 1 1
2851 1 1
3316 1 1
3031 1 1
3086 1 1
2526 1 1
3166 1 1
3391 1 1
3211 1 1
3146 1 1
3001 1 1
3241 1 1
3146 1 1
3181 1 1
2951 1 1
3261 1 1
2801 1 1
3046 1 1
3131 1 1
3091 1 1
3296 1 1
2726 1 1
3186 1 1
3266 1 1
2576 1 1
3021 1 1
3176 1 1
3096 1 1
3206 1 1
3196 1 1
3231 1 1
3156 1 1
3141 1 1
2651 1 1
2971 1 1
2096 1 1
3046 1 1
3211 1 1
2591 1 1
3021 1 1
2926 1 1
3216 1 1
3266 1 1
2941 1 1
3131 1 1
3201 1 1
2466 1 1
3311 1 1
3036 1 1
3001 1 1
3171 1 1
3176 1 1
2891 1 1
3246 1 1
2936 1 1
3316 1 1
2771 1 1
3101 1 1
3131 1 1
3321 1 1
2906 1 1
3486 1 1
3191 1 1
3181 1 1
3431 1 1
2811 1 1
3581 1 1
3171 1 1
3171 1 1
3111 1 1
3186 1 1
3426 1 1
3316 1 1
2976 1 1
3031 1 1
3021 1 1
2928 1 1
3379 1 1
3201 1 1
3336 1 1
2941 1 1
3521 1 1
3116 1 1
3401 1 1
3306 1 1
3361 1 1
2841 1 1
3501 1 1
3096 1 1
3141 1 1
2936 1 1
3086 1 1
3091 1 1
3086 1 1
3191 1 1
3101 1 1
3201 1 1
2996 1 1
3141 1 1
3426 1 1
2856 1 1
3186 1 1
3206 1 1
2536 1 1
3971 1 1
3221 1 1
1726 1 1
五、测试报告
主机日志:cat sysbench_oltpX_8_20180816.log
Threads started!
[ 10s ] thds: 100 tps: 406.50 qps: 11696.01 (r/w/o: 8472.68/428.79/2794.54) lat (ms,99%): 1771.29 err/s: 190.81 reconn/s: 0.00
[ 20s ] thds: 100 tps: 617.09 qps: 12358.18 (r/w/o: 8652.02/633.50/3072.67) lat (ms,99%): 390.30 err/s: 1.10 reconn/s: 0.00
[ 30s ] thds: 100 tps: 615.67 qps: 12311.33 (r/w/o: 8617.70/635.97/3057.66) lat (ms,99%): 344.08 err/s: 0.30 reconn/s: 0.00
[ 40s ] thds: 100 tps: 605.53 qps: 12116.38 (r/w/o: 8482.41/621.83/3012.14) lat (ms,99%): 325.98 err/s: 0.40 reconn/s: 0.00
[ 50s ] thds: 100 tps: 601.30 qps: 12016.50 (r/w/o: 8409.77/622.71/2984.02) lat (ms,99%): 404.61 err/s: 0.10 reconn/s: 0.00
[ 60s ] thds: 100 tps: 595.27 qps: 11976.49 (r/w/o: 8387.27/616.17/2973.05) lat (ms,99%): 404.61 err/s: 0.40 reconn/s: 0.00
[ 70s ] thds: 100 tps: 617.52 qps: 12349.92 (r/w/o: 8638.39/642.42/3069.10) lat (ms,99%): 325.98 err/s: 0.10 reconn/s: 0.00
[ 80s ] thds: 100 tps: 640.29 qps: 12707.61 (r/w/o: 8888.67/656.49/3162.45) lat (ms,99%): 404.61 err/s: 0.40 reconn/s: 0.00
[ 90s ] thds: 100 tps: 626.38 qps: 12557.64 (r/w/o: 8792.68/652.28/3112.69) lat (ms,99%): 383.33 err/s: 0.20 reconn/s: 0.00
[ 100s ] thds: 100 tps: 647.50 qps: 12976.51 (r/w/o: 9093.24/670.20/3213.08) lat (ms,99%): 530.08 err/s: 0.10 reconn/s: 0.00
[ 110s ] thds: 100 tps: 616.24 qps: 12397.55 (r/w/o: 8669.49/648.04/3080.01) lat (ms,99%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 100 tps: 639.19 qps: 12721.81 (r/w/o: 8911.17/657.99/3152.65) lat (ms,99%): 397.39 err/s: 0.10 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1040550
write: 74939
other: 367129
total: 1482618
transactions: 72384 (602.49 per sec.)
queries: 1482618 (12340.60 per sec.)
ignored errors: 1941 (16.16 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1403s
total number of events: 72384
Latency (ms):
min: 1.29
avg: 165.87
max: 6444.46
99th percentile: 475.79
sum: 12006265.24
Threads fairness:
events (avg/stddev): 723.8400/13.82
execution time (avg/stddev): 120.0627/0.04