MySQL Benchmark tool-sysbench installation test

Environment: centos6.5,mysql5.7.22
1. Download Sysbench
2. Installation

tar -zxvf sysbench-1.1.0.tar.gzyum install automake libtool –y   #安装依赖包cd sysbench-0.4.12.14./autogen.sh./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib --with-mysqlexport LD_LIBRARY_PATH=/usr/local/mysql/include #这里换成机器中mysql路径下的includemakemake installsysbench --version  # 检验安装是否成功

3. Introduction
Sysbench is a cross-platform benchmarking tool that supports multiple threads and supports multiple databases, mainly including the following tests:

• CPU Performance
• Disk IO Performance
• Scheduler performance
• memory allocation and transfer speed
• POSIX threading Performance
• Database performance (basic OLTP-based testing)
  4.sysbench syntax sysbench [options] [testname] [command]
• Command sysbench the commands to execute, including Prepare,run,cleanup.
• TestName specifying the tests to be performed

• Options

  • --mysql-host MySQL Server host name
  • --mysql-port MySQL server port, default is 3306
  • --mysql-user MySQL user name
  • --mysql-password mysql Password
  • --oltp-test-mode execution mode, default to Complex
  • --oltp-tables-size the size of the test table
  • --oltp-tables-count Number of test tables
  • --threads Concurrent connections for clients
  • --time Time to test execution
    5. Test examples
• CPU Test
  • --cpu-max-prime=n upper limit for primes generator [10000]

    sysbench    --cpu-max-prime=20000 --report-interval=10

• FileIO test
  * 4 threads, Random read/write, output results per 10s

  [[email protected] sysbench-master]# sysbench fileio helpsysbench 1.1.0 (using bundled Luajit 2.1.0-beta3) FileIO op Tions:--file-num=n number of files to create [+] #需要创建的文件数, default is 128--file-block-size=n block s The total size of the file created by ize to use the all IO operations [16384]--file-total-size=size-size of files to create [2G]--file-t Est-mode=string Test Mode {SEQWR, SEQREWR, Seqrd, RNDRD, Rndwr, RNDRW} #读写方式--file-io-mode=string file Operat Ions mode {sync,async,mmap} [sync] # file operation mode {sync (synchronous), async (async), Fastmmap (Fast map map), Slowmmap (slow map map)}. The default is sync--file-async-backlog=n number of asynchronous operatons to queue per thread [+]--file-extra-flags=[list. ..] List of additional flags to use to open files {sync,dsync,direct} [] #使用额外的标志来打开文件 {sync,dsync,direct}. Default is empty--file-fsync-freq=n do Fsync () after this number of requests (0-don ' t use Fsync ()) [100]--file-fsync-all[=o N|off] do Fsync () after each write operation [off]--file-fSync-end[=on|off] do Fsync () at the end of test [on]--file-fsync-mode=string which method to use for Synchronizati On {fsync, fdatasync} [Fsync] #使用哪种方法进行同步 {fsync, fdatasync}. The default is fsync--file-merged-requests=n merge at more this number of IO requests if possible (0-don ' t merge) [0] #如果可以, merging the most Number of IO requests (0– means no merging). The default is 0--file-rw-ratio=n reads/writes ratio for combined test [1.5] #测试时的读写比例. Default is 1.5

[[email protected] sysbench-master]# sysbench--threads=4--file-test-mode=rndwr--report-interval=10 FileIO Prepare #准备阶段 [[email protected] sysbench-master]# sysbench--threads=4--time=60--report-interval=10-- FILE-TEST-MODE=RNDRW FileIO runsysbench 1.1.0 (using bundled Luajit 2.1.0-beta3) Running the test with following Options:nu Mber of Threads:4report Intermediate results every second (s) Initializing random number generator from current TIMEEXTR  A File Open flags: (none) files, 16MiB each2gib total file sizeblock size 16KiBNumber of IO requests:0read/write ratio For combined random IO test:1.50periodic FSYNC enabled, calling FSYNC () requests. Calling Fsync () at the end of test, enabled.using synchronous I/O modedoing random r/w testinitializing worker threads...t Hreads started! [10s] reads:3.65 mib/s writes:2.44 mib/s fsyncs:494.49/s Latency (ms,95%): 30.265[20s] reads:3.00 mib/s writes:2. XX mib/s fsyncs:412.60/s Latency (ms,95%): 34.954[30s] Reads:3.28 mib/s writes:2.19 mib/s fsyncs:444.19/s Latency (ms,95%): 35.589[40s] reads:3.19 mib/s writes:2.13 MiB/s FSYNCS:434.41/S Latency (ms,95%): 38.247[50s] reads:3.00 mib/s writes:2.00 mib/s fsyncs:410.90/s Latency (ms,95%): 3 7.565[60s] reads:3.09 mib/s writes:2.06 mib/s fsyncs:419.19/s Latency (ms,95%): 35.589throughput:read:iops=        204.22 3.19 mib/s (3.35 MB/s) write:iops=136.15 2.13 mib/s (2.23 MB/s) fsync:iops=442.70latency (ms):                                min:0.00 avg:5.14 Max: 310.70 95th percentile:35.59 sum:239996.51[ [email protected] sysbench-master]# sysbench--threads=4--time=60--report-interval=10--FILE-TEST-MODE=RNDRW FileIO cleanup #清理文件sysbench 1.1.0 (using bundled Luajit 2.1.0-beta3) removing test files ...

• Memory Test
  • Test 4 threads, 60 seconds, output report information every 10 seconds, total memory size 100G, random access mode

