Sysbench oltp (2)

adminLinux, MariaDB, Mysql, Ubuntu

Loading

這邊繼續探討關於 sysbench OLTP 的參數使用,關於 sysbench 一般設定 https://benjr.tw/8715 ,與 sysbench 如何 測試 oltp 資料庫 https://benjr.tw/95251 請自行參考.
測試環境 Ubuntu 14.04

-–max-time 與 -–max-requests

測試時間可以指定為固定交易量或是固定時間兩種.

  1. -–max-time
    這參數是以固定時間為測試單位, -–max-time=60 表示要測試 60 秒.但是要注意的是sysbench 預設的 -–max-requests (Maximum number of requests for OLTP test ) 是 10000 ,如果這個先跑完可能會等不到預期的測試時間就先結束了.

    root@ubuntu:~# sysbench --test=oltp --db-driver=mysql --mysql-db=sbtest --mysql-user=sbtester --mysql-password=sbpwd --max-time=60 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

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
Threads started!
Time limit exceeded, exiting...
Done.

OLTP test statistics:
    queries performed:
        read:                            127792
        write:                           45640
        other:                           18256
        total:                           191688
    transactions:                        9128   (152.13 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 173432 (2890.45 per sec.)
    other operations:                    18256  (304.26 per sec.)

Test execution summary:
    total time:                          60.0016s
    total number of events:              9128
    total time taken by event execution: 59.9340
    per-request statistics:
         min:                                  2.51ms
         avg:                                  6.57ms
         max:                                 81.82ms
         approx.  95 percentile:              10.87ms

Threads fairness:
    events (avg/stddev):           9128.0000/0.00
    execution time (avg/stddev):   59.9340/0.00

    可以看到 total time: 60.0016s 就是剛剛設定的 -–max-time=60 (total number of events: 9128 沒有超過預設的 10000 ,所以沒有提前停止)

  2. -–max-requests
    這參數是以固定交易量為測試單位, -–max-requests=10000 表示要測試 10,000 個交易.

    root@ubuntu:~# sysbench --test=oltp --db-driver=mysql --mysql-db=sbtest --mysql-user=sbtester --mysql-password=sbpwd --max-requests=10000 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

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
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (134.90 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (2563.19 per sec.)
    other operations:                    20000  (269.81 per sec.)

Test execution summary:
    total time:                          74.1263s
    total number of events:              10000
    total time taken by event execution: 74.0467
    per-request statistics:
         min:                                  2.54ms
         avg:                                  7.40ms
         max:                                 53.44ms
         approx.  95 percentile:              12.19ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   74.0467/0.00

    可以看到 total number of events: 10000 就是剛剛設定的 -–max-requests=10000

-–num-threads

sysbench 預設只用一個 thread ,如果要讓資料庫可以發揮較大的效能可以透過修改 -–num-threads ,讓 sysbench 同時多跑幾個 threads.

root@ubuntu:~# sysbench --test=oltp --db-driver=mysql --mysql-db=sbtest --mysql-user=sbtester --mysql-password=sbpwd --max-requests=20000 --num-threads=16 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

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 20000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            285222
        write:                           100752
        other:                           40373
        total:                           426347
    transactions:                        20000  (512.14 per sec.)
    deadlocks:                           373    (9.55 per sec.)
    read/write requests:                 385974 (9883.63 per sec.)
    other operations:                    40373  (1033.83 per sec.)

Test execution summary:
    total time:                          39.0518s
    total number of events:              20000
    total time taken by event execution: 624.4808
    per-request statistics:
         min:                                  3.93ms
         avg:                                 31.22ms
         max:                                381.56ms
         approx.  95 percentile:              58.36ms

Threads fairness:
    events (avg/stddev):           1250.0000/76.01
    execution time (avg/stddev):   39.0300/0.01

執行結果可以看到 Number of threads: 16 , transactions 效能 從 134.90 per sec 提升到 512.14 per sec

-–oltp-test-mode, –oltp-nontrx-mode

怎麼知道剛剛的 SQL 做了哪一些測試, sysbench -–oltp-test-mode 參數有提供三種模式 simple (simple), complex (advanced transactional), nontrx (non-transactional) 可共選擇 (預設為 complex) .

  1. simple (simple) 模式
    Simple 模式使用下列的 SQL 語法來進行測試. SELECT 是在資料庫做搜尋的動作,這測試單純只做讀取.

    SELECT c FROM sbtest WHERE id=N

    N 是一個亂數.

    root@ubuntu:~# sysbench --test=oltp --db-driver=mysql --mysql-db=sbtest --mysql-user=sbtester --mysql-password=sbpwd --max-requests=20000 --num-threads=16 --oltp-test-mode=simple run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running simple 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 20000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            20003
        write:                           0
        other:                           0
        total:                           20003
    transactions:                        20003  (24565.20 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 20003  (24565.20 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          0.8143s
    total number of events:              20003
    total time taken by event execution: 12.9186
    per-request statistics:
         min:                                  0.10ms
         avg:                                  0.65ms
         max:                                 48.39ms
         approx.  95 percentile:               1.18ms

Threads fairness:
    events (avg/stddev):           1250.1875/125.18
    execution time (avg/stddev):   0.8074/0.00

    可以看到 transactions: 從預設的 complex (512.14 per sec) 爆升到 simple (24565.20 per sec)

  2. complex (advanced transactional) 模式
    Complex (Advanced Transactional) 模式使用下列的 SQL 語法來進行測試. 除了 SELECT 資料庫搜尋動作外,還增加了 UPDATE (資料更新) 與 DELETE (資料刪除) , INSERT (新增資料) .

    SELECT c FROM sbtest WHERE id=N
SELECT c FROM sbtest WHERE id BETWEEN N AND M
SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M
SELECT c FROM sbtest WHERE id between N and M ORDER BY c
SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c
UPDATE sbtest SET k=k+1 WHERE id=N
UPDATE sbtest SET c=N WHERE id=M
DELETE FROM sbtest WHERE id=N
INSERT INTO sbtest VALUES (...)

    這是預設的選項,剛剛有已經測試過了.進行測試前 sysbench 建議要先做 cleanup / prepare 再進行測試.

  3. nontrx (non-transactional) 模式
    剛剛的 simple 只提供 SELECT (Read) 的測試方式,complex 提供 SELECT , UPDATE , DELETE , INSERT (READ, WRITE , OTHER) 等複雜的測試方式,nontrx (non-transactional) 則可以讓你選擇 SQL 語法 SELECT , UPDATE_KEY , UPDATE_NOKEY , INSERT , DELETE (預設為 SELECT).

    • SELECT 
      SELECT pad FROM sbtest WHERE id=N
    • UPDATE_KEY 
      UPDATE sbtest SET k=k+1 WHERE id=N
    • UPDATE_NOKEY 
      UPDATE sbtest SET c=N WHERE id=M
    • INSERT 
      INSERT INTO sbtest (k, c, pad) VALUES(N, M, S)
    • DELETE 
      DELETE FROM sbtest WHERE id=N

    nontrx 在使用不同的模式進行測試前 sysbench 建議要先做 cleanup / prepare 再進行測試.並使用 –oltp-nontrx-mode 選擇要測試的模式 SELECT , UPDATE_KEY , UPDATE_NOKEY , INSERT , DELETE (預設為 SELECT).

    root@ubuntu:~# sysbench --test=oltp --db-driver=mysql --mysql-db=sbtest --mysql-user=sbtester --mysql-password=sbpwd --max-requests=20000 --num-threads=16 --oltp-test-mode=nontrx --oltp-nontrx-mode=select run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running non-transactional 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 20000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            20015
        write:                           0
        other:                           0
        total:                           20015
    transactions:                        20015  (25957.25 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 20015  (25957.25 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          0.7711s
    total number of events:              20015
    total time taken by event execution: 12.2528
    per-request statistics:
         min:                                  0.09ms
         avg:                                  0.61ms
         max:                                108.39ms
         approx.  95 percentile:               1.27ms

Threads fairness:
    events (avg/stddev):           1250.9375/454.60
    execution time (avg/stddev):   0.7658/0.00

    可以看到 transactions: 結果跟 simple (24565.20 per sec) 差不多 , nontrx (25957.25 per sec.) , complex (512.14 per sec)

沒有解決問題,試試搜尋本站其他內容

One thought on “Sysbench oltp (2)”

  1. 自動引用通知: Sysbench – oltp (1) – Benjr.tw

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。