Re: Best COPY Performance

On 10/25/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> > 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.
>
> Nope, nothing in the log.  I have set:
>  wal_buffers=128
>  checkpoint_segments=128
>  checkpoint_timeout=3000
> which I thought was rather generous.  Perhaps I should set it even
> higher for the loads?
>
> > Have you determined that pg is not swapping?  try upping maintenance_work_mem.
>
> maintenance_work_mem = 524288 ... should I increase it even more?
> Doesn't look like pg is swapping ...

nah, you already addressed it.  either pg is swapping or it isnt, and
i'm guessing it isn't.

> I'm currently running bonnie++ with the defaults ... should I change
> the execution to better mimic Postgres' behavior?

just post what you have...

> RHEL 4.3 x86_64
> HP DL585, 4 Dual Core Opteron 885s
>   16 GB RAM
>   2x300GB 10K SCSI320, RAID10
> HP MSA1000 SAN direct connected via single 2GB Fibre Channel Arbitrated Loop
>   10x300GB 10K SCSI320, RAID10

in theory, with 10 10k disks in raid 10, you should be able to keep
your 2fc link saturated all the time unless your i/o is extremely
random.  random i/o is the wild card here, ideally you should see at
least 2000 seeks in bonnie...lets see what comes up.

hopefully, bonnie will report close to 200 mb/sec.  in extreme
sequential cases, the 2fc link should be a bottleneck if the raid
controller is doing its job.

if you are having cpu issues, try breaking your process down to at
least 4 processes (you have quad dual core box after all)...thats a no
brainer.

merlin