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?
--
View this message in context: http://www.nabble.com/How-to-keep-a-...html#a13700771
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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@enterprisedb.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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com



---------------------------------
Never miss a thing. Make Yahoo your homepage.

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@enterprisedb.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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Monday 12 November 2007 18:31, Andrew Dunstan wrote:
> 1. when someone replies to your post at the bottom, please don't put
> your reply at the top. It makes everything totally unreadable.
>

+1

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

you might also consider creating a tablespace on tmpfs or ramfs or something
like pramfs

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

+1

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Robert Treat wrote:
> On Monday 12 November 2007 18:31, Andrew Dunstan wrote:
>> 1. when someone replies to your post at the bottom, please don't put
>> your reply at the top. It makes everything totally unreadable.
>>
>
> +1
>
>> 2. you should investigate one or more of: pg_memcache, solid state disk.
>>
>
> you might also consider creating a tablespace on tmpfs or ramfs or something
> like pramfs
>
>> FYI, Postgres is know to be used successfully on some *extremely* heavy
>> websites, without using tables pinned in memory.
>>
>
> +1

I give this a +/- 1. Yes extremely heavy websites can do this *but* they
require extremely expensive hardware to do so.

Joshua D. Drake



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hi,

On Mon, 2007-11-12 at 21:15 -0500, Robert Treat wrote:
> > 2. you should investigate one or more of: pg_memcache, solid state
> > disk.
>
> you might also consider creating a tablespace on tmpfs or ramfs or
> something like pramfs

IIRC, ramfs are not that good for database use: If you want to extend
its size, you have to stop the database instance -- which is not
considered good.

Regards,
--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQBHORA1pme12CBXnxERAkAlAJ9dRy+syCMgWbmBA8NVSt 2uh6OJpwCgghu5
t62D2cUw+ZI3IOlgzvQh+Qg=
=1npt
-----END PGP SIGNATURE-----

Joshua D. Drake wrote:
>>
>>> FYI, Postgres is know to be used successfully on some *extremely* heavy
>>> websites, without using tables pinned in memory.
>>>
>>
>> +1
>
> I give this a +/- 1. Yes extremely heavy websites can do this *but*
> they require extremely expensive hardware to do so.
>
>

I expect extremely heavy websites to require extremely expensive
equipment regardless of the software they use. Cost was not the issue
raised by the OP.

cheers

andrew
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Devrim GÜNDÜZ wrote:
> Hi,
>
> On Mon, 2007-11-12 at 21:15 -0500, Robert Treat wrote:
>>> 2. you should investigate one or more of: pg_memcache, solid state
>>> disk.
>> you might also consider creating a tablespace on tmpfs or ramfs or
>> something like pramfs
>
> IIRC, ramfs are not that good for database use: If you want to extend
> its size, you have to stop the database instance -- which is not
> considered good.

Well, depending on the size you could push the table to another table
space, drop the old table space, resize the ramfs, and reverse the
previous

Joshua D. Drake

>
> Regards,


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Andrew Dunstan wrote:
>
>> I give this a +/- 1. Yes extremely heavy websites can do this *but*
>> they require extremely expensive hardware to do so.
>>
>>
>
> I expect extremely heavy websites to require extremely expensive
> equipment regardless of the software they use. Cost was not the issue
> raised by the OP.

Cost is always an issue, even if implicit. If the person is so hung up
on the idea of pushing things into ram there is a pretty good
possibility they have priced out the 50 and 100 spindle devices needed
to get the same type of performance.

Sincerely,

Joshua D. Drake


>
> cheers
>
> andrew
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 1: 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