Not the answer you need?
Register and ask your own question!

benchmark with sysbench0.5

zhaogongpozhaogongpo ContributorInactive User Role Beginner
when I did a test on percona server5.5.35 with sysbench0.5,
I found a lot of threads lasted for a long time:
root>show processlist;
+
+
+
+
+
+
+
+
+
+
+
+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+
+
+
+
+
+
+
+
+
+
+
+
| 6535 | test | 127.0.0.1:62338 | sbtest | Query | 76 | Updating | UPDATE sbtest2 SET k=k+1 WHERE id=2643761 | 0 | 1 | 1 |
| 6536 | test | 127.0.0.1:62341 | sbtest | Query | 76 | update | INSERT INTO sbtest12 (id, k, c, pad) VALUES (4388288, 729827, '11814348648-19862261356-49042326463-7 | 0 | 0 | 0 |
| 6537 | test | 127.0.0.1:62339 | sbtest | Query | 76 | Updating | UPDATE sbtest13 SET k=k+1 WHERE id=1353444 | 0 | 1 | 1 |
| 6538 | test | 127.0.0.1:62340 | sbtest | Query | 76 | update | INSERT INTO sbtest7 (id, k, c, pad) VALUES (2586203, 4762462, '03409980337-30835298673-87271499567-0 | 0 | 0 | 0 |
| 6539 | test | 127.0.0.1:62342 | sbtest | Query | 76 | updating | DELETE FROM sbtest5 WHERE id=2586943 | 0 | 1 | 1 |
| 6540 | test | 127.0.0.1:62343 | sbtest | Query | 76 | Updating | UPDATE sbtest10 SET k=k+1 WHERE id=2425865 | 0 | 1 | 1 |
| 6541 | test | 127.0.0.1:62345 | sbtest | Query | 76 | update | INSERT INTO sbtest13 (id, k, c, pad) VALUES (2020063, 3216557, '92518267352-69783502520-55744444753- | 0 | 0 | 0 |
| 6542 | test | 127.0.0.1:62344 | sbtest | Query | 76 | Updating | UPDATE sbtest7 SET k=k+1 WHERE id=3771602 | 0 | 1 | 1 |
| 6543 | test | 127.0.0.1:62347 | sbtest | Query | 76 | Updating | UPDATE sbtest13 SET k=k+1 WHERE id=4793540 | 0 | 1 | 1 |
| 6544 | test | 127.0.0.1:62348 | sbtest | Query | 76 | updating | DELETE FROM sbtest3 WHERE id=4068758 | 0 | 1 | 1 |
| 6545 | test | 127.0.0.1:62353 | sbtest | Query | 76 | Updating | UPDATE sbtest8 SET k=k+1 WHERE id=1162365 | 0 | 1 | 1 |
| 6546 | test | 127.0.0.1:62354 | sbtest | Query | 76 | updating | DELETE FROM sbtest9 WHERE id=1937572 | 0 | 1 | 1 |
| 6547 | test | 127.0.0.1:62349 | sbtest | Query | 76 | updating | DELETE FROM sbtest8 WHERE id=4261626 | 0 | 1 | 1 |
| 6548 | test | 127.0.0.1:62350 | sbtest | Query | 76 | update | INSERT INTO sbtest7 (id, k, c, pad) VALUES (1489188, 2729824, '43180915332-55361622607-08953597333-2 | 0 | 0 | 0 |
| 6549 | test | 127.0.0.1:62352 | sbtest | Query | 76 | update | INSERT INTO sbtest5 (id, k, c, pad) VALUES (729929, 2956969, '03814822121-48667714674-14509476873-54 | 0 | 0 | 0 |
| 6550 | test | 127.0.0.1:62356 | sbtest | Query | 76 | Updating | UPDATE sbtest9 SET k=k+1 WHERE id=2232675 | 0 | 1 | 1 |
| 6553 | root | 127.0.0.1:62424 | NULL | Query | 0 | NULL | show processlist | 0 | 0 | 0 |
+
+
+
+
+
+
+
+
+
+
+
+


The test script:
sysbench --db-driver=mysql --test=/work/soft/sysbench/sysbench/tests/db/oltp.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=test --mysql-password=test --mysql-db=sbtest \
--mysql-table-engine=innodb --mysql-engine-trx=yes\
--oltp-test-mode=complex\
--oltp-read-only=off\
--oltp-reconnect-mode=random\
--oltp-table-size=5000000 \
--max-time=60\
--max-requests=0\
--num-threads=16\
--report-interval=1 \
--rand-init=on --oltp_tables_count=16 --rand-type=uniform \
run


(CPU:16 cores
mem: 64G (BP=25G)
disk: SAS)

Comments

  • zhaogongpozhaogongpo Contributor Inactive User Role Beginner
    show engine innodb status\G
    *************************** 1. row ***************************
    Type: InnoDB
    Name:
    Status:
    =====================================
    150114 0:04:20 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 59 seconds
    BACKGROUND THREAD
    srv_master_thread loops: 50035 1_second, 48722 sleeps, 500 10_second, 693 background, 693 flush
    srv_master_thread log flush and writes: 8791
    SEMAPHORES
    OS WAIT ARRAY INFO: reservation count 9468536, signal count 4559544
    Mutex spin waits 20087652, rounds 360702642, OS waits 7915831
    RW-shared spins 1870354, rounds 36002599, OS waits 668619
    RW-excl spins 865970, rounds 27926822, OS waits 483203
    Spin rounds per wait: 17.96 mutex, 19.25 RW-shared, 32.25 RW-excl
    FILE I/O
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (read thread)
    I/O thread 7 state: waiting for completed aio requests (read thread)
    I/O thread 8 state: waiting for completed aio requests (read thread)
    I/O thread 9 state: waiting for completed aio requests (read thread)
    I/O thread 10 state: waiting for completed aio requests (write thread)
    I/O thread 11 state: waiting for completed aio requests (write thread)
    I/O thread 12 state: waiting for completed aio requests (write thread)
    I/O thread 13 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 1 [0, 0, 0, 1] ,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    1198476 OS file reads, 9230454 OS file writes, 1358621 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 1161.17 writes/s, 150.78 fsyncs/s
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
    insert 0, delete mark 0, delete 0
    discarded operations:
    insert 0, delete mark 0, delete 0
    Hash table size 53124499, node heap has 109284 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 225085860040
    Log flushed up to 225085860040
    Last checkpoint at 223859552544
    Max checkpoint age 1303883551
    Checkpoint age target 1263137191
    Modified age 1121876309
    Checkpoint age 1226307496
    0 pending log writes, 0 pending chkp writes
    19725 log i/o's done, 0.00 log i/o's/second
    BUFFER POOL AND MEMORY
    Total memory allocated 27551334400; in additional pool allocated 0
    Total memory allocated by read views 17032
    Internal hash tables (constant factor + variable factor)
    Adaptive hash index 2215509264 (424995992 + 1790513272)
    Page hash 26563016 (buffer pool 0 only)
    Dictionary cache 106387979 (106250416 + 137563)
    File system 145312 (82672 + 62640)
    Lock system 73933704 (73919464 + 14240)
    Recovery system 0 (0 + 0)
    Dictionary memory allocated 137563
    Buffer pool size 1638399
    Buffer pool size, bytes 26843529216
    Free buffers 265905
    Database pages 1263210
    Old database pages 466282
    Modified db pages 763464
    Pending reads 0
    Pending writes: LRU 0, flush list 2, single page 0
    Pages made young 6420, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 1198453, created 64757, written 9056136
    0.00 reads/s, 0.00 creates/s, 1144.47 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 1263210, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    ROW OPERATIONS
    0 queries inside InnoDB, 0 queries in queue
    17 read views open inside InnoDB
    16 transactions active inside InnoDB
    16 out of 2000 descriptors used
    ---OLDEST VIEW---
    Normal read view
    Read view low limit trx n:o 196FF78
    Read view up limit trx id 196FF5B
    Read view low limit trx id 196FF7A
    Read view individually stored trx ids:
    Read view trx id 196FF79
    Read view trx id 196FF76
    Read view trx id 196FF75
    Read view trx id 196FF72
    Read view trx id 196FF70
    Read view trx id 196FF6F
    Read view trx id 196FF6D
    Read view trx id 196FF6A
    Read view trx id 196FF68
    Read view trx id 196FF67
    Read view trx id 196FF64
    Read view trx id 196FF62
    Read view trx id 196FF60
    Read view trx id 196FF5F
    Read view trx id 196FF5E
    Read view trx id 196FF5B
    Main thread process no. 22134, id 140461969188608, state: flushing log
    Number of rows inserted 7094739, updated 14190718, deleted 7094190, read 3036313685
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    TRANSACTIONS
    Trx id counter 196FF97
    Purge done for trx's n:o < 196FF78 undo n:o < 0
    History list length 228
  • zhaogongpozhaogongpo Contributor Inactive User Role Beginner
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0, not started
    MySQL thread id 6553, OS thread handle 0x7fbfd5f74700, query id 141953916 127.0.0.1 root
    show engine innodb status
    ---TRANSACTION 196FF96, ACTIVE 83 sec inserting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 4
    MySQL thread id 6536, OS thread handle 0x7fbebbaeb700, query id 141953901 127.0.0.1 test update
    INSERT INTO sbtest12 (id, k, c, pad) VALUES (4388288, 729827, '11814348648-19862261356-49042326463-77526757071-82829277861-22094938315-58164390979-36803021137-22470492505-05920389366', '00498032476-10542414472-91380854101-57870962358-73507531325')
    Trx read view will not see trx with id >= 196FF97, sees < 196FF79
    TABLE LOCK table `sbtest`.`sbtest12` trx id 196FF96 lock mode IX
    RECORD LOCKS space id 751 page no 14265 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest12` trx id 196FF96 lock_mode X locks rec but not gap
    RECORD LOCKS space id 751 page no 55940 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest12` trx id 196FF96 lock_mode X locks rec but not gap
    RECORD LOCKS space id 751 page no 65842 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest12` trx id 196FF96 lock_mode X locks rec but not gap
    ---TRANSACTION 196FF94, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
    MySQL thread id 6544, OS thread handle 0x7fc6744bf700, query id 141953906 127.0.0.1 test updating
    DELETE FROM sbtest3 WHERE id=4068758
    Trx read view will not see trx with id >= 196FF95, sees < 196FF75
    TABLE LOCK table `sbtest`.`sbtest3` trx id 196FF94 lock mode IX
    RECORD LOCKS space id 741 page no 60852 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest3` trx id 196FF94 lock_mode X locks rec but not gap
    RECORD LOCKS space id 741 page no 20325 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest3` trx id 196FF94 lock_mode X locks rec but not gap
    RECORD LOCKS space id 741 page no 60888 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest3` trx id 196FF94 lock_mode X locks rec but not gap
    ---TRANSACTION 196FF92, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    2 lock struct(s), heap size 376, 1 row lock(s)
    MySQL thread id 6537, OS thread handle 0x7fbecab2d700, query id 141953898 127.0.0.1 test Updating
    UPDATE sbtest13 SET k=k+1 WHERE id=1353444
    Trx read view will not see trx with id >= 196FF93, sees < 196FF75
    TABLE LOCK table `sbtest`.`sbtest13` trx id 196FF92 lock mode IX
    RECORD LOCKS space id 752 page no 20876 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest13` trx id 196FF92 lock_mode X locks rec but not gap
    ---TRANSACTION 196FF91, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
    MySQL thread id 6546, OS thread handle 0x7fbfd7b21700, query id 141953893 127.0.0.1 test updating
    DELETE FROM sbtest9 WHERE id=1937572
    Trx read view will not see trx with id >= 196FF92, sees < 196FF75
    TABLE LOCK table `sbtest`.`sbtest9` trx id 196FF91 lock mode IX
    RECORD LOCKS space id 748 page no 49431 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest9` trx id 196FF91 lock_mode X locks rec but not gap
    RECORD LOCKS space id 748 page no 72900 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest9` trx id 196FF91 lock_mode X locks rec but not gap
    RECORD LOCKS space id 748 page no 29198 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest9` trx id 196FF91 lock_mode X locks rec but not gap
    ---TRANSACTION 196FF8F, ACTIVE 83 sec inserting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 3
    MySQL thread id 6541, OS thread handle 0x7fbfc3659700, query id 141953903 127.0.0.1 test update
    INSERT INTO sbtest13 (id, k, c, pad) VALUES (2020063, 3216557, '92518267352-69783502520-55744444753-49638921881-71858504555-98630349358-07728484235-48576414096-04846838275-58007012428', '20868452276-02056206886-36175712877-92878561567-57817504341')
    Trx read view will not see trx with id >= 196FF90, sees < 196FF6F
    TABLE LOCK table `sbtest`.`sbtest13` trx id 196FF8F lock mode IX
    RECORD LOCKS space id 752 page no 51062 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest13` trx id 196FF8F lock_mode X locks rec but not gap
    RECORD LOCKS space id 752 page no 71943 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest13` trx id 196FF8F lock_mode X locks rec but not gap
    RECORD LOCKS space id 752 page no 30264 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest13` trx id 196FF8F lock_mode X locks rec but not gap
    ---TRANSACTION 196FF8D, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
    MySQL thread id 6539, OS thread handle 0x7fbfd613b700, query id 141953890 127.0.0.1 test updating
    DELETE FROM sbtest5 WHERE id=2586943
    Trx read view will not see trx with id >= 196FF8E, sees < 196FF6F
    TABLE LOCK table `sbtest`.`sbtest5` trx id 196FF8D lock mode IX
    RECORD LOCKS space id 744 page no 45055 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest5` trx id 196FF8D lock_mode X locks rec but not gap
    RECORD LOCKS space id 744 page no 48665 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest5` trx id 196FF8D lock_mode X locks rec but not gap
    RECORD LOCKS space id 744 page no 39758 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest5` trx id 196FF8D lock_mode X locks rec but not gap
    ---TRANSACTION 196FF8A, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
    MySQL thread id 6543, OS thread handle 0x7fbf63fbe700, query id 141953895 127.0.0.1 test Updating
    UPDATE sbtest13 SET k=k+1 WHERE id=4793540
    Trx read view will not see trx with id >= 196FF8B, sees < 196FF6D
    TABLE LOCK table `sbtest`.`sbtest13` trx id 196FF8A lock mode IX
    RECORD LOCKS space id 752 page no 72801 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest13` trx id 196FF8A lock_mode X locks rec but not gap
    ---TRANSACTION 196FF88, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
    MySQL thread id 6542, OS thread handle 0x7fbfd717b700, query id 141953869 127.0.0.1 test Updating
    UPDATE sbtest7 SET k=k+1 WHERE id=3771602
    Trx read view will not see trx with id >= 196FF89, sees < 196FF6A
    TABLE LOCK table `sbtest`.`sbtest7` trx id 196FF88 lock mode IX
    RECORD LOCKS space id 746 page no 56690 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest7` trx id 196FF88 lock_mode X locks rec but not gap
    ---TRANSACTION 196FF86, ACTIVE 83 sec updating or deleting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
    MySQL thread id 6547, OS thread handle 0x7fbec9494700, query id 141953879 127.0.0.1 test updating
    DELETE FROM sbtest8 WHERE id=4261626
    Trx read view will not see trx with id >= 196FF87, sees < 196FF67
    TABLE LOCK table `sbtest`.`sbtest8` trx id 196FF86 lock mode IX
    RECORD LOCKS space id 747 page no 18277 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest8` trx id 196FF86 lock_mode X locks rec but not gap
    RECORD LOCKS space id 747 page no 60501 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest8` trx id 196FF86 lock_mode X locks rec but not gap
    RECORD LOCKS space id 747 page no 63850 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest8` trx id 196FF86 lock_mode X locks rec but not gap
    . . . . . .
    ---TRANSACTION 196FF79, ACTIVE 83 sec inserting
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 4
    MySQL thread id 6549, OS thread handle 0x7fbf63c71700, query id 141953856 127.0.0.1 test update
    INSERT INTO sbtest5 (id, k, c, pad) VALUES (729929, 2956969, '03814822121-48667714674-14509476873-54860100018-06637249333-70296470367-17993552168-99101301830-77272760609-64123597401', '85240264537-55191441762-31045910968-81107751905-43935169053')
    Trx read view will not see trx with id >= 196FF7A, sees < 196FF5B
    TABLE LOCK table `sbtest`.`sbtest5` trx id 196FF79 lock mode IX
    RECORD LOCKS space id 744 page no 10625 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest5` trx id 196FF79 lock_mode X locks rec but not gap
    RECORD LOCKS space id 744 page no 57329 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest5` trx id 196FF79 lock_mode X locks rec but not gap
    RECORD LOCKS space id 744 page no 11247 n bits 144 index `PRIMARY` of table `sbtest`.`sbtest5` trx id 196FF79 lock_mode X locks rec but not gap
    END OF INNODB MONITOR OUTPUT
    ============================
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi zhaogongpo;

    A sysbench test on it's own does not really tell you much. The real power of the tool comes in comparing things, like different my.cnf settings, different hardware, different MySQL versions, etc. Having baseline test results then allows you to make small incremental changes, run the test again, and see if the change you made makes the results better or worse. The key there is small incremental changes; if you change 20 things at once, you'll have no idea what the real effect of any given change is.

    -Scott
  • zhaogongpozhaogongpo Contributor Inactive User Role Beginner
    Hi zhaogongpo;

    A sysbench test on it's own does not really tell you much. The real power of the tool comes in comparing things, like different my.cnf settings, different hardware, different MySQL versions, etc. Having baseline test results then allows you to make small incremental changes, run the test again, and see if the change you made makes the results better or worse. The key there is small incremental changes; if you change 20 things at once, you'll have no idea what the real effect of any given change is.

    -Scott

    Thank you for your reply, In fact, I want to test the effect of the "thread pool", but now I do not know if the "long queries" should be put into the result of the test, and how can I avoid this situation.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi zhaogongpo;

    I'd include whatever results you get. The key would be to run the test multiple times for each state, that way you can get an average which will weed out any outliers. I.e. currently I'm testing two new versions of MySQL, and I'm running sysbench 10 times at 6 different thread counts per version. So it's a lot of tests, but gives you the best overall picture.

    As for your slow threads, chances are your disk is getting bogged down, but you'd want to run some diagnostics on your system to see what is happening during that time. I.e. checking the load via "uptime", checking CPU usage via "top", checking I/O wait (and many other things) via "sar" or iostat, and checking memory utilization via "free -g". I would also check to see if your buffer pool is churning by running "show engine innodb status" and look at the end for the "evicted without access" rate in the buffer pool and memory section.

    All of those things will give you an idea of what your system is doing, and then you'll have to adjust accordingly.

    -Scott
  • zhaogongpozhaogongpo Contributor Inactive User Role Beginner
    Hi Scott, thanks for your reply. When the long threads appeared, the system seemed ok, io,load,memory. But all commands of mysql could not execute.,They would stay waiting until the long threads disappeared. I executed the "pstack" , and found all of that threads was waiting on
    a same function.
    I think the test couldnot be put into the final result.
    At last, I changed the max-time from 600 to 300, The "long queries" didnot appear.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.