Re: Very slow update / hash join - Mailing list pgsql-general

From Kurt Roeckx
Subject Re: Very slow update / hash join
Date
Msg-id 20160506102103.GA22621@roeckx.be
Whole thread Raw
In response to Re: Very slow update / hash join  (Kurt Roeckx <kurt@roeckx.be>)
Responses Re: Very slow update / hash join  (Steven Lembark <lembark@wrkhors.com>)
Re: Very slow update / hash join  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
On Fri, May 06, 2016 at 11:38:27AM +0200, Kurt Roeckx wrote:
> On Thu, May 05, 2016 at 09:32:28PM -0700, Jeff Janes wrote:
> > On Wed, May 4, 2016 at 3:22 PM, Kurt Roeckx <kurt@roeckx.be> wrote:
> > > Hi,
> > >
> > > I have an update query that's been running for 48 hours now.
> > > Since it started it used about 2.5% CPU, and is writing to the
> > > disk at about 3 MB/s, and reading at about 2 MB/s.  It's mostly
> > > waiting for the disks.
> >
> > The easiest way to figure out what is going on is to identify the
> > process, and then trace it with something like:
> >
> > strace -T -ttt -y -p <PID of process>
> >
> > That should make it obvious which file it is waiting for IO on.  Then
> > you can look up that relfilenode in pg_class to see what table/index
> > it is.
>
> Thanks for the hint, that I didn't think about it.
>
> So it's busy reading all the index files including the primary
> key, and only writing to the table I'm updating.
>
> > What version of PostgreSQL are you using?  Have you tried dropping the
> > foreign keys?
>
> I'm using 9.5.2.
>
> So I think the foreign keys are unrelated now.  They all obviously
> point to the primary key that's not changing, and it's reading all
> the index on the table itself, not those on the other tables.
>
> It's kind of annoying that I would need to drop the indexes that
> aren't modified just to run an update query.

I dropped all the index except for the primary key.  It was still
as slow when it started, but then I forced the primary key into
the filesystem cache and it seems to be much happier now, average
reading at about 10 MB/s, writing at 30 MB/s.


Kurt



pgsql-general by date:

Previous
From: Kurt Roeckx
Date:
Subject: Re: Very slow update / hash join
Next
From: Berend Tober
Date:
Subject: Re: Function PostgreSQL 9.2