Re: COPY Performance

On Sun, May 4, 2008 at 5:11 PM, Hans Zaunere <lists(at)zaunere(dot)com> wrote:
> Hello,
>
> We're using a statement like this to dump between 500K and >5 million rows.
>
> COPY(SELECT SomeID FROM SomeTable WHERE SomeColumn > '0')
> TO '/dev/shm/SomeFile.csv'
>
> Upon first run, this operation can take several minutes. Upon second run,
> it will be complete in generally well under a minute.
>

Almost certainly a buffering issue. First time it's reading the file
into memory WHILE also doing other things, file system wise. Second
time it's in memory (kernel cache) and zips right by.

What can you do? First you need to see what's really happening, which
means learning how to drive vmstat, iostat, top, etc to see what's
happening on your machine. You'll likely want to look into doing
something that will reduce contention on the database partition set
for starters. Table spaces, big RAID arrays (big meaning a lot of
spindles), battery backed RAID controller.