From: Kevin Grittner <kgrittn@ymail.com>
To: christian.echerer@manroland-web.com <christian.echerer@manroland-web.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Out of shared memory while creating a backup with pg_dump
Date: Thu, 25 Sep 2014 08:06:34 -0700
Message-ID: <1411657594.67331.YahooMailNeo@web122304.mail.ne1.yahoo.com> (raw)
In-Reply-To: <D5359E0908278642BB1747131D62694DA86A0460@AUSMXMBX01.mrws.biz>
References: <D5359E0908278642BB1747131D62694DA86A0460@AUSMXMBX01.mrws.biz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
"christian.echerer@manroland-web.com" <christian.echerer@manroland-web.com> wrote:
> I didn´t understand the HINT:
> You might need to increase max_pred_locks_per_transaction.
That is a configuration setting in postgresql.conf which controls
how much shared memory is reserved at start-up to hold information
on the predicate locks required to make serializable transactions
work.
> What is the reason for the error, that the shared memory ran out,
> or that the number of predicate locks is too small?
The shared memory reserved at start-up for predicate lock
information was too small.
> If i increase max_pred_locks_per_transaction i get probably even
> faster an "out of shared memory" error.
No.
> What is the best way to fix the problem?
Increase max_pred_locks_per_transaction. If you are using
serializable transactions this usually needs to be increased to see
optimal performance. The optimal setting depends on data structure
and the nature of the workload. Increases to 10 or 20 times the
default value are not unusual.
Be sure to review the "for optimal performance" suggestions at the
bottom of this section of the documentation:
http://www.postgresql.org/docs/9.3/interactive/transaction-iso.html#XACT-SERIALIZABLE
If you can declare some transactions which will not be modifying
data as READ ONLY you will probably reduce the number of predicate
locks required.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, christian.echerer@manroland-web.com
Subject: Re: Out of shared memory while creating a backup with pg_dump
In-Reply-To: <1411657594.67331.YahooMailNeo@web122304.mail.ne1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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