pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Greg Smith <greg@2ndquadrant.com>
To: VJK <vjkmail@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Date: Mon, 15 Mar 2010 10:42:42 -0400
Message-ID: <4B9E4762.7060703@2ndquadrant.com> (raw)
In-Reply-To: <600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com>
References: <20100313211742.GC2178@comppasch2>
	<600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com>
	<600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com>

VJK wrote:
> Since Pg does not use the concept of rollback segments,  it is unclear 
> why deletion produces so much disk IO (4GB).

With PostgreSQL's write-ahead log, MVCC and related commit log, and 
transactional DDL features, there's actually even more overhead that can 
be involved than a simple rollback segment design when you delete things:

http://www.postgresql.org/docs/current/static/wal.html
http://www.postgresql.org/docs/current/static/mvcc-intro.html
http://wiki.postgresql.org/wiki/Hint_Bits
http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis

One fun thing to try here is to increase shared_buffers and 
checkpoint_segments, then see if the total number of writes go down.  
The defaults for both are really low, which makes buffer page writes 
that might otherwise get combined as local memory changes instead get 
pushed constantly to disk.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

view thread (13+ messages)  latest in thread

Message-ID: <4B9E4762.7060703@2ndquadrant.com>
Permalink:  ../4B9E4762.7060703@2ndquadrant.com/
Also on:    postgresql.org/message-id/4B9E4762.7060703@2ndquadrant.com

copy link · copy postgr.es

reply

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, vjkmail@gmail.com
  Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
  In-Reply-To: <4B9E4762.7060703@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