[[email protected] sysbench-master]# sysbench--threads=4--time=60--report-interval=10--memory-total-size= 100G--memory-access-mode=rnd Memory Runsysbench 1.1.0 (using bundled Luajit 2.1.0-beta3) Running the test with following O  Ptions:number of Threads:4report Intermediate results every second (s) Initializing random number generator from current Timerunning memory Speed test with the following Options:block Size:1kib total Size:102400mib operation:write SCO Pe:globalinitializing Worker Threads ... Threads started! [10s] 671.96 mib/sec[20s] 672.65 mib/sec[30s] 667.18 mib/sec[40s] 675.73 mib/sec[50s] 676.82 mib/sectotal Operat                      ions:41341475 (689021.00 per second) 40372.53 MiB transferred (672.87 mib/sec) throughput:events/s (EPS): 689021.0010 time elapsed:60.0003s Total number of Events:41341475laten                          Cy (MS): min:0.00 avg:          0.01 max:20.25 95th percentile:0.00    Sum:218390.97threads fairness:events (Avg/stddev): 10335368.7500/59009.67 Execution Time (Avg/stddev): 54.5977/0.14

• mysql test
  • pressure test example, 4 tables, each table 10W records, pressure measurement 120 seconds

Sysbench--db-driver=mysql--time=120--threads=4--report-interval=10--mysql-host=localhost--mysql-port=2433-- Mysql-socket=/data/mysqldata/mysql.sock--MYSQL-USER=7ROADDBA--mysql-password=y%[email protected]_q1a-- Mysql-db=inc--tables=4--table-size=100000 oltp_read_write Prepare # #准备sysbench--db-driver=mysql--time=120-- threads=4--report-interval=10--mysql-host=localhost--mysql-port=3306--mysql-socket=/data/mysqldata/mysql.sock- -MYSQL-USER=7ROADDBA--mysql-password=y%[email protected]_q1a--mysql-db=inc--tables=4--table-size=100000 Oltp_read_write Run # #测试sysbench 1.1.0 (using bundled Luajit 2.1.0-beta3) Running the test with following Options:number O F Threads:4report Intermediate results every second (s) Initializing random number generator from current Timeinitializi Ng worker Threads ... Threads started! [10s] thds:4 tps:111.37 qps:2233.72 (r/w/o:1564.69/445.88/223.14) Lat (ms,95%): 101.13 err/s: 0.00 RECONN/S: 0.00[2 0s] thds:4 tps:197.50 qps:3950.22 (r/w/o:2764.42/790.80/395.00) Lat (ms,95%): 44.98 err/s: 0.00 RECONN/S: 0.00[30s] thds:4 tps:211.10 qps:4222.0 6 (r/w/o:2955.87/843.99/422.20) Lat (ms,95%): 44.17 err/s: 0.00 RECONN/S: 0.00[40s] thds:4 tps:213.70 qps:4272.79 (r /w/o: 2991.39/854.00/427.40) Lat (ms,95%): 43.39 err/s: 0.00 RECONN/S: 0.00[50s] thds:4 tps:211.80 qps:4239.27 (r/w/o : 2967.15/848.41/423.71) Lat (ms,95%): 43.39 err/s: 0.10 reconn/s: 0.00[60s] thds:4 tps:223.10 qps:4459.15 (r/w/o:31 21.36/891.59/446.19) Lat (ms,95%): 41.85 err/s: 0.00 RECONN/S: 0.00[70s] thds:4 tps:190.40 qps:3811.24 (r/w/o:2667.7 3/762.71/380.80) Lat (ms,95%): 45.79 err/s: 0.00 RECONN/S: 0.00[80s] thds:4 tps:183.00 qps:3660.05 (r/w/o:2561.97/73 2.09/366.00) Lat (ms,95%): 43.39 err/s: 0.00 RECONN/S: 0.00[90s] thds:4 tps:211.60 qps:4226.63 (r/w/o:2958.32/845.11 /423.20) Lat (ms,95%): 44.17 err/s: 0.00 RECONN/S: 0.00[100s] thds:4 tps:213.30 qps:4272.92 (r/w/o:2991.64/854.58/42 6.69) Lat (ms,95%): 39.65 err/s: 0.10 reconn/s: 0.00[110s] thds:4 tps:212.51 qps:4249.12 (r/w/o:2974.88/849.22/425.01) Lat (ms,95%): 41.10 E RR/S: 0.00 RECONN/S: 0.00[120s] thds:4 tps:183.00 qps:3660.19 (r/w/o:2561.80/732.40/366.00) Lat (ms,95%): 51.94 err/        s:0.00 reconn/s: 0.00SQL statistics:queries performed:read:330820 #总select数量 write:94513 # Total Update,insert,delete quantity other:47258 # #commi T,unlock Tables Quantity total:472591 transactions:23628 (196.88 pe  R sec.)                      #通常需要关注的数字 (TPS) queries:472591 (3937.94 per sec.) # Ignored errors: 2 (0.02 per Sec.) # Number of ignored errors reconnects:0 (0.00 per Sec.) THROUGHPUT:EVENTS/S (EPS): 196.8840 time elapsed:120.0098s Total Nu Mber of events:23628 #总的事件Number Latency (ms): min:6.03 avg:20.31                              max:847.12 95th percentile:44.98 sum: 479909.87Threads fairness:events (Avg/stddev): 5907.0000/60.54 Execution Time (avg/stdde V): 119.9775/0.00

MySQL Benchmark tool-sysbench installation test