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
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
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.
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.
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.
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.