Message-ID: <42CBEF84.8080007@egsgroup.com>
Date: Wed, 06 Jul 2005 15:49:40 +0100
From: Alexander Stanier <alexander.stanier@egsgroup.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Heavy virtual memory usage on production system
References: <42CAB1B6.50201@egsgroup.com> <23889.1120582516@sss.pgh.pa.us>
	<42CBC8A0.4070901@egsgroup.com> <25886.1120659333@sss.pgh.pa.us>
In-Reply-To: <25886.1120659333@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>  
>