Skip site navigation (1) Skip section navigation (2)

Re: I/O increase after upgrading to 8.3.5

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alexander Staubo <alex(at)bengler(dot)no>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: I/O increase after upgrading to 8.3.5
Date: 2009-02-14 19:23:08
Message-ID: 4987.1234639388@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-02-14 20:02:02
Subject: Re: Slow queries from information schema
Previous:From: Alexander StauboDate: 2009-02-14 18:27:14
Subject: Re: I/O increase after upgrading to 8.3.5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group