Re: data on devel code perf dip

Mary Edie Meredith <maryedie(at)osdl(dot)org> writes:
> I have an example of runs that illustrate a performance
> problem that occurred between installing the 7/18 and 8/1
> development release codes.

I dug through the CVS logs to see what had changed, and I'm afraid there
is just one plausible-looking candidate:

2005-07-28 23:22 momjian

* src/backend/access/transam/xlog.c:
Use O_DIRECT if available when using O_SYNC for wal_sync_method.

Also, write multiple WAL buffers out in one write() operation.

ITAGAKI Takahiro

Most of the CVS activity in that time period had to with stuff like
roles and the interval datatype. It's conceivable that these things
had some marginal performance cost, but if so I'd have expected it to
show up as extra CPU effort (more time checking permissions, say).
This figure:

> samples % app name symbol name
> 164623113 70.5372 kernel-2.6.11.3 .shared_idle

says pretty clearly that your problem is all I/O wait, and there are
no other commits that might have increased our tendency to wait for I/O.

I am sure I will get some pushback if I propose reverting the O_DIRECT
patch, so could you try to get some more-specific evidence? Like pull
the CVS tree from just before and just after this patch and compare
performance?

BTW I did check that both runs are using wal_sync_method = fdatasync
and wal_buffers = 1000, so it's not a problem of those parameters having
been changed by the patch.

regards, tom lane