pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of shared memory while creating a backup with pg_dump
5+ messages / 4 participants
[nested] [flat]
* Out of shared memory while creating a backup with pg_dump
@ 2014-09-24 09:05 christian.echerer@manroland-web.com
0 siblings, 3 replies; 5+ messages in thread
From: christian.echerer@manroland-web.com @ 2014-09-24 09:05 UTC (permalink / raw)
To: pgsql-admin
Hi,
while creating a backup with pg_dump and parallel write accesses to the database following error occured:
2014-09-24 07:46:46 ... ERROR: out of shared memory
2014-09-24 07:46:46 ... HINT: You might need to increase max_pred_locks_per_transaction.
I´m running postgresql 9.3.4 on Ubuntu 12.04 server. The server has 8GB RAM and 4 cores.
/proc/sys/kernel/shmmax: 2147483648
/proc/sys/kernel/shmall: 2097152
The original configuration was changed in only a few relevant points:
* shared_buffers = 128MB
* work_mem = 10MB
We run a hot standby slave, therefore the following changes were necessary:
* wal_level = hot_standby
* max_wal_senders = 1
* wal_keep_segments = 64
* hot_standby = on
* hot_standby_feedback = on
I didn´t understand the HINT:
You might need to increase max_pred_locks_per_transaction.
What is the reason for the error, that the shared memory ran out, or that the number of predicate locks is too small?
If i increase max_pred_locks_per_transaction i get probably even faster an "out of shared memory" error.
What is the best way to fix the problem?
The database consists of approximately 150 tables and is about 140GB in size. Where one table with 130 GB (~280000000 rows) requires the most space.
Please help me to troubleshoot this severe problem. I will feedback any required information.
Thanks in advance
Christian
________________________________
manroland web systems GmbH -- Managing Director: Joern Gossé
Registered Office: Augsburg -- Trade Register: AG Augsburg -- HRB-No.: 26816 -- VAT: DE281389840
Confidentiality note:
This eMail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, you are hereby notified that any use or dissemination of this communication is strictly prohibited. If you have received this eMail in error, then please delete this eMail.
! Please consider your environmental responsibility before printing this eMail !
________________________________
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Out of shared memory while creating a backup with pg_dump
@ 2014-09-24 10:02 Shreeyansh dba <shreeyansh2014@gmail.com>
parent: christian.echerer@manroland-web.com
2 siblings, 0 replies; 5+ messages in thread
From: Shreeyansh dba @ 2014-09-24 10:02 UTC (permalink / raw)
To: christian.echerer@manroland-web.com; +Cc: pgsql-admin
On Wed, Sep 24, 2014 at 2:35 PM, <christian.echerer@manroland-web.com>
wrote:
> Hi,
>
> while creating a backup with pg_dump and parallel write accesses to the
> database following error occured:
> 2014-09-24 07:46:46 ... ERROR: out of shared memory
> 2014-09-24 07:46:46 ... HINT: You might need to increase
> max_pred_locks_per_transaction.
>
> I´m running postgresql 9.3.4 on Ubuntu 12.04 server. The server has 8GB
> RAM and 4 cores.
> /proc/sys/kernel/shmmax: 2147483648
> /proc/sys/kernel/shmall: 2097152
>
> The original configuration was changed in only a few relevant points:
> * shared_buffers = 128MB
> * work_mem = 10MB
>
> We run a hot standby slave, therefore the following changes were necessary:
> * wal_level = hot_standby
> * max_wal_senders = 1
> * wal_keep_segments = 64
> * hot_standby = on
> * hot_standby_feedback = on
>
>
> I didn´t understand the HINT:
> You might need to increase max_pred_locks_per_transaction.
>
> What is the reason for the error, that the shared memory ran out, or that
> the number of predicate locks is too small?
> If i increase max_pred_locks_per_transaction i get probably even faster an
> "out of shared memory" error.
>
> What is the best way to fix the problem?
>
> The database consists of approximately 150 tables and is about 140GB in
> size. Where one table with 130 GB (~280000000 rows) requires the most space.
>
> Please help me to troubleshoot this severe problem. I will feedback any
> required information.
>
> Thanks in advance
> Christian
>
>
>
I suspect that this kind of errors occur due to less size of
shared_buffer,work_mem and maintenance_work_mem.
Solution to resolve this kind of errors , by increasing the size of
shared_buffers ,work_mem and maintenance_work_mem.
As your RAM is 8 GB , try to perform base-backup by setting the parameters
in postgresql.conf as given shared_buffers=(app 1GB to
2GB),work_mem=512MB,maintenance_work_mem=512MB.
This might resolve your issue of pg_basebackup.
Regards,
Chetan Sharma,
www.shreeyansh.com.
> ______________
> manroland web systems GmbH -- Managing Director: Joern Gossé
> Registered Office: Augsburg -- Trade Register: AG Augsburg -- HRB-No.:
> 26816 -- VAT: DE281389840
>
> Confidentiality note:
> This eMail and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you are not the intended recipient, you are hereby notified that any use
> or dissemination of this communication is strictly prohibited. If you have
> received this eMail in error, then please delete this eMail.
>
> ! Please consider your environmental responsibility before printing this
> eMail !
> ________________________________
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Out of shared memory while creating a backup with pg_dump
@ 2014-09-24 10:33 Raghu Ram <raghuchennuru@gmail.com>
parent: christian.echerer@manroland-web.com
2 siblings, 1 reply; 5+ messages in thread
From: Raghu Ram @ 2014-09-24 10:33 UTC (permalink / raw)
To: christian.echerer@manroland-web.com; +Cc: pgsql-admin
On Wed, Sep 24, 2014 at 2:35 PM, <christian.echerer@manroland-web.com>
wrote:
> Hi,
>
> while creating a backup with pg_dump and parallel write accesses to the
> database following error occured:
> 2014-09-24 07:46:46 ... ERROR: out of shared memory
> 2014-09-24 07:46:46 ... HINT: You might need to increase
> max_pred_locks_per_transaction.
>
>
This a new parameter introduced in PostgreSQL 9.1. This issue will be
resolved by increasing "max_pred_locks_per_transaction" parameter value
from default of 64 to 256 in PostgreSQL.conf file.
Thanks & Regards
Raghu Ram
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Out of shared memory while creating a backup with pg_dump
@ 2014-09-24 11:14 Shreeyansh dba <shreeyansh2014@gmail.com>
parent: Raghu Ram <raghuchennuru@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Shreeyansh dba @ 2014-09-24 11:14 UTC (permalink / raw)
To: Raghu Ram <raghuchennuru@gmail.com>; +Cc: pgsql-admin
On Wed, Sep 24, 2014 at 4:03 PM, Raghu Ram <raghuchennuru@gmail.com> wrote:
> On Wed, Sep 24, 2014 at 2:35 PM, <christian.echerer@manroland-web.com>
> wrote:
>
>> Hi,
>>
>> while creating a backup with pg_dump and parallel write accesses to the
>> database following error occured:
>> 2014-09-24 07:46:46 ... ERROR: out of shared memory
>> 2014-09-24 07:46:46 ... HINT: You might need to increase
>> max_pred_locks_per_transaction.
>>
>>
>
> This a new parameter introduced in PostgreSQL 9.1. This issue will be
> resolved by increasing "max_pred_locks_per_transaction" parameter value
> from default of 64 to 256 in PostgreSQL.conf file.
>
>
Along with this above parameters also change from max_wal_sender=1 to
max_wal_sender=5
Hope this will resolve your issue.
Thanks & Regards ,
Chetan Sharma
www.shreeyansh.com
Thanks & Regards
> Raghu Ram
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Out of shared memory while creating a backup with pg_dump
@ 2014-09-25 15:06 Kevin Grittner <kgrittn@ymail.com>
parent: christian.echerer@manroland-web.com
2 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2014-09-25 15:06 UTC (permalink / raw)
To: christian.echerer@manroland-web.com <christian.echerer@manroland-web.com>; pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-09-25 15:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-24 09:05 Out of shared memory while creating a backup with pg_dump christian.echerer@manroland-web.com
2014-09-24 10:02 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2014-09-24 10:33 ` Raghu Ram <raghuchennuru@gmail.com>
2014-09-24 11:14 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2014-09-25 15:06 ` Kevin Grittner <kgrittn@ymail.com>
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