RE: a comparison of 4 databases

As an, admittedly avid, and long time user of Informix database products I
felt I could not leave this shoddy comparison stand without comment. There
are many errors concerning the features and capabilities of Informix database
servers and Mr. Kirkwood conducted several of his tests poorly which did not
fairly represent this fine set of database server products, nor, likely, the
other fine databases included in the comparison either. Anyone interested in
his results should read on. Since I have moused the original post I will
preceed my own comments with 'ASK - ' and indent to make them clear.

A Comparison Of 4 Databases
---------------------------

Intro
-----

This paper shows the results of an evaluation of 4 databases. I am posting it
to this mail group as I think Postgresql emerged quite favourably.

The evaluated databases were Oracle, Informix, Mysql and Postgresql.

Features and performance were examined.

Hardware And Operating System
-----------------------------

2 x HP Vertra VE 7 each with 160M RAM + 1x3.2G + 1x13G Quantum IDE Drives were
used.
Redhat 6.0 was used as the operating system. No kernel changes were made.

Initial Experiences
-------------------

Mysql was obtained in rpm format and was amazingly easy to install. The
installation process created and started a database. The version was 3.22.27

Documentation was supplied and was good.

Postgresql was similarly elementary to install, and again a database was
created and started. The product comes with the flavour of Linux used and
was in rpm format. The version was 6.5.2

Documentation was supplied and was very good.

Informix was more cryptic to install. It was obtained in rpm format and
installed. However this merely installed an archive and the real installation
process had to be run thereafter. After this it had to be divined as to what
was required next - the install does not create a database.

ASK - Indeed the install can be cryptic but there are detailed installation
instructions that can be downloaded from the Informix site as well
as an available How-To that explains how to make an initial
configuration and bring up the engine for the first time. All of
which are also explained in detail in the Informix Administrators
Guide.

Using some of the (not supplied) documentation it was discovered how to create
and configure a database. The version was 7.30 ( This is old, but all they
are supplying on this platform - 9.x is current)

ASK - I assume Mr. Kirkwood downloaded the Informix Dynamic Server v7.30
which is NOT old. The Informix server product line includes 4
different servers and Informix is in the process of merging the code
lines of some of these. The Informix enterprise level engine for
transaction processing IS the IDS7.3x product the latest release of
which is 7.31 which IS available for Linux but the link that Mr.
Kirkwood followed took him to the older (by 3 months) 7.30 version.
The IDS/UDO v9.1x product is known as the Universal Data Option and
is Informix's Object Relational product descended ultimately, like
PostgreSQL, from the Postgres project. UDO was based on the older
IDS 7.1x code base and some features needed for transaction oriented
applications were missing. In the last 3 weeks, indeed on the same
day Mr. Kirkwood posted his report, Informix released IDS.2000 which
is IDS v9.20 and is a merger of the 7.31 and 9.1x code bases in a new
product promising Object Relational features, full transaction server
features, and enterprise quality performance. But, this is a brand
new code base - and so - many users are waiting for IDS.2000 v9.21
or at least the first few maintenance releases of v9.20. It is
important to note that Informix considers Linux a Tier I port and
released the IDS.2000 product on Linux on the same day as it did its
other Tier I ports like Sun/Solaris and HP/HP-UX.

Documentation was not supplied, it was available on the Informix web site. It is
ok.

ASK - The documentation is available for perusal online at the Informix
website from whence it can also be downloaded for local access or
you can purchase the docs on CD-ROM for $45US or in hard copy (I
think they are about $750US) from Informix. Note that Informix's
documentation has won numerous awards for quality and clarity and
compared to Oracle's completely opaque docs I have always found it
a pleasure to use.

