From: Tom Lane <tgl@sss.pgh.pa.us>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: COPY into table too slow with index: now an I/O
Date: Thu, 01 Dec 2005 22:10:06 -0500
Message-ID: <21102.1133493006@sss.pgh.pa.us> (raw)
In-Reply-To: <BFB4F0E2.14D3C%llonergan@greenplum.com>
References: <BFB4F0E2.14D3C%llonergan@greenplum.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, llonergan@greenplum.com, rschumeyer@ieee.org
Subject: Re: COPY into table too slow with index: now an I/O
In-Reply-To: <21102.1133493006@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox