Skip site navigation (1) Skip section navigation (2)

Re: Notes on lock table spilling

From: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Notes on lock table spilling
Date: 2005-03-31 20:18:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Thu, Mar 31, 2005 at 12:19:08AM -0500, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> > We have a problem as soon as somebody tries to delete a lot of rows from
> > a big table.  We cannot possibly extend the memory requirements forever,
> > so we need to spill to disk without having an in-shared-memory index.
> Yes.  I'm not sure that I see the point of the in-memory index at all...
> there is some intermediate regime where it would improve performance,
> but it surely does not solve the basic problem that shared memory is
> finite.

My idea was that it would help eliminate I/O.  But probably you are
right that it may be the wrong idea; probably it's better to have an
on-disk index for the on-disk storage of LOCK (we don't want sequential
scanning of an on-disk lock array, do we?).  If it's cached in memory,
then no I/O would be needed until we started recording a lot of locks,
thus achieving the same effect with simpler code and better degradation.

I'm thinking in sketching some sort of simple btree on top of slru
pages.  Nothing concrete yet.

> Maybe something involving lossy storage would work?  Compare recent
> discussions about lossy bitmaps generated from index scans.

Hmm.  Some problems come to mind:

- How to decide when to use lossy storage.  Perhaps if the executor (or
  rather, the planner) thinks it is about to acquire lots of tuple
  locks, then it could hint the lock manager.

- How to unlock?  (or: how to know when the lock is released for sure?)
  I think this can be solved by counting lockers/unlockers.

Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe)

In response to

pgsql-hackers by date

Next:From: Steve CrawfordDate: 2005-03-31 20:44:15
Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables
Previous:From: Tom LaneDate: 2005-03-31 20:06:13
Subject: Re: [ADMIN] Excessive growth of pg_attribute and other system tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group