sysbench 项目测试过程与分析-斯图尔特

一、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 测试准备

测试环境

? 主机、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(,?1,?index(,".")?-?1);
????????????????????????load?=?NF?-??2;
????????????????????????diff?=?ts?-prev_ts;
????????????????????????prev_ts?=?ts;
????????????????????????printf?"\n%s?%s?%s?%s",ts,,,substr($load,?1,?length($load)-1);
????????????????}
????????????????/Queries/?{
????????????????????????printf?fmt,?(-Queries)/diff;
????????????????????????Queries=
????????????????}
????????????????'?"$@"
[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