Re: atrocious update performance

"Rosser Schwarz" <rschwarz(at)totalcardinc(dot)com> writes:
> `strace -p 21882` run behind the below query and plan ... below that.

Hmm ... that took 20 seconds eh?

It is a fairly interesting trace. It shows that the backend needed to
read 63 system catalog pages (that weren't already in shared memory),
which is not too unreasonable I think ... though I wonder if more of
them shouldn't have been in memory already. The odd thing is that for
*every single read* it was necessary to first dump out a dirty page
in order to make a buffer free. That says you are running with the
entire contents of shared buffer space dirty at all times. That's
probably not the regime you want to be operating in. I think we already
suggested increasing shared_buffers. You might also want to think about
not using such a large checkpoint interval. (The background-writing
logic already committed for 7.5 should help this problem, but it's not
there in 7.4.)

Another interesting fact is that the bulk of the writes were "blind
writes", involving an open()/write()/close() sequence instead of keeping
the open file descriptor around for re-use. This is not too surprising
in a freshly started backend, I guess; it's unlikely to have had reason
to create a relation descriptor for the relations it may have to dump
pages for. In some Unixen, particularly Solaris, open() is fairly
expensive and so blind writes are bad news. I didn't think it was a big
problem in Linux though. (This is another area we've improved for 7.5:
there are no more blind writes. But that won't help you today.)

What's not immediately evident is whether the excess I/O accounted for
all of the slowdown. Could you retry the strace with -r and -T options
so we can see how much time is being spent inside and outside the
syscalls?

regards, tom lane