The following bug has been logged on the website:
Bug reference: 7884
Logged by: Denys Rtveliashvili
Email address: rtvd@me.com
PostgreSQL version: 9.1.7
Operating system: Ubuntu 12.04
Description: =
I have tried upgrading a database from 8.4 to 9.1 and pg_upgradecluster is
terribly slow.
The bandwidth of data stream is about 10MB/sec (measured by iostat). This is
quite bad, as this runs on a quite new and powerful machine (new, with
proper battery-backed HW RAID, many disks).
IO is certainly not a bottleneck. The problem is that "psql" and "pg_dump"
are consuming a lot of CPU power while doing almost nothing.
When I do "pg_dump > /dev/null" I see read bandwidth about 10MB/sec. This is
tiny comparing to 500MB/sec shown by iozone3.
Also, I think there are obvious problems with pg_upgradecluster:
1. It uses "-Fc". This is bad as it enables compression by default. Which is
pointless and wastes CPU power.
2. It seems to migrate the schema first and only then it moves data. This is
bad as indices are being re-built when the data is finally poured in.
3. The migration does not happen within a transaction. Thus, there are two
consequences:
3.1. The migration will not fail obviously in case data cannot be moved
carefully for whatever reason. This is just not safe.
3.2. The migration does not work as fast as it could. "COPY" can run faster
in case it happens within the same transaction where table has been created.
However, as creation of the table and copying into it are separated, the
postgres has to do a lot of useless work which slows it down.
I was able to improve the performance of upgrade approximately 4 times by
following a different approach:
1. migrate the schema
2. drop all databases (leaving roles in)
3. re-creating the databases (without any content)
4. for each database run pg_dump <database> | psql --single-transaction
<database>
Perhaps the same thing should also be used in pg_upgradecluster?
