Oracle 8.1.7 vs PostgreSQL 7.03: Benchmarks

Home	Blog	Bio	Projects	Contact	Latest Blog (new site): How to Get to Genius

JamesThornton.com $-\>$ ACS $-\>$ Oracle 8.1.7 vs PostgreSQL 7.03

Oracle 8.1.7 vs PostgreSQL 7.03: Benchmarks

Test System

PIII 500 MHz
256 MB RAM
20 GB IDE 8ms RAID 1

During my installation of PostgreSQL, I increased the blocksize to 16384 -- no additional tuning has been done.
Summary

Time in min. per 10,000 inserts or updates:

Normal autocommit on fsync off

insert update insert update insert update

PostgreSQL 7.03 5:16 6:46 n/a n/a 0:10 0:18

Oracle results omitted due to the OTN Developer License Agreement restrictions on benchmarking.
SQL Statements

Tests results are based on 10,000 inserts or updates in various modes. Two PERL scripts, generate-insert.pl and generate-update.pl, were used to generate the insert and update SQL statements. Also, see the seminal document -- DBMS Benchmark -- by Philip Greenspun.
insert-test.sql

insert into access_log values ( '1', 'yada 1','klsdfkjl'); insert into access_log values ( '2', 'yada 2','klsdfkjl'); insert into access_log values ( '3', 'yada 3','klsdfkjl'); . . . insert into access_log values ( '9997', 'yada 9997','klsdfkjl'); insert into access_log values ( '9998', 'yada 9998','klsdfkjl'); insert into access_log values ( '9999', 'yada 9999','klsdfkjl');

update-test.sql

update access_log set page_id = '1 yowsa' where the_key = '1'; update access_log set page_id = '2 yowsa' where the_key = '2'; update access_log set page_id = '3 yowsa' where the_key = '3'; . . . update access_log set page_id = '9997 yowsa' where the_key = '9997'; update access_log set page_id = '9998 yowsa' where the_key = '9998'; update access_log set page_id = '9999 yowsa' where the_key = '9999';

NOTE: If someone wanted to try this on an Oracle system with autocommit set to on, then they could simply put the statement:
autocommit on

on the first line of insert-test.sql and update-test.sql (after they are generated).
Oracle 8.1.7 Results

Oracle Insert (autocommit off)

$ time sqlplus james/password < insert-test.sql > /dev/null

Oracle results omitted due to the OTN Developer License Agreement restrictions on benchmarking.
SQL> select count(*) from access_log; COUNT(*) ---------- 9999

Oracle Update (autocommit off)

$ time sqlplus james/password < update-test.sql > /dev/null

Oracle results omitted due to the OTN Developer License Agreement restrictions on benchmarking.
Oracle Insert (autocommit on)

$ time sqlplus james/password < insert-test.sql > /dev/null

Oracle results omitted due to the OTN Developer License Agreement restrictions on benchmarking.
Oracle Update (autocommit on)

$ time sqlplus james/password < update-test.sql > /dev/null

Oracle results omitted due to the OTN Developer License Agreement restrictions on benchmarking.
PostgreSQL 7.03

PostgreSQL Insert (fsync on)

$ time psql james < insert-test.sql > /dev/null 0.94user 0.32system 5:16.42elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (205major+21minor)pagefaults 0swaps james=# select count(*) from access_log; count ------- 9999 (1 row)

PostgreSQL Update (fsync on)

$ time psql james < update-test.sql > /dev/null 0.89user 0.39system 6:46.19elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (205major+21minor)pagefaults 0swaps

PostgreSQL Insert (fsync off)

$ time psql james < insert-test.sql > /dev/null 0.84user 0.29system 0:10.91elapsed 10%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (205major+21minor)pagefaults 0swaps

PostgreSQL Update (fsync off)

$ time psql james < update-test.sql > /dev/null 0.75user 0.27system 0:18.00elapsed 5%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (373major+21minor)pagefaults 0swaps

About fsync from the PostgreSQL FAQ

To turn fsync off, start postmaster using -o -F:
Example: /usr/local/pgsql/bin/postmaster -o -F -D /usr/local/pgsql/data

http://www.postgresql.org/docs/faq-english.html
1.14) How does PostgreSQL compare to other DBMS's?
Performance
PostgreSQL runs in two modes. Normal fsync mode flushes every completed transaction to disk, guaranteeing that if the OS crashes or loses power in the next few seconds, all your data is safely stored on disk. In this mode, we are slower than most commercial databases, partly because few of them do such conservative flushing to disk in their default modes. In no-fsync mode, we are usually faster than commercial databases, though in this mode, an OS crash could cause data corruption. We are working to provide an intermediate mode that suffers less performance overhead than full fsync mode, and will allow data integrity within 30 seconds of an OS crash.
3.10) How do I tune the database engine for better performance?
Certainly, indices can speed up queries. The EXPLAIN command allows you to see how PostgreSQL is interpreting your query, and which indices are being used.
If you are doing a lot of INSERTs, consider doing them in a large batch using the COPY command. This is much faster than individual INSERTS. Second, statements not in a BEGIN WORK/COMMIT transaction block are considered to be in their own transaction. Consider performing several statements in a single transaction block. This reduces the transaction overhead. Also consider dropping and recreating indices when making large data changes.
There are several tuning options. You can disable fsync() by starting the postmaster with a -o -F option. This will prevent fsync()'s from flushing to disk after every transaction.
You can also use the postmaster -B option to increase the number of shared memory buffers used by the backend processes. If you make this parameter too high, the postmaster may not start because you've exceeded your kernel's limit on shared memory space. Each buffer is 8K and the default is 64 buffers.
You can also use the backend -S option to increase the maximum amount of memory used by the backend process for temporary sorts. The -S value is measured in kilobytes, and the default is 512 (ie, 512K).
You can also use the CLUSTER command to group data in tables to match an index. See the CLUSTER manual page for more details.