dear sir:
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.
we use InnoDB as mysql's store engine.
follow is the os and hardware information:
=09os : Windows advance server 2000
=09CPU: 2X3.0Ghz
=09memory:2G
=09disk: only one 40G IDE disk
table struct:
=09for one warehouse,we stat all table list:
=09table name=09| record numbers |=09type record size(bytes)=09| type
tab=
le
size(K bytes)
=09 Warehouse=09 | 1=09 | 89=09 |
0.0=
89
District=09 | 10=09 | 95=09 |
0.95=
0
Customer=09 | 30k=09 | 655=09 |
1965=
0
History=09 | 30k=09 | 46=09 | 1380
Order=09 | 30k=09 | 24=09 | 720
New-Order=09 | 9k=09 | 8=09 | 72
Order-Line=09| 300k=09 | 54=09 |
1620=
0
Stock=09 | 100k=09 | 306=09 |
30=
600
Item=09 | 100k=09 | 82=09 | 8200
=09SQL sentence:
=09create 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) );
=09create 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) );
=09create 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) );
=09create 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) );
=09create 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) );
=09create 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) );
=09create 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) );
=09create 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) );
=09create 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) );
=09
=09index:
=09create index i_orders on orders (o_w_id, o_d_id, o_c_id, o_id);
=09create index i_customer on customer (c_w_id, c_d_id, c_last, c_first,
c_=
id);
=09
=09
transaction information:
=09 transaction type | min percent(%) | min keyboard time(sec) | =20
90% response time(sec) | min think time(sec)
=091 New-Order | N/a | 18sec | =20
5sec | 12sec
=092 Payment | 43.0 | 3sec | =20
5sec | 12sec
=093 Order-Status | 4.0 | 2sec=09 | =
=20
5sec=09 | 10sec
=094 Delivery | 4.0=09 | 2sec=09 |
=
=20
5sec=09 | 5sec
=095 Stock-Level=09 | 4.0=09 | 2sec=09 |
=
=20
20sec=09 | 5sec
=09
transaction isolation level : SERIALIZABLE
my.ini:
=09default-storage-engine=3DINNODB
=09max_connections=3D100
=09query_cache_type=3D0
=09table_cache =3D 200
=09thread_concurrency =3D 8
=09tmp_table_size=3D10M
=09thread_cache_size=3D8
=09key_buffer_size=3D4M
=09read_buffer_size=3D2M
=09read_rnd_buffer_size=3D8M
=09sort_buffer_size=3D2M
=09[innodb]
=09innodb_data_file_path=3Dibdata1:8000M:autoextend
=09innodb_additional_mem_pool_size=3D20M
=09innodb_flush_log_at_trx_commit=3D0
=09innodb_log_buffer_size=3D8M
=09innodb_buffer_pool_size=3D1600M
=09innodb_log_file_size=3D500M
=09innodb_thread_concurrency=3D16
=09transaction-isolation=3DSERIALIZABLE
=09innodb_log_files_in_group =3D 3
=09innodb_file_io_threads=3D12
=09innodb_table_locks=3D0
=09innodb_lock_wait_timeout=3D15
=09
=09--skip-innodb-doublewrite
=09--skip-innodb-checksums
=09
=09
test method:
=09for every warehouse simulate 10 users. and connect server via a
connect pool(create by test client)
=09follow is all of SQL sentence,please don't change these:
=091:delivery transaction(step by step)
=09=09=09begin
=09=09=09SELECT no_o_id FROM new_order WHERE no_d_id =3D %d AND no_w_id
=3D=
%d
ORDER BY no_d_id ASC;
=09=09=09DELETE FROM new_order WHERE no_o_id =3D %s AND no_d_id =3D %d AND
=
no_w_id =3D %d;
=09=09=09SELECT o_c_id FROM orders WHERE o_id =3D %s AND o_d_id =3D %d AND
=
o_w_id=3D %d;
=09=09=09UPDATE orders SET o_carrier_id =3D %d WHERE o_id =3D %s AND
o_d_id=
=3D %d
AND o_w_id =3D %d;
=09=09=09UPDATE order_line SET ol_delivery_d =3D %s WHERE ol_o_id =3D %s
AN=
D
ol_d_id =3D %d AND ol_w_id =3D %d;
=09=09=09SELECT SUM(ol_amount) FROM order_line WHERE ol_o_id =3D %s AND
ol_d_id =3D %d AND ol_w_id=3D %d;
=09=09=09UPDATE customer SET c_balance =3D c_balance + %f ,
c_delivery_cnt=
=3D
c_delivery_cnt + 1 WHERE c_id =3D %s AND c_d_id =3D %d AND c_w_id =3D %d;
=09=09=09commit
=092:new_order transacyion(step by step)
=09=09=09begin
=09=09=09SELECT c_discount, c_last, c_credit,w_tax FROM customer,warehouse
WHERE w_id =3D %d AND c_w_id =3D w_id AND c_d_id =3D %d AND c_id =3D %d;
=09=09=09SELECT d_next_o_id, d_tax FROM district WHERE d_id =3D %d AND
d_w_=
id =3D %d;
=09=09=09UPDATE district SET d_next_o_id =3D %d WHERE d_id =3D %d AND
d_w_i=
d =3D %d;
=09=09=09INSERT 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);
=09=09=09INSERT INTO new_order (no_o_id, no_d_id, no_w_id) VALUES (%s, %d,
=
%d);
=09=09=09SELECT i_price, i_name, i_data FROM item WHERE i_id =3D %d;
=09=09=09SELECT 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_di=
st_08,s_dist_09,s_dist_10
FROM stock WHERE s_i_id =3D %d AND s_w_id =3D %d;
=09=09=09UPDATE stock SET s_quantity =3D %d, s_ytd=3Ds_ytd +
%d,s_order_cnt=3Ds_order_cnt+1,s_remote_cnt=3Ds_remote_cnt+%d WHERE s_i_id
=3D %d AND s_w_id =3D %d;
=09=09=09INSERT 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');
=09=09=09commit
=093:order_status transaction(step by step)
=09=09=09begin
=09=09=09SELECT count(c_id) FROM customer WHERE c_last =3D '%s' AND c_d_id
=
=3D
%d AND c_w_id =3D %d;
=09=09=09SELECT c_balance,c_first,c_middle,c_id FROM customer WHERE c_last
=
=3D
'%s' AND c_d_id =3D %d AND c_w_id =3D %d ORDER BY c_first;
=09=09=09SELECT c_balance,c_first, c_middle, c_last FROM customer WHERE
c_i=
d
=3D %d AND c_d_id =3D %d AND c_w_id =3D %d;
=09=09=09SELECT o_id, o_carrier_id, o_entry_d FROM orders WHERE o_c_id =3D
=
%d
AND o_d_id =3D %d AND o_w_id =3D %d ORDER BY o_id DESC;
=09=09=09SELECT ol_i_id, ol_supply_w_id, ol_quantity,
ol_amount,ol_delivery_d FROM order_line WHERE ol_o_id =3D %s AND ol_d_id
=3D %d AND ol_w_id =3D %d;
=09=09=09commit
=094:payment transaction(step by step)
=09=09=09begin
=09=09=09SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name
FRO=
M
warehouse WHERE w_id =3D %d;
=09=09=09SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name
FROM
district WHERE d_w_id =3D %d AND d_id =3D %d;
=09=09=09SELECT count(c_id) FROM customer WHERE c_last =3D '%s' AND c_d_id
=
=3D
%d AND c_w_id =3D %d;
=09=09=09SELECT 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 =3D %d AND c_d_id =3D %d AND c_last =3D
'%s' ORDER BY c_first;
=09=09=09SELECT 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 =3D %d AND c_d_id =3D %d AND
c_id =3D %d;
=09=09=09SELECT c_data FROM customer WHERE c_w_id =3D %d AND c_d_id =3D
%d=
AND c_id =3D %d;
=09=09=09UPDATE customer SET c_balance =3D %f, c_ytd_payment =3D
c_ytd_paym=
ent +
%f, c_payment_cnt =3D c_payment_cnt +1 , c_data=3D'%s' WHERE c_w_id =3D %d
AND c_d_id =3D %d AND c_id =3D %d;
=09=09=09UPDATE customer SET c_balance =3D %f, c_ytd_payment =3D
c_ytd_paym=
ent +
%f, c_payment_cnt =3D c_payment_cnt +1 WHERE c_w_id =3D %d AND c_d_id
=3D
%d AND c_id =3D %d;
=09=09=09UPDATE district SET d_ytd =3D d_ytd + %f WHERE d_w_id =3D %d AND
d=
_id =3D %d;
=09=09=09INSERT 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');
=09=09=09UPDATE warehouse SET w_ytd =3D w_ytd + %f WHERE w_id =3D %d;
=09=09=09commit
=095:stock_level(step by step)
=09=09=09begin
=09=09=09SELECT d_next_o_id FROM district WHERE d_w_id =3D %d AND d_id =3D
=
%d;
=09=09=09SELECT count(DISTINCT (s_i_id) ) FROM order_line, stock WHERE
ol_w_id =3D %d AND ol_d_id =3D %d AND ol_o_id < %d AND ol_o_id>=3D%d AND
s_w_id =3D ol_w_id AND s_i_id =3D ol_i_id AND s_quantity < %d;
=09=09=09commit
=09
our test result is :
=09for mysql 5.0.12 , we can pass the 60 Warehouse test use 10 connect
pool and simulate 600users ,and the result list:
=09=09Transaction | % | Avg Response Time (s) | Total |
Rollback=
s | %
=09=09delivery | 4.02 | 1.109 | 7962 |
=
7 | 0.09
=09=09new-order | 44.87 | 0.700 | 88944 |
124=
6 | 1.40
=09=09order-status | 4.01 | 1.014 | 7939 |
=
0 | 0.00
=09=09payment | 43.10 | 0.797 | 85439 |
328=
2 | 3.84
=09=09stock-level | 4.00 | 1.544 | 7931 |
=
0 | 0.00
=09
=09=09739.01 new-order transactions per minute (NOTPM)
=09=09119.8 minute duration
=09=090 total unknown errors
=09
=09=09Transaction | 90th percentile Response Time (s) | Max Response
Ti=
me (s)
=09=09delivery | 2.205 |
=
5.306
=09=09new-order | 1.721 |
=
5.622
=09=09order-status | 2.185 |
=
7.376
=09=09payment | 1.894 |
=
7.839
=09=09stock-level | 3.233 |
=
7.907
=09but we can't pass 70 warehouse test use 10 connect pool and simulate
700users. the result and mysqlreport list is:
=09=09test result:
=09=09=09Transaction | % | Avg Response Time (s) | Total |
Rollbacks | %
=09=09=09delivery | 4.03 | 7.106 | 1212 |
=
=20
1 | 0.08
=09=09=09new-order | 44.99 | 6.396 | 13524 |
=
=20
211 | 1.56
=09=09=09order-status | 3.91 | 6.919 | 1175 |
=
=20
0 | 0.00
=09=09=09payment | 43.00 | 6.639 | 12926 |
=
=20
571 | 4.42
=09=09=09stock-level | 4.08 | 7.743 | 1226 |
=
=20
0 | 0.00
=09=09=09
=09=09=09679.33 new-order transactions per minute (NOTPM)
=09=09=0919.8 minute duration
=09=09=090 total unknown errors
=09=09=09
=09=09=09Transaction | 90th percentile Response Time (s) | Max
Response=
Time (s)
=09=09=09delivery | 9.112 |
=
12.773
=09=09=09new-order | 8.376 |
=
13.144
=09=09=09order-status | 8.893 |
=
12.486
=09=09=09payment | 8.640 |
=
13.703
=09=09=09stock-level | 10.130 |
=
14.453
=09=09=09
=09=09mysqlreport export:
=09=09=09__ Key
___________________________________________________________=
______
=09=09=09Buffer usage 0 of 4.00M %Used: 0.00
=09=09=09Write ratio 0.00
=09=09=09Read ratio 0.00
=09
=09=09=09__ Questions
_____________________________________________________=
______
=09=09=09Total 969.15k 548.79/s
=09 =09=09DMS 930.97k 527.16/s %Total: 96.06
=09 =09=09Com_ 38.18k 21.62/s 3.94
=09 =09=09COM_QUIT 16 0.01/s 0.00
=09 =09=09-Unknown 10 0.01/s 0.00
=09=09=09Slow 23 0.01/s 0.00 %DMS: 0.00
=09=09=09DMS 930.97k 527.16/s 96.06
=09 =09=09SELECT 452.29k 256.11/s 46.67 48.58
=09 =09=09UPDATE 261.06k 147.83/s 26.94 28.04
=09 =09=09INSERT 203.32k 115.13/s 20.98 21.84
=09 =09=09DELETE 14.29k 8.09/s 1.47 1.54
=09 =09=09REPLACE 0 0.00/s 0.00 0.00
=09=09=09Com_ 38.18k 21.62/s 3.94
=09 =09=09commit 34.27k 19.41/s 3.54
=09 =09=09set_option 2.90k 1.64/s 0.30
=09 =09=09rollback 984 0.56/s 0.10
=09
=09=09=09__ SELECT and Sort
_______________________________________________=
______
=09=09=09Scan 20 0.01/s %SELECT: 0.00
=09=09=09Range 1.43k 0.81/s 0.32
=09=09=09Full join 0 0.00/s 0.00
=09=09=09Range check 0 0.00/s 0.00
=09=09=09Full rng join 0 0.00/s 0.00
=09=09=09Sort scan 0 0.00/s
=09=09=09Sort range 0 0.00/s
=09=09=09Sort mrg pass 0 0.00/s
=09=09=09
=09=09=09__ Query Cache
___________________________________________________=
______
=09=09=09Memory usage 8.57k of 16.00M %Used: 0.05
=09=09=09Block Fragmnt 100.00%
=09=09=09Hits 0 0.00/s
=09=09=09Inserts 1 0.00/s
=09=09=09Prunes 1 0.00/s
=09=09=09Insrt:Prune 1:1 0.00/s
=09=09=09Hit:Insert 0.00:1
=09=09=09
=09=09=09__ Table Locks
___________________________________________________=
______
=09=09=09Waited 0 0.00/s %Total: 0.00
=09=09=09Immediate 948.14k 536.89/s
=09=09=09
=09=09=09__ Tables
________________________________________________________=
______
=09=09=09Open 70 of 200 %Cache: 35.00
=09=09=09Opened 82 0.05/s
=09=09=09
=09=09=09__ Connections
___________________________________________________=
______
=09=09=09Max used 11 of 100 %Max: 11.00
=09=09=09Total 18 0.01/s
=09=09=09
=09=09=09__ Created Temp
__________________________________________________=
______
=09=09=09Disk table 0 0.00/s
=09=09=09Table 1.45k 0.82/s
=09=09=09File 3 0.00/s
=09
=09
=09for 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.
best regard
george yang
10/24/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
