From: Chris <dmagick@gmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: update query taking too long
Date: Thu, 28 Jun 2007 18:18:16 +1000
Message-ID: <46836EC8.20807@gmail.com> (raw)
In-Reply-To: <46835D14.6010507@archonet.com>
References: <46834124.5010600@gmail.com>
<21072.1183009270@sss.pgh.pa.us>
<46835252.5070901@gmail.com>
<468357B2.1040205@archonet.com>
<46835A16.3050602@gmail.com>
<46835D14.6010507@archonet.com>
Richard Huxton wrote:
> Chris wrote:
>> db=# UPDATE email_upd_test SET domainname=substring(email from
>> position('@' in email));
>> UPDATE 1000000
>> Time: 43796.030 ms
>>
>> I think I'm I/O bound from my very limited understanding of vmstat.
>
> Well, 43 seconds to update 1 million rows suggests your real query
> should be complete in a few minutes, even if your real table has more
> columns.
Yep.
I think I have solved it though - the server was checkpointing so much
not much else was going on.
I didn't have logging set up before but it's up and running now and I
was getting
LOG: checkpoints are occurring too frequently (26 seconds apart)
HINT: Consider increasing the configuration parameter
"checkpoint_segments".
So I increased that from 10 to 30 and it finished:
UPDATE 3500101
Time: 146513.349 ms
Thanks for all the help :)
--
Postgresql & php tutorials
http://www.designmagick.com/
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: dmagick@gmail.com, dev@archonet.com, tgl@sss.pgh.pa.us
Subject: Re: update query taking too long
In-Reply-To: <46836EC8.20807@gmail.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