How Can I upgrade TPC-C performance test result for mysql

How Can I upgrade TPC-C performance test result for mysql

Post by Heikki Tuur » Sat, 29 Oct 2005 16:59:57



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 9CvDXe@4O8|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 9CvDXe@4O8|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 9CvDXe@4O8|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 ...

read more »

 
 
 

How Can I upgrade TPC-C performance test result for mysql

Post by yanghaifen » Thu, 03 Nov 2005 10:28:18


SGVpa2tpLA0KICBXZSB0aGFua3MgdmFyeSBtdWNoIGZvciB5b3VyIHJlc3BvbnNlLg0KICBUaGUg
Q1BVIHVzYWdlIGlzIGF2ZyAxMCUgZHVyaW5nIHRoZSB0ZXN0LiANCiAgV2UgdGhpbmsgaWYgeW91
IG5lZWQgd2Ugd2lsbCBnaXZlIHlvdSBvdXIgdGVzdCBzb3VyY2UgY29kZS4NCg0KQmVzdCByZWdh
cmRzLA0KZ2Vvcmdl

 
 
 

How Can I upgrade TPC-C performance test result for mysql

Post by Heikki Tuur » Fri, 04 Nov 2005 06:10:15


George,

is the utilization of both CPUs only 10 %? How big it is in the test with MS
SQL Server?

The workload is very much disk-bound if CPU usage is only 10 %.

Regards,

Heikki
Oracle/Innobase

----- Original Message -----

Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 02, 2005 3:28 AM
Subject: Re:Re: Re: How Can I upgrade TPC-C performance test result for
mysql

Heikki,
  We thanks vary much for your response.
  The CPU usage is avg 10% during the test.
  We think if you need we will give you our test source code.

Best regards,
george

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql