測試readsysbench測試MySQL伺服器性能（cpu，io，記憶體，mysql等）

在本篇文章中,我們主要介紹測試read的内容,自我感覺有個不錯的建議和大家分享下

-- Sysbench測試

Sysbench的安裝請參考http://blog.csdn.net/mchdba/article/details/8951289

1 CPU測試

sysbench采用尋覓最大素數的方法來測試CPU的性能

   [root@xx sysbench-0.4.12]# sysbench --test=cpu --cpu-max-prime=2000 run

   sysbench 0.4.12:  multi-threaded system evaluation benchmark

   Running the test with following options:

Number of threads: 1

   Doing CPU performance benchmark

   Threads started!

WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.000000

WARNING: Percentile statistics will be inaccurate

Done.

   Maximum prime number checked in CPU test: 2000

   Test execution summary:

   total time:                          1.5034s

   total number of events:              10000

   total time taken by event execution: 1.4998

   per-request statistics:

        min:                                  0.00ms

        avg:                                  0.15ms

        max:                                  0.57ms

        approx.  95 percentile:               0.31ms

   Threads fairness:

   events (avg/stddev):           10000.0000/0.00

   execution time (avg/stddev):   1.4998/0.00

2 線程測試

sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run

[root@xx sysbench-0.4.12]# sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Number of threads: 64

   Doing thread subsystem performance test

Thread yields per test: 100 Locks used: 2

Threads started!

Test execution summary:

   total time:                          2.0189s

   total time taken by event execution: 128.2852

        avg:                                 12.83ms

        max:                                108.17ms

        approx.  95 percentile:              42.09ms

   events (avg/stddev):           156.2500/12.16

   execution time (avg/stddev):   2.0045/0.00

3 檔案IO性能測試

首先生成須要的測試檔案，檔案總大小1000M，16個并發線程，随機讀寫模式。執行完後會在目前目錄下生成一堆小檔案。

3.1 預備測試檔案：sysbench --test=fileio --num-threads=16 --file-total-size=1000M --file-test-mode=rndrw prepare

[root@xx sysbench-0.4.12]# sysbench --test=fileio --num-threads=16 --file-total-size=1000M --file-test-mode=rndrw prepare

   128 files, 8000Kb each, 1000Mb total

Creating files for the test...

3.2 執行測試

sysbench --test=fileio --num-threads=16 --file-total-size=1000M --file-test-mode=rndrw run

[root@xx sysbench-0.4.12]# sysbench --test=fileio --num-threads=16 --file-total-size=1000M --file-test-mode=rndrw run

Number of threads: 16

   Extra file open flags: 0

128 files, 7.8125Mb each

1000Mb total file size

Block size 16Kb

Number of random requests for random IO: 10000

Read/Write ratio for combined random IO test: 1.50

Periodic FSYNC enabled, calling fsync() each 100 requests.

Calling fsync() at the end of test, Enabled.

Using synchronous I/O mode

Doing random r/w test

   Operations performed:  6007 Read, 4005 Write, 12675 Other = 22687 Total

Read 93.859Mb  Written 62.578Mb  Total transferred 156.44Mb  (214.4Mb/sec)  看到這裡發現write很高，我這裡是SSD，普通機器磁盤可能就沒有這麼高了。

13721.48 Requests/sec executed

   total time:                          0.7297s

   total number of events:              10012

   total time taken by event execution: 1.3667

        avg:                                  0.14ms

        max:                                 20.50ms

        approx.  95 percentile:               0.41ms

   events (avg/stddev):           625.7500/159.20

   execution time (avg/stddev):   0.0854/0.02

3.3 清算測試生成的臨時檔案

sysbench --test=fileio --num-threads=16 --file-total-size=1000M --file-test-mode=rndrw cleanup

   Removing test files...

4. mutex測試

sysbench --test=mutex --num-threads=16 \--mutex-num=1024 --mutex-locks=10000 --mutex-loops=5000 run

[root@xx sysbench-0.4.12]# sysbench --test=mutex --num-threads=16 \--mutex-num=1024 --mutex-locks=10000 --mutex-loops=5000 run

   Doing mutex performance test

   total time:                          0.1167s

   total number of events:              16

   total time taken by event execution: 1.7472

        min:                                 96.94ms

        avg:                                109.20ms

        max:                                114.26ms

        approx.  95 percentile:             114.19ms

   events (avg/stddev):           1.0000/0.00

   execution time (avg/stddev):   0.1092/0.01

5，記憶體測試

sysbench --test=memory --num-threads=512 --memory-block-size=262144 --memory-total-size=32G run

[root@xx sysbench-0.4.12]#  sysbench --test=memory --num-threads=512 --memory-block-size=262144 --memory-total-size=32G run

Number of threads: 512

   Doing memory operations speed test

Memory block size: 256K

   Memory transfer size: 32768M

   Memory operations type: write

Memory scope type: global

