Re: Heavy virtual memory usage on production system

Looks as though there are several processes which are acquiring a load
of locks:

pid  | count
------+-------
 3193 |     2
 3192 |     9
 3191 |     7
 3190 |     3
 3189 |     2
 3188 |     3
 3187 |     3
 3186 |     3
 3185 |     3
 3184 |     3
 3183 |     3
 3182 |    13
 3181 |     3
 3179 |    10
 3175 |    13
 3174 |     2
 3173 |    10
 2917 |     3
 3153 |     8
 3150 |     8
 3149 |     8
 3146 |     9
 3145 |     8
 3144 |     8
 3143 |     9
 3142 |     3
 3141 |    10
 3127 |     8
 3125 |    13
 3124 |    13
 3121 |     8
 3118 |     8
 3114 |     8
 3113 |     8
 3110 |     8
 3106 |     8
 3104 |     9
 3102 |     8
 3100 |    13
 2314 |     2
(40 rows)

I guess it might be worth us getting this server up to PostgreSQL 8.0.3.
At least we can then discount that as a problem.

Regards, Alex Stanier.

Tom Lane wrote:

>Alexander Stanier <alexander.stanier@egsgroup.com> writes:
>
>
>>The problem happened again this morning and I took the chance to check
>>out the locking situation. The number of locks increased dramatically up
>>to over 1000, but they were all "AccessShareLocks" and all were granted.
>>The odd "RowExclusiveLock" appeared but none persisted. On the basis
>>that nothing seems to be waiting for a lock, I don't think it is a
>>locking problem.
>>
>>
>
>Hmm.  How many active processes were there, and how many locks per
>process?  (A quick "SELECT pid, count(*) GROUP BY pid" query should give
>you this info next time.)  We just recently got rid of some O(N^2)
>behavior in the lock manager for cases where a single backend holds many
>different locks.  So if there's a single query acquiring a whole lot of
>locks, that could possibly have something to do with this.
>
>            regards, tom lane
>
>
>