[GENERAL] huge RAM use in multi-command ALTER of table heirarchy
От | Justin Pryzby |
---|---|
Тема | [GENERAL] huge RAM use in multi-command ALTER of table heirarchy |
Дата | |
Msg-id | 20170706180420.GH30252@telsasoft.com обсуждение исходный текст |
Список | pgsql-general |
I've seen this before while doing SET STATISTICS on a larger number of columns using xargs, but just came up while doing ADD of a large number of columns. Seems to be roughly linear in number of children but superlinear WRT columns. I think having to do with catalog update / cache invalidation with many ALTERs*children*columns? 32 cols and 2 children => 12MB 256 cols and 11 children => 74MB 256 cols and 111 children => 582MB 512 cols and 11 children => 229MB (in our "huge" case, there were ~1600 columns and maybe even more children) I was testing with this command PGHOST=/tmp PGPORT=9999 sh -ec 'for maxcols in 512 ; do ~/src/postgresql.install/bin/postgres -D ~/src/postgres.dat -c port=9999& sleep 4; cols=$(for d in `seq 1 $maxcols`; do echo "ADD c$d int,"; done |xargs); PGOPTIONS="-c client_min_messages=warning"psql postgres -qc "DROP TABLE t CASCADE" || [ $? -eq 1 ]; psql postgres -qc "CREATE TABLE t()";for c in `seq 1 11`; do psql postgres -qc "CREATE TABLE c$c() INHERITS(t)"; done; for d in `seq 1 $maxcols`; do echo"ALTER TABLE t ADD c$d int;"; done |PGOPTIONS="-c client_min_messages=DEBUG3 -c log_statement_stats=on" psql postgres-c "ALTER TABLE t ${cols%,}" 2>/tmp/pg.err2; ~/src/postgresql.install/bin/pg_ctl -swD ~/src/postgres.dat stop; done' ..and log_statment_stats with a variation on the getrusage patch here https://www.postgresql.org/message-id/20170615145824.GC15684%40telsasoft.com Justin
В списке pgsql-general по дате отправления: