[ic] postgres vs mysql (fwd)

Michael Widenius monty@mysql.com
Thu, 18 Jan 2001 23:12:39 +0200 (EET)

Hi again!

>> MySQL 3.23.15 introduced experimental support for Berkeley DB to handle
>> transactions (5/9/2000).

Jeff>     The berkeleydb route to transactions is a kludge, mysql using berkeleydb
Jeff> runs at a fraction of the speed of any other database server I have ever
Jeff> seen.

I have benchmarked this a bit :)

When inserting 350768 rows,

MySQL with MyISAM tables:				    130.00
MySQL witb BDB tables, with sync to disk enabled:	   3017.00
MySQL with BDB tables, no sync:				    186.00

PostgreSQL 7.0.1 without syncing with periodic vacum():     375.00  (*)
PostgreSQL 7.0.1 without syncinc without periodic vacum(): 8542.00

(*) Not including vacum time (I don't have a exact figure for this
    test, only a total for all tests I run)

I know that the above is not complete; We need to do some tests also
with syncing enabled on postgreSQL. We are just waiting for the next
PostgreSQL release before doing a new round and then we will release
all figures.  For now, you can test these yourself by just downloading
the MySQL source distribution and running the benchmark against
PostgreSQL.  Our benchmark page has very clear instructions of how to
do this!

In most of the tests we have run, MySQL with BDB tables was faster
than PostgreSQL, especially when we turned of syncing to disk.
The cases when PostgreSQL was faster than BDB tables was some
searching on a secondary index, updating the primary key and deleting
in tables with many keys. (This comes from the fact that BDB tables
uses the primary key to refer from a secondary index to the row).

>> So, I think MySQL is alive and kicking....Yes, I like MySQL.  But with all
>> that said and done, I have heard good stuff about PostgresQL 7.0, and I
> plan on checking it out.

Jeff>     We used to be an all-mysql shop but switched to postgres about 8 months
Jeff> back as version 7 just offers too much over mysql.  I was aware of the
Jeff> availability of the berkeleydb flavor of mysql but until the code is more
Jeff> refined I don't consider that anything other than a novelty.  Implementing
Jeff> transactional support this far into the software's life and then having to
Jeff> refine the code to get the performance back up will be a significant feat.

We have already done that :)
MySQL 3.23.31 compiles cleanly with BDB and we have purified and
purecoveraged all Berkeley interface code!  One can now run our full
benchmark suite on BDB tables without any problems!

Note that MySQL was from the start coded to be able to have different
storage engines, so there was no need to go through a lot of code to
increase performance; All changes were done in a very limited
interface area.

MySQL has also two other transactional database handlers coming into
4.0 with different performance characteristics, so a lot of new
interesting stuff is happening!

Jeff> I suspect that postgres will remain ahead for quite some time, the primary
Jeff> area for improvement would be blob support and a more advanced
Jeff> security/permissions system.  Mysql now has the start of replication, I
Jeff> would expect to see that coming in postgres also.  It just looks like the
Jeff> mysql team has their work cut out for them whereas the postgres team doesn't
Jeff> really have anything too intricate that needs to be done.

I doubt that the postgreSQL team would agree with you. If that would
be the case, they would be out of work now, wouldn't they :)

Open areas are, among many others, a Windows port, repliction, hot
backup, better ODBC support, more speed, better syncing to disk, no
vacuum()..  The MySQL crash-me pages also shows that PostgreSQL is
still lacking a lot of features (even if it has some heavy features
that MySQL doesn't yet have).

(This doesn't mean that the MySQL TODO list isn't long;  I just wanted to
point out that PostgreSQL lists isn't any shorter...)

I think it's good that PostgreSQL starts to get better;
This means just that life gets more interesting...

Jeff>  Now lets see one
Jeff> of those development teams take on multi-phase commit and then we will be
Jeff> talking!
Jeff>     Mysql does seem to still be more widely used by far, it does have a
Jeff> serious foothold, and there is nothing wrong with it.