Re: COPY into table too slow with index: now an I/O - Mailing list pgsql-performance

From Tom Lane
Subject Re: COPY into table too slow with index: now an I/O
Date
Msg-id 21102.1133493006@sss.pgh.pa.us
Whole thread Raw
In response to Re: COPY into table too slow with index: now an I/O  ("Luke Lonergan" <llonergan@greenplum.com>)
List pgsql-performance
"Luke Lonergan" <llonergan@greenplum.com> writes:
> The problem I see is nicely shown by the increase in IOWAIT between the two
> patterns (with and without index).  It seems likely that the pattern is:
> A - insert a tuple into the table
> B - insert an entry into the index
> C - fsync the WAL
> - repeat

> This can be as bad as having a disk seek to access the table data every time
> the 8KB page boundary is crossed, then again for the index, then again for
> the WAL, and random disk seeks happen only as fast as about 10ms, so you can
> only do those at a rate of 100/s.

That analysis is far too simplistic, because only the WAL write has to
happen before the transaction can commit.  The table and index writes
will normally happen at some later point in the bgwriter, and with any
luck there will only need to be one write per page, not per tuple.

It is true that having WAL and data on the same spindle is bad news,
because the disk head has to divide its time between synchronous WAL
writes and asynchronous writes of the rest of the files.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Steve Oualline"
Date:
Subject: Database restore speed
Next
From: "Rick Schumeyer"
Date:
Subject: Re: COPY into table too slow with index: now an I/O question