Flying with MySQL and Oracle

In my work, I use both MySQL and Oracle. MySQL is like a fighter jet - sleek, fast, powerful. It carries a smaller payload but packs a wonderful punch and is excellent for analyzing Taliban data. Oracle is like a big B-52 bomber. Ancient, not very pretty, and lumbering, it carries such a big payload that even Osama cannot hide from its powerful data-crunching abilities.

One of the headaches we database pilots have is scoping the size of project, and making a recommendation on what database to use. Recently I conducted some benchmarks on the two databases to investigate how best to perform complex processing of millions of records stored in Oracle in the shortest possible time. I considered using in-memory data structures (eg. bypassing the database altogether), and off-loading the data into a faster database such as MySQL.

Here's what I found:

1. For high speed data access for a single user, use your own data structures, don't use a database. However make sure you have enough memory for all your data.
2. MySQL without transactions is amazingly fast, but still slower than rolling your own data structures.
3. MySQL heap (in-memory) tables did not give an exponential improvement in speed like I hoped - it seems that MyISAM table caching is quite efficient.
4. Oracle as expected is very scalable, and the results on old Sun hardware are pretty good, but the results on the Win2000 workstation shows that it requires a lot of tuning, otherwise the results are horrible, and you need to be an expert to tune it. Compared to MySQL, the strength of Oracle is still in handling large numbers of transactions safely, which these benchmarks do not test. I hope to get Oracle running on some high-end hardware in the near future and will retest then.
5. Using bind variables is actually faster than sending straight SQL in Oracle (formerly I said it was six times slower, but I found a bug in my benchmark - turns out I was commiting on every insert).
6. The tight integration of PL/SQL and Oracle is a big advantage. Converting the PHP code to PL/SQL code contributed to a 30% increase in insert performance for Oracle and a 10 times improvement in select speed.
7. At first, I was surprised at the PL/SQL results. The Windows 2000 select benchmark was faster than the Sun server, while for inserts, the Sun server was better. I finally figured it out when I realized that the Windows 2000 notebook has a CPU that is twice as fast as the Sun server, so the selects are twice as fast too as the selects are CPU bound when all the data is cached in RAM. Conversely, the Sun server has several hard disks to spread the insert i/o over, so the Sun server beats Windows 2000 flat.
8. Another hint derived from the benchmarks is to make sure that all servers are physically close to each other and on the same network. Because in the last test between PHP running on a Win2000 workstation and an Oracle Sun server had to go through a router, the throughput was quite poor.
9. The Oracle select timings become slower relative to the inserts the more complex the network gets, probably because selects require a reply, inserts do not.
10. Oracle by default does not return the number of records found in a query. The ADOdb library emulates this feature. This was found to slow down performance by at least 20%. We have modified the library to avoid counting when not required. So the general advice is to set $ADODB_COUNTRECS = false when possible. Added 15 Feb 2002.

Conclusions
If I used my own home-brewed database, bypassing MySQL and Oracle, it would be like flying a SR-71 Blackbird, a plane custom built for speed. However there are benefits to using an established system like MySQL or Oracle - better maintainability and ease-of-use. I guess it boils down to how much you are willing to pay and how much time you want to spend - so are you flying coach or private jet?

Server	100,000 inserts time (s)	Inserts/Sec	50,000 selects time (s)	Selects/Sec	DBMS Hardware
PHP Assoc Arrays (Mem used: 45Mb)	1.36	73,529	0.44	113,600	Win2000
MySQL 3.23 Heap (Mem used: 10Mb)	23.82	4,198	21.96	2,276	Win2000
MySQL 3.23 MyISAM	33.42	2,992	25.91	1,930	Win2000
PostgreSQL 7.2.3 added 2003	69.74	1434	58.33	857	PostgreSQL 7.2.3 on Win2000
Firebird 1.0.2 added 2003	no-bind: 137.67 bind: 43.08	726 2321	125.33 68.39	399 731	PHP and Firebird running on Win2000
Oracle 8.1.7 (PHP)	no-bind: 114.32 bind: 75.14	875 1331	77.21	648	E450 Sun Server
Oracle 8.1.7 (100% PL/SQL)	50.60	1976	11.62	4310	E450 Sun Server
Oracle 8.1.7 and PHP	no-bind: 201.94 bind: 96.94	495 1034	89.24 57.41	560 871	Oracle on Win2000
Oracle 8.1.7 (100% PL/SQL)	73.00	1370	5.82	8591	Oracle on Win2000
Oracle 8.1.7	259.81	385	232.77	215	PHP running on Win2000 and Oracle on Sun Server on same network
Oracle 8.1.7	1002.29	100	606.03	83	PHP running on Win2000 and Oracle on Sun Server through a router

Changes

11 March 2002
Tests were done using PHP 4.1.1 and the ADOdb class library, except for the Oracle bind variable test, which used the oci8 libraries directly for speed.

09 Feb 2003
Added benchmarks for PostgreSQL and Interbase/Firebird with 100,000 recs inserted deleted.

