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@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
