[HACKERS] pg_upgrade performance with 150k tables
I received a private email report yesterday from someone using pg_upgrade with PG 9.0 who found it took five hours for pg_upgrade to upgrade a database with 150k tables. Yes, that is a lot of tables, but pg_upgrade should be able to do better than that. I have modified pg_upgrade in git master to cache scandir() and reduce array lookups and the time is down to 38 minutes. (He prototyped a hash implementation that was 30 minutes but it was too much code for my taste.) I don't think this is reasonable to backpatch. If anyone else sees cases for pg_upgrade improvement, please let me know. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pg_upgrade performance
On 21/09/10 16:14, Mark Kirkwood wrote: I've been having a look at this guy, trying to get a handle on how much down time it will save. As a quick check, I tried upgrading a cluster with a 1 non default db containing a scale 100 pgbench schema: - pg_upgrade : 57 s - pgdump/pg_restore : 154 s So, a reasonable saving all up - but I guess still a sizable chunk of downtime in the case of a big database to copy the user relation files. I notice there is a "link" option that would be quicker I guess - would it make sense to have a "move" option too? (perhaps with pg_upgrade writing an "un-move" script to move them back just in case). Replying to this - looking more carefully at what the --link option does, it is clear that this is in fact covered. Sorry for the (my) confusion. For completeness, with this option the upgrade is substantially faster: - pg_upgrade (link): 12 s regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Pg_upgrade performance
I've been having a look at this guy, trying to get a handle on how much down time it will save. As a quick check, I tried upgrading a cluster with a 1 non default db containing a scale 100 pgbench schema: - pg_upgrade : 57 s - pgdump/pg_restore : 154 s So, a reasonable saving all up - but I guess still a sizable chunk of downtime in the case of a big database to copy the user relation files. I notice there is a "link" option that would be quicker I guess - would it make sense to have a "move" option too? (perhaps with pg_upgrade writing an "un-move" script to move them back just in case). Regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade performance test
I run performance test on in-place patch prototype which I sent for review and I got nice result: Original: - MQThL (Maximum Qualified Throughput LIGHT): 2202.12 tpm MQThM (Maximum Qualified Throughput MEDIUM): 4706.60 tpm MQThH (Maximum Qualified Throughput HEAVY): 3956.64 tpm TRANSACTION MIX Total number of transactions = 330457 TYPETX. COUNT MIX - --- Light: 55053 16.66% Medium: 117665 35.61% DSS:36825 11.14% Heavy: 98916 29.93% Connection: 21998 6.66% RESPONSE TIMES AVG.MAX.90TH Light 0.093 1.080 0.400 Medium 0.096 1.663 0.400 DSS 0.092 0.990 0.020 Heavy 0.095 1.262 2.000 Connections 0.092 1.030 0.400 Number of users = 60 Sum of Avg. RT * TPS for all Tx. Types = 17.154543 pg_upgrade: --- MQThL (Maximum Qualified Throughput LIGHT): 2185.16 tpm MQThM (Maximum Qualified Throughput MEDIUM): 4674.04 tpm MQThH (Maximum Qualified Throughput HEAVY): 3915.28 tpm TRANSACTION MIX Total number of transactions = 327811 TYPETX. COUNT MIX - --- Light: 54629 16.66% Medium: 116851 35.65% DSS:36367 11.09% Heavy: 97882 29.86% Connection: 22082 6.74% RESPONSE TIMES AVG.MAX.90TH Light 0.095 1.073 0.400 Medium 0.098 1.167 0.400 DSS 0.093 1.049 0.010 Heavy 0.098 1.173 2.000 Connections 0.097 1.115 0.400 Number of users = 60 Sum of Avg. RT * TPS for all Tx. Types = 17.385362 -- It looks like it has only 1% performance gap. And good to mention that it is not optimized version. It would be good if somebody will run different performance test on it and verify my results. I used iGen OLTP test with 60 concurrent users and run it for 30minutes. Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
