Re: I/O increase after upgrading to 8.3.5

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