目录
5.4 mysql中innodb_flush_method参数
5.5 mysql中innodb_io_capacity参数
1. sysbench下载
github下载：
GitHub - akopytov/sysbench: Scriptable database and system performance benchmark
使用该命令也可下载
[root@localhost ~]# git clone https://github.com/akopytov/sysbench2. sysbench安装
[root@localhost soft]# unzip sysbench-master.zip
.......
[root@localhost soft]# cd sysbench-master
[root@localhost sysbench-master]# ./autogen.sh
......
[root@localhost sysbench-master]# ./configure
......
##如果MySQL并不是安装在标准目录/usr/local/bin/下的话，那么就需要自己指定 MySQL 的路径了
[root@localhost sysbench-master]# ./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
#如果是make -j 4，就是4并行跑
[root@localhost sysbench-master]# make
......
[root@localhost sysbench-master]# make install
.......3. sysbench报错解决
#报错1
[root@localhost soft]# sysbench --test=fileio --file-num=4 --file-block-size=4096 --file-total-size=4G prepare
sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
#mysql安装在/usr/local/mysql下，运行
[root@localhost soft]# ldconfig /usr/local/mysql/lib
#报错2
[root@localhost soft]# sysbench --test=fileio --file-num=4 --file-block-size=4096 --file-total-size=4G prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
FATAL: Cannot find benchmark 'prepare': no such built-in test, file or module
#去掉--test=
[root@localhost soft]# sysbench fileio --file-num=4 --file-block-size=4096 --file-total-size=4G prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
4 files, 1048576Kb each, 4096Mb 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
4294967296 bytes written in 47.59 seconds (86.08 MiB/sec).4. sysbench主要参数指令
[root@localhost sysbench-master]# make --helpUsage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
#普通选项
General options:
--threads=N 指定线程数 [1]，--num-threads=N的别名(已弃用)
--events=N 事件总数的限制 [0]，--max-requests=N的别名(已弃用)
--time=N 限制总执行时间(以秒为单位)[10]，--max-time=N的别名(已弃用)
--warmup-time=N 在启用统计信息运行实际基准测试之前，在禁用统计信息的情况下执行事件这么长时间 [0]
--forced-shutdown=STRING 强制关闭或禁用“off”之前，在—时间限制之后等待的秒数 [off]
--thread-stack-size=SIZE 每个线程的堆栈大小 [64K]
--thread-init-timeout=N 等待工作线程初始化的时间(以秒为单位) [30]
--rate=N 平均事务率。0表示不限制[0]，--tx-rate=N的别名(已弃用)
--report-interval=N 以秒为单位定期报告中间统计信息，0禁用间隔报告 [0]
--report-checkpoints=[LIST,...] 转储完整的统计信息，并在指定的时间点重置所有计数器。参数是一个逗号分隔的值列表，表示从测试开始到必须执行报表检查点所花费的时间(以秒为单位)。默认情况下，报表检查点是关闭的。 []
--debug[=on|off] 打印更多调试信息 [off]
--validate[=on|off] 在可能的情况下执行验证检查[off]
--help[=on|off] 打印帮助并退出 [off]
--version[=on|off] 打印版本并退出[off]
--config-file=FILENAME 包含命令行选项的文件
--luajit-cmd=STRING 执行LuaJIT控制命令。这个选项相当于“luajit -j”。有关更多信息，请参见LuaJIT文档
#伪随机数生成器选项
Pseudo-Random Numbers Generator options:
--rand-type=STRING 随机数分布{uniform，gaussian，pareto，zipfian}默认使用[uniform]
--rand-seed=N 种子为随机数发生器。当为0时，将当前时间用作RNG种子。 [0]
--rand-pareto-h=N 形状参数为pareto分布[0.2]
--rand-zipfian-exp=N Zipfian分布的形状 参数 (exponent, theta) [0.8]
#日志选项
Log options:
--verbosity=N 冗长级别{5 - 调试, 0 - 只有关键消息} [3]
--percentile=N 在延迟统计中计算的百分位数(1-100)。使用0的特殊值禁用百分位数计算 [95]
--histogram[=on|off] 在报表中打印延迟直方图 [off]
#一般数据库选项
General database options:
--db-driver=STRING 指定要使用的数据库驱动程序(“帮助”获取可用驱动程序列表) [mysql]
--db-ps-mode=STRING 语句使用模式 {auto, disable} [auto]
--db-debug[=on|off] 打印特定于数据库的调试信息 [off]
#数据库驱动
Compiled-in database drivers:
mysql - MySQL driver
mysql options:
--mysql-host=[LIST,...] MySQL服务器主机[localhost]
--mysql-port=[LIST,...] MySQL服务器端口 [3306]
--mysql-socket=[LIST,...] MySQL socket
--mysql-user=STRING MySQL 用户[sbtest]
--mysql-password=STRING MySQL 密码[]
--mysql-db=STRING MySQL 数据库名称 [sbtest]
--mysql-ssl=STRING SSL模式。这接受与MySQL客户机实用程序中的——ssl-mode选项相同的值。默认情况下禁用 [disabled]
--mysql-ssl-key=STRING 客户端私钥文件的路径名称
--mysql-ssl-ca=STRING CA文件的路径名
--mysql-ssl-cert=STRING 客户端公钥证书文件的路径名
--mysql-ssl-cipher=STRING 为SSL连接使用特定的密码 []
--mysql-compression[=on|off] 如果在客户端库中可用，则使用压缩 [off]
--mysql-debug[=on|off] 跟踪所有客户机库调用 [off]
--mysql-ignore-errors=[LIST,...] 要忽略的错误列表，或“全部” [1213,1020,1205]
--mysql-dry-run[=on|off] 试运行，假设所有MySQL客户端API调用都是成功的，而没有执行它们 [off] them [off]
#编译测试
Compiled-in tests:
fileio - 文件 I/O 测试
cpu - CPU性能测试
memory - 内存功能速度测试
threads - 线程子系统性能测试
mutex - 互斥锁的性能测试
See 'sysbench <testname> help' for a list of options for each test.5. sysbench简单使用
1、使用自带的测试模块，如对cpu,I/O,memory等的测试。
2、使用自带的lua脚本进行测试，如果使用快速安装的方式，默认的脚本路径为：/usr/share/sysbench
3、使用自定义的lua脚本。
5.1 CPU测试
[root@localhost sysbench-master]# sysbench cpu help
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
cpu options:
--cpu-max-prime=N 质数发生器的上限 [10000]
[root@localhost ~]# sysbench cpu --cpu-max-prime=2000 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
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: 6684.08
Throughput:
events/s (eps): 6684.0846
time elapsed: 10.0002s
total number of events: 66842
Latency (ms):
min: 0.12
avg: 0.15
max: 21.63
95th percentile: 0.20
sum: 9947.02
Threads fairness:
events (avg/stddev): 66842.0000/0.00
execution time (avg/stddev): 9.9470/0.005.2 memory测试
[root@localhost bin]# sysbench memory help
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
memory options:
--memory-block-size=SIZE 测试内存块的大小 [1K]
--memory-total-size=SIZE 要传输的数据的总大小 [100G]
--memory-scope=STRING 内存访问范围 {global,local} [global]
--memory-hugetlb[=on|off] 从内存池中分配内存[off]
--memory-oper=STRING 存储操作类型 {read, write, none} [write]
--memory-access-mode=STRING 内存访问模式 {seq,rnd} [seq]
[root@localhost ~]# sysbench memory --memory-block-size=16k --memory-total-size=2G run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
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: 16KiB
total size: 2048MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 131072 (945638.23 per second)
2048.00 MiB transferred (14775.60 MiB/sec)
Throughput:
events/s (eps): 945638.2256
time elapsed: 0.1386s
total number of events: 131072
Latency (ms):
min: 0.00
avg: 0.00
max: 0.53
95th percentile: 0.00
sum: 114.99
Threads fairness:
events (avg/stddev): 131072.0000/0.00
execution time (avg/stddev): 0.1150/0.005.3 文件IO测试
[root@localhost ~]# sysbench fileio help
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
fileio options:
--file-num=N 代表生成测试文件的数量 [128]
--file-block-size=N 在所有IO操作中使用的块大小 [16384]
--file-total-size=SIZE 要创建的文件的总大小 [2G]
--file-test-mode=STRING 测试模式 {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
--file-io-mode=STRING 文件操作模式 {sync,async,mmap} [sync]
--file-extra-flags=[LIST,...] 用于打开文件的附加标志列表 {sync,dsync,direct} []
--file-fsync-freq=N 执行fsync()函数的频率。fsync主要是同步磁盘文件，因为可能有系统和磁盘缓冲的关系。 0代表不使用fsync函数。默认值为100，意思是每写100次，刷新到磁盘1次。
--file-fsync-all[=on|off] 每执行完一次写操作，就执行一次fsync。默认为off。
--file-fsync-end[=on|off] 在测试结束时执行fsync函数。默认为on。
--file-fsync-mode=STRING 文件同步函数的选择，同样是和API相关的参数，由于多个操作系统对于fdatasync支持不同，因此不建议使用fdatasync。默认为fsync。
--file-merged-requests=N 大多情况下，合并可能的IO的请求数，默认为0。
--file-rw-ratio=N 测试时的读写比例，默认时为1.5，即可3：2。#准备
[root@localhost soft]# sysbench fileio --file-num=4 --file-block-size=4096 --file-total-size=4G prepare
#运行
[root@localhost soft]# sysbench --time=30 --threads=4 --events=0 --report-interval=1 fileio --file-num=4 --file-block-size=4096 --file-total-size=4G --file-test-mode=rndrd --file-extra-flags=direct --file-fsync-freq=1 run
#清理
[root@localhost soft]# sysbench --time=30 --threads=4 --events=0 --report-interval=1 fileio --file-num=4 --file-block-size=4096 --file-total-size=4G --file-test-mode=rndrd --file-extra-flags=direct --file-fsync-freq=1 cleanup
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Removing test files...上述参数指定运行时间为30秒，4个线程，每隔1秒生成报告，文件数量为4个总大小为4G，文件块为4KB，文件测试模式为随机读，每写1次数据就刷新1次到磁盘，--file-extra-flags=direct表示直接写入到磁盘，不经过内存
#准备阶段，生成文件
[root@localhost soft]# sysbench fileio --file-num=4 --file-block-size=4096 --file-total-size=4G prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
4 files, 1048576Kb each, 4096Mb 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
4294967296 bytes written in 47.59 seconds (86.08 MiB/sec).
#运行阶段，测试IO
[root@localhost soft]# sysbench --time=30 --threads=4 --events=0 --report-interval=1 fileio --file-num=4 --file-block-size=4096 --file-total-size=4G --file-test-mode=rndrd --file-extra-flags=direct --file-fsync-freq=1 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: directio
4 files, 1GiB each
4GiB total file size
Block size 4KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 1 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Initializing worker threads...
Threads started!
[ 1s ] reads: 7.94 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 9.222
[ 2s ] reads: 19.00 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 5.277
[ 3s ] reads: 33.83 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.702
[ 4s ] reads: 38.00 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.566
[ 5s ] reads: 35.82 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.608
[ 6s ] reads: 42.13 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.481
[ 7s ] reads: 37.11 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.536
[ 8s ] reads: 46.46 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.490
[ 9s ] reads: 65.62 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.448
[ 10s ] reads: 34.38 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.597
[ 11s ] reads: 3.60 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 25.278
[ 12s ] reads: 72.33 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.336
[ 13s ] reads: 73.48 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.336
[ 14s ] reads: 68.58 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.440
[ 15s ] reads: 69.85 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.440
[ 16s ] reads: 73.12 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.348
[ 17s ] reads: 76.02 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.388
[ 18s ] reads: 60.05 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.456
[ 19s ] reads: 70.11 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.440
[ 20s ] reads: 71.63 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.448
[ 21s ] reads: 69.33 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.432
[ 22s ] reads: 61.86 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.527
[ 23s ] reads: 69.31 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.409
[ 24s ] reads: 72.91 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.388
[ 25s ] reads: 72.76 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.409
[ 26s ] reads: 73.62 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.395
[ 27s ] reads: 73.40 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.395
[ 28s ] reads: 72.39 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.424
[ 29s ] reads: 71.49 MiB/s writes: 0.00 MiB/s fsyncs: 0.00/s latency (ms,95%): 0.424
Throughput:
read: IOPS=14614.94 57.09 MiB/s (59.86 MB/s)
write: IOPS=0.00 0.00 MiB/s (0.00 MB/s)
fsync: IOPS=0.00
Latency (ms):
min: 0.05
avg: 0.27
max: 333.23
95th percentile: 0.45
sum: 118025.67
#清理文件
[root@localhost soft]# sysbench --time=30 --threads=4 --events=0 --report-interval=1 fileio --file-num=4 --file-block-size=4096 --file-total-size=4G --file-test-mode=rndrd --file-extra-flags=direct --file-fsync-freq=1 cleanup
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Removing test files...5.4 mysql中innodb_flush_method参数
- fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
- O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成
- O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲
生产环境中使用innodb_flush_method=O_DIRECT
官方建议：
How each setting affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine theInnodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDBbuffer pool and the operating system file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment.
意思就是说，具体的取值跟硬件配置和工作负载相关，最好做一次压测来决定。不过通常来说，linux环境下具有raid控制器和write-back写策略，O_DIRECT是比较好的选择，避免操作系统和存储系统两次缓存；如果使用SAN存储来存放数据文件和日志文件，对于读负载较高的存储系统，使用fsync()或O_DSYNC可能更快。
5.5 mysql中innodb_io_capacity参数
官方建议：
The innodb_io_capacity variable defines the overall I/O capacity available to InnoDB. It should be set to approximately the number of I/O operations that the system can perform per second (IOPS). When innodb_io_capacity is set, InnoDB estimates the I/O bandwidth available for background tasks based on the set value.
innodb_io_capacity表示每秒写能用的IOPS是多少，对读无效对写有决定意义，该值根据磁盘写的IOPS来设置，innodb_io_capacity是innodb_io_capacity_max的一半
5.6 threads测试
[root@localhost soft]# sysbench threads help
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
threads options:
--thread-yields=N 每个请求要执行的收益率数 [1000]
--thread-locks=N 每个线程的锁数量 [8]
[root@localhost soft]# sysbench threads --threads=8 --thread-yields=100 --thread-locks=2 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 8
Initializing random number generator from current time
Initializing worker threads...
Threads started!
Throughput:
events/s (eps): 5894.2951
time elapsed: 10.0010s
total number of events: 58949
Latency (ms):
min: 0.07
avg: 1.35
max: 70.26
95th percentile: 4.41
sum: 79574.87
Threads fairness:
events (avg/stddev): 7368.6250/204.72
execution time (avg/stddev): 9.9469/0.02
316
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
