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(dot)stanier(at)egsgroup(dot)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
>
>
>