From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: read only transactions
Date: Tue, 12 Oct 2010 16:32:31 -0400
Message-ID: <87zkuj884g.fsf@cbbrowne.afilias-int.info> (raw)
References: <AANLkTikaFMqNPcyAch5t3tL7Sj+HU0eiDaCj_vnFACOz@mail.gmail.com>
jnelson+pgsql@jamponi.net (Jon Nelson) writes:
> Are there any performance implications (benefits) to executing queries
> in a transaction where
> SET TRANSACTION READ ONLY;
> has been executed?
Directly? No.
Indirectly, well, a *leetle* bit...
Transactions done READ ONLY do not generate actual XIDs, which reduces
the amount of XID generation (pretty tautological!), which reduces the
need to do VACUUM to protect against XID wraparound.
<http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html#VACUUM-BASICS;
If you process 50 million transactions, that chews thru 50 million XIDs.
If 45 million of those were processed via READ ONLY transactions, then
the same processing only chews thru 5 million XIDs, meaning that the
XID-relevant vacuums can be done rather less frequently.
This only terribly much matters if:
a) your database is so large that there are tables on which VACUUM
would run for a very long time, and
b) you are chewing through XIDs mighty quickly.
If either condition isn't true, then the indirect effect isn't important
either.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
"I'm not switching from slrn. I'm quite confident that anything that
*needs* to be posted in HTML is fatuous garbage not worth my time."
-- David M. Cook <davecook@home.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: read only transactions
In-Reply-To: <87zkuj884g.fsf@cbbrowne.afilias-int.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox