I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes into memory when it's possible. Am I right? Is there any method to tell postgres don't load indexes into memory.
4
-
Why would you ever store tables/indexes on ramdisk? Why not give the memory directly to postgres via configuration?– Ihor RomanchenkoCommented Nov 15, 2013 at 15:48
-
Postgresql uses the OS caching mechanisms, so there is no way to not have the indexes and relations in cache if they are being used to service queries. (at least, no way that I have ever heard of)– bmaCommented Nov 15, 2013 at 15:48
-
@IgorRomanchenko: it can make some sense when running a cache with unlogged tables in a ramdisk — though admittedly, one might as well use memcached in that case. :-)– Denis de BernardyCommented Nov 15, 2013 at 16:15
-
1How exactly did you "store tables on a ramdisk"? I really, really hope you didn't use a tablespace for that, unless you don't care about any of that database's contents at all.– Craig RingerCommented Dec 23, 2013 at 4:45
Add a comment
|
1 Answer
If you force indexes and tables into RAM by using a ramdisk, all you're likely to be doing is wasting RAM.
PostgreSQL uses the operating system's buffer cache. So when a table/index page is first read, it's cached by the OS, and future reads are returned by the OS cache, not fetched from disk. If the database is bigger than the available memory then the OS will throw oldest, least-used data away and it'll get read from disk next time it's needed.
If you create a ramdisk, you are reducing the amount of memory the OS can use for cache. It'll still cache each block read from the ram disk before returning it to PostgreSQL. If there's not enough space left for efficient memory management of the buffer cache the kernel will do a lot of work trying to free pages for buffered reads, and your ramdisk will actually slow things down.
For writes, the main effect the ramdisk has is to make all disk sync operations effectively no-ops. So it's like running PostgreSQL with fsync=off, where it never tries to flush writes to disk at all. It has the same property of being able to eat your data whenever it feels like it, too.
There's generally not much benefit in running PostgreSQL on a ramdisk vs running it with data durability settings turned off, a very high dirty writeback threshold, etc.
(On a side note, if you create a tablespace on a ramdisk and put anything in it, you're making the whole database cluster - i.e. all databases on that server - at risk of severe data loss. Don't do it. Either initdb the whole database on the ramdisk or, preferably, just tune the server so you don't need to mess about with ramdisks.)
answered Dec 23, 2013 at 4:50
