From: | <christian(dot)echerer(at)manroland-web(dot)com> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Out of shared memory while creating a backup with pg_dump |
Date: | 2014-09-24 09:05:49 |
Message-ID: | D5359E0908278642BB1747131D62694DA86A0460@AUSMXMBX01.mrws.biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 !
________________________________
From | Date | Subject | |
---|---|---|---|
Next Message | Shreeyansh dba | 2014-09-24 10:02:58 | Re: Out of shared memory while creating a backup with pg_dump |
Previous Message | Axel Rau | 2014-09-24 09:00:05 | [RESOLVED]Re: Cert verify failed on client side after renewal of certs |