Thread

Re: [GENERAL] Postgres INSERTs much slower than MySQL?

Vadim Mikheev <vadim@krs.ru> — 1999-10-22T05:52:40Z

Lincoln Yeoh wrote:
> 
> At 04:38 PM 20-10-1999 +0800, Vadim Mikheev wrote:
> >You hit buffer manager/disk manager problems or eat all disk space.
> >As for "modifying" - I meant insertion, deletion, update...
> 
> There was enough disk space (almost another gig more). So it's probably
> some buffer manager problem. Is that the postgres buffer manager or is it a
> Linux one?
> 
> Are you able to duplicate that problem? All I did was to turn off
> autocommit and start inserting.

I created table with text column and inserted 1000000 rows
with '!' x rand(256) without problems on Sun Ultra, 6.5.2
I run postmaster only with -S flag.
And while inserting I run select count(*) from _table_
in another session from time to time - wonder what was
returned all the time before commit? -:))

> >> Well anyway the Postgres inserts aren't so much slower if I only commit
> >> once in a while. Only about 3 times slower for the first 100,000 records.
> >> So the subject line is now inaccurate :). Not bad, I like it.
> >
> >Hope that it will be much faster when WAL will be implemented...
> 
> What's WAL? Is postgres going to be faster than MySQL? That would be pretty
                                ^^^^^^^^^^^^^^^^^^^^^^^
                                No.

> impressive- transactions and all. Woohoo!

WAL is Write Ahead Log, transaction logging.
This will reduce # of fsyncs (among other things) Postgres has
to perform now.
Test above took near 38 min without -F flag and 24 min
with -F (no fsync at all).
With WAL the same test without -F will be near as fast as with
-F now.

But what makes me unhappy right now is that with -F COPY FROM takes
JUST 3 min !!! (And 16 min without -F)
Isn't parsing/planning overhead toooo big ?!

Vadim