pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedmax_locks_per_transaction and partitioned tables
4+ messages / 2 participants
[nested] [flat]
* max_locks_per_transaction and partitioned tables
@ 2012-09-19 18:25 Michael Holt <MHolt@terapeak.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Holt @ 2012-09-19 18:25 UTC (permalink / raw)
To: pgsql-admin
We've had a system in operation for a few years that makes use of a substantial amount of partitioning. The parent table now has over 4,000 children tables. Within the last couple of days the server started giving "out of shared memory" errors with the suggestion to increase the max_locks_per_transaction.
If the parent table is queried will it require a lock for each one of the child tables? I'm guessing it will.
--
Michael Holt | Manager, Data Services | Linkedin Profile<http://ca.linkedin.com/pub/michael-holt/48/a21/5ab;
michael@terapeak.com<mailto:michael@terapeak.com>
102-3962 Borden Street, Victoria, B.C., Canada V8P 3H8
^ permalink raw reply [nested|flat] 4+ messages in thread
* max_locks_per_transaction and partitioned tables
@ 2012-09-19 18:28 Michael Holt <MHolt@terapeak.com>
2012-09-19 21:03 ` Re: max_locks_per_transaction and partitioned tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Michael Holt @ 2012-09-19 18:28 UTC (permalink / raw)
To: pgsql-admin
We've had a system in operation for a few years that makes use of a substantial amount of partitioning. The parent table now has over 4,000 children tables. Within the last couple of days the server started giving "out of shared memory" errors with the suggestion to increase the max_locks_per_transaction.
If the parent table is queried will it require a lock for each one of the child tables? I'm guessing it will.
--
Michael Holt | Manager, Data Services | Linkedin Profile<http://ca.linkedin.com/pub/michael-holt/48/a21/5ab;
michael@terapeak.com<mailto:michael@terapeak.com>
102-3962 Borden Street, Victoria, B.C., Canada V8P 3H8
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: max_locks_per_transaction and partitioned tables
2012-09-19 18:28 max_locks_per_transaction and partitioned tables Michael Holt <MHolt@terapeak.com>
@ 2012-09-19 21:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-19 21:17 ` Re: max_locks_per_transaction and partitioned tables Michael Holt <MHolt@terapeak.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2012-09-19 21:03 UTC (permalink / raw)
To: Michael Holt <MHolt@terapeak.com>; +Cc: pgsql-admin
Michael Holt <MHolt@terapeak.com> writes:
> We've had a system in operation for a few years that makes use of a substantial amount of partitioning. The parent table now has over 4,000 children tables. Within the last couple of days the server started giving "out of shared memory" errors with the suggestion to increase the max_locks_per_transaction.
> If the parent table is queried will it require a lock for each one of the child tables? I'm guessing it will.
Yup, it will. I'm a bit astonished that you've gotten this far without
horrid performance problems. The underlying mechanisms for inheritance
aren't really designed to scale past perhaps a hundred child tables.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: max_locks_per_transaction and partitioned tables
2012-09-19 18:28 max_locks_per_transaction and partitioned tables Michael Holt <MHolt@terapeak.com>
2012-09-19 21:03 ` Re: max_locks_per_transaction and partitioned tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-09-19 21:17 ` Michael Holt <MHolt@terapeak.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Holt @ 2012-09-19 21:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks Tom. In the original plan a query of this sort was never supposed to happen, but it looks like some coding issues may have allowed it.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: September-19-12 2:04 PM
To: Michael Holt
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] max_locks_per_transaction and partitioned tables
Michael Holt <MHolt@terapeak.com> writes:
> We've had a system in operation for a few years that makes use of a substantial amount of partitioning. The parent table now has over 4,000 children tables. Within the last couple of days the server started giving "out of shared memory" errors with the suggestion to increase the max_locks_per_transaction.
> If the parent table is queried will it require a lock for each one of the child tables? I'm guessing it will.
Yup, it will. I'm a bit astonished that you've gotten this far without horrid performance problems. The underlying mechanisms for inheritance aren't really designed to scale past perhaps a hundred child tables.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-09-19 21:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-19 18:25 max_locks_per_transaction and partitioned tables Michael Holt <MHolt@terapeak.com>
2012-09-19 18:28 max_locks_per_transaction and partitioned tables Michael Holt <MHolt@terapeak.com>
2012-09-19 21:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-09-19 21:17 ` Michael Holt <MHolt@terapeak.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