From: Greg Smith <greg@2ndquadrant.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Dave Crooke <dcrooke@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Followup: vacuum'ing toast
Date: Thu, 05 Nov 2009 09:28:15 -0500
Message-ID: <4AF2E0FF.5050702@2ndquadrant.com> (raw)
In-Reply-To: <4AF24006.1010509@postnewspapers.com.au>
References: <ca24673e0911041552k70b4af34k6f689ab116fc8752@mail.gmail.com>
<4AF229F5.201@2ndquadrant.com>
<4AF24006.1010509@postnewspapers.com.au>
Craig Ringer wrote:
> Hibernate is pretty well behaved with transaction management. In fact,
> it's downright nuts about keeping transactions open for as short a
> period of time as possible. It even implements its own row-versioning
> based optimistic locking scheme (oplock) rather than relying on holding
> a transaction open with row locks in the database.
>
It's probably more nuts than it needs to be with PostgreSQL as the
backing store, since MVCC prevents some of the common sources of row
locks from being needed. But since Hibernate is database-agnostic and
it worried about locally cached copies of things too, it ends up needing
to do this extra work regardless.
> 3) Autocommit being disabled. At least when using Hibernate via JPA,
> that'll cause a major mess and would easily explain the issues you're
> seeing. Hibernate manages transactions explicitly when required, and
> expects autocommit to be off.
>
Downthread it suggests there's still some confusion here, but everyone
should be clear about one thing: turning autocommit on is the first
step down a road that usually leads to bad batch performance. If your
problems go away by enabling it, which they sometimes do, that is a sign
there's a problem to be investigated, not a true solution. One day
you're going to find yourself wanting transactions to be explicitly
committed only when required, both for atomicity and performance
reasons, and you won't be able to rely on autocommit as a crutch at that
point. Better to never get used to be there in the first place.
--
Greg Smith greg@2ndquadrant.com Baltimore, MD
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: greg@2ndquadrant.com, craig@postnewspapers.com.au, dcrooke@gmail.com
Subject: Re: Followup: vacuum'ing toast
In-Reply-To: <4AF2E0FF.5050702@2ndquadrant.com>
* 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