On Mon, Feb 17, 2014 at 2:33 PM, Gabriel Sánchez Martínez < gabrielesanc...@gmail.com> wrote:
> Is there a way of asking PostgreSQL to read the files of a table directly > off the disk, asking the OS not to use the file cache? I am running > PostgreSQL 9.1 on Ubuntu Server 64-bit. The server in question has the > maximum amount of RAM it supports, but the database has grown much larger. > Most of the time it doesn't matter, because only specific tables or parts > of indexed tables are queried, and all of that fits in the file cache. But > we have a new requirement of queries to a table several times larger than > the total RAM, and the database has slowed down considerably for the other > queries. > > I am assuming that with every query to the large table, the OS caches the > files containing the table's data, and since the table is larger than total > RAM, all the old caches are cleared. The caches that were useful for other > smaller tables are lost, and the new caches of the large table are useless > because on the next query caching will start again from the first files of > the table. Please point out if there is a problem with this assumption. > Note that I am refering to OS file caching and not PostgreSQL caching. > > Is there a way around this? I have read that there is a way of asking the > OS not to cache a file when the file is opened. Is there a way of telling > PostgreSQL to use this option when reading files that belong a specific > table? > > What about putting the table on a tablespace that is on a different device > partition with the sync mount option? Would that help? > > All suggestions will be appreciated. > Can you please check the following extension, it may be useful to you. https://github.com/klando/pgfincore Regards, Hari Babu Fujitsu Australia
