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 15:38:46
Message-ID: b42b73150610250838g7e1cb6ccl30156787343bc79c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-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

In response to

Responses

pgsql-performance by date

Next:From: Carlo StonebanksDate: 2006-10-25 15:46:41
Subject: commit so slow program looks frozen
Previous:From: Jim C. NasbyDate: 2006-10-25 15:32:37
Subject: Re: Problems using a function in a where clause

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