(last message repeated 1 times)

   Operations performed: 131072 (60730.95 ops/sec)

   32768.00 MB transferred (15182.74 MB/sec)

   total time:                          2.1582s

   total number of events:              131072

   total time taken by event execution: 643.2354

        avg:                                  4.91ms

        max:                               1173.07ms

        approx.  95 percentile:               0.42ms

   events (avg/stddev):           256.0000/84.51

   execution time (avg/stddev):   1.2563/0.32

6 MySQL資料庫測試 select

首先須要創立預設的test資料庫,或者應用–mysql-db指定一個已存在的資料庫生成測試資料，引擎為innodb,表大小為30000000條記載

6.1 預備資料

--oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp} [complex]

--oltp-nontrx-mode=STRING       mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test  --oltp-table-size=300000000 --oltp-table-name=t1 --oltp-nontrx-mode=insert --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock prepare

報錯如下：

   FATAL: no database driver specified

FATAL: failed to initialize database driver!

   test庫沒有建立，去create database

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

   Creating table 't1'...

Creating 30000000 records in table 't1'...

   real    13m49.102s

user    0m11.982s

sys     0m0.646s

6.2 執行測試

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test  --oltp-table-size=300000000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

Running the test with following options:

   Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 10000

   OLTP test statistics:

   queries performed:

       read:                            140000

       write:                           50000

       other:                           20000

       total:                           210000

   transactions:                        10000  (350.28 per sec.)

   deadlocks:                           0      (0.00 per sec.)

   read/write requests:                 190000 (6655.38 per sec.)

   other operations:                    20000  (700.57 per sec.)

   total time:                          28.5483s

   total time taken by event execution: 28.4897

        min:                                  1.80ms

        avg:                                  2.85ms

        max:                                 47.35ms

        approx.  95 percentile:               5.95ms

   execution time (avg/stddev):   28.4897/0.00

real    0m28.646s

user    0m2.270s

sys     0m1.516s

6.3 clean test data

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test  --oltp-table-size=30000000 --oltp-table-name=t1   --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock clearup

7 MySQL資料庫測試 insert 19304W

7.1 執行insert測試，資料預備參考6.1步調

--oltp-nontrx-mode=insert

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=insert --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

[root@xx bin]# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=insert --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

   transactions:                        10000  (222.06 per sec.)

read/write requests:                 190000 (4219.22 per sec.)

   other operations:                    20000  (444.13 per sec.)

   total time:                          45.0321s

   total time taken by event execution: 44.9398

        min:                                  1.96ms

        avg:                                  4.49ms

        max:                                 37.26ms

        approx.  95 percentile:               6.96ms

   execution time (avg/stddev):   44.9398/0.00

real    0m45.063s

user    0m3.279s

sys     0m2.295s

7.2 --oltp-nontrx-mode=update_key 修改測試 帶index

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=update_key --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

[root@xx bin]# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=update_key --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

   transactions:                        10000  (304.16 per sec.)

   read/write requests:                 190000 (5778.95 per sec.)

   other operations:                    20000  (608.31 per sec.)

   total time:                          32.8780s

   total time taken by event execution: 32.8053

        min:                                  1.89ms

        avg:                                  3.28ms

        max:                                 28.03ms

        approx.  95 percentile:               6.06ms

   execution time (avg/stddev):   32.8053/0.00

real    0m32.909s

user    0m2.628s

sys     0m1.683s

7.3 --oltp-nontrx-mode=delete 删除測試

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=delete --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

[root@xx bin]# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=delete --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

   transactions:                        10000  (330.13 per sec.)

   read/write requests:                 190000 (6272.53 per sec.)

   other operations:                    20000  (660.27 per sec.)

   total time:                          30.2908s

   total time taken by event execution: 30.2212

        min:                                  1.84ms

        avg:                                  3.02ms

        max:                                 13.14ms

        approx.  95 percentile:               6.02ms

   execution time (avg/stddev):   30.2212/0.00

real    0m30.328s

user    0m2.416s

sys     0m1.658s

7.4 --oltp-nontrx-mode=update_nokey 修改測試 No Index

time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=update_nokey --mysql-db=test  --oltp-table-size=193040000 --max-requests --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

[root@xx bin]# time sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --oltp-nontrx-mode=update_nokey --mysql-db=test  --oltp-table-size=193040000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run

   transactions:                        10000  (319.11 per sec.)

   read/write requests:                 190000 (6063.01 per sec.)

   other operations:                    20000  (638.21 per sec.)

   total time:                          31.3376s

   total time taken by event execution: 31.2650

        min:                                  1.83ms

        avg:                                  3.13ms

        max:                                 13.44ms

        approx.  95 percentile:               6.11ms

   execution time (avg/stddev):   31.2650/0.00

real    0m31.367s

user    0m2.422s

sys     0m1.680s

7.4 總結

--max-requests=N           limit for total number of requests [10000]

