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

Re: Why Does UPDATE Take So Long?

From: Bill Thoen <bthoen(at)gisnet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Why Does UPDATE Take So Long?
Date: 2008-09-30 21:53:09
Message-ID: 48E29FC5.4030702@gisnet.com (view raw or flat)
Thread:
Lists: pgsql-general
Sorry for the hyperbole; I should have qualified that ridiculous 
statement with "...on my machines." No doubt the problem has something 
to do with configuration, because I don't know much about that. One of 
my machines is running PG 8.1 on Linux Fedora Core 5. It's got an AMD 
64bit CPU with a GB RAM and plenty of normal disk space (not running 
RAID 5). The other machine is running Linux FC9 and PG 8.3. It's got a 
i686 cpu with a GB RAM and also not using RAID.

Since I don't understand much about configuring PostgreSQL, both of 
these machines use the default PostgreSQL configuration. I figured that 
it was optimized for general use but maybe since my files are large-ish 
(in the low multi-million record ranges) mayb ethta doesn't qualify as 
general use. Anyway, here's the configuration settings you mentioned.
Shared_buffers are = 1000
#checkpoint_segments = 3 
#checkpoint_timeout = 300
#checkpoint_warning = 30 

What should I be looking for in the configuration to improve UPDATE 
performance?

Thanks,
- Bill Thoen

Bill Moran wrote:
> In response to Bill Thoen <bthoen(at)gisnet(dot)com>:
>
>   
>> Doesn't look like that's the problem. I moved my table over to another 
>> Linux box running PG 8.3 and update performance was pretty bad there as 
>> well. In the time that PG 8.3 was struggling with update there I created 
>> a copy of my table on my PG 8.1 machine and inserted all columns with 
>> one containing the altered values I wanted and that took less than two 
>> minutes. Meanwhile, a half-hour later, my PG 8.3 machine was still 
>> thrashing away trying to update that one column that's not even part of 
>> any index..
>>
>> Something is really wrong with UPDATE in PostgreSQL I think.
>>     
>
> That's an interesting theory, although it's completely wrong and founded
> in ridiculosity.  If something were "really wrong with UPDATE" in every
> version of PostgreSQL, you'd be reading about it on the mailing lists,
> and you won't.
>
> What I suspect is that the typical tuning advice applies here.  I don't
> see any information about your configuration or your hardware setup.
> * What are shared_buffers set at?
> * What do the checkpoint configs look like?
> * In general, what does your postgresql.conf look like, how much tuning
>   have you done?
> * What is your hardware setup?  You're not running RAID 5 are you?
>
>   


In response to

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2008-09-30 21:57:34
Subject: Re: Why Does UPDATE Take So Long?
Previous:From: Alvaro HerreraDate: 2008-09-30 21:05:31
Subject: Re: Why Does UPDATE Take So Long?

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