Also benchmarked MySQL InnoDB and BDB with for 1000 recs inserted/selected. Results below:

       Inserts/Sec   Selects/Sec
HEAP      4150         2395
MYISAM    2968         2079  
INNODB    3042         2051
BDB       2602         2107

Also tested 1000 records with MS SQL Server 2000:

       Inserts/Sec   Selects/Sec
MSSQL    1550          931
ODBC     1349          402 (non-bind)

12 Feb 2003
Corrected PL/SQL benchmark calculation for selects/sec.

15 Feb 2003
Code for Oracle on PHP (Win2000) revised upwards due to improvements in ADOdb 3.12.

Create Statements

Here are the Oracle statements used (the MySQL ones are similar):

create table test5000(name varchar(32) not null, price numeric(16,2), stock integer)

create unique index test_idx on test5000 (name) TABLESPACE kbidx

Insert Statement

Not using bind variables (MySQL and Oracle):

$DB->BeginTrans();
for ($i=0; $i < $NUMRECS; $i++) {
	$DB->Execute(
        "insert into test5000 (name,price,stock) values ('a name $i',$i,$i)");
}

$DB->CommitTrans();

Using bind variables:

$DB->BeginTrans();
$a = '';
$b = 0;
$c = 0;
$stmt = OCIParse($DB->_connectionID,
        "insert into test5000 (name,price,stock) values (:a,:b,:c)");
OCIBindByName($stmt,":a",&$a,32);
OCIBindByName($stmt,":b",&$b,32);
OCIBindByName($stmt,":c",&$c,32);
for ($i=0; $i < $NUMRECS; $i++) {
	$a = "a name $i";
	$b = $i;
	$c = $i;
	$e = OCIExecute($stmt,OCI_DEFAULT); # bug-fix, added OCI_DEFAULT
	if (!$e) {
		print $DB->ErrorMsg();
		break;
	}

}
$DB->CommitTrans();

Select Statement

mt_srand(20);
for ($i=0, $max = $NUMRECS/2; $i < $max; $i++) {
	$n = 'a name '.mt_rand(0,$NUMRECS-1);
    # retrieve only 1 record
	$arr = $DB->getone(
   "select price from test5000 where name='$n'");
	if (!is_numeric($arr)) {
		print "Error searching for '$n'\n";
		break;
	}
}

PHP Associative Array Code

function getmicrotime()
{ 
    list($usec, $sec) = explode(" ",microtime()); 
    return ((float)$usec + (float)$sec); 
} 


$NUMRECS = 100000;


$arr = array();


$start = getmicrotime();
for ($i=0; $i < $NUMRECS; $i++) {

	$arr["a name $i"] = array($i,$i);
}
$end = getmicrotime() - $start;


printf("Time %4.4f to insert $i records
n",$end);


mt_srand(10);
$start = getmicrotime();
for ($i=0, $max = $NUMRECS/2; $i < $max; $i++) {
	$n = 'a name '.mt_rand(0,$NUMRECS-1);
	$arr2 = $arr[$n];
	if (!$arr2) {

		print "Error searching for $n
n";
	}
}
$end = getmicrotime() - $start;
printf("Time %4.4f to search for $i records
n",$end);

PL/SQL Insert Benchmark

CREATE OR REPLACE PROCEDURE bench(maxd in pls_integer) IS


startd number;
endd number;
i PLS_INTEGER;
a PLS_INTEGER;
b PLS_INTEGER;
c PLS_INTEGER;
j PLS_INTEGER;
pr numeric(16,2);
txt varchar(32);
begin


/*
drop table test5000;
create table test5000(name varchar(32) not null, price numeric(16,2), stock integer);
create unique index test_idx on test5000 (name);
*/
delete from test5000;
commit;


startd := dbms_utility.get_time;
i := 0;


while (i < maxd) loop
	  a := i;
	  b := i;
	  c := i;
	  insert into test5000 (name, price, stock) 
	  		 values ('Test name '|| to_char(a), b, c);
	  i := i + 1;
end loop;
commit;
endd := dbms_utility.get_time - startd;


outp('secs to insert ' || maxd || ' records = '||to_char(endd/100.00));



dbms_random.initialize(10);
j := maxd/2;
startd := dbms_utility.get_time;
for i in 1 ..j loop
	txt := 'Test name '||to_char(mod(abs(dbms_random.random),maxd));
	--outp(to_char(i) || ' ' || txt);
	select price into pr from test5000 where name=txt;
end loop;


endd := dbms_utility.get_time - startd;


outp('secs to search ' || j || ' records = '||to_char(endd/100.00));
EXCEPTION
when others then
		 outp(txt);
		 raise;
END bench;
/

Hardware Tested

E450 Sun Server: PHP 4.1.1 and Oracle on SunOS, 2xSparc 480Mhz, 2 Gb RAM, 6x36Gb HD

Win2000 workstation: PHP 4.1.1 and Oracle on Win2000, Pentium III 833 Mhz, 256 Mb RAM,1x20 Gb HD