From: Pavel Velikhov <pvelikhov@yahoo.com>
To: Kenneth Marshall <ktm@rice.edu>
Cc: Jonah H. Harris <jonah.harris@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: need help with a query
Date: Sat, 20 Oct 2007 13:11:00 -0700 (PDT)
Message-ID: <639622.45761.qm@web56412.mail.re3.yahoo.com> (raw)
Thanks a lot folks,
Left the query running for 10+ hours and had to kill it. I guess there really was no need to have lots of
shared buffers (the hope was that postgresql will cache the whole table). I ended up doing this step inside
the application as a pre-processing step. Can't have postgres running with different fsych options since this
will be part of an "easy to install and run" app, that should just require a typical PosgreSQL installation.
Pavel Velikhov
----- Original Message ----
From: Kenneth Marshall <ktm@rice.edu>
To: Pavel Velikhov <pvelikhov@yahoo.com>
Cc: Jonah H. Harris <jonah.harris@gmail.com>; pgsql-performance@postgresql.org
Sent: Friday, October 19, 2007 8:17:48 PM
Subject: Re: [PERFORM] need help with a query
On Fri, Oct 19, 2007 at 09:11:36AM -0700, Pavel Velikhov wrote:
> Thanks for you help!
>
> Got a very different query plan this time, with a hash join between
links and articles. At least now postgres is using both shared memory
buffers and working mem, but its still completely IO bound, only getting
in 5-6% CPU once in a while. I guess I can't squeeze more out of the
laptop, but I also have a machine with 16GB RAM that I'll try this on
next. Should I allocate tons of memory into shared buffers or into the
working memory?
This is an extremely I/O intensive query that must rewrite every
entry in the table. You could speed it up by starting postgresql
with fsync disabled, run the update, and then restart it with
fsync re-enabled.
Ken
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.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: pvelikhov@yahoo.com, ktm@rice.edu, jonah.harris@gmail.com
Subject: Re: need help with a query
In-Reply-To: <639622.45761.qm@web56412.mail.re3.yahoo.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