Re: pg_lock_status() performance

On Tue, Apr 28, 2009 at 5:42 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Apr 28, 2009 at 5:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Merlin Moncure <mmoncure@gmail.com> writes:
>>> I have a unloaded development server running 8.4b1 that is returning
>>> from a 'select * from pg_locks' in around 5 ms.  While the time itself
>>> is not a big deal, I was curious and tested querying locks on a fairly
>>> busy (200-500 tps sustained)  running 8.2 on inferior hardware.  This
>>> returned (after an initial slower time) in well under 1 ms most of the
>>> time.  Is this noteworthy?  What factors slow down best case
>>> pg_lock_status() performance?
>>
>>> edit: I bet it's the max_locks_per_transaction parameter. I really
>>> cranked it on the dev box during an experiment, to 16384.
>>> testing...yup that's it.  Are there any negative performance
>>> side-effects that could result from (perhaps overly) cranked
>>> max_locks_per_transaction?
>>
>> [squint...]  AFAICS the only *direct* cost component in pg_lock_status
>> is the number of locks actually held or awaited.  If there's a
>> noticeable component that depends on max_locks_per_transaction, it must
>> be from hash_seq_search() iterating over empty hash buckets.  Which is
>> a mighty tight loop.  What did you have max_connections set to?
>
> 16384 :D
>
> (I was playing with a function that created a large number of tables/schemas)

oops. misread that...the default 100.

merlin