如果這裡不指定--max-requests，預設為10000，就是執行1W次操作就結束了，如果要做壓力測試的話，就須要手動設定此值。

--num-threads=N            number of threads to use [1] (clients to access mysql db)

這裡指的是有多少個mysql clents來拜訪mysql伺服器，用show full processlist就可以看到大概的記載數。

7.5 接下來重新壓力測試，設定2000W request測試。

錄入測試資料 2000W

time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --num-threads=500 --max-requests=20000000  --oltp-nontrx-mode=insert --mysql-db=test  --oltp-table-size=20000000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock prepare

Creating table 't1'...

Creating 20000000 records in table 't1'...

   real    8m46.287s

user    0m8.048s

sys     0m0.428s

7.6開始測試 純粹寫

nohup time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --num-threads=500 --max-requests=20000000  --oltp-nontrx-mode=insert --mysql-db=test  --oltp-table-size=20000000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run >3.log &

time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --num-threads=500 --max-requests=20000000  --oltp-nontrx-mode=insert --mysql-db=test  --oltp-table-size=20000000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run  1> 2.log

       read:                            0

       write:                           20007732

       other:                           0

       total:                           20007732

   transactions:                        20007732 (8556.00 per sec.)

   read/write requests:                 20007732 (8556.00 per sec.)

   other operations:                    0      (0.00 per sec.)

   total time:                          2338.4451s

   total number of events:              20007732

   total time taken by event execution: 1169095.8829

        min:                                  0.35ms

        avg:                                 58.43ms

        max:                                317.35ms

        approx.  95 percentile:              83.54ms

   events (avg/stddev):           40015.4640/13.47

   execution time (avg/stddev):   2338.1918/0.02

   900.69user 667.19system 39:07.55elapsed 66%CPU (0avgtext+0avgdata 128176maxresident)k

0inputs+16outputs (0major+8251minor)pagefaults 0swaps

7.7 混合讀寫測試

nohup time sysbench --test=oltp --oltp-test-mode=complex --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --num-threads=500 --max-requests=20000000  --oltp-nontrx-mode=select --mysql-db=test  --oltp-table-size=20000000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock run >4.log &

Number of threads: 500

Maximum number of requests for OLTP test is limited to 20000000

       read:                            280001652

       write:                           100000590

       other:                           40000236

       total:                           420002478

   transactions:                        20000118 (1150.48 per sec.)

   read/write requests:                 380002242 (21859.10 per sec.)

   other operations:                    40000236 (2300.96 per sec.)

   total time:                          17384.1703s

   total number of events:              20000118

   total time taken by event execution: 8691857.1553

        min:                                 28.97ms

        avg:                                434.59ms

        max:                              14634.16ms

        approx.  95 percentile:             856.09ms

   events (avg/stddev):           40000.2360/119.14

   execution time (avg/stddev):   17383.7143/0.06

   5142.95user 4422.48system 4:49:50elapsed 55%CPU (0avgtext+0avgdata 256224maxresident)k

2440inputs+16outputs (15major+16290minor)pagefaults 0swaps

avg:    434.59ms

看到這裡，大leader說這個數值偏高了，在game領域，最好<10ms，并且他猜測我是local測試的，local500個線程或許會

對db server有一些壓力的，建議我remote測試拜訪測試下看

7.8.1 混合讀寫測試 遠端讀寫資料預備，遠端跑背景程序預備資料。

nohup time sysbench --test=oltp --oltp-test-mode=nontrx --mysql-table-engine=innodb --mysql-host=eanprdnucmydbc048db01.eao.abn-iad.ea.com --mysql-user=chunman --mysql-password=hRhsYzbm  --db-driver=mysql --num-threads=500 --max-requests=20000000  --oltp-nontrx-mode=select --mysql-db=test  --oltp-table-size=20000000 --oltp-table-name=t1  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock prepare > 6.log &

8.48user 1.96system 40:23.30elapsed 0%CPU (0avgtext+0avgdata 12224maxresident)k

0inputs+8outputs (0major+854minor)pagefaults 0swaps

7.8.2 混合讀寫測試 遠端讀寫

nohup time sysbench --test=oltp --oltp-test-mode=complex --mysql-table-engine=innodb --mysql-host=eanprdnucmydbc048db01.eao.abn-iad.ea.com --mysql-user=chunman --mysql-password=hRhsYzbm --db-driver=mysql --mysql-db=test --oltp-table-name=t1  --num-threads=500 --max-requests=20000000  --oltp-nontrx-mode=select --oltp-table-size=20000000  --mysql-socket=/opt/mysql/product/mysql/mysql3306.sock  run >5.log &

ALERT: Error: failed to determine table 't1' type!

ALERT: MySQL error:

FATAL: failed to get database capabilities!

0.00user 0.00system 0:00.01elapsed 64%CPU (0avgtext+0avgdata 9856maxresident)k

0inputs+8outputs (0major+706minor)pagefaults 0swaps