Re: lazy vxid locks, v1

On Mon, Jun 13, 2011 at 8:10 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Sun, Jun 12, 2011 at 2:39 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> ...
>>
>> Profiling reveals that the system spends enormous amounts of CPU time
>> in s_lock.  LWLOCK_STATS reveals that the only lwlock with significant
>> amounts of blocking is the BufFreelistLock;
>
> This is curious.  Clearly the entire working set fits in RAM, or you
> wouldn't be getting number like this.  But does the entire working set
> fit in shared_buffers?  If so, you shouldn't see any traffic on
> BufFreelistLock once all the data is read in.  I've only seen
> contention here when all data fits in OS cache memory but not in
> shared_buffers.

Yeah, that does seem odd:

rhaas=# select pg_size_pretty(pg_database_size(current_database()));
pg_size_pretty
----------------
1501 MB
(1 row)

rhaas=# select pg_size_pretty(pg_table_size('pgbench_accounts'));
pg_size_pretty
----------------
1281 MB
(1 row)

rhaas=# select pg_size_pretty(pg_table_size('pgbench_accounts_pkey'));
pg_size_pretty
----------------
214 MB
(1 row)

rhaas=# show shared_buffers;
shared_buffers
----------------
8GB
(1 row)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company