Re: max_locks_per_transactions ...

Simon Riggs wrote:
> On Thu, 2007-02-01 at 09:15 +0100, Hans-Juergen Schoenig wrote:
>
>> Right now max_locks_per_transactions defines the average number of locks
>> taken by a transaction. thus, shared memory is limited to
>> max_locks_per_transaction * (max_connections + max_prepared_transactions).
>> this is basically perfect. however, recently we have seen a couple of
>> people having trouble with this. partitioned tables are becoming more
>> and more popular so it is very likely that a single transaction can eat
>> up a great deal of shared memory. some people having a lot of data
>> create daily tables. if done for 3 years we already lost 1000 locks per
>> inheritance-structure.
>>
>> i wonder if it would make sense to split max_locks_per_transaction into
>> two variables: max_locks (global size) and max_transaction_locks (local
>> size). if set properly this would prevent "good" short running
>> transactions from running out of shared memory when some "evil" long
>> running transactions start to suck up shared memory.
>>
>
> Do partitioned tables use a lock even when they are removed from the
> plan as a result of constraint_exclusion? I thought not. So you have
> lots of concurrent multi-partition scans.
>
>

maybe i was a bit unprecise before - let me clarify.
the application we are talking about contains > 1 tb of data. the main
table (about 90% of the data) is partitioned into about 3.700 subtables.
for this kind of application this makes perfect sense as subsets of data
(= subtable) change frequently.

two types of queries are executed by the system:

- short OLTP operations adding data to the huge tables
- a large set of analysis stuff which tortures the database with
more complex queries.

the main issue is that to a large extend those analysis queries have to
run concurrently.
the thing now is: if there are many concurrent operations which need
this partitioned structure the amount of locks is growing quite fast (in
this +3700 locks per transaction).
so, it can happen that we run out of shared memory inside some OLTP
transaction just because too many background processes are sucking up
shared memory.

of course it would be simple to pump max_locks_per_transaction - this is
not the point.
the idea is rather: max_locks_per_transaction is a somehow obscure way
of putting things. many people are simply misleaded. most people assume
that this is indeed a per transaction limit and then they are surprised
when a transaction which hardly needs locks fails.

i would suggest to replace the existing parameter but something else:

- a switch to define the global size of the lock pool (e.g. "max_locks")
- a switch which defines the upper limit for the current backend /
transaction

we could make a transaction fail which takes too many locks.
the advantage would be that the transaction causes the problem and not
some other "innocent" small operation.

best regards,

hans

--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at