From: "Rick Schumeyer" <rschumeyer@ieee.org>
To: "'Luke Lonergan'" <llonergan@greenplum.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: COPY into table too slow with index: now an I/O question
Date: Thu, 1 Dec 2005 22:26:56 -0500
Message-ID: <00ee01c5f6f0$3faec750$0200a8c0@dell8200>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <BFB4F0E2.14D3C%llonergan@greenplum.com>
Importance: Normal

I only have one CPU.  Is my copy of iostat confused, or does this have
something to do with hyperthreading or dual core?  (AFAIK, I don't have =
a
dual core!)

The problem (for me) with dropping the index during a copy is that it =
takes
tens of minutes (or more) to recreate the geometry index once the table =
has,
say, 50 million rows.

> -----Original Message-----
> From: Luke Lonergan [mailto:llonergan@greenplum.com]
> Sent: Thursday, December 01, 2005 9:27 PM
> To: Rick Schumeyer; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] COPY into table too slow with index: now an I/O
> question
>=20
> Rick,
>=20
> On 12/1/05 2:18 PM, "Rick Schumeyer" <rschumeyer@ieee.org> wrote:
>=20
> > As a follow up to my own question:
> >
> > I reran the COPY both ways (with the index and without) while =
running
> iostat.
> > 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
> >
> > I=B9m no performance guru, so please indulge a couple of silly =
questions:
> >
> > 1)      Why is there so much idle time?  I would think the CPU would
> either be
> > busy or waiting for IO.
>=20
> The 100% represents 2 CPUs.  When one CPU is fully busy you should see =
50%
> idle time.
>=20
> > 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.
>=20
> 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.
>=20
> 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
>=20
> 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.
>=20
> > Please let me know if anyone has additional ideas.
>=20
> This is a fairly common problem, some people drop the index, load the
> data,
> then recreate the index to get around it.
>=20
> - Luke