Обсуждение: Re: [GENERAL] Postgres INSERTs much slower than MySQL?

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

> 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 ?!

Yikes.  I always thought it would be nice to try and cache query plans
by comparing parse trees, and if they match cached versions, replace any
constants with new ones and use cached query plan.  Hard to do right,
though.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> 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 ?!

> Yikes.  I always thought it would be nice to try and cache query plans
> by comparing parse trees, and if they match cached versions, replace any
> constants with new ones and use cached query plan.  Hard to do right,
> though.

But INSERT ... VALUES(...) has such a trivial plan that it's hardly
likely to be worth caching.  We probably ought to do some profiling to
see where the time is going, and see if we can't speed things up for
this simple case.

In the meantime, the conventional wisdom is still that you should use
COPY, if possible, for bulk data loading.  (If you need default values
inserted in some columns then this won't do...)

            regards, tom lane

> 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.

This sounds impressive. So I did some testings with my pgbench to see
how WAL improves the performance without -F using current.

100000 records insertation + vacuum took 1:02 with -F (4:10 without -F)

TPC-B like transactions(mix of insert/update/select) per second:
    21 (with -F)
    3  (without -F)

I couldn't see any improvement against 6.5.2 so far. Maybe some part
of WAL is not yet committed to current?
---
Tatsuo Ishii

Tatsuo Ishii wrote:
>
> > With WAL the same test without -F will be near as fast as with
> > -F now.
>
> This sounds impressive. So I did some testings with my pgbench to see
> how WAL improves the performance without -F using current.
>
> 100000 records insertation + vacuum took 1:02 with -F (4:10 without -F)
>
> TPC-B like transactions(mix of insert/update/select) per second:
>         21 (with -F)
>         3  (without -F)
>
> I couldn't see any improvement against 6.5.2 so far. Maybe some part
> of WAL is not yet committed to current?
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
...is not implemented.

Vadim