Oracle was difficult to judge as the author frequently installs it. However
pretending to be coming to it new, it would be very difficult to install.
It does not come in rpm format. It is downloadable from the Oracle web site.
The small amount of included documentation is sufficient to enable someone
to work out how to start the installer program. This program is a rudimentary
wizard that asks questions and presents a list of components to choose....
a newcomer would suffer confusion here. The installer can create a database as
part of the install. The version was 8.0.5 (this is slightly old - 8.1.5 is
Current but buggy, 8.0.5 is the latest stable release on this platform).

Documentation is not supplied, it is available from the Oracle web site. It is
ok.

Tests And results
-----------------

Database Feature
Comparison

Database Cost Trans Row Const Program Sec Fail Hot
actions Lock raints mable ure Safe back

Mysql 0 /3000 No No No Partial Yes No No
Postgresql 0 Yes Yes Partial Yes Yes Yes No
Oracle 3200 Yes Yes Yes Yes Yes Yes Yes
Informix 2000 Yes No Yes Yes Yes No No

ASK - See below. ALL OF THE 'No's above should be 'Yes'es. I address
each point below.

Cost

NZ$ for 10 user license. Two prices mean that the product is charged
for if resold as part of an application ( Mysql )
Support is not included

Transactions

Commit, rollback, isolation levels of at least read commited

ASK - Informix permits the user to create a database in one of four modes.
Three of these: Mode ANSI, Buffered Logged, and Unbuffered Logged
support multiple statement transactions with BEGIN WORK (ANSI mode
databases infer a BEGIN WORK with the first statement not in a
transaction, ie following a COMMIT or ROLLBACK). The fourth, which
for historical reasons is the default mode, is non-logged which does
NOT support multiple statement transactions, though individual SQL
statements are treated as singleton transactions. Based on the
results for Fail Safe I'd say that the database was created in
default mode without logging or perhaps in BUFFERED LOG mode and
the COMMIT record was still in the log buffer and had not flushed
to disk in which case Informix rightly rolled back the transaction
(this would be noted in the engine's message log at the time the
engine was restarted). For this type of secure testing Mr. Kirkwood
should have beenusing an UNBUFFERED LOG mode database which
automatically flushes the log buffer when a commit record is written
to it. There is an extensive discussion of these issues and their
ramifications for performance and safety in the Administrators
Guide. Note that most Informix DBAs run their databases in
UNBUFFERED LOG mode.

Row Locking

select for update that locks only the rows selected and does not
block reads

ASK - Informix DOES indeed support row level locking though the default
for tables is page level locking. One need only include the LOCK
MODE ROW clause in the CREATE TABLE statement or ALTER the table
afterward to have row level locks.

Constraints

primary and foreign key, with ability to enable/ disable or drop / add
existence will give ""Partial"" and enable etc will give "Yes"

Programmable

create trigger, procedural language extensions to SQL
A "Partial" means that external functions only are supported

Secure

Requires username and password validation

Fail Safe

delete of 100 out of 100000 rows, commit ( if have it ) then power
off and on, check after power on - 999900 rows gets a "Yes"

ASK - If Mr. Kirkwood had created his database with one of the logged
transaction modes he would have had complete 'fail safe' behavior
from the Informix server. Informix cannot recover transactions
that have not been checkpointed out to disk in a database without
logging (the default) but it can for a logged database.

Hot Backup

Can a consistent backup of the database be run backup while it is up
with transactions performed against it.

ASK - Informix was the first relational database to support online live
consistent backup of its database servers. Indeed Informix provides
three archive products all of which can backup the server while being
actively updated. Only one of these, ontape, is available on Linux
so far (due to some third party software the others, onarchive and
onbar, depend on which was not available for Linux at release time)
but that may soon change.

Database Performance Comparison - Data
Warehouse

Database Bulk Load Summarize 3% Summarize 0.3% Summarize 0.3%
1M of 3M of 3M of 3M
1 dim 1 fact 2 dim 1 fact 3 dim 1 fact

Mysql 20 14 90 57
Postgresql 420 16 4 7
Oracle 65 5 3 3
Informix 170 8 5 6

Notes

Bulk Load

