--安装yum包 yum -y install bzr libtool --对压缩包进行解 --注意不要在mysql的官网下载,因为其只支持mysql不支持pg https://github.com/akopytov/sysbench/tree/0.4 tar zxf sysbench-0.4.12-1.1.tgz mv sysbench-0.4.12-1.1 sysbench unzip -d sysbench sysbench-0.5.zip --如果MySQL不是安装在标准默认目录下,那么就还需要指定MySQL的路径 ./autogen.sh mkdir -p /usr/local/sysbencd_0.5/log ./configure --prefix=/usr/local/sysbencd_0.5 --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib make && make install ./configure --prefix=/usr/local/sysbench/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib 编译sysbench的问题 drv_pgsql.c:29:22: error: libpq-fe.h: No such file or directory drv_pgsql.c:104: error: expected specifier-qualifier-list before ‘Oid’ 编译时指定CFLAGS CFLAGS="-L/usr/local/postgresql/9.3.4/lib -I/usr/local/postgresql/9.3.4/include -lpq" ./configure --prefix=/usr/local/sysbench/sysbench --with-gnu-ld --with-mysql --with-pgsql --enable-dependency-tracking --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib --with-pgsql-includes=/usr/local/postgresql/9.3.4/include --with-pgsql-libs=/usr/local/postgresql/9.3.4/lib make时报错: /usr/bin/ld: warning: libssl.so.1.0.0, needed by /usr/local/postgresql/9.3.4/lib/libpq.so, not found (try using -rpath or -rpath-link) /usr/bin/ld: warning: libcrypto.so.1.0.0, needed by /usr/local/postgresql/9.3.4/lib/libpq.so, not found (try using -rpath or -rpath-link) env LDFLAGS=-R/usr/local/postgresql/9.3.4/lib ./configure --prefix=/usr/local/sysbench/sysbench --with-gnu-ld --with-pgsql --enable-dependency-tracking --with-pgsql-includes=/usr/local/postgresql/9.3.4/include --with-pgsql-libs=/usr/local/postgresql/9.3.4/lib cp /usr/local/sysbencd_0.5/bin/sysbench /usr/local/bin/ cp -a /usr/local/sysbench/sysbench/tests /usr/local/sysbencd_0.5/ [root@mars sysbencd_0.5]# sysbench sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory --sysbench无法找到mysql的库文件,这很可能是环境变量LD_LIBRARY_PATH没有设置,设置后即可解决该问题: [root@mars sysbencd_0.5]# export LD_LIBRARY_PATH=/usr/local/mysql/lib export LD_LIBRARY_PATH=/usr/local/mysql/lib ./sysbench --test=oltp --oltp-table-size=1000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --db-driver=mysql prepare sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='123456' --oltp-table-size=1000 --num-threads=8 --oltp-read-only=off run >> /tmp/sysbench_oltp.log Mysql数据库测试 create database sbtest; sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='laba!123' --test=/usr/local/sysbencd_0.5/tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=1000 --rand-init=on prepare # --test=tests/db/oltp.lua 表示调用 tests/db/oltp.lua 脚本进行 oltp 模式测试 # --oltp_tables_count=10 表示会生成 10 个测试表 # --oltp-table-size=100000 表示每个测试表数据行数 # --rand-init=on 表示每个测试表都是用随机数据来填充的 sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='laba!123' --test=/usr/local/sysbencd_0.5/tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=1000 --num-threads=8 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=120 --max-requests=0 --percentile=99 run >> /usr/local/sysbencd_0.5/log/sysbench_oltp.log # --num-threads=8 表示发起 8个并发连接 # --oltp-read-only=off 表示不要进行只读测试,也就是会采用读写混合模式测试 # --report-interval=10 表示每10秒输出一次测试进度报告 # --rand-type=uniform 表示随机类型为固定模式,其他几个可选随机模式:uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托) # --max-time=120 表示最大执行时长为 120秒 # --max-requests=0 表示总请求数为 0,因为上面已经定义了总执行时长,所以总请求数可以设定为 0;也可以只设定总请求数,不设定最大执行时长 # --percentile=99 表示设定采样比例,默认是 95%,即丢弃1%的长请求,在剩余的99%里取最大值 ==========================测试结果解读如下========================== sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Report intermediate results every 10 second(s) Random number generator seed is 0 and will be ignored Threads started! -- 每10秒钟报告一次测试结果,tps、每秒读、每秒写、99%以上的响应时长统计 [ 10s] threads: 8, tps: 26.60, reads/s: 410.22, writes/s: 115.11, response time: 1050.29ms (99%) [ 20s] threads: 8, tps: 32.40, reads/s: 462.00, writes/s: 131.60, response time: 699.68ms (99%) [ 30s] threads: 8, tps: 33.00, reads/s: 467.60, writes/s: 133.20, response time: 496.50ms (99%) [ 40s] threads: 8, tps: 30.80, reads/s: 441.00, writes/s: 125.30, response time: 617.02ms (99%) [ 50s] threads: 8, tps: 35.80, reads/s: 504.00, writes/s: 143.80, response time: 526.03ms (99%) [ 60s] threads: 8, tps: 33.20, reads/s: 468.50, writes/s: 133.30, response time: 774.87ms (99%) ......... [3540s] threads: 8, tps: 41.90, reads/s: 602.00, writes/s: 170.90, response time: 483.88ms (99%) [3550s] threads: 8, tps: 43.40, reads/s: 616.00, writes/s: 175.40, response time: 383.69ms (99%) [3560s] threads: 8, tps: 38.00, reads/s: 543.20, writes/s: 154.40, response time: 475.41ms (99%) [3570s] threads: 8, tps: 39.60, reads/s: 560.00, writes/s: 159.60, response time: 425.82ms (99%) [3580s] threads: 8, tps: 42.10, reads/s: 600.60, writes/s: 170.80, response time: 384.04ms (99%) [3590s] threads: 8, tps: 38.10, reads/s: 547.40, writes/s: 155.40, response time: 542.17ms (99%) [3600s] threads: 8, tps: 40.40, reads/s: 574.00, writes/s: 163.40, response time: 459.05ms (99%) OLTP test statistics: queries performed: read: 2068584 -- 读总数 write: 588759 -- 写总数 other: 293247 -- 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等) total: 2950590 -- 全部总数 transactions: 145491 (40.41 per sec.) -- 总事务数(每秒事务数) deadlocks: 2265 (0.63 per sec.) -- 发生死锁总数 read/write requests: 2657343 (738.12 per sec.) -- 读写总数(每秒读写次数) other operations: 293247 (81.45 per sec.) -- 其他操作总数(每秒其他操作次数) General statistics: total time: 3600.1452s -- 总耗时 total number of events: 145491 -- 共发生多少事务数 total time taken by event execution: 28800.5977s -- 所有事务耗时相加(不考虑并行因素) response time: min: 49.84ms -- 最小耗时 avg: 197.95ms -- 平均耗时 max: 1355.40ms -- 最长耗时 approx. 99 percentile: 433.54ms -- 超过99%平均耗时 Threads fairness: events (avg/stddev): 18186.3750/63.14 execution time (avg/stddev): 3600.0747/0.04 3.2 CPU测试 sysbench CPU测试使用64位整数,测试计算素数直到某个最大值所需要的时间 shell> sysbench --test=cpu --cpu-max-prime=2000 run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignored Primer numbers limit: 2000 Threads started! Test execution summary: total time: 2.2452s total number of events: 10000 total time taken by event execution: 2.2347s per-request statistics: min: 0.20ms avg: 0.22ms max: 3.35ms approx. 95 percentile: 0.27ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 2.2347/0.00 补充: 查看CPU信息方法 查看物理cpu个数 grep "physical id" /proc/cpuinfo | sort -u | wc -l 查看核心数量 grep "core id" /proc/cpuinfo | sort -u | wc -l 查看线程数量 grep "processor" /proc/cpuinfo | sort -u | wc -l 在sysbench的测试中,--num-threads取值为"线程数量"即可 3.3 线程(thread)测试 测试线程调度器的性能。对于高负载情况下测试线程调度器的行为非常有用 shell> sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 64 Random number generator seed is 0 and will be ignored Threads started! Test execution summary: total time: 1.9581s total number of events: 10000 total time taken by event execution: 124.8938s per-request statistics: min: 0.05ms avg: 12.49ms max: 151.15ms approx. 95 percentile: 50.83ms Threads fairness: events (avg/stddev): 156.2500/14.48 execution time (avg/stddev): 1.9515/0.00 3.4 文件IO性能测试 生成需要的测试文件,文件总大小5G,16个并发线程。执行完后会在当前目录下生成一堆小文件。 shell> sysbench --test=fileio --num-threads=16 --file-total-size=1G prepare 执行测试,指定随机读写模式 指定读写模式: seqwr 顺序写入 seqrewr 顺序重写 seqrd 顺序读取 rndrd 随机读取 rndwr 随机写入 rndrw 混合随机读/写 shell> sysbench --test=fileio --num-threads=16 --init-rng=on --file-total-size=5G --file-test-mode=rndrw run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 16 Random number generator seed is 0 and will be ignored Threads started! Operations performed: 5999 reads, 4001 writes, 12800 Other = 22800 Total Read 93.734Mb Written 62.516Mb Total transferred 156.25Mb (9.2561Mb/sec) ##吞吐量 592.39 Requests/sec executed Test execution summary: total time: 16.8808s total number of events: 10000 total time taken by event execution: 176.1816s per-request statistics: min: 0.01ms avg: 17.62ms max: 416.73ms approx. 95 percentile: 104.82ms Threads fairness: events (avg/stddev): 625.0000/62.39 execution time (avg/stddev): 11.0114/0.67 清除测试文件 shell> sysbench --test=fileio --num-threads=16 --file-total-size=5G cleanup 3.5 互斥锁(Mutex)测试 测试互斥锁的性能,方式是模拟所有线程在同一时刻并发运行,并都短暂请求互斥锁。 shell> sysbench --test=mutex --num-threads=16 --mutex-num=1024 --mutex-locks=10000 --mutex-loops=5000 run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 16 Random number generator seed is 0 and will be ignored Threads started! Test execution summary: total time: 0.0135s total number of events: 16 total time taken by event execution: 0.0411s per-request statistics: min: 0.70ms avg: 2.57ms max: 9.19ms approx. 95 percentile: 9.16ms Threads fairness: events (avg/stddev): 1.0000/0.00 execution time (avg/stddev): 0.0026/0.00 3.6 内存测试 内存测试测试了内存的连续读写性能。 shell> sysbench --test=memory --num-threads=16 --memory-block-size=8192 --memory-total-size=1G run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 16 Random number generator seed is 0 and will be ignored Threads started! Operations performed: 131072 (381158.38 ops/sec) 1024.00 MB transferred (2977.80 MB/sec) ##吞吐量 Test execution summary: total time: 0.3439s total number of events: 131072 total time taken by event execution: 3.9915s per-request statistics: min: 0.00ms avg: 0.03ms max: 51.02ms approx. 95 percentile: 0.00ms ##大约95%的时间分布 Threads fairness: events (avg/stddev): 8192.0000/1166.77 execution time (avg/stddev): 0.2495/0.02 --测试mysql ./sysbench --test=oltp --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --db-driver=mysql prepare ./sysbench --max-requests=0 --max-time=60 --num-threads=16 --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --db-driver=mysql --oltp-reconnect-mode=session --oltp-test-mode=simple run ./sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --db-driver=mysql cleanup Done. OLTP test statistics: queries performed: read: 694551 write: 0 other: 0 total: 694551 transactions: 694551 (11575.10 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 694551 (11575.10 per sec.) other operations: 0 (0.00 per sec.) General statistics: total time: 60.0039s total number of events: 694551 total time taken by event execution: 958.2249 response time: min: 0.06ms avg: 1.38ms max: 731.49ms approx. 95 percentile: 20.89ms Threads fairness: events (avg/stddev): 43409.4375/273.26 execution time (avg/stddev): 59.8891/0.02 --测试postgresql ./sysbench --test=oltp --oltp-table-size=10000 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=123456 --pgsql-db=postgres --db-driver=pgsql prepare ./sysbench --max-requests=0 --max-time=60 --num-threads=16 --test=oltp --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=123456 --pgsql-db=postgres --oltp-test-mode=simple --oltp-reconnect-mode=session run ./sysbench --test=oltp --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=123456 --pgsql-db=postgres cleanup Done. OLTP test statistics: queries performed: read: 793367 write: 0 other: 0 total: 793367 transactions: 793367 (13222.31 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 793367 (13222.31 per sec.) other operations: 0 (0.00 per sec.) General statistics: total time: 60.0021s total number of events: 793367 total time taken by event execution: 958.3070 response time: min: 0.05ms avg: 1.21ms max: 809.49ms approx. 95 percentile: 0.31ms Threads fairness: events (avg/stddev): 49585.4375/266.92 execution time (avg/stddev): 59.8942/0.01 综上可知postgresql的tps(11575)略大于mysql的tps(13222)