Skip site navigation (1) Skip section navigation (2)

Re: PG 8.3 and large shared buffer settings

From: Pierre Frédéric Caillaud <lists(at)peufeu(dot)com>
To: "Greg Smith" <gsmith(at)gregsmith(dot)com>, "Dan Sugalski" <dan(at)sidhe(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PG 8.3 and large shared buffer settings
Date: 2009-09-26 17:24:16
Message-ID: op.u0vjyqcbcke6l8@soyouz (view raw or flat)
Thread:
Lists: pgsql-performance
>> Is there any practical limit to the number of shared buffers PG 8.3.7  
>> can handle before more becomes counter-productive?

It is more efficient to have the page in shared buffers, rather than doing  
a context switch to the OS, copying the entire page from the OS's cache  
into shared buffers, and coming back to postgres. Shared buffers use less  
CPU. However, this is totally negligible versus the disk wait time of an  
uncached IO.

The same page may be cached once in shared_buffers, and once in the OS  
cache, so if your shared buffers is half your RAM, and the other half is  
disk cache, perhaps it won't be optimal: is stuff is cached twice, you can  
cache half as much stuff.

If your entire database can fit in shared buffers, good for you though.  
But then a checkpoint comes, and postgres will write all dirty buffers to  
disk in the order it finds them in Shared Buffers, which might be totally  
different from the on-disk order. If you have enough OS cache left to  
absorb these writes, the OS will reorder them. If not, lots of random  
writes are going to occur. On a RAID5 this can be a lot of fun.

In response to

pgsql-performance by date

Next:From: Grzegorz JaśkiewiczDate: 2009-09-26 17:58:35
Subject: Re: Bad performance of SELECT ... where id IN (...)
Previous:From: Tom LaneDate: 2009-09-26 17:03:57
Subject: Re: Bad performance of SELECT ... where id IN (...)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group