Re: Best COPY Performance

On 10/25/06, Worky Workerson <worky(dot)workerson(at)gmail(dot)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