tgl wrote:
> There is a secondary issue here, which is that we don't have provision
> to recycle hash table entries back into the general shared memory pool
> (mainly because there *is* no "shared memory pool", only never-yet-
> allocated space).  So when you do release these locks, the freed space
> only goes back to the lock hash table's freelist.  That means there
> won't be any space for expansion of the buffer hash table, nor any
other
> shared data structures.  This could lead to problems if you hadn't
been
> running the server long enough to expand the buffer table to full
size.

Ok, I confirmed that I'm running the server out of shared memory space,
not necessarily the lock table.  My server settings were:
max_connections: 100
shred bufs: 8192 buffers
max_locks: 64 (stock).

According to postgresql.conf, using these settings the lock table eats
64*260*100 bytes = < 2M.  Well, if it's running my server out of shared
memory, it's eating much, much more shmem than previously thought.

Also, I was able to acquire around 10k locks before the server borked.
This is obviously a lot more than than 64*100.  However, I set the
max_locks down to 10 and this did affect how many locks could be
acquired (and in this case, a server restart was not required).

Doubling shared buffers to 16k bumped my limit to over 20k locks, but
less than 25k.  As I see it, this means the user-locks (and perhaps all
locks...?) eat around ~ 6k bytes memory each.

This is not really a big deal, 10k locks is way more than a lock heavy
application would be expected to use.  I'll look into this a bit more...

Merlin



Tue, 28 Sep 2004 16:24:59 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: shared memory release following failed lock acquirement. 
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> According to postgresql.conf, using these settings the lock table eats
> 64*260*100 bytes = < 2M.  Well, if it's running my server out of shared
> memory, it's eating much, much more shmem than previously thought.

Hmm, the 260 is out of date I think.  I was seeing about 184 bytes/lock
in my tests just now.

> Also, I was able to acquire around 10k locks before the server borked.
> This is obviously a lot more than than 64*100.

Sure, because there's about 100K of deliberate slop in the shared memory
size allocation, and you are probably also testing a scenario where the
buffer and FSM hash tables haven't ramped to full size yet, so the lock
table is able to eat more than the nominal amount of space.

> As I see it, this means the user-locks (and perhaps all
> locks...?) eat around ~ 6k bytes memory each.

They're allocated in groups of 32, which would work out to close to 6k;
maybe you were measuring the incremental cost of allocating the first one?

I did some digging, and as far as I can see the only shared memory
allocations that occur after postmaster startup are for the four shmem
hash tables: buffers, FSM relations, locks, and proclocks.  Of these,
the buffer and FSM hashtables have predetermined maximum sizes.  So
arranging for the space in those tables to be fully preallocated should
prevent any continuing problems from lock table overflow.  I've
committed a fix that does this.  I verified that after running the thing
out of shared memory via creating a lot of user locks and then releasing
same, I could run the regression tests.

			regards, tom lane

Tue, 28 Sep 2004 16:54:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: shared memory release following failed lock acquirement. 
"Simon Riggs" <simon@2ndquadrant.com> writes:
> Does this mean that the parameter max_locks_per_transaction isn't honoured
> at all, it is just used to size the lock table

Yes, and that's how it's documented.

			regards, tom lane

Wed, 29 Sep 2004 17:45:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: shared memory release following failed lock acquirement. 
>Tom Lane
> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > According to postgresql.conf, using these settings the lock table eats
> > 64*260*100 bytes = < 2M.  Well, if it's running my server out of shared
> > memory, it's eating much, much more shmem than previously thought.
>
> Hmm, the 260 is out of date I think.  I was seeing about 184 bytes/lock
> in my tests just now.
>
> > Also, I was able to acquire around 10k locks before the server borked.
> > This is obviously a lot more than than 64*100.
>
> Sure, because there's about 100K of deliberate slop in the shared memory
> size allocation, and you are probably also testing a scenario where the
> buffer and FSM hash tables haven't ramped to full size yet, so the lock
> table is able to eat more than the nominal amount of space.
>
> > As I see it, this means the user-locks (and perhaps all
> > locks...?) eat around ~ 6k bytes memory each.
>
> They're allocated in groups of 32, which would work out to close to 6k;
> maybe you were measuring the incremental cost of allocating the first one?
>
> I did some digging, and as far as I can see the only shared memory
> allocations that occur after postmaster startup are for the four shmem
> hash tables: buffers, FSM relations, locks, and proclocks.  Of these,
> the buffer and FSM hashtables have predetermined maximum sizes.  So
> arranging for the space in those tables to be fully preallocated should
> prevent any continuing problems from lock table overflow.  I've
> committed a fix that does this.  I verified that after running the thing
> out of shared memory via creating a lot of user locks and then releasing
> same, I could run the regression tests.
>

Few questions:

Is that fix in 8.0?

Does this mean that the parameter max_locks_per_transaction isn't honoured
at all, it is just used to size the lock table - which itself can expand
beyond that max limit in various circumstances? (Though with the bug fix,
not THAT much more than the max limit)
Should we rename and redocument the parameter? If that is so, the current
name is so far away from its real meaning as to constitute a bug in
itself....

Best Regards, Simon Riggs



Wed, 29 Sep 2004 22:48:47 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: shared memory release following failed lock acquirement. 
>Tom Lane
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > Does this mean that the parameter max_locks_per_transaction
> isn't honoured
> > at all, it is just used to size the lock table
>
> Yes, and that's how it's documented.
>

The name max_locks_per_transaction indicates a limit of some kind. The
documentation doesn't mention anything about whether that limit is enforced
or not.

I suggest the additional wording:
"This parameter is not a hard limit: No limit is enforced on the number of
locks in each transaction. System-wide, the total number of locks is limited
by the size of the lock table."

The recent patch stops the system from crashing with an out of memory
condition, though this probably slightly hastens the condition of "no locks"
available. It would be good to clarify what behaviour the system exhibits
when we run out of locks.

I'm not sure myself now what that behaviour is: My understanding is that we
do not perform lock escalation (as does DB2), so presumably we just grind to
a halt? I take it that there is no automated way of getting out of this
situation? i.e. the deadlock detector doesn't start killing transactions
that hold lots of locks to free up space? So, we would basically just start
to build up lots of people waiting on locks - though without any mechanism
for diagnosing this is happening? What does happen and where does it end
(now)?

Best Regards, Simon Riggs


Thu, 30 Sep 2004 08:50:23 +0100
"Simon Riggs" <simon@2ndquadrant.com>
Re: shared memory release following failed lock acquirement. 