Re: Database restore speed

On Fri, 2 Dec 2005, Luke Lonergan wrote:

> Steve,
>
>> When we restore the postmaster process tries to use 100% of the CPU.
>>
>> The questions we have are:
>>
>> 1) What is postmaster doing that it needs so much CPU?
>
> Parsing mostly, and attribute conversion from text to DBMS native
> formats.
>
>> 2) How can we get our system to go faster?
>
> Use Postgres 8.1 or Bizgres. Get a faster CPU.
>
> These two points are based on our work to improve COPY speed, which led
> to a near doubling in Bizgres, and in the 8.1 version it's about 60-70%
> faster than in Postgres 8.0.
>
> There are currently two main bottlenecks in COPY, one is parsing +
> attribute conversion (if the postgres CPU is nailed at 100% that's what
> your limit is) and the other is the write speed through the WAL. You
> can roughly divide the write speed of your disk by 3 to get that limit,
> e.g. if your disk can write 8k blocks at 100MB/s, then your COPY speed
> might be limited to 33MB/s. You can tell which of these limits you've
> hit using "vmstat 1" on Linux or iostat on Solaris and watch the blocks
> input/output on your disk while you watch your CPU.

Luke, would it help to have one machine read the file and have it connect
to postgres on a different machine when doing the copy? (I'm thinking that
the first machine may be able to do a lot of the parseing and conversion,
leaving the second machine to just worry about doing the writes)

David Lang