Can we upgrade TPC-C performance test result for mysql VS Sqlserver2000
Posted by: george yang
Date: October 17, 2005 02:09AM
Date: October 17, 2005 02:09AM
we develop a TPC-C test progrom,this program use mysql ODBC3.51 to connect mysql server, and use microsoft sqlserver odbc to connect sqlserver2000.
we suppose you are know well the TPC-C.
mysql's store engine is InnoDB
follow is the os and hardware information:
os : Windows advance server 2000
CPU: 2X3.0Ghz
memory:2G
disk: only one 40G IDE disk
table struct:
for one warehouse,we stat all table list:
table name record numbers type record size(bytes) type table size(K bytes)
Warehouse 1 89 0.089
District 10 95 0.950
Customer 30k 655 19650
History 30k 46 1380
Order 30k 24 720
New-Order 9k 8 72
Order-Line 300k 54 16200
Stock 100k 306 30600
Item 100k 82 8200
SQL sentence:
create table warehouse ( w_id int not null, w_name varchar(10) null, w_street_1 varchar(20) null, w_street_2 varchar(20) null, w_city varchar(20) null, w_state char(2) null, w_zip char(9) null, w_tax float null, w_ytd float null, primary key(w_id) );
create table district ( d_id int not null, d_w_id int not null, d_name varchar(10) null, d_street_1 varchar(20) null, d_street_2 varchar(20) null, d_city varchar(20) null, d_state char(2) null, d_zip char(9) null, d_tax float null, d_ytd float null, d_next_o_id int null, primary key(d_w_id, d_id),foreign key(d_w_id)references warehouse(w_id) );
create table customer ( c_id int not null, c_d_id int not null, c_w_id int not null, c_first varchar(16) null, c_middle char(2) null, c_last varchar(16) null, c_street_1 varchar(20) null, c_street_2 varchar(20) null, c_city varchar(20) null, c_state char(2) null, c_zip char(9) null, c_phone char(16) null, c_since timestamp null, c_credit char(2) null, c_credit_lim float null, c_discount float null, c_balance float null, c_ytd_payment float null, c_payment_cnt int null, c_delivery_cnt int null, c_data varchar(500) null, primary key(c_w_id, c_d_id, c_id),foreign key(c_w_id,c_d_id) references district(d_w_id,d_id) );
create table history ( h_c_id int null, h_c_d_id int null, h_c_w_id int null, h_d_id int null, h_w_id int null, h_date timestamp null, h_amount float null, h_data varchar(24) null,foreign key(h_c_w_id,h_c_d_id,h_c_id) references customer(c_w_id,c_d_id,c_id),foreign key(h_w_id,h_d_id)references district(d_w_id,d_id) );
create table orders ( o_id int not null, o_d_id int not null, o_w_id int not null, o_c_id int null, o_entry_d timestamp null, o_carrier_id int null, o_ol_cnt int null, o_all_local int null, primary key(o_w_id, o_d_id, o_id),foreign key(o_w_id,o_d_id,o_c_id)references customer(c_w_id,c_d_id,c_id) );
create table new_order ( no_o_id int not null, no_d_id int not null, no_w_id int not null, primary key(no_w_id, no_d_id, no_o_id),foreign key(no_w_id,no_d_id,no_o_id)references orders(o_w_id,o_d_id,o_id) );
create table item ( i_id int not null, i_im_id int null, i_name varchar(24) null, i_price float null, i_data varchar(50) null, primary key(i_id) );
create table stock ( s_i_id int not null, s_w_id int not null, s_quantity int null, s_dist_01 varchar(24) null, s_dist_02 varchar(24) null, s_dist_03 varchar(24) null, s_dist_04 varchar(24) null, s_dist_05 varchar(24) null, s_dist_06 varchar(24) null, s_dist_07 varchar(24) null, s_dist_08 varchar(24) null, s_dist_09 varchar(24) null, s_dist_10 varchar(24) null, s_ytd int null, s_order_cnt int null, s_remote_cnt int null, s_data varchar(50) null, primary key(s_w_id, s_i_id),foreign key(s_w_id)references warehouse(w_id),foreign key(s_i_id)references item(i_id) );
create table order_line ( ol_o_id int not null, ol_d_id int not null, ol_w_id int not null, ol_number int not null, ol_i_id int null, ol_supply_w_id int null, ol_delivery_d timestamp null, ol_quantity int null, ol_amount float null, ol_dist_info char(24) null, primary key(ol_w_id, ol_d_id, ol_o_id, ol_number),foreign key(ol_w_id,ol_d_id,ol_o_id) references orders(o_w_id,o_d_id,o_id),foreign key(ol_supply_w_id,ol_i_id)references stock(s_w_id,s_i_id) );
index:
create index i_orders on orders (o_w_id, o_d_id, o_c_id, o_id);
create index i_customer on customer (c_w_id, c_d_id, c_last, c_first, c_id);
transaction information:
transaction type min percent(%) min keyboard time(sec) 90% response time(sec) min think time(sec)
1 New-Order N/a 18sec 5sec 12sec
2 Payment 43.0 3sec 5sec 12sec
3 Order-Status 4.0 2sec 5sec 10sec
4 Delivery 4.0 2sec 5sec 5sec
5 Stock-Level 4.0 2sec 20sec 5sec
transaction isolation level : SERIALIZABLE
my.ini:
default-storage-engine=INNODB
max_connections=100
query_cache_type=0
table_cache = 200
thread_concurrency = 8
tmp_table_size=10M
thread_cache_size=8
key_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=8M
sort_buffer_size=2M
[innodb]
innodb_data_file_path=ibdata1:8000M:autoextend
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8M
innodb_buffer_pool_size=1600M
innodb_log_file_size=500M
innodb_thread_concurrency=16
transaction-isolation=SERIALIZABLE
innodb_log_files_in_group = 3
innodb_file_io_threads=12
innodb_table_locks=0
innodb_lock_wait_timeout=15
--skip-innodb-doublewrite
--skip-innodb-checksums
test method:
for every warehouse simulate 10 users. and connect server via a connect pool(create by test client)
follow is all of SQL sentence,please don't change these:
1:delivery transaction(step by step)
begin
SELECT no_o_id FROM new_order WHERE no_d_id = %d AND no_w_id = %d ORDER BY no_d_id ASC;
DELETE FROM new_order WHERE no_o_id = %s AND no_d_id = %d AND no_w_id = %d;
SELECT o_c_id FROM orders WHERE o_id = %s AND o_d_id = %d AND o_w_id= %d;
UPDATE orders SET o_carrier_id = %d WHERE o_id = %s AND o_d_id = %d AND o_w_id = %d;
UPDATE order_line SET ol_delivery_d = %s WHERE ol_o_id = %s AND ol_d_id = %d AND ol_w_id = %d;
SELECT SUM(ol_amount) FROM order_line WHERE ol_o_id = %s AND ol_d_id = %d AND ol_w_id= %d;
UPDATE customer SET c_balance = c_balance + %f , c_delivery_cnt = c_delivery_cnt + 1 WHERE c_id = %s AND c_d_id = %d AND c_w_id = %d;
commit
2:new_order transacyion(step by step)
begin
SELECT c_discount, c_last, c_credit,w_tax FROM customer,warehouse WHERE w_id = %d AND c_w_id = w_id AND c_d_id = %d AND c_id = %d;
SELECT d_next_o_id, d_tax FROM district WHERE d_id = %d AND d_w_id = %d;
UPDATE district SET d_next_o_id = %d WHERE d_id = %d AND d_w_id = %d;
INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (%s, %d, %d, %d, %s, %d, %d);
INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (%s, %d, %d);
SELECT i_price, i_name, i_data FROM item WHERE i_id = %d;
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 = %d AND s_w_id = %d;
UPDATE stock SET s_quantity = %d, s_ytd=s_ytd + %d,s_order_cnt=s_order_cnt+1,s_remote_cnt=s_remote_cnt+%d WHERE s_i_id = %d AND s_w_id = %d;
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 (%s, %d, %d, %d, %d, %d, %d, %f,'%s');
commit
3:order_status transaction(step by step)
begin
SELECT count(c_id) FROM customer WHERE c_last = '%s' AND c_d_id = %d AND c_w_id = %d;
SELECT c_balance,c_first,c_middle,c_id FROM customer WHERE c_last = '%s' AND c_d_id = %d AND c_w_id = %d ORDER BY c_first;
SELECT c_balance,c_first, c_middle, c_last FROM customer WHERE c_id = %d AND c_d_id = %d AND c_w_id = %d;
SELECT o_id, o_carrier_id, o_entry_d FROM orders WHERE o_c_id = %d AND o_d_id = %d AND o_w_id = %d ORDER BY o_id DESC;
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount,ol_delivery_d FROM order_line WHERE ol_o_id = %s AND ol_d_id = %d AND ol_w_id = %d;
commit
4:payment transaction(step by step)
begin
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name FROM warehouse WHERE w_id = %d;
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name FROM district WHERE d_w_id = %d AND d_id = %d;
SELECT count(c_id) FROM customer WHERE c_last = '%s' AND c_d_id = %d AND c_w_id = %d;
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 = %d AND c_d_id = %d AND c_last = '%s' ORDER BY c_first;
SELECT c_first, c_middle, c_last, 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 = %d AND c_d_id = %d AND c_id = %d;
SELECT c_data FROM customer WHERE c_w_id = %d AND c_d_id = %d AND c_id = %d;
UPDATE customer SET c_balance = %f, c_ytd_payment = c_ytd_payment + %f, c_payment_cnt = c_payment_cnt +1 , c_data='%s' WHERE c_w_id = %d AND c_d_id = %d AND c_id = %d;
UPDATE customer SET c_balance = %f, c_ytd_payment = c_ytd_payment + %f, c_payment_cnt = c_payment_cnt +1 WHERE c_w_id = %d AND c_d_id = %d AND c_id = %d;
UPDATE district SET d_ytd = d_ytd + %f WHERE d_w_id = %d AND d_id = %d;
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 (%d, %d, %d, %d, %d, %s, %f, '%s');
UPDATE warehouse SET w_ytd = w_ytd + %f WHERE w_id = %d;
commit
5:stock_level(step by step)
begin
SELECT d_next_o_id FROM district WHERE d_w_id = %d AND d_id = %d;
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = %d AND ol_d_id = %d AND ol_o_id < %d AND ol_o_id>=%d AND s_w_id = ol_w_id AND s_i_id = ol_i_id AND s_quantity < %d;
commit
our test result is :
for mysql 5.0.12 , we can pass the 60 Warehouse test use 10 connect pool and simulate 600users ,and the result list:
Transaction % Avg Response Time (s) Total Rollbacks %
delivery 4.02 1.109 7962 7 0.09
new-order 44.87 0.700 88944 1246 1.40
order-status 4.01 1.014 7939 0 0.00
payment 43.10 0.797 85439 3282 3.84
stock-level 4.00 1.544 7931 0 0.00
739.01 new-order transactions per minute (NOTPM)
119.8 minute duration
0 total unknown errors
Transaction 90th percentile Response Time (s) Max Response Time (s)
delivery 2.205 5.306
new-order 1.721 5.622
order-status 2.185 7.376
payment 1.894 7.839
stock-level 3.233 7.907
but we can't pass 70 warehouse test use 10 connect pool and simulate 700users. the result and mysqlreport list is:
test result:
Transaction % Avg Response Time (s) Total Rollbacks %
delivery 4.03 7.106 1212 1 0.08
new-order 44.99 6.396 13524 211 1.56
order-status 3.91 6.919 1175 0 0.00
payment 43.00 6.639 12926 571 4.42
stock-level 4.08 7.743 1226 0 0.00
679.33 new-order transactions per minute (NOTPM)
19.8 minute duration
0 total unknown errors
Transaction 90th percentile Response Time (s) Max Response Time (s)
delivery 9.112 12.773
new-order 8.376 13.144
order-status 8.893 12.486
payment 8.640 13.703
stock-level 10.130 14.453
mysqlreport export:
__ Key _________________________________________________________________
Buffer usage 0 of 4.00M %Used: 0.00
Write ratio 0.00
Read ratio 0.00
__ Questions ___________________________________________________________
Total 969.15k 548.79/s
DMS 930.97k 527.16/s %Total: 96.06
Com_ 38.18k 21.62/s 3.94
COM_QUIT 16 0.01/s 0.00
-Unknown 10 0.01/s 0.00
Slow 23 0.01/s 0.00 %DMS: 0.00
DMS 930.97k 527.16/s 96.06
SELECT 452.29k 256.11/s 46.67 48.58
UPDATE 261.06k 147.83/s 26.94 28.04
INSERT 203.32k 115.13/s 20.98 21.84
DELETE 14.29k 8.09/s 1.47 1.54
REPLACE 0 0.00/s 0.00 0.00
Com_ 38.18k 21.62/s 3.94
commit 34.27k 19.41/s 3.54
set_option 2.90k 1.64/s 0.30
rollback 984 0.56/s 0.10
__ SELECT and Sort _____________________________________________________
Scan 20 0.01/s %SELECT: 0.00
Range 1.43k 0.81/s 0.32
Full join 0 0.00/s 0.00
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 0 0.00/s
Sort range 0 0.00/s
Sort mrg pass 0 0.00/s
__ Query Cache _________________________________________________________
Memory usage 8.57k of 16.00M %Used: 0.05
Block Fragmnt 100.00%
Hits 0 0.00/s
Inserts 1 0.00/s
Prunes 1 0.00/s
Insrt:Prune 1:1 0.00/s
Hit:Insert 0.00:1
__ Table Locks _________________________________________________________
Waited 0 0.00/s %Total: 0.00
Immediate 948.14k 536.89/s
__ Tables ______________________________________________________________
Open 70 of 200 %Cache: 35.00
Opened 82 0.05/s
__ Connections _________________________________________________________
Max used 11 of 100 %Max: 11.00
Total 18 0.01/s
__ Created Temp ________________________________________________________
Disk table 0 0.00/s
Table 1.45k 0.82/s
File 3 0.00/s
for sqlserver200,we can pass 100 warehouse test.
so,we think mysql's performance is better than sqlserver ,but we can't get the good test result.
we suppose you are know well the TPC-C.
mysql's store engine is InnoDB
follow is the os and hardware information:
os : Windows advance server 2000
CPU: 2X3.0Ghz
memory:2G
disk: only one 40G IDE disk
table struct:
for one warehouse,we stat all table list:
table name record numbers type record size(bytes) type table size(K bytes)
Warehouse 1 89 0.089
District 10 95 0.950
Customer 30k 655 19650
History 30k 46 1380
Order 30k 24 720
New-Order 9k 8 72
Order-Line 300k 54 16200
Stock 100k 306 30600
Item 100k 82 8200
SQL sentence:
create table warehouse ( w_id int not null, w_name varchar(10) null, w_street_1 varchar(20) null, w_street_2 varchar(20) null, w_city varchar(20) null, w_state char(2) null, w_zip char(9) null, w_tax float null, w_ytd float null, primary key(w_id) );
create table district ( d_id int not null, d_w_id int not null, d_name varchar(10) null, d_street_1 varchar(20) null, d_street_2 varchar(20) null, d_city varchar(20) null, d_state char(2) null, d_zip char(9) null, d_tax float null, d_ytd float null, d_next_o_id int null, primary key(d_w_id, d_id),foreign key(d_w_id)references warehouse(w_id) );
create table customer ( c_id int not null, c_d_id int not null, c_w_id int not null, c_first varchar(16) null, c_middle char(2) null, c_last varchar(16) null, c_street_1 varchar(20) null, c_street_2 varchar(20) null, c_city varchar(20) null, c_state char(2) null, c_zip char(9) null, c_phone char(16) null, c_since timestamp null, c_credit char(2) null, c_credit_lim float null, c_discount float null, c_balance float null, c_ytd_payment float null, c_payment_cnt int null, c_delivery_cnt int null, c_data varchar(500) null, primary key(c_w_id, c_d_id, c_id),foreign key(c_w_id,c_d_id) references district(d_w_id,d_id) );
create table history ( h_c_id int null, h_c_d_id int null, h_c_w_id int null, h_d_id int null, h_w_id int null, h_date timestamp null, h_amount float null, h_data varchar(24) null,foreign key(h_c_w_id,h_c_d_id,h_c_id) references customer(c_w_id,c_d_id,c_id),foreign key(h_w_id,h_d_id)references district(d_w_id,d_id) );
create table orders ( o_id int not null, o_d_id int not null, o_w_id int not null, o_c_id int null, o_entry_d timestamp null, o_carrier_id int null, o_ol_cnt int null, o_all_local int null, primary key(o_w_id, o_d_id, o_id),foreign key(o_w_id,o_d_id,o_c_id)references customer(c_w_id,c_d_id,c_id) );
create table new_order ( no_o_id int not null, no_d_id int not null, no_w_id int not null, primary key(no_w_id, no_d_id, no_o_id),foreign key(no_w_id,no_d_id,no_o_id)references orders(o_w_id,o_d_id,o_id) );
create table item ( i_id int not null, i_im_id int null, i_name varchar(24) null, i_price float null, i_data varchar(50) null, primary key(i_id) );
create table stock ( s_i_id int not null, s_w_id int not null, s_quantity int null, s_dist_01 varchar(24) null, s_dist_02 varchar(24) null, s_dist_03 varchar(24) null, s_dist_04 varchar(24) null, s_dist_05 varchar(24) null, s_dist_06 varchar(24) null, s_dist_07 varchar(24) null, s_dist_08 varchar(24) null, s_dist_09 varchar(24) null, s_dist_10 varchar(24) null, s_ytd int null, s_order_cnt int null, s_remote_cnt int null, s_data varchar(50) null, primary key(s_w_id, s_i_id),foreign key(s_w_id)references warehouse(w_id),foreign key(s_i_id)references item(i_id) );
create table order_line ( ol_o_id int not null, ol_d_id int not null, ol_w_id int not null, ol_number int not null, ol_i_id int null, ol_supply_w_id int null, ol_delivery_d timestamp null, ol_quantity int null, ol_amount float null, ol_dist_info char(24) null, primary key(ol_w_id, ol_d_id, ol_o_id, ol_number),foreign key(ol_w_id,ol_d_id,ol_o_id) references orders(o_w_id,o_d_id,o_id),foreign key(ol_supply_w_id,ol_i_id)references stock(s_w_id,s_i_id) );
index:
create index i_orders on orders (o_w_id, o_d_id, o_c_id, o_id);
create index i_customer on customer (c_w_id, c_d_id, c_last, c_first, c_id);
transaction information:
transaction type min percent(%) min keyboard time(sec) 90% response time(sec) min think time(sec)
1 New-Order N/a 18sec 5sec 12sec
2 Payment 43.0 3sec 5sec 12sec
3 Order-Status 4.0 2sec 5sec 10sec
4 Delivery 4.0 2sec 5sec 5sec
5 Stock-Level 4.0 2sec 20sec 5sec
transaction isolation level : SERIALIZABLE
my.ini:
default-storage-engine=INNODB
max_connections=100
query_cache_type=0
table_cache = 200
thread_concurrency = 8
tmp_table_size=10M
thread_cache_size=8
key_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=8M
sort_buffer_size=2M
[innodb]
innodb_data_file_path=ibdata1:8000M:autoextend
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8M
innodb_buffer_pool_size=1600M
innodb_log_file_size=500M
innodb_thread_concurrency=16
transaction-isolation=SERIALIZABLE
innodb_log_files_in_group = 3
innodb_file_io_threads=12
innodb_table_locks=0
innodb_lock_wait_timeout=15
--skip-innodb-doublewrite
--skip-innodb-checksums
test method:
for every warehouse simulate 10 users. and connect server via a connect pool(create by test client)
follow is all of SQL sentence,please don't change these:
1:delivery transaction(step by step)
begin
SELECT no_o_id FROM new_order WHERE no_d_id = %d AND no_w_id = %d ORDER BY no_d_id ASC;
DELETE FROM new_order WHERE no_o_id = %s AND no_d_id = %d AND no_w_id = %d;
SELECT o_c_id FROM orders WHERE o_id = %s AND o_d_id = %d AND o_w_id= %d;
UPDATE orders SET o_carrier_id = %d WHERE o_id = %s AND o_d_id = %d AND o_w_id = %d;
UPDATE order_line SET ol_delivery_d = %s WHERE ol_o_id = %s AND ol_d_id = %d AND ol_w_id = %d;
SELECT SUM(ol_amount) FROM order_line WHERE ol_o_id = %s AND ol_d_id = %d AND ol_w_id= %d;
UPDATE customer SET c_balance = c_balance + %f , c_delivery_cnt = c_delivery_cnt + 1 WHERE c_id = %s AND c_d_id = %d AND c_w_id = %d;
commit
2:new_order transacyion(step by step)
begin
SELECT c_discount, c_last, c_credit,w_tax FROM customer,warehouse WHERE w_id = %d AND c_w_id = w_id AND c_d_id = %d AND c_id = %d;
SELECT d_next_o_id, d_tax FROM district WHERE d_id = %d AND d_w_id = %d;
UPDATE district SET d_next_o_id = %d WHERE d_id = %d AND d_w_id = %d;
INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (%s, %d, %d, %d, %s, %d, %d);
INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (%s, %d, %d);
SELECT i_price, i_name, i_data FROM item WHERE i_id = %d;
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 = %d AND s_w_id = %d;
UPDATE stock SET s_quantity = %d, s_ytd=s_ytd + %d,s_order_cnt=s_order_cnt+1,s_remote_cnt=s_remote_cnt+%d WHERE s_i_id = %d AND s_w_id = %d;
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 (%s, %d, %d, %d, %d, %d, %d, %f,'%s');
commit
3:order_status transaction(step by step)
begin
SELECT count(c_id) FROM customer WHERE c_last = '%s' AND c_d_id = %d AND c_w_id = %d;
SELECT c_balance,c_first,c_middle,c_id FROM customer WHERE c_last = '%s' AND c_d_id = %d AND c_w_id = %d ORDER BY c_first;
SELECT c_balance,c_first, c_middle, c_last FROM customer WHERE c_id = %d AND c_d_id = %d AND c_w_id = %d;
SELECT o_id, o_carrier_id, o_entry_d FROM orders WHERE o_c_id = %d AND o_d_id = %d AND o_w_id = %d ORDER BY o_id DESC;
SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount,ol_delivery_d FROM order_line WHERE ol_o_id = %s AND ol_d_id = %d AND ol_w_id = %d;
commit
4:payment transaction(step by step)
begin
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name FROM warehouse WHERE w_id = %d;
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name FROM district WHERE d_w_id = %d AND d_id = %d;
SELECT count(c_id) FROM customer WHERE c_last = '%s' AND c_d_id = %d AND c_w_id = %d;
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 = %d AND c_d_id = %d AND c_last = '%s' ORDER BY c_first;
SELECT c_first, c_middle, c_last, 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 = %d AND c_d_id = %d AND c_id = %d;
SELECT c_data FROM customer WHERE c_w_id = %d AND c_d_id = %d AND c_id = %d;
UPDATE customer SET c_balance = %f, c_ytd_payment = c_ytd_payment + %f, c_payment_cnt = c_payment_cnt +1 , c_data='%s' WHERE c_w_id = %d AND c_d_id = %d AND c_id = %d;
UPDATE customer SET c_balance = %f, c_ytd_payment = c_ytd_payment + %f, c_payment_cnt = c_payment_cnt +1 WHERE c_w_id = %d AND c_d_id = %d AND c_id = %d;
UPDATE district SET d_ytd = d_ytd + %f WHERE d_w_id = %d AND d_id = %d;
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 (%d, %d, %d, %d, %d, %s, %f, '%s');
UPDATE warehouse SET w_ytd = w_ytd + %f WHERE w_id = %d;
commit
5:stock_level(step by step)
begin
SELECT d_next_o_id FROM district WHERE d_w_id = %d AND d_id = %d;
SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE ol_w_id = %d AND ol_d_id = %d AND ol_o_id < %d AND ol_o_id>=%d AND s_w_id = ol_w_id AND s_i_id = ol_i_id AND s_quantity < %d;
commit
our test result is :
for mysql 5.0.12 , we can pass the 60 Warehouse test use 10 connect pool and simulate 600users ,and the result list:
Transaction % Avg Response Time (s) Total Rollbacks %
delivery 4.02 1.109 7962 7 0.09
new-order 44.87 0.700 88944 1246 1.40
order-status 4.01 1.014 7939 0 0.00
payment 43.10 0.797 85439 3282 3.84
stock-level 4.00 1.544 7931 0 0.00
739.01 new-order transactions per minute (NOTPM)
119.8 minute duration
0 total unknown errors
Transaction 90th percentile Response Time (s) Max Response Time (s)
delivery 2.205 5.306
new-order 1.721 5.622
order-status 2.185 7.376
payment 1.894 7.839
stock-level 3.233 7.907
but we can't pass 70 warehouse test use 10 connect pool and simulate 700users. the result and mysqlreport list is:
test result:
Transaction % Avg Response Time (s) Total Rollbacks %
delivery 4.03 7.106 1212 1 0.08
new-order 44.99 6.396 13524 211 1.56
order-status 3.91 6.919 1175 0 0.00
payment 43.00 6.639 12926 571 4.42
stock-level 4.08 7.743 1226 0 0.00
679.33 new-order transactions per minute (NOTPM)
19.8 minute duration
0 total unknown errors
Transaction 90th percentile Response Time (s) Max Response Time (s)
delivery 9.112 12.773
new-order 8.376 13.144
order-status 8.893 12.486
payment 8.640 13.703
stock-level 10.130 14.453
mysqlreport export:
__ Key _________________________________________________________________
Buffer usage 0 of 4.00M %Used: 0.00
Write ratio 0.00
Read ratio 0.00
__ Questions ___________________________________________________________
Total 969.15k 548.79/s
DMS 930.97k 527.16/s %Total: 96.06
Com_ 38.18k 21.62/s 3.94
COM_QUIT 16 0.01/s 0.00
-Unknown 10 0.01/s 0.00
Slow 23 0.01/s 0.00 %DMS: 0.00
DMS 930.97k 527.16/s 96.06
SELECT 452.29k 256.11/s 46.67 48.58
UPDATE 261.06k 147.83/s 26.94 28.04
INSERT 203.32k 115.13/s 20.98 21.84
DELETE 14.29k 8.09/s 1.47 1.54
REPLACE 0 0.00/s 0.00 0.00
Com_ 38.18k 21.62/s 3.94
commit 34.27k 19.41/s 3.54
set_option 2.90k 1.64/s 0.30
rollback 984 0.56/s 0.10
__ SELECT and Sort _____________________________________________________
Scan 20 0.01/s %SELECT: 0.00
Range 1.43k 0.81/s 0.32
Full join 0 0.00/s 0.00
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 0 0.00/s
Sort range 0 0.00/s
Sort mrg pass 0 0.00/s
__ Query Cache _________________________________________________________
Memory usage 8.57k of 16.00M %Used: 0.05
Block Fragmnt 100.00%
Hits 0 0.00/s
Inserts 1 0.00/s
Prunes 1 0.00/s
Insrt:Prune 1:1 0.00/s
Hit:Insert 0.00:1
__ Table Locks _________________________________________________________
Waited 0 0.00/s %Total: 0.00
Immediate 948.14k 536.89/s
__ Tables ______________________________________________________________
Open 70 of 200 %Cache: 35.00
Opened 82 0.05/s
__ Connections _________________________________________________________
Max used 11 of 100 %Max: 11.00
Total 18 0.01/s
__ Created Temp ________________________________________________________
Disk table 0 0.00/s
Table 1.45k 0.82/s
File 3 0.00/s
for sqlserver200,we can pass 100 warehouse test.
so,we think mysql's performance is better than sqlserver ,but we can't get the good test result.
Subject
Views
Written By
Posted
Can we upgrade TPC-C performance test result for mysql VS Sqlserver2000
2598
October 17, 2005 02:09AM
1368
October 23, 2005 07:02PM
1394
October 24, 2005 05:21PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.