Re: [GENERAL] RE: a comparison of 4 databases - Mailing list pgsql-general

another example that do not trust any "comparison" that is done
by one party. As our beloved pg developer suggested, any real
comparison should by done based on some reasonable-often-used-real-life
cases and by at least more than 2 or 3 proved (certificed -- for
Orakal -- or beloved -- for PG) EXPERTS of each target-dbms
by using all possible approaches available for that database system.

this kind of thing, like religion/culture "comparison", should
be read VERY VERY carefully.


On Fri, 10 Dec 1999, Art S. Kagel wrote:

>
> 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
>
> ************
>