George,
there seem to be two bottlenecks:
1) InnoDB has contention on the buffer pool mutex;
2) the workload is also disk-read-bound.
For 1), we might have an improvement available in the future. We must let
the threads leave the 'wait array' in sync0arr.c without reserving the wait
array mutex. Also, we need to study if we can reduce the code that is
executed when we have the buffer pool mutex reserved.
To reduce 2), you can try increasing innodb_buffer_pool_size further. But be
careful that Windows will not start swapping.
What does the Task Manager say about CPU usage during the test?
Regards,
Heikki
Oracle/Innobase
...............
Dear Heikki,
we change the system's environment. now we use RAID0 disk to store the
InnoDB data file,and other disk to store log file.
follow is system's information:
CPU: 4X2.8GHz
RAM: 1G
Disk: 4X36G
the SHOW INNIDB STATUS\G result is:
=====================================
051021 10:51:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1416712, signal count 577079
--Thread 2364 has waited at
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c
line 560 for 0.00 seconds the semaphore:
S-lock on RW-latch at 01471B80 created in file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c
line 139
a writer (thread id 2400) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c
line 560
Last time write locked in file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c
line 489
wait has ended
--Thread 2408 has waited at
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 1286 for 0.00 seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2400 has waited at
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c
line 489 for 0.00 seconds the semaphore:
X-lock on RW-latch at 01471B80 created in file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c
line 139
a writer (thread id 2400) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0cur.c
line 560
Last time write locked in file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\btr\btr0sea.c
line 489
--Thread 2332 has waited at ../innobase/include\log0log.ic line 315 for 0.00
seconds the semaphore:
Mutex at 014740D0 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\log\log0log.c
line 744, lock var 1
waiters flag 1
--Thread 2420 has waited at ../innobase/include\log0log.ic line 315 for 0.00
seconds the semaphore:
Mutex at 014740D0 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\log\log0log.c
line 744, lock var 1
waiters flag 1
--Thread 2404 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00
seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2380 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00
seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2348 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00
seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2424 has waited at
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 1088 for 0.00 seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 380 has waited at
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 1088 for 0.00 seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2392 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00
seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2396 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00
seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
--Thread 2372 has waited at ../innobase/include\buf0buf.ic line 621 for 0.00
seconds the semaphore:
Mutex at 01471880 created file
e:\mysqldev\build\mysql-5.0.12-beta-build\mysql-5.0.12-beta\innobase\buf\buf0buf.c
line 531, lock var 0
waiters flag 0
wait is ending
Mutex spin waits 5935883, rounds 41155239, OS waits 963597
RW-shared spins 112603, OS waits 66259; RW-excl spins 52408, OS waits 36085
------------------------
LATEST DETECTED DEADLOCK
------------------------
051021 10:51:57
*** (1) TRANSACTION:
TRANSACTION 0 330658, ACTIVE 0 sec, OS thread id 1084 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 5, query id 809398 SERVER3 192.168.1.3 dbt Updating
UPDATE district SET d_ytd = d_ytd + 222.429993 WHERE d_w_id = 29 AND d_id =
9
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 194 n bits 208 index `PRIMARY` of table
`dbt2/district` trx id 0 330658 lock_mode X locks rec but not gap waiting
Record lock, heap no 80 PHYSICAL RECORD: n_fields 13; compact format; info
bits 0
0: len 4; hex 8000001d; asc ? ;; 1: len 4; hex 80000009; asc ? ;; 2:
len 6; hex 00000004fd51; asc Q;; 3: len 7; hex 000009802214d6; asc
?" ;; 4: len 6; hex 762e76483c21; asc v.vH<!;; 5: len 14; hex
5a4b31326f6a3876382358727238; asc ZK12oj8v8#Xrr8;; 6: len 15; hex
39437644586540344f387c6b534821; asc [EMAIL PROTECTED]|kSH!;; 7: len 19; hex
593d2d347b6b7e455559763163782d6d26254f; asc Y=-4{k~EUYv1cx-m&%O;; 8: len 2;
hex 464c; asc FL;; 9: len 9; hex 383335323131313131; asc 835211111;; 10: len
4; hex e71d273e; asc '>;; 11: len 4; hex ab7e2647; asc ~&G;; 12: len 4;
hex 80000c08; asc ? ;;
*** (2) TRANSACTION:
TRANSACTION 0 330603, ACTIVE 1 sec, OS thread id 2392 starting index read
mysql tables in use 1, locked 1
20 lock struct(s), heap size 2496, undo log entries 1
MySQL thread id 22, query id 809651 SERVER3 192.168.1.3 dbt Updating
UPDATE district SET d_ytd = d_ytd + 176.399994 WHERE d_w_id = 29 AND d_id =
9
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 194 n bits 208 index `PRIMARY` of table
`dbt2/district` trx id 0 330603 lock mode S locks rec but not gap
Record lock, heap no 80 PHYSICAL RECORD: n_fields 13; compact format; info
bits 0
0: len 4; hex 8000001d; asc ? ;; 1: len 4; hex 80000009; asc ? ;; 2:
len 6; hex 00000004fd51; asc Q;; 3: len 7; hex 000009802214d6; asc
?" ;; 4: len 6; hex 762e76483c21; asc v.vH<!;; 5: len 14; hex
5a4b31326f6a3876382358727238; asc ZK12oj8v8#Xrr8;; 6: len 15; hex
39437644586540344f387c6b534821; asc [EMAIL PROTECTED]|kSH!;; 7: len 19; hex
593d2d347b6b7e455559763163782d6d26254f; asc Y=-4{k~EUYv1cx-m&%O;; 8: len 2;
hex 464c; asc FL;; 9: len 9; hex 383335323131313131; asc 835211111;; 10: len
4; hex e71d273e; asc '>;; 11: len 4; hex ab7e2647; asc ~&G;; 12: len 4;
hex 80000c08; asc ? ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 194 n bits 208 index `PRIMARY` of table
`dbt2/district` trx id 0 330603 lock_mode X locks rec but not gap waiting
Record lock, heap no 80 PHYSICAL RECORD: n_fields 13; compact format; info
bits 0
0: len 4; hex 8000001d; asc ? ;; 1: len 4; hex 80000009; asc ? ;; 2:
len 6; hex 00000004fd51; asc Q;; 3: len 7; hex 000009802214d6; asc
?" ;; 4: len 6; hex 762e76483c21; asc v.vH<!;; 5: len 14; hex
5a4b31326f6a3876382358727238; asc ZK12oj8v8#Xrr8;; 6: len 15; hex
39437644586540344f387c6b534821; asc [EMAIL PROTECTED]|kSH!;; 7: len 19; hex
593d2d347b6b7e455559763163782d6d26254f; asc Y=-4{k~EUYv1cx-m&%O;; 8: len 2;
hex 464c; asc FL;; 9: len 9; hex 383335323131313131; asc 835211111;; 10: len
4; hex e71d273e; asc '>;; 11: len 4; hex ab7e2647; asc ~&G;; 12: len 4;
hex 80000c08; asc ? ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 0 330789
Purge done for trx's n:o < 0 330629 undo n:o < 0 0
History list length 73
Total number of lock structs in row lock hash table 367
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 330787, ACTIVE 0 sec, OS thread id 2348 starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320
MySQL thread id 14, query id 811460 SERVER3 192.168.1.3 dbt Sending data
SELECT count(c_id) FROM customer WHERE c_last = 'ABLEBARCALLY' AND c_d_id =
9 AND c_w_id = 55
---TRANSACTION 0 330785, ACTIVE 0 sec, OS thread id 1472 estimating records
in index range
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320
MySQL thread id 3, query id 811407 SERVER3 192.168.1.3 dbt statistics
SELECT count(c_id) FROM customer WHERE c_last = 'EINGOUGHTEING' AND c_d_id =
5 AND c_w_id = 7
---TRANSACTION 0 330784, ACTIVE 0 sec, OS thread id 2352 estimating records
in index range
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320
MySQL thread id 4, query id 811423 SERVER3 192.168.1.3 dbt statistics
SELECT count(c_id) FROM customer WHERE c_last = 'CALLYANTICALLY' AND c_d_id
= 5 AND c_w_id = 2
---TRANSACTION 0 330781, ACTIVE 0 sec, OS thread id 2372 fetching rows
mysql tables in use 1, locked 1
13 lock struct(s), heap size 2496, undo log entries 8
MySQL thread id 17, query id 811427 SERVER3 192.168.1.3 dbt Updating
UPDATE order_line SET ol_delivery_d = now() WHERE ol_o_id = 2164 AND ol_d_id
= 1 AND ol_w_id = 55
---TRANSACTION 0 330780, ACTIVE 0 sec, OS thread id 2324 starting index read
mysql tables in use 1, locked 1
13 lock struct(s), heap size 1024, undo log entries 3
MySQL thread id 1, query id 811440 SERVER3 192.168.1.3 dbt statistics
SELECT s_quantity, s_data
,s_dist_01,s_dist_02,s_dist_03,s_dist_04,s_dist_05,s_dist_06,s_dist_07,s_dist_08,s_dist_09,s_dist_10
FROM stock WHERE s_i_id = 15775 AND s_w_id = 44
---TRANSACTION 0 330779, ACTIVE 0 sec, OS thread id 1084 starting index read
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1024, undo log entries 3
MySQL thread id 5, query id 811457 SERVER3 192.168.1.3 dbt Updating
UPDATE stock SET s_quantity = 89, s_ytd=s_ytd +
4,s_order_cnt=s_order_cnt+1,s_remote_cnt=s_remote_cnt+0 WHERE s_i_id = 32764
AND s_w_id = 52
---TRANSACTION 0 330775, ACTIVE 0 sec, OS thread id 860 inserting
mysql tables in use 1, locked 1
18 lock struct(s), heap size 2496, undo log entries 4
MySQL thread id 13, query id 811431 SERVER3 192.168.1.3 dbt update
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id,
ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3084, 10, 23,
1, 3821, 23, 3, 86.550003,'hiNAmswdqbNtOurzXYgCxqUk')
---TRANSACTION 0 330773, ACTIVE 0 sec, OS thread id 2416 fetching rows
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1024
MySQL thread id 28, query id 811409 SERVER3 192.168.1.3 dbt Sending data
SELECT c_first, c_middle, c_id, c_street_1, c_street_2, c_city, c_state,
c_zip, c_phone, c_credit,c_credit_lim, c_discount, c_balance, c_since FROM
customer WHERE c_w_id = 72 AND c_d_id = 10 AND c_last = 'CALLYANTIANTI'
ORDER BY c_first
---TRANSACTION 0 330770, ACTIVE 0 sec, OS thread id 2368 fetching rows
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1024
MySQL thread id 16, query id 811390 SERVER3 192.168.1.3 dbt Sending data
SELECT c_first, c_middle, c_id, c_street_1, c_street_2, c_city, c_state,
c_zip, c_phone, c_credit,c_credit_lim, c_discount, c_balance, c_since FROM
customer WHERE c_w_id = 36 AND c_d_id = 4 AND c_last = 'CALLYANTICALLY'
ORDER BY c_first
---TRANSACTION 0 330769, ACTIVE 0 sec, OS thread id 2392 starting index read
mysql tables in use 2, locked 2
2 lock struct(s), heap size 320
MySQL thread id 22, query id 811240 SERVER3 192.168.1.3 dbt statistics
SELECT c_discount, c_last, c_credit,w_tax FROM customer,warehouse WHERE w_id
= 91 AND c_w_id = w_id AND c_d_id = 1 AND c_id = 2549
---TRANSACTION 0 330766, ACTIVE 0 sec, OS thread id 2424 inserting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 1024, undo log entries 2
MySQL thread id 30, query id 811459 SERVER3 192.168.1.3 dbt update
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id,h_date,
h_amount, h_data) VALUES (4, 62, 1810, 4, 62, now(), 67.489998, '{Ec>bm.$0')
---TRANSACTION 0 330765, ACTIVE 0 sec, OS thread id 2396 starting index read
mysql tables in use 1, locked 1
22 lock struct(s), heap size 2496, undo log entries 17
MySQL thread id 23, query id 811455 SERVER3 192.168.1.3 dbt Updating
UPDATE orders SET o_carrier_id = 9 WHERE o_id = 2162 AND o_d_id = 2 AND
o_w_id = 75
---TRANSACTION 0 330763, ACTIVE 0 sec, OS thread id 2380 inserting
mysql tables in use 1, locked 1
24 lock struct(s), heap size 2496, undo log entries 8
MySQL thread id 19, query id 811461 SERVER3 192.168.1.3 dbt update
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id,
ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3063, 10, 100,
3, 12600, 100, 8, 269.040009,'RToOxVYndOAUfqAnaSzbJFyk')
---TRANSACTION 0 330761, ACTIVE 0 sec, OS thread id 1476 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 1024, undo log entries 3
MySQL thread id 8, query id 811078 SERVER3 192.168.1.3 dbt Updating
UPDATE warehouse SET w_ytd = w_ytd + 263.040009 WHERE w_id = 11
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 50 n bits 168 index `PRIMARY` of table
`dbt2/warehouse` trx id 0 330761 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 11; compact format; info
bits 0
0: len 4; hex 8000000b; asc ? ;; 1: len 6; hex 000000050897; asc ;;
2: len 7; hex 000009c00a1b95; asc ;; 3: len 7; hex 3435776c6c7c65;
asc 45wll|e;; 4: len 15; hex 784d3e6f6e61692a754e396e337c76; asc
xM>onai*uN9n3|v;; 5: len 10; hex 4f6e395433292b29577e; asc On9T3)+)W~;; 6:
len 19; hex 2e265f745735755f53242d3e4147307a5d484c; asc
.&_tW5u_S$->AG0z]HL;; 7: len 2; hex 464b; asc FK;; 8: len 9; hex
373639373131313131; asc 769711111;; 9: len 4; hex 029a083e; asc >;; 10:
len 4; hex da6cdc48; asc l H;;
------------------
---TRANSACTION 0 330760, ACTIVE 0 sec, OS thread id 2388 fetching rows
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1024
MySQL thread id 21, query id 811299 SERVER3 192.168.1.3 dbt Sending data
SELECT c_first, c_middle, c_id, c_street_1, c_street_2, c_city, c_state,
c_zip, c_phone, c_credit,c_credit_lim, c_discount, c_balance, c_since FROM
customer WHERE c_w_id = 96 AND c_d_id = 8 AND c_last = 'PRIATIONABLE' ORDER
BY c_first
---TRANSACTION 0 330756, ACTIVE 0 sec, OS thread id 2384
27 lock struct(s), heap size 2496, undo log entries 11
MySQL thread id 20, query id 811447 SERVER3 192.168.1.3 dbt cleaning up
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id,
ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3076, 1, 11,
4, 32366, 11, 5, 271.299988,'VPwukBnPSxAgsjaKbHdWqomv')
---TRANSACTION 0 330754, ACTIVE 0 sec, OS thread id 2420 fetching rows
mysql tables in use 1, locked 1
45 lock struct(s), heap size 5504, undo log entries 47
MySQL thread id 29, query id 811439 SERVER3 192.168.1.3 dbt Updating
UPDATE order_line SET ol_delivery_d = now() WHERE ol_o_id = 2170 AND ol_d_id
= 5 AND ol_w_id = 56
---TRANSACTION 0 330747, ACTIVE 1 sec, OS thread id 2360 starting index read
mysql tables in use 1, locked 1
36 lock struct(s), heap size 2496, undo log entries 17
MySQL thread id 12, query id 811450 SERVER3 192.168.1.3 dbt statistics
SELECT s_quantity, s_data
,s_dist_01,s_dist_02,s_dist_03,s_dist_04,s_dist_05,s_dist_06,s_dist_07,s_dist_08,s_dist_09,s_dist_10
FROM stock WHERE s_i_id = 23935 AND s_w_id = 36
---TRANSACTION 0 330743, ACTIVE 1 sec, OS thread id 380 starting index read
mysql tables in use 1, locked 1
31 lock struct(s), heap size 2496, undo log entries 13
MySQL thread id 11, query id 811456 SERVER3 192.168.1.3 dbt statistics
SELECT s_quantity, s_data
,s_dist_01,s_dist_02,s_dist_03,s_dist_04,s_dist_05,s_dist_06,s_dist_07,s_dist_08,s_dist_09,s_dist_10
FROM stock WHERE s_i_id = 31464 AND s_w_id = 98
---TRANSACTION 0 330738, ACTIVE 1 sec, OS thread id 2404 inserting
mysql tables in use 1, locked 1
41 lock struct(s), heap size 5504, undo log entries 18
MySQL thread id 25, query id 811458 SERVER3 192.168.1.3 dbt update
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id,
ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3078, 7, 21,
8, 7406, 21, 7, 550.479980,'BVXAtDhesneYTXacCYbKzxSN')
---TRANSACTION 0 330735, ACTIVE 1 sec, OS thread id 1332 fetching rows
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1024
MySQL thread id 7, query id 810713 SERVER3 192.168.1.3 dbt Sending data
SELECT c_first, c_middle, c_id, c_street_1, c_street_2, c_city, c_state,
c_zip, c_phone, c_credit,c_credit_lim, c_discount, c_balance, c_since FROM
customer WHERE c_w_id = 62 AND c_d_id = 5 AND c_last = 'CALLYANTIPRI' ORDER
BY c_first
---TRANSACTION 0 330733, ACTIVE 1 sec, OS thread id 2400 starting index read
mysql tables in use 1, locked 1
45 lock struct(s), heap size 5504, undo log entries 23
MySQL thread id 24, query id 811445 SERVER3 192.168.1.3 dbt statistics
SELECT s_quantity, s_data
,s_dist_01,s_dist_02,s_dist_03,s_dist_04,s_dist_05,s_dist_06,s_dist_07,s_dist_08,s_dist_09,s_dist_10
FROM stock WHERE s_i_id = 28096 AND s_w_id = 86
---TRANSACTION 0 330727, ACTIVE 1 sec, OS thread id 2332 committing
39 lock struct(s), heap size 2496, undo log entries 19
MySQL thread id 10, query id 811452 SERVER3 192.168.1.3 dbt
COMMIT
---TRANSACTION 0 330709, ACTIVE 1 sec, OS thread id 2412 inserting
mysql tables in use 1, locked 1
25 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 27, query id 811351 SERVER3 192.168.1.3 dbt update
INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number,ol_i_id,
ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3059, 2, 68,
3, 31648, 68, 1, 8.410000,'gJYodCMRBUvwTGJPXgabvGnH')
---TRANSACTION 0 330707, ACTIVE 1 sec, OS thread id 2364 starting index read
mysql tables in use 2, locked 0
, holds adaptive hash latch
MySQL thread id 15, query id 810147 SERVER3 192.168.1.3 dbt Sending data
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = 67
AND ol_d_id = 1 AND ol_o_id < 3064 AND ol_o_id>=3044 AND s_w_id = ol_w_id
AND s_i_id = ol_i_id AND s_quantity < 15
Trx read view will not see trx with id >= 0 330711, sees < 0 330529
---TRANSACTION 0 330681, ACTIVE 2 sec, OS thread id 2408 fetching rows
mysql tables in use 1, locked 1
47 lock struct(s), heap size 5504, undo log entries 48
MySQL thread id 26, query id 811384 SERVER3 192.168.1.3 dbt Sending data
SELECT no_o_id FROM new_order WHERE no_d_id = 5 AND no_w_id = 48 ORDER BY
no_d_id ASC
---TRANSACTION 0 330648, ACTIVE 3 sec, OS thread id 1484 starting index read
mysql tables in use 2, locked 0
, holds adaptive hash latch
MySQL thread id 6, query id 809283 SERVER3 192.168.1.3 dbt Sending data
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = 93
AND ol_d_id = 3 AND ol_o_id < 3050 AND ol_o_id>=3030 AND s_w_id = ol_w_id
AND s_i_id = ol_i_id AND s_quantity < 19
Trx read view will not see trx with id >= 0 330674, sees < 0 330529
---TRANSACTION 0 330646, ACTIVE 3 sec, OS thread id 2336 starting index read
mysql tables in use 2, locked 0
, holds adaptive hash latch
MySQL thread id 9, query id 809272 SERVER3 192.168.1.3 dbt Sending data
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = 72
AND ol_d_id = 1 AND ol_o_id < 3064 AND ol_o_id>=3044 AND s_w_id = ol_w_id
AND s_i_id = ol_i_id AND s_quantity < 18
Trx read view will not see trx with id >= 0 330669, sees < 0 330529
---TRANSACTION 0 330622, ACTIVE 3 sec, OS thread id 2376 starting index read
mysql tables in use 2, locked 0
, holds adaptive hash latch
MySQL thread id 18, query id 808931 SERVER3 192.168.1.3 dbt Sending data
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = 16
AND ol_d_id = 8 AND ol_o_id < 3090 AND ol_o_id>=3070 AND s_w_id = ol_w_id
AND s_i_id = ol_i_id AND s_quantity < 19
Trx read view will not see trx with id >= 0 330625, sees < 0 330488
---TRANSACTION 0 330619, ACTIVE 3 sec, OS thread id 1204 starting index read
mysql tables in use 2, locked 0
, holds adaptive hash latch
MySQL thread id 2, query id 808922 SERVER3 192.168.1.3 dbt Sending data
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = 80
AND ol_d_id = 5 AND ol_o_id < 3059 AND ol_o_id>=3039 AND s_w_id = ol_w_id
AND s_i_id = ol_i_id AND s_quantity < 12
Trx read view will not see trx with id >= 0 330645, sees < 0 330529
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (read thread)
I/O thread 7 state: wait Windows aio (read thread)
I/O thread 8 state: wait Windows aio (read thread)
I/O thread 9 state: wait Windows aio (read thread)
I/O thread 10 state: wait Windows aio (read thread)
I/O thread 11 state: wait Windows aio (read thread)
I/O thread 12 state: wait Windows aio (read thread)
I/O thread 13 state: wait Windows aio (read thread)
I/O thread 14 state: wait Windows aio (read thread)
I/O thread 15 state: wait Windows aio (read thread)
I/O thread 16 state: wait Windows aio (read thread)
I/O thread 17 state: wait Windows aio (read thread)
I/O thread 18 state: wait Windows aio (write thread)
I/O thread 19 state: wait Windows aio (write thread)
I/O thread 20 state: wait Windows aio (write thread)
I/O thread 21 state: wait Windows aio (write thread)
I/O thread 22 state: wait Windows aio (write thread)
I/O thread 23 state: wait Windows aio (write thread)
I/O thread 24 state: wait Windows aio (write thread)
I/O thread 25 state: wait Windows aio (write thread)
I/O thread 26 state: wait Windows aio (write thread)
I/O thread 27 state: wait Windows aio (write thread)
I/O thread 28 state: wait Windows aio (write thread)
I/O thread 29 state: wait Windows aio (write thread)
I/O thread 30 state: wait Windows aio (write thread)
I/O thread 31 state: wait Windows aio (write thread)
I/O thread 32 state: wait Windows aio (write thread)
I/O thread 33 state: wait Windows aio (write thread)
Pending normal aio reads: 1, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 16
Pending flushes (fsync) log: 0; buffer pool: 0
500232 OS file reads, 195736 OS file writes, 7456 OS fsyncs
688.77 reads/s, 16384 avg bytes/read, 234.17 writes/s, 1.44 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1787, free list len 2376, seg size 4164, is not empty
Ibuf for space 0: size 1787, free list len 2376, seg size 4164,
152636 inserts, 250105 merged recs, 44196 merges
Hash table size 3320377, used cells 411665, node heap has 432 buffer(s)
497.59 hash searches/s, 2978.00 non-hash searches/s
---
LOG
---
Log sequence number 3 3047845732
Log flushed up to 3 3047756967
Last checkpoint at 3 2957785285
0 pending log writes, 0 pending chkp writes
5711 log i/o's done, 1.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 897775285; in additional pool allocated 2309760
Buffer pool size 51200
Free buffers 0
Database pages 50768
Modified db pages 19934
Pending reads 19
Pending writes: LRU 0, flush list 0, single page 0
Pages read 500228, created 2444, written 190030
695.21 reads/s, 4.81 creates/s, 232.86 writes/s
Buffer pool hit rate 977 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
6 read views open inside InnoDB
Main thread id 2268, state: sleeping
Number of rows inserted 170029, updated 319443, deleted 11947, read 12318619
257.92 inserts/s, 462.22 updates/s, 16.25 deletes/s, 16958.19 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Best regards,
george
----- Original Message -----
From: Heikki TuuriDate: October 25 2005 12:28am
Subject: Re: How Can I upgrade TPC-C performance test result for mysql
George,
can you please post a few typical
SHOW INNODB STATUS\G
outputs during the stress test.
Best regards,
Heikki
Oracle/Innobase
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
