Re: Best COPY 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