pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of shared memory in postgres 8.4.2 and locks
4+ messages / 2 participants
[nested] [flat]
* Out of shared memory in postgres 8.4.2 and locks
@ 2010-03-09 09:38 Vidhya Bondre <meetvbondre@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Vidhya Bondre @ 2010-03-09 09:38 UTC (permalink / raw)
To: pgsql-performance
Hi All,
We have installed postgres 8.4.2 on production.
We have a parition table structure for one of the table.
when i am drop the master table we get the following error.
drop table table_name cascade;
WARNING: out of shared memory
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
FYI : This sql was working fine on Postgres 8.3
Can some one please let me know what is going wrong here.
Regards
Vidhya
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Out of shared memory in postgres 8.4.2 and locks
@ 2010-03-09 12:34 Merlin Moncure <mmoncure@gmail.com>
parent: Vidhya Bondre <meetvbondre@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Merlin Moncure @ 2010-03-09 12:34 UTC (permalink / raw)
To: Vidhya Bondre <meetvbondre@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 9, 2010 at 4:38 AM, Vidhya Bondre <meetvbondre@gmail.com> wrote:
> Hi All,
>
> We have installed postgres 8.4.2 on production.
>
> We have a parition table structure for one of the table.
>
> when i am drop the master table we get the following error.
>
> drop table table_name cascade;
> WARNING: out of shared memory
> ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
>
> FYI : This sql was working fine on Postgres 8.3
>
> Can some one please let me know what is going wrong here.
are you using the same postgresql.conf? have you created more
partitions? using advisory locks?
In any event, increase the max_locks_per_transaction setting and
restart the database.
merlin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Out of shared memory in postgres 8.4.2 and locks
@ 2010-03-09 13:27 Vidhya Bondre <meetvbondre@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Vidhya Bondre @ 2010-03-09 13:27 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 9, 2010 at 6:04 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Mar 9, 2010 at 4:38 AM, Vidhya Bondre <meetvbondre@gmail.com>
> wrote:
> > Hi All,
> >
> > We have installed postgres 8.4.2 on production.
> >
> > We have a parition table structure for one of the table.
> >
> > when i am drop the master table we get the following error.
> >
> > drop table table_name cascade;
> > WARNING: out of shared memory
> > ERROR: out of shared memory
> > HINT: You might need to increase max_locks_per_transaction.
> >
> > FYI : This sql was working fine on Postgres 8.3
> >
> > Can some one please let me know what is going wrong here.
>
> are you using the same postgresql.conf? have you created more
> partitions? using advisory locks?
>
Yes we are using same conf files. In a week we create around 5 partitions.
We are not using advisory locks
>
> In any event, increase the max_locks_per_transaction setting and
> restart the database.
>
Currently the value of max_locks_per_transaction is 64 modifying it to 96
works.
Have a couple of questions
1] As and when we add partitions will we have to increase this parameter ?
2] will we have to consider any othe parameter twick while increasing this
one?
>
> merlin
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Out of shared memory in postgres 8.4.2 and locks
@ 2010-03-09 15:03 Merlin Moncure <mmoncure@gmail.com>
parent: Vidhya Bondre <meetvbondre@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Merlin Moncure @ 2010-03-09 15:03 UTC (permalink / raw)
To: Vidhya Bondre <meetvbondre@gmail.com>; +Cc: pgsql-performance
On Tue, Mar 9, 2010 at 8:27 AM, Vidhya Bondre <meetvbondre@gmail.com> wrote:
>>
>> are you using the same postgresql.conf? have you created more
>> partitions? using advisory locks?
>
> Yes we are using same conf files. In a week we create around 5 partitions.
> We are not using advisory locks
>>
>> In any event, increase the max_locks_per_transaction setting and
>> restart the database.
>
>
> Currently the value of max_locks_per_transaction is 64 modifying it to 96
> works.
> Have a couple of questions
> 1] As and when we add partitions will we have to increase this parameter ?
> 2] will we have to consider any othe parameter twick while increasing this
> one?
If you are doing 'in transaction' operations that involve a lot of
tables this figure has to be bumped, sometimes significantly. You
might be tempted just crank it, and be done with this problem. If
so, be advised of what happens when you do:
*) more shared memory usage (make sure you have memory and
shared_buffers is appropriately set). however for what you get the
usage is relatively modest.
*) anything that scans the entire in memory lock table takes longer in
relationship to this .conf value. AFAIK, the only noteworthy thing
that does this is the pg_locks view.
The 'other' big shared memory tradeoff you historically had to deal
with, the fsm map, is gone in 8.4.
merlin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-03-09 15:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-09 09:38 Out of shared memory in postgres 8.4.2 and locks Vidhya Bondre <meetvbondre@gmail.com>
2010-03-09 12:34 ` Merlin Moncure <mmoncure@gmail.com>
2010-03-09 13:27 ` Vidhya Bondre <meetvbondre@gmail.com>
2010-03-09 15:03 ` Merlin Moncure <mmoncure@gmail.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