From: Lars Feistner <feistner@uni-heidelberg.de>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: very long updates very small tables
Date: Mon, 04 Apr 2011 10:11:41 +0200
Message-ID: <4D997D3D.1080104@uni-heidelberg.de> (raw)
In-Reply-To: <4D931A0C020000250003BF80@gw.wicourts.gov>
References: <4D91EEFA.90707@uni-heidelberg.de>
<4D91EC7E020000250003BEEC@gw.wicourts.gov>
<4D92DD2C.8010305@uni-heidelberg.de>
<4D931A0C020000250003BF80@gw.wicourts.gov>
On 03/30/2011 06:54 PM, Kevin Grittner wrote:
> Lars Feistner<feistner@uni-heidelberg.de> wrote:
>> On 03/29/2011 09:28 PM, Kevin Grittner wrote:
>>> Lars Feistner<feistner@uni-heidelberg.de> wrote:
>>>
>>>> The log tells me that certain update statements take sometimes
>>>> about 3-10 minutes. But we are talking about updates on tables
>>>> with 1000 to 10000 rows and updates that are supposed to update
>>>> 1 row.
>>>
>>> The top possibilities that come to my mind are:
>
>> [all eliminated as possibilities]
>
> 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.)
>
> -Kevin
>
Hello Kevin,
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.
Thanx a lot
Lars
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Lars Feistner
Kompetenzzentrum für Prüfungen in der Medizin
Medizinische Fakultät Heidelberg,
Im Neuenheimer Feld 346, Raum 013
69120 Heidelberg
E-Mail: feistner@uni-heidelberg.de
Fon: +49-6221-56-8269
Fax: +49-6221-56-7175
WWW: http://www.ims-m.dehttp://www.kompmed.de
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: feistner@uni-heidelberg.de, Kevin.Grittner@wicourts.gov
Subject: Re: very long updates very small tables
In-Reply-To: <4D997D3D.1080104@uni-heidelberg.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox