Thread: Mapping a database completly into Memory
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
Daniel Migowski <postgresql@Mig-O.de> 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
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
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:
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.
Attachment
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
Franco Bruno Borghesi <franco@akyasociados.com.ar> 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
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
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
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:
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.
Attachment
Josh Berkus <josh@agliodbs.com> 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
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> Franco Bruno Borghesi <franco@akyasociados.com.ar> 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: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
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 <tgl@sss.pgh.pa.us> writes: > > TL> Franco Bruno Borghesi <franco@akyasociados.com.ar> 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: khera@kciLink.com 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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@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
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 <tgl@sss.pgh.pa.us> writes: > > TL> Franco Bruno Borghesi <franco@akyasociados.com.ar> 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: khera@kciLink.com 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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@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
>>>>> "BM" == Bruce Momjian <pgman@candle.pha.pa.us> 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 ;-(