Skip site navigation (1) Skip section navigation (2)

Re: Best COPY Performance

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Worky Workerson" <worky(dot)workerson(at)gmail(dot)com>
Cc: "Markus Schaber" <schabi(at)logix-tt(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Best COPY Performance
Date: 2006-10-25 13:20:02
Message-ID: b42b73150610250620r37cf9269m7b6e43b9835bdbcf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 10/23/06, Worky Workerson <worky(dot)workerson(at)gmail(dot)com> wrote:
> The disk load is where I start to get a little fuzzy, as I haven't
> played with iostat to figure what is "normal".  The local drives
> contain PG_DATA as well as all the log files, but there is a
> tablespace on the FibreChannel SAN that contains the destination
> table.  The disk usage pattern that I see is that there is a ton of
> consistent activity on the local disk, with iostat reporting an
> average of 30K Blk_wrtn/s, which I assume is the log files.  Every
> several seconds there is a massive burst of activity on the FC
> partition, to the tune of 250K Blk_wrtn/s.
>
> > On a table with no indices, triggers and contstraints, we managed to
> > COPY about 7-8 megabytes/second with psql over our 100 MBit network, so
> > here the network was the bottleneck.

I'm guessing the high bursts are checkpoints.  Can you check your log
files for pg and see if you are getting warnings about checkpoint
frequency?   You can get some mileage here by increasing wal files.

Have you determined that pg is not swapping?  try upping maintenance_work_mem.

What exactly is your architecture?  is your database server direct
attached to the san? if so, 2gb/4gb fc?  what san?  have you bonnie++
the san?  basically, you can measure iowait to see if pg is waiting on
your disks.

regarding perl, imo the language performance is really about which
libraries you use. the language itself is plenty fast.

merlin

In response to

Responses

pgsql-performance by date

Next:From: Luke LonerganDate: 2006-10-25 15:06:36
Subject: Re: Best COPY Performance
Previous:From: Markus SchaberDate: 2006-10-25 12:18:52
Subject: Re: Best COPY Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group