Thread

1. Re: [HACKERS] Postgres Performance

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-09-03T16:09:21Z

   Edwin Ramirez <ramirez@doc.mssm.edu> writes:
   > I have a couple of large(?) tables which I would like to keep them in
   > memory (cached) so that searches are performed as fast as possible.
   > Is it possible to 'pin' the tables and it's indexes in memory?  
   
   If the tables are being touched often, then they will stay in buffer
   cache of their own accord.  I doubt that pinning them would improve
   performance --- if they do get swapped out it'd be because some other
   table(s) need to be accessed now, and if you did have these tables
   pinned you'd be taking a large hit in access performance for those other
   tables because of inadequate buffer space.  LRU buffering policy really
   works pretty well, so I don't think you need to worry about it.
   
   > currently I run the postmaster with the following setting: 
   > 	postmaster -i -B 2048 -o '-S 2048'
   > Are there any other options/values which would yield better performance?
   
   If you have a reliable OS and power source, consider -o -F (no fsync).
   This usually makes for a very substantial performance improvement, and
   it can only hurt if your machine goes down without having performed
   all the writes the kernel was told to do.
   
   			regards, tom lane
   
   

2. Re: [HACKERS] Postgres Performance

   Edwin Ramirez <ramirez@doc.mssm.edu> — 1999-09-08T21:05:38Z

   If I do a large search the first time is about three times slower than
   any subsequent overlapping (same data) searches.  I would like to always
   get the higher performance. 
   
   How are the buffers that I specify to the postmaster used?
   Will increasing this number improve things?
   
   The issue that I am encountering is that no matter how much memory I
   have on a computer, the performance is not improving.  I am willing to
   fund a project to implement a postgres specific, user configurable
   cache.
   
   Any ideas?
   -Edwin S. Ramirez-
   
   Tom Lane wrote:
   > 
   > Edwin Ramirez <ramirez@doc.mssm.edu> writes:
   > > I have a couple of large(?) tables which I would like to keep them in
   > > memory (cached) so that searches are performed as fast as possible.
   > > Is it possible to 'pin' the tables and it's indexes in memory?
   > 
   > If the tables are being touched often, then they will stay in buffer
   > cache of their own accord.  I doubt that pinning them would improve
   > performance --- if they do get swapped out it'd be because some other
   > table(s) need to be accessed now, and if you did have these tables
   > pinned you'd be taking a large hit in access performance for those other
   > tables because of inadequate buffer space.  LRU buffering policy really
   > works pretty well, so I don't think you need to worry about it.
   > 
   > > currently I run the postmaster with the following setting:
   > >       postmaster -i -B 2048 -o '-S 2048'
   > > Are there any other options/values which would yield better performance?
   > 
   > If you have a reliable OS and power source, consider -o -F (no fsync).
   > This usually makes for a very substantial performance improvement, and
   > it can only hurt if your machine goes down without having performed
   > all the writes the kernel was told to do.
   > 
   >                         regards, tom lane
   > 
   > ************
   
   

3. Re: [HACKERS] Postgres Performance

   Michael Simms <grim@argh.demon.co.uk> — 1999-09-08T21:41:04Z

   > 
   > If I do a large search the first time is about three times slower than
   > any subsequent overlapping (same data) searches.  I would like to always
   > get the higher performance. 
   > 
   > How are the buffers that I specify to the postmaster used?
   > Will increasing this number improve things?
   > 
   > The issue that I am encountering is that no matter how much memory I
   > have on a computer, the performance is not improving.  I am willing to
   > fund a project to implement a postgres specific, user configurable
   > cache.
   > 
   > Any ideas?
   > -Edwin S. Ramirez-
   
   I think that the fact you are seeing an improvement already shows a good level
   of caching.
   
   What happens the first time is that it must read the data off the disc. After
   that the data comes from memory IF it is cached. Disc read will always be
   slower with current disc technology.
   
   I would imagine (Im not an expert, but through observation) that if you
   drasticly increase the number of shared memory buffers, then when you
   startup your front-end simply do a select * from the tables, it may even keep
   them all in memory from the start.
   
   						M Simms