4,973 瀏覽數

Sysbench oltp (2)

這邊繼續探討關於 sysbench OLTP 的參數使用,關於 sysbench 一般設定 http://benjr.tw/8715 ,與 sysbench 如何 測試 oltp 資料庫 http://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 Reply to “Sysbench oltp (2)”

  1. 自動參照通知: Sysbench – oltp (1) – Benjr.tw

發表迴響