elapsed seconds to load 1000000 rows via whatever bulk load tool is
supplied.

ASK - Informix has several bulk load utilities. It would appear that Mr.
Kirkwood either did not use the ipoader or did not run it in either
of the higher speed modes which includes a mode which bypasses all
logging and is several times faster than logged modes.

ASK - On the performance tests, Informix performance is very dependent on
the tuning of the engine and the presence of proper database
statistics. I cannot know how well or poorly Mr. Kirkwood tuned the
engine or whether he ran the recommended suite of UPDATE STATISTICS
commands after loading the database. Without details of how well
he prepared the various servers and databases so the optimizers
could do their jobs properly one cannot evaluate the quality of
his results. Note that I state all of this even though Informix
performed reasonably anyway. I just wonder how much better ANY of
the tested server could have performed.

Summarize 3%
1 dim + 1 fact

Measure elapsed seconds to display summarized count and one timelike
dimension attribute for basic star query
Access path can be via an index scan

Summarize 0.3%
2 dim + 1 fact

Measure elapsed seconds to display summarized count and one non timelike
dimesion attribute for less basic star query
Access path can be via index scans

Summarize 0.3%
3 dim + 1 fact

Measure elapsed seconds to display a summarized count and 1 non timelike
dimension attribute for more complex star query
Access path can be via index scans

Database Performance Comparison -
Online

Database Create Create Query 1 Query 4 Query Summary
10K 1M of 10K of 1M 10% of 1M

Mysql 7 891 0 0 1
Postgresql 21 4430 0 0 2
Oracle 31 3694 0 0 2
Informix 0 0 10

Database Tpm Tpm Tpm Tpm
1 sess. 2 sess. 4 sess. 8 sess.

Mysql 59/58 59/115 59/229 58/425
Postgresql 48 90 169 233
Oracle 55 108 180 291
Informix

Notes


Unfortunately no Informix SDK was available, so there is no result for
the first two or last tests.

ASK - Informix's SDK is not included in the basic engine download but IS
available for free download for Linux. The SDK includes ESQL/C,
CLI/ODBC, JDBC, a C++ interface, and more. Also available in
separate packages are I4GL (compiled version of Informix's award
winning 4th Generation Language), R4GL (pcode version of I4GL which
includes a source level pcode debugger), ISQL (Informix's forms,
menus, and report writer package), and D4GL (a GUI aware version of
I4GL). All of these are available for download including free
evaluation licenses and most are available with a free single user
development license. The SDK is free for all developers and the
runtime package, iConnect, is free for all users. With VERY little
effort Mr. Kirkwood could have compiled and run his tests with
Informix.

Create 10K

elapsed seconds for Php app to insert 10000 rows one by
one

Create 1M

elapsed seconds for Php app to insert 1000000 rows one by one

Query 1

0 seconds means that no delay was measurable
Measure elapsed seconds to show master record 1 row located 70% though
the table. Access path can be via an index scan

Query 4

Measure elapsed seconds to display 4 detail rows for one of the master
records in previous test. Access path can be via index scans

Query Summary

Measure elapsed seconds to display a summarized sum and count
for 5% of 1000000 rows. This is a 2 table join . Access path can be via
index scans

Tpm n sessions

Transactions per minute for n sessions, achievable for lightweight Perl
DBI application. Two results for Mysql refer to two possible locking
schemes

Analysis
--------

Features

Oracle clearly has the most features. However it is Postgresql that is next in
line.

The features fail safe, transactions and security are seen as essential,
with row locking as highly desirable.

This means that at face value only Oracle and Postgresql satisfy the criteria.

ASK - As does Informix if one takes the 10 seconds it takes to add the
WITH LOG clause to the CREATE DATABASE and LOCK MODE ROW clause to
the CREATE TABLE statements. In several cases below Mr. Kirkwood
bothered to check the docs and even contact developers to find out
if or how he could get some feature he wanted that Oracle or
PostgreSQL did not seem to have could be made to work. It is
suspect that he did not do the same for MySQL and Informix. His
Informix download came with a free support contract so if reading
the docs or posting queries to comp.databases.informix did not get
the information he needed (and I assure you either the docs or the
newsgroup would have given him the answers) he could have called
tech support toll free.

