From: Laszlo Nagy <gandalf@shopzeus.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Slow table update
Date: Mon, 22 Dec 2008 12:06:02 +0100
Message-ID: <494F749A.5090603@shopzeus.com> (raw)
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
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: gandalf@shopzeus.com
Subject: Re: Slow table update
In-Reply-To: <494F749A.5090603@shopzeus.com>
* 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