Skip site navigation (1) Skip section navigation (2)

Re: SV: MySQL and PostgreSQL speed compare

From: "Dominic J(dot) Eidson" <sauron(at)the-infinite(dot)org>
To: Jarmo Paavilainen <netletter(at)comder(dot)com>
Cc: MYSQL <mysql(at)lists(dot)mysql(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SV: MySQL and PostgreSQL speed compare
Date: 2000-12-29 20:30:46
Message-ID: Pine.LNX.4.21.0012291418010.5654-100000@morannon.the-infinite.org (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, 29 Dec 2000, Jarmo Paavilainen wrote:

> > > > Well I expected MySQL to be the faster one, but this much.
> ...
> > > To me, all this is pointing toward the possibility that you haven't
> > > switched of fsync. This will make a MASSIVE difference to insert/update
> 
> The idea was to run as recomended and as default as possible. But with the
> latest (alpha/beta/development) code.

There's quite a difference between "recommended" and "default" - default
tends to err grossly on the side of protective and safe, while we all can
suggest better ways (maybe not as safe?) to do things.

> Ill test that. Even thou it feels like tweaking PostgreSQL away from what
> its considered safe by PostgreSQL developers. If it would be safe it would
> be default.

Once 7.1 is out, It would probably be safe to do so.
> ...
> What if I do a SELECT to check for a row. Then I do a INSERT. But between
> SELECT and INSERT someone else inserted a row. NO I do not think that "good
> programming" will solve this.

Good design, together with good implementation, gets you a long way.

> >>> Sir, thanks for sharing this with us. However, unless you can explain
> >>> why queries inside of transactions run faster than queries outside of
> >>> transactions, I would be inclined to mistrust the test. I haven't
> 
> I was suprised too. But the only difference is that I do a "BEGIN" before I
> start inserting/modifying/deleting and then when Im done I do a "COMMIT".

This will be because of the difference with fsync() - as somebody else
already stated, if you don't explicitly wrap your SQL in BEGIN ... COMMIT,
every SQL query you run, becomes a transaction - and fsync() is called
after each transaction - So, if you do "BEGIN", followed by 7 SQL
queries, followed by "COMMIT" - that's 7 fsync()'s without begin/commit,
while it's only one fsync() with begin/commit. I hope I need not explain
the significance/cost of flushing disk buffers to disk - but it's not
cheap.

> Everything between those are exactly the same. Ive been told that MySQL does
> not support transactions (by default) so there the test is broken. And with
> PostgreSQL, well something inside PostgreSQL is broken (it cant be right
> that with transaction PostgreSQL is 10 times faster than without).

Nothing's broken - you just haven't read the documentation.


-- 
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/


In response to

Responses

pgsql-general by date

Next:From: Dominic J. EidsonDate: 2000-12-29 20:39:18
Subject: Re: SV: MySQL and PostgreSQL speed compare
Previous:From: Benjamin PflugmannDate: 2000-12-29 20:15:55
Subject: Re: MySQL and PostgreSQL speed compare

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group