Re: COPY into table too slow with index: now an I/O

Rick,

On 12/1/05 2:18 PM, "Rick Schumeyer" <rschumeyer(at)ieee(dot)org> wrote:

> 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¹m 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.

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 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.

> 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