From: Tom Lane <tgl@sss.pgh.pa.us>
To: Markus Schaber <schabi@logix-tt.com>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Read only transactions - Commit or Rollback
Date: Tue, 20 Dec 2005 10:48:08 -0500
Message-ID: <29672.1135093688@sss.pgh.pa.us> (raw)
In-Reply-To: <43A7F9B9.3010906@logix-tt.com>
References: <16F953410A0F1346848DCB476A989CFE01D6AF@swtexchange2.technology.de>
<43A7F303.8040804@logix-tt.com>
<87mziwj6na.fsf@gate450.dyndns.org>
<43A7F9B9.3010906@logix-tt.com>
Markus Schaber <schabi@logix-tt.com> writes:
> Some time ago, I had some tests with large bulk insertions, and it
> turned out that SERIALIZABLE seemed to be 30% faster, which surprised us.
That surprises me too --- can you provide details on the test case so
other people can reproduce it? AFAIR the only performance difference
between SERIALIZABLE and READ COMMITTED is the frequency with which
transaction status snapshots are taken; your report suggests you were
spending 30% of the time in GetSnapshotData, which is a lot higher than
I've ever seen in a profile.
As to the original question, a transaction that hasn't modified the
database does not bother to write either a commit or abort record to
pg_xlog. I think you'd be very hard pressed to measure any speed
difference between saying COMMIT and saying ROLLBACK after a read-only
transaction. It'd be worth your while to let transactions run longer
to minimize their startup/shutdown overhead, but there's a point of
diminishing returns --- you don't want client code leaving transactions
open for hours, because of the negative side-effects of holding locks
that long (eg, VACUUM can't reclaim dead rows).
regards, tom lane
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: tgl@sss.pgh.pa.us, schabi@logix-tt.com
Subject: Re: Read only transactions - Commit or Rollback
In-Reply-To: <29672.1135093688@sss.pgh.pa.us>
* 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