Re: SV: MySQL and PostgreSQL speed compare

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/