From: | <kaiq(at)realtyideas(dot)com> |
---|---|
To: | "Art S(dot) Kagel" <kagel(at)bloomberg(dot)net> |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] RE: a comparison of 4 databases |
Date: | 1999-12-10 17:32:26 |
Message-ID: | Pine.LNX.4.10.9912101123300.10905-100000@picasso.realtyideas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
>
> ************
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 1999-12-10 17:47:06 | Re: [GENERAL] \d shows all my tables twice |
Previous Message | Karl DeBisschop | 1999-12-10 17:25:06 | Re: [GENERAL] \d shows all my tables twice |