The following bug has been logged on the website: Bug reference: 7884 Logged by: Denys Rtveliashvili Email address: r...@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? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
