Re: I/O increase after upgrading to 8.3.5

Alexander Staubo <alex(at)bengler(dot)no> writes:
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> You should definitely set effective_cache_size.

> Wow -- I set this to 10GB (5GB for shared buffers + another 5GB for
> cache), and today's average write frequency went from 20MB/sec to just
> 1MB/sec. The documentation suggests that effective_cache_size is only
> used for query planning in conjunction with indexes. So how come it
> affects write access?

It *is* only used for query planning. A plausible theory is that you
caused some join queries to change from hash or merge joining involving
a temporary hash or sort file to an index nestloop that doesn't use any
temporary storage. If the required hash or sort storage exceeded
work_mem, which you have set to just 10MB, that would have created some
write traffic.

Did you happen to notice whether your queries got faster or slower when
you did this? Watching only aggregate write traffic is a pretty limited
view of what is happening in your database.

regards, tom lane