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