Re: How to keep a table in memory?

Alex Drobychev wrote:
> Hi Heikki,
>
> Thanks for the response!
>
> I understand that relying on cache management would be the easiest
> solution. However, I had a similar issue with other RDBMS (MSSQL, to
> be specific) in the past and observed a lot of disk activity until the
> table was pinned in memory (fortunately MSSQL has 'dbcc pintable' for
> that).
>
> Basically, this is all about a high-traffic website, where virtually
> _all_ data in the DB get accessed frequently - so it's not obvious
> which DB pages are going to win the eviction war. However, the overall
> cost of access is different for different tables - for the table in
> question it very well may ~20 disk seeks per webpage view, so very
> high cache hit rate (ideally 100%) has to be assured.
>
> So - will the 'mlock' hack work? Or any other ideas for "pinning" a
> table in memory?
>
> - Alex
>
> */Heikki Linnakangas <heikki(at)enterprisedb(dot)com>/* wrote:
>
> adrobj wrote:
> > I have a pretty small table (~20MB) that is accessed very
> frequently and
> > randomly, so I want to make sure it's 100% in memory all the
> time. There is
> > a lot of other staff that's also gets accessed frequently, so I
> don't want
> > to just hope that Linux file cache would do the right thing for me.
> >
> > Is there any way to do that?
> >
> > One idea I have in my head is to start a process that does
> mmap() and
> > mlock() with the table file. Will it work? If so, are there any
> potential
> > problems?
>
> Just leave it to the cache management algorithms in Postgres and
> Linux.
> If it really is frequently accessed, it should stay in Postgres
> shared
> buffers.
>
> You can use the pg_buffercache contrib module to see what's in cache.
>

1. when someone replies to your post at the bottom, please don't put
your reply at the top. It makes everything totally unreadable.

2. you should investigate one or more of: pg_memcache, solid state disk.

FYI, Postgres is know to be used successfully on some *extremely* heavy
websites, without using tables pinned in memory.

cheers

andrew