Initially Postgresql did not force password entry and thus was not secure,
however it was discovered that access to the database is configured at install
time as trusted and has to be changed to require passwords.

It should be noted that Oracle needed a patch to be fail safe, and that this
was not advertised on the web site, thus is possible that Informix and Mysql
can also be similarly patched.

Repeated tests with Informix page level locks showed that readers were blocked
at this point. This was viewed as extremely undesirable.

ASK - In addition to setting row level locking mode for the tables Mr.
Kirkwood should note that Informix implements all of the ANSI
Isolation Modes. The default for a logged database

Mysql's lack of transactions was a killer for it, the complexity of having to
programmatically undo an n-table operation was viewed as prohibitive.
( It was noted that the Mysql to do list had atomic operations - pseudo
transactions, on it .)

In conclusion on the features front, Oracle and Postgresql emerge as the
leaders.

Performance

1 Online operations

Initially Postgreql refused to use indexes to help with queries. A mail to one
of the Postgresql Development Team traced this to an unusual pickiness with
respect to the implied datatypes of constants in the where clause.

Informix had problems with the summary query, it would not go faster that 10s,
however it seemed to perform ok for the 10000 and 1000000 query searches.

Mysql demonstrates the overhead associated with multiple versioning and
transactions. It was obviously the fastest raw speed terms. It was in
general 4-5 times faster than anything else.

Postgresql was in general about 20% slower than Oracle.

The multiple session transaction test is most interesting. The idea was to
mimic a Tpc A style banking application with some user think time during the
transaction (1 second ).
Clearly Mysql with its table level locks could not scale at all. However it has
application locks that are nameable, so can mimic row locks - as long as every
access uses them. If these were turned on then a dramatic turnaround was
observed.

Thus Mysql is fastest, and most scalable if applications locks can be used.
Oracle and Postgreql are similar in scalability, but Postgresql begins to tail
off a little before Oracle does.

2 Data warehousing operations

Mysql has a very fast bulk load facility.
Oracle also has a very fast bulk load.
Infomix has a reasonably fast one.
Postgresql is noticeably slower than any of the others.

Mysql performed extremely poorly at the star type queries.
Oracle and Informix performed very well.
Postgresql performed very well in all but the first query - the simplest
(ironically), but it scanned the most data. This points to Oracle and Informix
having faster Nested Loop or Sort algorithms.

Both Oracle and Postgresql would experience a period of poor performance on a
table immediately after it was bulk loaded - typically until it was vacuumed or
analyzed.

In conclusion for this section Oracle, Informix are very good.
Postgresql is good but is hampered by slow bulk load and sorting of large
datasets.
Mysql can get the data in fast but is poor at querying it.

Overall
-------

Informix performs mostly ok, but its locking scheme precludes it for serious on
line use (it would have been interesting to test this).

ASK - Does this make sense? There are literally thousands of mission
critical, speed dependent, multi-user applications running against
Informix engines. It makes no sense that Informix's locking
prohibits reasonable performance. Just this reasonablness test
should have clued the author in that he had missed something.

Oracle performs well.
Mysql's lack of transactions would have been ok for data warehousing, but it
could not do star queries. This rules it out.
Postgresql performed about 20% slower than Oracle, except for bulk loads and
large dataset sorts. These things mean that it is suitable for data warehousing
and on line operations, with the proviso that large data loads and some large
summary queries may be unpleasantly slow.

ASK - In brief, these tests need to be performed again, certainly for
Informix but most likely for Oracle and the rest as well, with
an eye to more accurately portraying the performance and features
of each. More care is needed.

[Benchmark code SNIPPED]

Art S. Kagel