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

Slow table update

From: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Slow table update
Date: 2008-12-22 11:06:02
Message-ID: 494F749A.5090603@shopzeus.com (view raw or flat)
Thread:
Lists: pgsql-performance
SQL:

update product set sz_category_id=null where am_style_kw1 is not null 
and sz_category_id is not null

query plan:

"Seq Scan on product  (cost=0.00..647053.30 rows=580224 width=1609)"
"  Filter: ((am_style_kw1 IS NOT NULL) AND (sz_category_id IS NOT NULL))"

Information on the table:

row count ~ 2 million
table size: 4841 MB
toast table size: 277mb
indexes size: 4434 MB

Computer: FreeBSD 7.0 stable, Dual Xeon Quad code 5420 2.5GHZ, 8GB 
memory, 6 ES SATA disks in hw RAID 6 (+2GB write back cache) for the 
database.

Autovacuum is enabled. We also perform "vacuum analyze" on the database, 
each day.

Here are some non-default values from postgresql.conf:

shared_buffers=400MB
maintenance_work_mem = 256MB
max_fsm_pages = 1000000

There was almost no load on the machine (CPU: mostly idle, IO: approx. 
5% total) when we started this update.

Maybe I'm wrong with this, but here is a quick calculation: the RAID 
array should do at least 100MB/sec. Reading the whole table should not 
take more than 1 min. I think about 20% of the rows should have been 
updated. Writting out all changes should not take too much time. I 
believe that this update should have been completed within 2-3 minutes.

In reality, after 2600 seconds I have cancelled the query. We monitored 
disk I/O and it was near 100% all the time.

What is wrong?

Thank you,

   Laszlo


Responses

pgsql-performance by date

Next:From: Laszlo NagyDate: 2008-12-22 11:34:32
Subject: Re: Slow table update
Previous:From: Gregory StarkDate: 2008-12-22 10:56:50
Subject: Re: dbt-2 tuning results with postgresql-8.3.5

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