Re: in-transaction insert performance in 7.5devel

От: Merlin Moncure
Тема: Re: in-transaction insert performance in 7.5devel
Дата: ,
Msg-id: 6EE64EF3AB31D5448D0007DD34EEB34101AE5B@Herge.rcsinc.local
(см: обсуждение, исходный текст)
Ответ на: in-transaction insert performance in 7.5devel  ("Merlin Moncure")
Ответы: Re: in-transaction insert performance in 7.5devel  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

in-transaction insert performance in 7.5devel  ("Merlin Moncure", )
 Re: in-transaction insert performance in 7.5devel  (Rod Taylor, )
 Re: in-transaction insert performance in 7.5devel  ("Merlin Moncure", )
  Re: in-transaction insert performance in 7.5devel  (Rod Taylor, )
 Re: in-transaction insert performance in 7.5devel  (Tom Lane, )
 Re: in-transaction insert performance in 7.5devel  ("Merlin Moncure", )
  Re: in-transaction insert performance in 7.5devel  (Tom Lane, )
 Re: in-transaction insert performance in 7.5devel  ("Merlin Moncure", )

Tom Lane wrote:
> "Merlin Moncure" <> writes:
> > I am batch inserting insert statements into a database with fsync =
on.
> > My single disk system is on a 10k drive...even though I am inside a
> > transaction there is at least 1 file sync per row insert.
>
> Are you certain you're inside a transaction?
>
> Tracing a process doing simple inserts within a transaction block,
> I don't see the process doing any I/O at all, just send/recv.  The
> background writer process is doing the work, but it shouldn't block
> the inserter.
>
> [ thinks for a bit... ]  Hmm.  I see that XLogFlush for a buffer's LSN
> is done while holding share lock on the buffer (see FlushBuffer in
> bufmgr.c).  This would mean that anyone trying to acquire exclusive
lock
> on the buffer would have to wait for WAL fsync.  In a situation where
> you were repeatedly inserting into the same table, it's somewhat
likely
> that the inserter would block this way while the bgwriter is trying to
> flush a previous update of the same page.  But that shouldn't happen
for
> *every* insert; it could happen at most once every bgwriter_delay
msec.
>
> Does it help if you change FlushBuffer to release buffer lock while
> flushing xlog?

Putting your change in resulted in about a 15% increase in insert
performance.  There may be some quirky things going on here with NTFS...

I did an update clean from cvs and I noticed big speedup across the
board.  Right now sync performance is right in line with my
expectations.  In any case, I checked and confirm that there are no
spurious fsyncs running when they are not supposed to be.

Merlin




В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: in-transaction insert performance in 7.5devel
От: Vitaly Belman
Дата:
Сообщение: Additional select fields in a GROUP BY