Re: very long updates very small tables

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Lars Feistner" <feistner(at)uni-heidelberg(dot)de>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: very long updates very small tables
Date: 2011-04-04 14:32:40
Message-ID: 4D999038020000250003C207@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Lars Feistner <feistner(at)uni-heidelberg(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2011-04-04 16:22:48 Re: Postgres Performance Tuning
Previous Message Adarsh Sharma 2011-04-04 12:33:54 Re: Postgres Performance Tuning