|
For a test, I compared Oracle 10.2.0.1 on Windows and mySQL 5.0 on Windows. The test was based on 50 concurrent sessions randomly selecting a parent row and any children related to it. After warming up the cache for each database, mySQL averaged response times of about 75% slower than Oracle.
In Oracle, we created two tables that, related to each other by a parent child relationship.
|
create table parent(a number primary key);
create table child(b number, a number references parent(a));
begin
for i in 0..10000 loop
insert into parent values(i);
end loop;
end;
/
begin
for i in 0..100000 loop
insert into child values(i,mod(i,10000));
end loop;
end;
/
create index child_a on child(a);
In mySQL, do the following...
create table parent(a int);
create table child(b int, a int references parent(a));
set autocommit=0;
delimiter //
drop procedure if exists populatedata
//
create procedure populatedata()
begin
declare l_loop int default 0;
loop1: loop
set l_loop := l_loop + 1;
IF l_loop >= 10000 THEN
leave loop1;
else
insert into parent values(l_loop);
end if;
end loop loop1;
end
//
call populatedata();
//
commit;
//
drop procedure if exists populatedata
//
create procedure populatedata()
begin
declare l_loop int default 0;
loop1: loop
set l_loop := l_loop + 1;
IF l_loop >= 100000 THEN
leave loop1;
else
insert into child values(l_loop,mod(l_loop,10000));
end if;
end loop loop1;
end
//
call populatedata();
//
commit;
//
create index parent_a on parent(a);
create index child_a on child(a);
Here is the source code used for the performance test.
After running this test, I noticed two things:
1) Oracle was much faster at getting the data
2) mySQL was much faster at creating and releasing connections
C:\SCRIPTS\java>java runPerfChecker rep rep oracle 50
Using URL oracle
Starting 1
Starting 2
Starting 3
Starting 4
Starting 5
Starting 6
Starting 7
Starting 8
Starting 9
Starting 10
Starting 11
Starting 12
Starting 13
Starting 14
Starting 15
Starting 16
Starting 17
Starting 18
Starting 19
Starting 20
Starting 21
Starting 22
Starting 23
Starting 24
Starting 25
Starting 26
Starting 27
Starting 28
Starting 29
Starting 30
Starting 31
Starting 32
Starting 33
Starting 34
Starting 35
Starting 36
Starting 37
Starting 38
Starting 39
Starting 40
Starting 41
Starting 42
Starting 43
Starting 44
Starting 45
Starting 46
Starting 47
Starting 48
Starting 49
Starting 50
1 431
2 211
3 301
4 340
5 160
6 130
7 150
8 240
0 150
10 180
11 561
12 210
13 140
14 210
15 151
23 461
20 150
22 210
21 220
16 361
17 90
18 150
19 121
24 230
25 381
26 170
27 110
28 141
29 170
30 150
31 191
32 450
33 180
34 231
35 191
36 551
37 661
38 220
39 311
40 271
41 160
42 160
43 160
48 220
47 130
46 190
45 241
44 170
49 130
50 130
C:\SCRIPTS\java>java runPerfChecker root welcome mysql 50
Using URL mysql
Starting 1
Starting 2
Starting 3
Starting 4
Starting 5
Starting 6
Starting 7
Starting 8
Starting 9
Starting 10
Starting 11
Starting 12
Starting 13
Starting 14
Starting 15
Starting 16
Starting 17
Starting 18
Starting 19
Starting 20
Starting 21
Starting 22
Starting 23
Starting 24
Starting 25
Starting 26
Starting 27
Starting 28
Starting 29
Starting 30
Starting 31
Starting 32
Starting 33
Starting 34
Starting 35
Starting 36
Starting 37
Starting 38
Starting 39
Starting 40
Starting 41
Starting 42
Starting 43
Starting 44
Starting 45
Starting 46
Starting 47
Starting 48
Starting 49
Starting 50
1 310
2 421
3 461
4 471
5 421
7 381
10 180
6 651
11 160
9 480
12 290
8 732
13 611
14 371
16 351
0 631
20 470
18 651
19 651
17 661
22 591
28 331
23 501
31 331
27 681
24 951
30 922
29 1122
21 1522
26 1572
37 1031
35 1171
32 1272
42 1102
40 1162
38 1182
47 962
45 1052
44 1202
36 1342
34 1472
49 962
25 1903
39 1362
33 1532
50 992
48 1092
41 1392
43 1362
46 1252
C:\SCRIPTS\java>
|