User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Thu, 01 Dec 2005 18:26:42 -0800
Subject: Re: COPY into table too slow with index: now an I/O
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Rick Schumeyer" <rschumeyer@ieee.org>,
	pgsql-performance@postgresql.org
Message-ID: <BFB4F0E2.14D3C%llonergan@greenplum.com>
Thread-Topic: [PERFORM] COPY into table too slow with index: now an I/O
	question
Thread-Index: AcX2xgRQYOdxLI0BQiKG1sSBUd6zLAAIdCbq
In-Reply-To: <00df01c5f6c5$2e84c310$0200a8c0@dell8200>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Rick,

On 12/1/05 2:18 PM, "Rick Schumeyer" <rschumeyer@ieee.org> wrote:

> As a follow up to my own question:
> =20
> I reran the COPY both ways (with the index and without) while running ios=
tat.
> The following values
> are averages:
>             %user  %nice  %sys  %iowait %idle
> no index     39      0    2.8    11      47
> index        16      1.5   2.1   34      46
> =20
> I=B9m no performance guru, so please indulge a couple of silly questions:
> =20
> 1)      Why is there so much idle time?  I would think the CPU would eith=
er be
> busy or waiting for IO.

The 100% represents 2 CPUs.  When one CPU is fully busy you should see 50%
idle time.

> 2)      It seems that I need to improve my disk situation.  Would it help=
 to
> add another drive to my PC and
> keep the input data on a separate drive from my pg tables?  If so, some
> pointers on the best way to set that up
> would be appreciated.

Putting the index and the table on separate disks will fix this IMO.  I
think you can do that using the "TABLESPACE" concept for each.

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 tim=
e
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 ca=
n
only do those at a rate of 100/s.

> Please let me know if anyone has additional ideas.

This is a fairly common problem, some people drop the index, load the data,
then recreate the index to get around it.

- Luke