Re: COPY Performance

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.

Some observations/thoughts:

-- When watching the file grow, it will "trickle" upon first run, increasing
in size by only a few kb/s. Upon second run, it will grow by megabytes per
second.

-- If we dump to disk, the same behavior is observed - no apparent RAM
contention issues.

-- The SELECT by itself will complete typically in a few seconds, either on
second or first run.

-- Upon the first run, the PostgreSQL process typically consumes <10% CPU.

-- In very rare cases, we've seen even the first run be as fast as the
second run.

-- There is no other activity on the server while performing these tests.

-- SomeID is just an int

-- CPU usage (wait %) is low; seems as though there is some other bottleneck

Any thoughts on:

-- tuning parameters to speed up the initial execution

-- why is it so slow to begin with?

-- possible areas to check for bottlenecks?

-- better method for accomplishing the same thing?

Thank you,

H

"Hans Zaunere" <lists(at)zaunere(dot)com> writes:
> 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.

Hmmm ... define "first" versus "second". What do you do to return it to
the slow state?

regards, tom lane

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.

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

We do have some pretty fair hardware:

-- PowerEdge R900 with 132gb and 16 cores

-- PowerVault MD1000 with 1.6 TB

I am getting more exact information from the NOC.

The contention might not be an issue. I ran a test last night with zero
activity on the server, and the results were the same. In fact, even the
second run took the same amount of time as the first run.

The queries, however, are always very simple - a single column equal to or
greater than a single value. And typically the column being either an
integer or a one byte char.

Would a cluster index or ordered table help here - partitioning - something
to get sequential reads from the disk? I could experiment, but alas we only
have this one production environment right now, so I'm looking for low
hanging fruit from prior experiences.

The table is about 600+ columns wide with a total of >32 million rows.

Would SELECTing into a temporary table first, then doing the COPY(), make
any difference?

Regards,

H

> > 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.
>
> Hmmm ... define "first" versus "second". What do you do to return it
> to the slow state?

Interesting that you ask. I haven't found a very reliable way to reproduce
this.

Typically, just waiting a while to run the same query the second time will
reproduce this behavior. I restarted postgresql and it was reproduced as
well. However, I can't find a way to flush buffers/etc, to reproduce the
problem on-demand. The first time vs second time is usually simply defined
as the temp. CSV file being created slowly once, and then fast, although
frankly sometimes it's always slow, and sometimes seemingly a bit quicker,
depending on the query.

As I mentioned in my other post, the query is always of the simple form
above - single column greater than or equal to a single value (int or single
byte char).

We had set shared_buffers to 1028M, which at first seemed to make a
significant difference. With some testing, various queries ran under 20
seconds, the "first time", and the second time at the same speed or a bit
quicker.

However, operations then restarted the system (and downgraded to Red Hat
5.0) and now we're back where we started. Even with the increased
shared_buffers, things are slow - perhaps I'll try to increase it more.

Upon first starting postgresql, and executing the above statement, the
process begins to grow to 1gb and then stops. When it stops, the CSV file
starts to "trickle" out to disk.

Best,

H

On Mon, May 5, 2008 at 6:18 AM, Hans Zaunere <lists(at)zaunere(dot)com> wrote:
> > > 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.
> >
> > Hmmm ... define "first" versus "second". What do you do to return it
> > to the slow state?
>
> Interesting that you ask. I haven't found a very reliable way to reproduce
> this.
>
> Typically, just waiting a while to run the same query the second time will
> reproduce this behavior. I restarted postgresql and it was reproduced as
> well. However, I can't find a way to flush buffers/etc, to reproduce the

what happens if you do something like:

select count(*) from (select ...);

i.e. don't make the .csv file each time. How's the performance
without making the csv versus making it?

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'

Wait, are you really creating a .csv file in shared memory? Can such
a thing even work?

If you're not creating a file in /dev/shm, where is it being built?
On the main OS drive? the battery backed cached RAID array?

On Mon, May 5, 2008 at 9:03 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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'
>
> Wait, are you really creating a .csv file in shared memory? Can such
> a thing even work?
>
> If you're not creating a file in /dev/shm, where is it being built?
> On the main OS drive? the battery backed cached RAID array?

OK, looked it up. you're making your .csv file in a ramdisk? If it
gets big it's gonna make the machine start swapping. i'd suggest
storing only small things in a ram disk really. Other than that, I'm
not sure what the problem is.

> > > > 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.
> > > >
> > > Hmmm ... define "first" versus "second". What do you do to return
> > > it to the slow state?
> >
> > Interesting that you ask. I haven't found a very reliable way to
> > reproduce this.
> >
> > Typically, just waiting a while to run the same query the second
> > time will reproduce this behavior. I restarted postgresql and i
> > was reproduced as well. However, I can't find a way to flush
> > buffers/etc, to reproduce the
>
> what happens if you do something like:
>
> select count(*) from (select ...);
>
> i.e. don't make the .csv file each time. How's the performance
> without making the csv versus making it?

It's the same.

And regarding the /dev/shm, we do watch that memory doesn't become
contentious. We've also done the dump to another set of disk spindles, and
we've seen the same performance.

So at the end of the day, it certainly does seem like a read-bottleneck off
of the disks. Unfortunately, from a hardware perspective, there's not much
we can do about it currently.

Does anyone have any experiences they can share about using partitioning or
index tricks to speed up what should be basically large contiguous rows from
a table, based on a single column WHERE constraint?

H