一、sysbench项目测试过程
1.1 sysbench基准压测 my.cnf
自动生成MySQL的配置文件：http://imysql.cn/my_cnf_generator
[client]
port = 3306
socket = /data/mysql/mysql.sock
[mysql]
prompt="\u@mysqldb \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/
socket = /data/mysql/mysql.sock
pid-file = mysqldb.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65536
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1400
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /var/log/slow.log
log-error = /var/log/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /var/log/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 64
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 2G
innodb_undo_directory = undolog
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 2000
innodb_checksum_algorithm = 0
innodb_lock_wait_timeout = 5
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_sort_buffer_size = 64M
innodb_autoextend_increment = 64
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_open_files = 65536
innodb_purge_rseg_truncate_frequery = 128
binlog_gtid_simple_recovery = 1
# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 604800
#innodb_dedicated_server = 0
innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
[mysqldump]
quick
max_allowed_packet = 32M
1.2 sysbench 测试准备
1. 测试环境
主机、CPU、内存、RAID、硬盘、文件系统、内存、MySQL、sysbench
2. 压测基准值
多少张表，每张表多少数据量，测试脚本，测试多少时间，最大请求数多少，并发线程数，生成多大数量
1.3 进行sysbench OLTP 测试-混合读写
-
准备数据
[root@mysql8 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database sbtest;
Query OK, 1 row affected (0.42 sec)
mysql> exit
Bye
[root@mysql8 ~]# cd /sysbench/
[root@mysql8 sysbench]# sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=root --mysql-password=#root# \
--oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 \
--threads=10 --time=120 --report-interval=10 prepare
# --threads=10:表示发起10个并发连接
# --report-interval=10:表示每10秒输出一次测试进度报告
# --oltp-tables-count=10:表示会生成10个测试表
# --oltp-table-size=100000:表示每个测试表填充数据量为100000
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'...
2. 执行测试
[root@mysql8 ~]# mkdir /sysbench/report/ -p
[root@mysql8 sysbench]# sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=root --mysql-password=#root# \
--oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 \
--threads=10 --time=120 --report-interval=10 run >> /sysbench/report/mysysbench-20190225.log
3. 清理数据
[root@mysql8 sysbench]# sysbench ./share/sysbench/tests/include/oltp_legacy/oltp.lua \
--mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=root --mysql-password=#root# cleanup
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Dropping table 'sbtest1'...
4. 测试结果
重点关注：TPS，QPS，95%以上的响应时长统计
[root@mysql8 report]# more mysysbench-20190225.log
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
# 每10秒钟报告一次测试结果，tps,qps,95%以上的响应时长统计
[ 10s ] thds: 10 tps: 235.04 qps: 4719.82 (r/w/o: 3304.58/944.16/471.08) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 178.21 qps: 3563.14 (r/w/o: 2494.90/711.83/356.41) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 330.10 qps: 6598.23 (r/w/o: 4619.12/1319.01/660.10) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 10 tps: 310.48 qps: 6208.25 (r/w/o: 4346.35/1240.83/621.06) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 10 tps: 113.91 qps: 2278.52 (r/w/o: 1594.59/456.12/227.81) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 10 tps: 293.66 qps: 5880.66 (r/w/o: 4115.41/1177.83/587.42) lat (ms,95%): 59.99 err/s: 0.10 reconn/s: 0.00
[ 70s ] thds: 10 tps: 289.04 qps: 5773.85 (r/w/o: 4042.82/1152.95/578.07) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 10 tps: 187.30 qps: 3746.45 (r/w/o: 2622.66/749.19/374.59) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 234.98 qps: 4705.58 (r/w/o: 3292.91/942.72/469.96) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 10 tps: 306.92 qps: 6138.76 (r/w/o: 4296.85/1228.07/613.84) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 10 tps: 187.60 qps: 3749.19 (r/w/o: 2625.20/748.60/375.40) lat (ms,95%): 46.63 err/s: 0.20 reconn/s: 0.00
[ 120s ] thds: 10 tps: 290.62 qps: 5815.27 (r/w/o: 4071.46/1162.57/581.24) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 414288 # 读总数
write: 118360 # 写总数
other: 59181 # 其它操作，如commit
total: 591829 # 全部总数
transactions: 29589 (246.53 per sec.) # 总事务数（TPS，每秒事务数）
queries: 591829 (4931.09 per sec.) # 读写数（QPS，每秒读写次数）
ignored errors: 3 (0.02 per sec.) # 忽略的错误数
reconnects: 0 (0.00 per sec.)
General statistics: # 一些统计结果
total time: 120.0183s # 总耗时
total number of events: 29589 # 共发生了多少事务数
Latency (ms): # 响应时长统计
min: 3.97 # 最小耗时
avg: 40.56 # 平均耗时
max: 5478.23 # 最长耗时
95th percentile: 70.55 # 95%请求的最大响应时间
sum: 1199998.40 # 总耗时
Threads fairness: # 与线程相关的指标
events (avg/stddev): 2958.9000/47.60 # 事件（平均值/偏差）
execution time (avg/stddev): 119.9998/0.00 # 执行时间（平均值/偏差）
1.4 生成图片报告
gnuplot下载地址： https://sourceforge.net/projects/gnuplot/files/latest/download
通过Excel进行数据分析： https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power%20Systems/page/nmon_analyser
1.4.1 安装及使用 gnuplot
[root@mysql8 software]# yum install gnuplot
[root@mysql8 ~]# gnuplot
G N U P L O T
Version 4.6 patchlevel 2 last modified 2013-03-14
Build System: Linux x86_64
Copyright (C) 1986-1993, 1998, 2004, 2007-2013
Thomas Williams, Colin Kelley and many others
gnuplot home: http://www.gnuplot.info
faq, bugs, etc: type "help FAQ"
immediate help: type "help" (plot window: hit 'h')
Terminal type set to 'x11'
gnuplot> plot '/root/mysysbench-20190225.log' using 9 with lines title 'QPS'
# using 9：表示使用第9列数据作图
# with lines：定义图中的趋势使用线来表示
# title ‘QPS’：定义线的名称
# 使用，（逗号）分割，进行多列数据的绘制
http://sourceforge.net/projects/nmon/files/nmon16g_x86.tar.gz
[root@mysql8 report]# vim sysbench_monitor.sh
#!/bin/bash
#开始前获取全局配置参数
#每五秒获取一次cpu load,MySQL全局信息，InnoDB引擎相关信息，线程信息
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/sysbench/report
mysql -uroot -p123456 -e 'show global variables'>>mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_%H)
sleep=$(date +%s.%N |awk "{print $INTERVAL -(\$1 % $INTERVAL)}")
sleep $sleep
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)" #通过uptime命令获取cpu load
echo "$ts $loadavg">> $PREFIX-${file}-status
mysql -uroot -p123456 -e "show global status" >> $PREFIX-${file}-status 2>/dev/null & #获取MySQL全局信息
echo "$ts $loadavg">> $PREFIX-${file}-innodbstatus
mysql -uroot -p123456 -e "show engine innodb status\G" >> $PREFIX-${file}-innodbstatus 2>/dev/null & #获取引擎信息
echo "$ts $loadavg">> $PREFIX-${file}-processlist
mysql -uroot -p123456 -e "show full processlist\G" >>$PREFIX-${file}-processlist 2>/dev/null & #获取线程信息
echo $ts
done
echo Exiting because $RUNFILE not exist
[root@mysql8 report]# chmod +x sysbench_monitor.sh
[root@mysql8 report]# nohup /sysbench/report/sysbench_monitor.sh &
[root@mysql8 report]# vim sysbench_anaylyze.sh
#!/bin/bash
awk '
BEGIN{
printf "#ts date time load QPS";
fmt = " %.2f";
}
/^TS/ { # The timestamp lines begin with TS.
ts = substr($2, 1, index($2,".") - 1);
load = NF - 2;
diff = ts -prev_ts;
prev_ts = ts;
printf "\n%s %s %s %s",ts,$3,$4,substr($load, 1, length($load)-1);
}
/Queries/ {
printf fmt, ($2-Queries)/diff;
Queries=$2
}
' "$@"
[root@mysql8 report]# chmod +x sysbench_anaylyze.sh
[root@mysql8 report]# /sysbench/report/sysbench_anaylyze.sh 5-sec-status-2019-03-02_15-status > sysbench_status_out.log1.5 每次压缩后要做什么
1.5 每次压缩后要做什么
-
清数据
-
重启主机
二、sysbench 其它类型测试
2.1 只读（从库查询）
[root@mysql8 sysbench]# sysbench /sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=20 --report-interval=10 --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=on --oltp-skip-trx=on prepare
[root@mysql8 sysbench]# sysbench /sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=20 --report-interval=10 --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=on --oltp-skip-trx=on run > /sysbench/report/mysysbench-20190302.log
[root@mysql8 sysbench]# sysbench /sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=123456--mysql-password=root --oltp-tables-count=10 cleanup
2.2 只写-更新测试（主库写）
如果基准测试的时候，你只想比较两个项目的update（或insert）效率，那可以不使用oltp脚本，而直接改用update_index.lua。
[root@mysql8 sysbench]# sysbench /sysbench/share/sysbench/tests/include/oltp_legacy/update_index.lua --mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --oltp-tables-count=10 --oltp-table-size=10000 --threads=10 --time=20 --report-interval=10 --rand-init=on --oltp-read-only=off prepare
[root@mysql8 sysbench]# sysbench /sysbench/share/sysbench/tests/include/oltp_legacy/update_index.lua --mysql-host=172.16.216.195 --mysql-port=3306 --mysql-user=123456 --mysql-password=root --mysql-db=sbtest --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=20 --report-interval=10 --rand-init=on --oltp-read-only=off run > /sysbench/report/mysysbench-19030205.log
[root@mysql8 sysbench]# sysbench /sysbench/share/sysbench/tests/include/oltp_legacy/update_index.lua --mysql-host=172.16.216.195--mysql-port=3306 --mysql-user=root --oltp-tables-count=10 --mysql-password=123456 cleanup
三、IO性能测试-fileio
[root@mysql8 sysbench]# sysbench fileio help
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
fileio options:
--file-num=N number of files to create [128] # 创建测试文件的数量。默认是 128
--file-block-size=N block size to use in all IO operations [16384] # 测试时文件块的大小。默认是 16384(16K)
--file-total-size=SIZE total size of files to create [2G] # 测试文件的总大小。默认是 2G
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} # 文件测试模式{seqwr(顺序写), seqrewr(顺序读写),
seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)}
--file-io-mode=STRING file operations mode {sync,async,mmap} [sync] # 文件操作模式{sync(同步),async(异步),fastmmap(快速
map 映射),slowmmap(慢 map 映射)}。默认是 sync
--file-async-backlog=N number of asynchronous operatons to queue per thread [128]
--file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100] # 执行 fsync()的频率。(0 – 不使用 fsync())。默认是 100
--file-fsync-all[=on|off] do fsync() after each write operation [off] # 每执行完一次写操作就执行一次 fsync。默认是 off
--file-fsync-end[=on|off] do fsync() at the end of test [on] # 在测试结束时才执行 fsync。默认是 on
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync] # 使用额外的标志来打开文件{sync,dsync,direct} 。
默认为空
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0] # 如果可以，合并最多的 IO 请求数(0 – 表示不合并)。默
认是 0
--file-rw-ratio=N reads/writes ratio for combined test [1.5] # 测试时的读写比例。默认是 1.5
Sysbench 的文件测试模式:
seqwr 顺序写
seqrew r 顺序读写
seqrd 顺序读
rndrd 随机读
rndwr 随机写
rndrw 随机读写
3.1 准备 IO 测试文件：执行如下命令，生成 16 个文件，用于本次测试
[root@mysql8 ~]# mkdir /mysql/data/tmp -p
[root@mysql8 ~]# cd /mysql/data/tmp
[root@mysql8 tmp]# sysbench fileio --file-num=16 --file-total-size=20M prepare
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
16 files, 1280Kb each, 20Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
20971520 bytes written in 0.61 seconds (32.86 MiB/sec).
[root@mysql8 tmp]# ls
test_file.0 test_file.10 test_file.12 test_file.14 test_file.2 test_file.4 test_file.6 test_file.8
test_file.1 test_file.11 test_file.13 test_file.15 test_file.3 test_file.5 test_file.7 test_file.9
3.2 测试多线程下小 IO 的随机只读性能
[root@mysql8 tmp]# sysbench fileio --file-num=16 --file-total-size=20M --file-test-mode=rndrd --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 run
# --file-num=16：文件数量是16个
# --file-total-size=20M：文件总大小20M
# --file-test-mode=rndrd：测试模式是随机读取
# --file-extra-flags=direct：使用额外的标志来打开文件{sync,dsync,direct}
# --file-fsync-freq=0：执行fsync()的频率
# --file-block-size=16384：测试时文件块的大小位16384（16k）
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Extra file open flags: directio
16 files, 1.25MiB each
20MiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Initializing worker threads...
Threads started!
File operations:
reads/s: 7332.59 # 每秒读写次数，也就是IOPS，越大越好
writes/s: 0.00 # 每秒写的次数
fsyncs/s: 0.00 # 每秒同步的次数
Throughput: #吞吐量
read, MiB/s: 114.57 # 每秒读多少兆，越大越好
written, MiB/s: 0.00
General statistics:
total time: 10.0002s # 总时间
total number of events: 73340 # 总事件
Latency (ms):
min: 0.06
avg: 0.14
max: 8.55
95th percentile: 0.18 # 95%的响应时间，越小越好
sum: 9939.93
Threads fairness:
events (avg/stddev): 73340.0000/0.00
execution time (avg/stddev): 9.9399/0.00
3.3 测试多线程下小IO的随机写入性能
[root@mysql8 tmp]# sysbench fileio --file-num=16 --file-total-size=20M --file-test-mode=rndwr --max-time=120 --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Extra file open flags: directio
16 files, 1.25MiB each
20MiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Initializing worker threads...
Threads started!
File operations:
reads/s: 0.00
writes/s: 6236.45
fsyncs/s: 0.13
Throughput:
read, MiB/s: 0.00
written, MiB/s: 97.44
General statistics:
total time: 120.0013s
total number of events: 748392
Latency (ms):
min: 0.06
avg: 0.16
max: 1417.16
95th percentile: 0.20
sum: 119277.92
Threads fairness:
events (avg/stddev): 748392.0000/0.00
execution time (avg/stddev): 119.2779/0.00
[root@mysql8 ~]# iostat 5
Linux 3.10.0-957.5.1.el7.x86_64 (mysql8.linuxplus.com) 03/02/2019 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
3.30 0.03 2.42 2.42 0.00 91.82
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 109.04 259.23 1440.35 3636386 20204721
scd0 0.00 0.07 0.00 1028 0
dm-0 109.23 258.41 1438.81 3624842 20183129
dm-1 0.37 0.22 1.39 3016 19560
avg-cpu: %user %nice %system %iowait %steal %idle
1.83 0.00 13.36 37.39 0.00 47.41
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 4130.40 0.00 66077.60 0 330388
scd0 0.00 0.00 0.00 0 0
dm-0 4130.20 0.00 66074.40 0 330372
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
2.16 0.00 20.48 30.49 0.00 46.87
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 6307.80 0.00 100981.60 0 504908
scd0 0.00 0.00 0.00 0 0
dm-0 6308.20 0.00 100988.00 0 504940
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
2.36 0.00 20.11 29.10 0.00 48.43
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 6778.60 0.00 108450.40 0 542252
scd0 0.00 0.00 0.00 0 0
dm-0 6778.40 0.00 108447.20 0 542236
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
2.16 0.00 18.97 27.16 0.00 51.72
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 6572.20 0.00 105148.00 0 525740
scd0 0.00 0.00 0.00 0 0
dm-0 6572.00 0.00 105144.80 0 525724
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
1.49 0.00 10.73 14.45 0.00 73.33
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 3265.20 0.00 52256.80 0 261284
scd0 0.00 0.00 0.00 0 0
dm-0 3265.20 0.00 52256.80 0 261284
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
1.11 0.00 1.31 4.92 0.00 92.66
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 2.99 3.19 38.22 16 191
scd0 0.00 0.00 0.00 0 0
dm-0 3.59 3.19 39.02 16 195
dm-1 0.00 0.00 0.00 0 0
3.4 测试多线程下小IO的随机读写性能
[root@mysql8 tmp]# sysbench fileio --file-num=16 --file-total-size=20M --file-test-mode=rndrw --file-extra-flags=direct --file-fsync-freq=0 --max-time=60 --file-block-size=16384 run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Extra file open flags: directio
16 files, 1.25MiB each
20MiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
File operations:
reads/s: 4239.61
writes/s: 2826.41
fsyncs/s: 0.27
Throughput:
read, MiB/s: 66.24
written, MiB/s: 44.16
General statistics:
total time: 60.0002s
total number of events: 423975
Latency (ms):
min: 0.06
avg: 0.14
max: 558.82
95th percentile: 0.18
sum: 59647.57
Threads fairness:
events (avg/stddev): 423975.0000/0.00
execution time (avg/stddev): 59.6476/0.00
[root@mysql8 ~]# iostat 10
Linux 3.10.0-957.5.1.el7.x86_64 (mysql8.linuxplus.com) 03/02/2019 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
3.25 0.03 2.47 2.50 0.00 91.75
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 140.33 491.59 1709.91 7095746 24681318
scd0 0.00 0.07 0.00 1028 0
dm-0 140.51 490.79 1708.41 7084202 24659710
dm-1 0.36 0.21 1.36 3016 19560
avg-cpu: %user %nice %system %iowait %steal %idle
1.60 0.00 12.18 15.37 0.00 70.85
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 4111.30 39457.60 26351.60 394576 263516
scd0 0.00 0.00 0.00 0 0
dm-0 4111.30 39457.60 26351.60 394576 263516
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
1.82 0.00 18.68 30.46 0.00 49.03
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 6489.40 62289.60 41530.40 622896 415304
scd0 0.00 0.00 0.00 0 0
dm-0 6489.50 62289.60 41532.00 622896 415320
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
1.95 0.00 21.36 28.00 0.00 48.69
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 7318.10 70248.00 46834.40 702480 468344
scd0 0.00 0.00 0.00 0 0
dm-0 7318.00 70248.00 46832.80 702480 468328
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
2.07 0.00 21.02 28.28 0.00 48.63
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 7119.20 68332.80 45603.20 683328 456032
scd0 0.00 0.00 0.00 0 0
dm-0 7119.20 68332.80 45603.20 683328 456032
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
2.05 0.00 18.25 27.48 0.00 52.21
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 6722.40 64524.80 43022.00 645248 430220
scd0 0.00 0.00 0.00 0 0
dm-0 6722.50 64524.80 43022.00 645248 430220
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
2.17 0.00 20.75 24.93 0.00 52.15
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 7425.50 71278.40 47521.20 712784 475212
scd0 0.00 0.00 0.00 0 0
dm-0 7425.50 71278.40 47521.20 712784 475212
dm-1 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
1.40 0.00 9.11 10.04 0.00 79.45
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 3218.20 30884.80 20594.65 308848 205946
scd0 0.00 0.00 0.00 0 0
dm-0 3218.20 30884.80 20594.65 308848 205946
dm-1 0.00 0.00 0.00 0 0
3.5 清理测试时生成的文件
[root@mysql8 tmp]# sysbench fileio --threads=20 --file-total-size=20M --file-test-mode=rndrw cleanup
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Removing test files...
四、CPU 性能测试
[root@mysql8 tmp]# sysbench cpu --cpu-max-prime=2000 run
# --cpu-max-prime=N 最大质数发生器数量。默认是 10000
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 2000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 9643.27
General statistics:
total time: 10.0002s
total number of events: 96451
Latency (ms):
min: 0.09
avg: 0.10
max: 28.52
95th percentile: 0.12
sum: 9970.21
Threads fairness:
events (avg/stddev): 96451.0000/0.00
execution time (avg/stddev): 9.9702/0.00
五、内存性能测试
[root@mysql8 tmp]# sysbench memory help
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
memory options:
--memory-block-size=SIZE size of memory block for test [1K] # 测试时内存块大小。默认是 1K
--memory-total-size=SIZE total size of data to transfer [100G] # 传输数据的总大小。默认是 100G
--memory-scope=STRING memory access scope {global,local} [global] # 内存访问范围{global,local}。默认是 global
--memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off] # 从 HugeTLB 池内存分配。默认是 off
--memory-oper=STRING type of memory operations {read, write, none} [write] # 内存操作类型。{read, write, none} 默认是 write
--memory-access-mode=STRING memory access mode {seq,rnd} [seq] # 存储器存取方式{seq,rnd} 默认是 seq
[root@mysql8 tmp]# sysbench memory --memory-block-size=8k --memory-total-size=1G run
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 8KiB
total size: 1024MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 131072 (1153336.20 per second)
1024.00 MiB transferred (9010.44 MiB/sec)
General statistics:
total time: 0.1119s
total number of events: 131072
Latency (ms):
min: 0.00
avg: 0.00
max: 3.77
95th percentile: 0.00
sum: 92.14
Threads fairness:
events (avg/stddev): 131072.0000/0.00
execution time (avg/stddev): 0.0921/0.00
六、线程性能测试
[root@mysql8 ~]# sysbench threads help
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
threads options:
--thread-yields=N number of yields to do per request [1000] # 每个请求产生多少个线程。默认是 1000
--thread-locks=N number of locks per thread [8] # 每个线程的锁的数量。默认是 8
[root@mysql8 ~]# sysbench threads --num-threads=100 --thread-yields=100 --thread-locks=4 run
WARNING: --num-threads is deprecated, use --threads instead
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Initializing random number generator from current time
Initializing worker threads...
Threads started!
General statistics:
total time: 10.0367s
total number of events: 15943
Latency (ms):
min: 0.20
avg: 62.86
max: 7007.05
95th percentile: 118.92
sum: 1002219.97
Threads fairness:
events (avg/stddev): 159.4300/157.31
execution time (avg/stddev): 10.0222/0.01
You have mail in /var/spool/mail/root
七、OLTP性能测试
oltp options:
--oltp-test-mode=STRING # 执行模式 {simple,complex(advanced transactional),nontrx(non-transactional),sp}。默认是 complex
--oltp-reconnect-mode=STRING # 重新连接模式{session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个 SQL 语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)}。默认是 session
--oltp-sp-name=STRING # 存储过程的名称。默认为空
--oltp-read-only=[on|off] # 只读模式。Update，delete，insert 语句不可执行。默认是 off
--oltp-skip-trx=[on|off] # 省略 begin/commit 语句。默认是 off
--oltp-range-size=N # 查询范围。默认是 100
--oltp-point-selects=N # number of point selects [10]
--oltp-simple-ranges=N # number of simple ranges [1]
--oltp-sum-ranges=N # number of sum ranges [1]
--oltp-order-ranges=N # number of ordered ranges [1]
--oltp-distinct-ranges=N # number of distinct ranges [1]
--oltp-index-updates=N # number of index update [1]
--oltp-non-index-updates=N # number of non-index updates [1]
--oltp-nontrx-mode=STRING # 查询类型对于非事务执行模式{select, update_key,update_nokey, insert, delete} [select]
--oltp-auto-inc=[on|off] AUTO_INCREMENT #是否开启。默认是 on
--oltp-connect-delay=N # 在多少微秒后连接数据库。默认是 10000
--oltp-user-delay-min=N # 每个请求最短等待时间。单位是 ms。默认是 0
--oltp-user-delay-max=N # 每个请求最长等待时间。单位是 ms。默认是 0
--oltp-table-name=STRING # 测试时使用到的表名。默认是 sbtest
--oltp-table-size=N # 测试表的记录数。默认是 10000
--oltp-dist-type=STRING # 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是 special
--oltp-dist-iter=N # 产生数的迭代次数。默认是 12
--oltp-dist-pct=N # 值的百分比被视为'special' (for special distribution)。默认是 1
--oltp-dist-res=N # ‘special’的百分比值。默认是 75
参考文档：
https://blog.csdn.net/u012423685/article/details/108617102
https://blog.51cto.com/stuart/2379233
标签：Mysql
没有评论