On Fri, May 6, 2016 at 3:21 AM, Kurt Roeckx <kurt@xxxxxxxxx> wrote: > 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. It is probably dirtying the indexes as well, but the writes are being buffered in shared_buffers. The dirty blocks will eventually get written by the background writer or the checkpoint writer (which you won't see if you aren't tracing them). >> > 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. OK, so it sounds like what is happening is that your update cannot do a "Heap-Only Tuple" (HOT) update, because there is not enough room in each data page for the new copy of rows being updated. So it is forced to put the new copy on a different page, which means it has to update all the indexes so they know where to find the new version. If this not a one-time event, then one thing you could do is lower the table's fillfactor, so that the table is more loosely packed and future updates are more likely to be able to do HOT updates. If the rows being updated are randomly scattered, it wouldn' take much lowering to make this happen (maybe 90). But if the rows being updated in a single transaction are co-located with each other, then you might have to lower it to below 50 before it would solve the problem, which might be a solution worse than the problem. When you change the parameter, it won't take full effect until the table has been completely rewritten, either due to natural churn, or running a VACUUM FULL or CLUSTER. If lowering the fillfactor isn't a good solution, then pre-warming your indexes (using pg_prewarm or just doing it yourself from the filesystem) might be your best bet. If not all of the relevant indexes can fit in cache at the same time, then you might still have to drop some of them, or just be patient. If this giant update does not have to occur atomically in order for your application to behave correctly, then I would probably break it up into a series of smaller transactions. Then you could even run them in parallel, which would be a big help if you have a RAID (which can efficiently work on multiple random IO read requests in parallel) but not help so much if you have a single disk. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
