Re: Database restore speed

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.

> Note: We've tried adjusting the checkpoint_segements
> parameter to no effect.

No surprise.

- Luke