Re: I/O increase after upgrading to 8.3.5 - Mailing list pgsql-performance

From Tom Lane
Subject Re: I/O increase after upgrading to 8.3.5
Date
Msg-id 4987.1234639388@sss.pgh.pa.us
Whole thread Raw
In response to Re: I/O increase after upgrading to 8.3.5  (Alexander Staubo <alex@bengler.no>)
Responses Re: I/O increase after upgrading to 8.3.5  (Alexander Staubo <alex@bengler.no>)
List pgsql-performance
Alexander Staubo <alex@bengler.no> writes:
> <Kevin.Grittner@wicourts.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

pgsql-performance by date:

Previous
From: Alexander Staubo
Date:
Subject: Re: I/O increase after upgrading to 8.3.5
Next
From: Tom Lane
Date:
Subject: Re: Slow queries from information schema