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
