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 <[EMAIL PROTECTED]>/* 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
