Lars Feistner <feistner@uni-heidelberg.de> wrote:
> On 03/30/2011 06:54 PM, Kevin Grittner wrote:
>> If you haven't already done so, you should probably turn on
>> checkpoint logging to see if this corresponds to checkpoint
>> activity. If it does, you can try cranking up how aggressive
>> your background writer is, and perhaps limiting your
>> shared_buffers to something around the size of your RAID
>> controller's BBU cache. (I hope you have a RAID controller with
>> BBU cache configured for write-back, anyway.)
> i am sorry to disappoint you here. As I said in my first E-Mail we
> don't have much traffic and the database fits easily into memory.
> The traffic might increase, at least it was increasing the last 12
> months. The database will always fit into memory.
> No, we don't have a raid and thus we don't have a bbu. Actually
> we started off with a big SAN that our data centre offered. But
> sometimes this SAN was a bit slow and when we first encountered
> the very long updates i thought there was a connection between the
> long running updates and the slowliness of the SAN, so i started
> to use the local disk (we are talking about one disk not disks)
> for the database. I am still seeing the long running inserts and
> updates. I am still following the auto vacuum trail, it does still
> not run frequently enough. Thanks a lot for the replies so far. I
> will keep you guys informed about my next steps and the results.
Nothing there makes a write glut on checkpoint less likely to be the
cause. Without a BBU write-back cache it is actually *more* likely,
and having enough RAM to hold the whole database makes it *more*
likely. If you haven't placed your pg_xlog directory on a separate
file system, it is also more likely.
Turning on logging of checkpoint activity and checking whether that
correlates with your problem times is strongly indicated.
-Kevin