[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 по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Renaming Raster Table
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] CREATE AGGREGATE on jsonb concat