RE: a comparison of 4 databases - Mailing list pgsql-general

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