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

Re: I/O increase after upgrading to 8.3.5

From: Greg Smith <gsmith(at)gregsmith(dot)com>
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-15 17:35:04
Message-ID: Pine.GSO.4.64.0902151222440.1312@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, 14 Feb 2009, Alexander Staubo wrote:

> Are there any statistics, either in PostgreSQL proper or in the OS, that 
> I can use as metrics to guide the tuning? For example, is there anything 
> in pg_stat_bgwriter that can help me tune the bgwriter_lru_* settings?

http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm goes 
over this topic, with "Appendix B: pg_stat_bgwriter sample analysis" 
covering a look at what to do based on a pg_stat_bgwriter snapshot.

> Do transactions that only contain query statements end up writing
> entries to the WAL when they commit?

You need a transactions XID before you can write to the WAL, and quoting 
from the transaction management docs:

"Transactions and subtransactions are assigned permanent XIDs only when/if 
they first do something that requires one --- typically, 
insert/update/delete a tuple"

The main thing that will cause writes even when reading are hint bit 
updates, which have been mentioned here already. 
http://wiki.postgresql.org/wiki/Hint_Bits has an intro to that topic.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2009-02-15 17:38:00
Subject: Re: I/O increase after upgrading to 8.3.5
Previous:From: Alexander StauboDate: 2009-02-15 16:45:42
Subject: Re: Bad plan for nested loop + limit

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