pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedshared memory size during upgrade pgsql with partitions
3+ messages / 2 participants
[nested] [flat]
* shared memory size during upgrade pgsql with partitions
@ 2019-12-17 20:03 =?iso-8859-2?Q?Piotr_W=B3odarczyk?= <piotr.wlodarczyk@gnb.pl>
0 siblings, 1 reply; 3+ messages in thread
From: Piotr Włodarczyk @ 2019-12-17 20:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hello,
Currently we're working on PSQL 11.5 and we're trying upgrade to 12.1.
During that we have a problem:
command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql --port 50432
--username postgres --schema-only --quote-all-identifiers --binary-upgrade
--format=custom --file="pg_upgrade_dump_281535902.custom" 'dbname=sprint'
>> "pg_upgrade_dump_281535902.log" 2>&1
pg_dump: error: query failed: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: error: query was: LOCK TABLE
"some_schemaa"."table_part_80000000_2018q3" IN ACCESS SHARE MODE
On current instance we have about one thousand of partitions, partitioned in
two levels: first by id_product, and second level by quarter of the year, as
you can see on above log.
How have we to calculate shared memory, and (eventually
max_locks_per_transaction) to be fit to the limits during upgrade?
Attachments:
[application/pkcs7-signature] smime.p7s (5.4K, ../../86a9cb749c7749c2aeca5e1ebb212598@EX13MBX02.gb.ad.corp/2-smime.p7s)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction)
@ 2019-12-18 04:01 Justin Pryzby <pryzby@telsasoft.com>
parent: =?iso-8859-2?Q?Piotr_W=B3odarczyk?= <piotr.wlodarczyk@gnb.pl>
0 siblings, 0 replies; 3+ messages in thread
From: Justin Pryzby @ 2019-12-18 04:01 UTC (permalink / raw)
To: Piotr Włodarczyk <piotr.wlodarczyk@gnb.pl>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Dec 17, 2019 at 08:03:41PM +0000, Piotr Włodarczyk wrote:
> Currently we're working on PSQL 11.5 and we're trying upgrade to 12.1.
>
> During that we have a problem:
>
> command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql --port 50432
> --username postgres --schema-only --quote-all-identifiers --binary-upgrade
> --format=custom --file="pg_upgrade_dump_281535902.custom" 'dbname=sprint'
> >> "pg_upgrade_dump_281535902.log" 2>&1
> pg_dump: error: query failed: ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
> pg_dump: error: query was: LOCK TABLE
> "some_schemaa"."table_part_80000000_2018q3" IN ACCESS SHARE MODE
>
> On current instance we have about one thousand of partitions, partitioned in
> two levels: first by id_product, and second level by quarter of the year, as
> you can see on above log.
>
> How have we to calculate shared memory, and (eventually
> max_locks_per_transaction) to be fit to the limits during upgrade?
Great question. Clearly, if you can run that (or similar) pg_dump command,
then you can pg_upgrade. I think you could also do pg_upgrade --check,
The query looks like
FROM pg_class c...
WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') "
..and then does:
if (tblinfo[i].dobj.dump &&
(tblinfo[i].relkind == RELKIND_RELATION ||
tblinfo->relkind == RELKIND_PARTITIONED_TABLE) &&
(tblinfo[i].dobj.dump & DUMP_COMPONENTS_REQUIRING_LOCK))
{
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"LOCK TABLE %s IN ACCESS SHARE MODE",
fmtQualifiedDumpable(&tblinfo[i]));
ExecuteSqlStatement(fout, query->data);
}
..then filters by -N/-n/-t/-T (which doesn't apply to pg_upgrade):
selectDumpableTable(&tblinfo[i], fout);
So it looks like COUNT(1) FROM pg_class WHERE relkind IN ('r','p') should do it.
But actually, during pg_upgrade, since nothing else is running, you actually
have max_connections*max_locks_per_transaction total locks.
Said differently, I think you could set max_locks_per_transaction to:
SELECT (SELECT COUNT(1) FROM pg_class WHERE relkind IN ('r','p'))/current_setting('max_connections')::int;
..probably with a fudge factor of +10 for any system process (and due to
integer truncation).
Someone might say that pg_upgrade or pg_dump could check for that specifically..
Justin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction)
@ 2019-12-18 09:16 =?iso-8859-2?Q?Piotr_W=B3odarczyk?= <piotr.wlodarczyk@gnb.pl>
0 siblings, 0 replies; 3+ messages in thread
From: Piotr Włodarczyk @ 2019-12-18 09:16 UTC (permalink / raw)
To: pryzby@telasoft.com <pryzby@telasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>> On Tue, Dec 17, 2019 at 08:03:41PM +0000, Piotr Włodarczyk wrote:
>> Currently we're working on PSQL 11.5 and we're trying upgrade to 12.1.
>>
>> During that we have a problem:
>>
>> command: "/usr/pgsql-12/bin/pg_dump" --host /cluster/postgresql --port
50432
>> --username postgres --schema-only --quote-all-identifiers
--binary-upgrade
>> --format=custom --file="pg_upgrade_dump_281535902.custom"
'dbname=sprint'
>> >> "pg_upgrade_dump_281535902.log" 2> &1
>> pg_dump: error: query failed: ERROR: out of shared memory
>> HINT: You might need to increase max_locks_per_transaction.
>> pg_dump: error: query was: LOCK TABLE
>> "some_schemaa"."table_part_80000000_2018q3" IN ACCESS SHARE MODE
>>
>> On current instance we have about one thousand of partitions, partitioned
in
>> two levels: first by id_product, and second level by quarter of the year,
as
>> you can see on above log.
>>
>> How have we to calculate shared memory, and (eventually
>> max_locks_per_transaction) to be fit to the limits during upgrade?
>
>
>
> Great question. Clearly, if you can run that (or similar) pg_dump
command,
> then you can pg_upgrade. I think you could also do pg_upgrade --check,
pg_upgrade --check doesn't prompt any error or warning
>
>
>
> The query looks like
> FROM pg_class c...
> WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c',
'%c') "
>
>
>
> ..and then does:
>
>
>
> if (tblinfo[i].dobj.dump &&
> (tblinfo[i].relkind == RELKIND_RELATION ||
> tblinfo-> relkind == RELKIND_PARTITIONED_TABLE) &&
> (tblinfo[i].dobj.dump &
DUMP_COMPONENTS_REQUIRING_LOCK))
> {
> resetPQExpBuffer(query);
> appendPQExpBuffer(query,
> "LOCK TABLE %s IN
ACCESS SHARE MODE",
>
fmtQualifiedDumpable(&tblinfo[i]));
> ExecuteSqlStatement(fout, query-> data);
> }
>
>
>
> ..then filters by -N/-n/-t/-T (which doesn't apply to pg_upgrade):
> selectDumpableTable(&tblinfo[i], fout);
>
>
>
> So it looks like COUNT(1) FROM pg_class WHERE relkind IN ('r','p') should
do it.
>
>
>
> But actually, during pg_upgrade, since nothing else is running, you
actually
> have max_connections*max_locks_per_transaction total locks.
>
>
>
> Said differently, I think you could set max_locks_per_transaction to:
> SELECT (SELECT COUNT(1) FROM pg_class WHERE relkind IN
('r','p'))/current_setting('max_connections')::int;
>
>
>
> ..probably with a fudge factor of +10 for any system process (and due to
> integer truncation).
>
>
>
> Someone might say that pg_upgrade or pg_dump could check for that
specifically..
Yes, and temporarily increase, or HINT how to calculate proper value.
>
>
>
> Justin
>
>
We realized that the problem is with pg_dump doing during pg_upgreade.
Now we're after upgrade and we can't check Yours calculation. We simply
increased max_connections until migration passed :)
I'll try to check it on empty, fake database.
Attachments:
[application/pkcs7-signature] smime.p7s (5.5K, ../../462a31615a1944548b95e713e981d277@EX13MBX02.gb.ad.corp/2-smime.p7s)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-12-18 09:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-17 20:03 shared memory size during upgrade pgsql with partitions =?iso-8859-2?Q?Piotr_W=B3odarczyk?= <piotr.wlodarczyk@gnb.pl>
2019-12-18 04:01 ` Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction) Justin Pryzby <pryzby@telsasoft.com>
2019-12-18 09:16 Re: shared memory size during upgrade pgsql with partitions (max_locks_per_transaction) =?iso-8859-2?Q?Piotr_W=B3odarczyk?= <piotr.wlodarczyk@gnb.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox