Обсуждение: Mapping a database completly into Memory

От:
Daniel Migowski
Дата:

Hallo pgsql-performance,

I just wondered if there is a possibility to map my database running
on a linux system completly into memory and to only use disk
accesses for writes.

I got a nice machine around with 2 gigs of ram, and my database at
the moment uses about 30MB on the disks.

Or does Postgresql do this automtatically, with some cache adjusting
parameters, and after doing a select * from <everything> on my
database?

Thank you and ciao,
 Mig-O



От:
Tom Lane
Дата:

Daniel Migowski <> writes:
> I just wondered if there is a possibility to map my database running
> on a linux system completly into memory and to only use disk
> accesses for writes.

That happens for free, if you have enough RAM.  The kernel will use
spare RAM to hold copies of every disk block it's ever read.

            regards, tom lane

От:
Josh Berkus
Дата:

Daniel,

> > I just wondered if there is a possibility to map my database running
> > on a linux system completly into memory and to only use disk
> > accesses for writes.
>
> That happens for free, if you have enough RAM.  The kernel will use
> spare RAM to hold copies of every disk block it's ever read.

Also, don't forget to raise your effective_cache_size so that PostgreSQL
*knows* that you have lots of RAM.

--
Josh Berkus
Aglio Database Solutions
San Francisco

От:
Franco Bruno Borghesi
Дата:

wouldn't also increasing shared_buffers to 64 or 128 MB be a good performance improvement? This way, pages belonging to heavily used indexes would be already cached by the database itself.

Please, correct me if I'm wrong.

On Mon, 2003-07-28 at 01:14, Josh Berkus wrote:
Daniel,

> > I just wondered if there is a possibility to map my database running
> > on a linux system completly into memory and to only use disk
> > accesses for writes.
>
> That happens for free, if you have enough RAM.  The kernel will use
> spare RAM to hold copies of every disk block it's ever read.

Also, don't forget to raise your effective_cache_size so that PostgreSQL 
*knows* that you have lots of RAM.
От:
Josh Berkus
Дата:

Tom,

> If we had a portable way
> of preventing the kernel from caching the same page, it would make more
> sense to run with large shared_buffers.

Really?  I thought we wanted to move the other way ... that is, if we could
get over the portability issues, eliminate shared_buffers entirely and rely
completely on the OS cache.

--
Josh Berkus
Aglio Database Solutions
San Francisco

От:
Tom Lane
Дата:

Franco Bruno Borghesi <> writes:
> wouldn't also increasing shared_buffers to 64 or 128 MB be a good
> performance improvement? This way, pages belonging to heavily used
> indexes would be already cached by the database itself.

Not necessarily.  The trouble with large shared_buffers settings is you
end up with lots of pages being doubly cached (both in PG's buffers and
in the kernel's disk cache), thus wasting RAM.  If we had a portable way
of preventing the kernel from caching the same page, it would make more
sense to run with large shared_buffers.

            regards, tom lane

От:
Andrew Sullivan
Дата:

On Mon, Jul 28, 2003 at 12:25:57PM -0400, Tom Lane wrote:
> in the kernel's disk cache), thus wasting RAM.  If we had a portable way
> of preventing the kernel from caching the same page, it would make more
> sense to run with large shared_buffers.

Plus, Postgres seems not to be very good at managing very large
buffer sets.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<>                              M2P 2A8
                                         +1 416 646 3304 x110


От:
Franco Bruno Borghesi
Дата:

But I think it's still a good option.

For example, in servers where there are other applications running (a web server, for example) that are constantly accesing the disk and replacing cached postgresql pages in the kernel, having shared buffers could reduce this efect and assure the precense of our pages in memory... I gues :)

On Mon, 2003-07-28 at 13:50, Josh Berkus wrote:
Tom,

> If we had a portable way
> of preventing the kernel from caching the same page, it would make more
> sense to run with large shared_buffers.

Really?  I thought we wanted to move the other way ... that is, if we could 
get over the portability issues, eliminate shared_buffers entirely and rely 
completely on the OS cache.
От:
Tom Lane
Дата:

Josh Berkus <> writes:
>> If we had a portable way
>> of preventing the kernel from caching the same page, it would make more
>> sense to run with large shared_buffers.

> Really?  I thought we wanted to move the other way ... that is, if we could
> get over the portability issues, eliminate shared_buffers entirely and rely
> completely on the OS cache.

That seems unlikely to happen: there are cache-coherency problems if you
don't do your page-level access through shared buffers.  Some have
suggested using mmap access to the data files in place of shared memory,
but that introduces a slew of issues of its own.  It might happen but
I'm not holding my breath.

            regards, tom lane

От:
Vivek Khera
Дата:

>>>>> "TL" == Tom Lane <> writes:

TL> Franco Bruno Borghesi <> writes:
>> wouldn't also increasing shared_buffers to 64 or 128 MB be a good
>> performance improvement? This way, pages belonging to heavily used
>> indexes would be already cached by the database itself.

TL> Not necessarily.  The trouble with large shared_buffers settings is you
TL> end up with lots of pages being doubly cached (both in PG's buffers and

I think if you do a lot of inserting/updating to your table, then more
SHM is better (and very high fsm settings), since you defer pushing
out the dirty pages to the disk.  For read-mostly, I agree that
letting the OS do the caching is a better way.


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet:        Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

От:
Bruce Momjian
Дата:

I think it all depends on your working set.  Having shared memory be
smaller than you working set causes pages to have to be copied in from
the kernel buffers (not a huge problem, but a small penalty), while
having shared memory larger than the working set causes overhead of
searching through all those buffers.

---------------------------------------------------------------------------

Vivek Khera wrote:
> >>>>> "TL" == Tom Lane <> writes:
>
> TL> Franco Bruno Borghesi <> writes:
> >> wouldn't also increasing shared_buffers to 64 or 128 MB be a good
> >> performance improvement? This way, pages belonging to heavily used
> >> indexes would be already cached by the database itself.
>
> TL> Not necessarily.  The trouble with large shared_buffers settings is you
> TL> end up with lots of pages being doubly cached (both in PG's buffers and
>
> I think if you do a lot of inserting/updating to your table, then more
> SHM is better (and very high fsm settings), since you defer pushing
> out the dirty pages to the disk.  For read-mostly, I agree that
> letting the OS do the caching is a better way.
>
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                Khera Communications, Inc.
> Internet:        Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to  so that your
>       message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Bruce Momjian
Дата:

You make an interesting distinction that read/write needs more shared
memory.  I think this is because if you want to reused a read-only
shared buffer, you can just throw away the contents, while a dirty
buffer requires you to write it into the kernel before you can use it.

---------------------------------------------------------------------------

Vivek Khera wrote:
> >>>>> "TL" == Tom Lane <> writes:
>
> TL> Franco Bruno Borghesi <> writes:
> >> wouldn't also increasing shared_buffers to 64 or 128 MB be a good
> >> performance improvement? This way, pages belonging to heavily used
> >> indexes would be already cached by the database itself.
>
> TL> Not necessarily.  The trouble with large shared_buffers settings is you
> TL> end up with lots of pages being doubly cached (both in PG's buffers and
>
> I think if you do a lot of inserting/updating to your table, then more
> SHM is better (and very high fsm settings), since you defer pushing
> out the dirty pages to the disk.  For read-mostly, I agree that
> letting the OS do the caching is a better way.
>
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                Khera Communications, Inc.
> Internet:        Rockville, MD       +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to  so that your
>       message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
Vivek Khera
Дата:

>>>>> "BM" == Bruce Momjian <> writes:

BM> I think it all depends on your working set.  Having shared memory be
BM> smaller than you working set causes pages to have to be copied in from
BM> the kernel buffers (not a huge problem, but a small penalty), while
BM> having shared memory larger than the working set causes overhead of
BM> searching through all those buffers.

i.e., It is a black art, and no single piece of advice can be taken in
isolation ;-(