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

help / color / mirror / Atom feed
From: Greg Smith <gsmith@gregsmith.com>
To: Alexy Khrabrov <deliverable@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: two memory-consuming postgres processes
Date: Fri, 2 May 2008 17:23:45 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0805021714580.2455@westnet.com> (raw)
In-Reply-To: <C25E5DBA-B649-4483-B1A2-2A881522B891@gmail.com>
References: <A728C858-76CF-473F-917B-3C9EA4AA75CF@gmail.com>
	<dcc563d10805021230i5916561k3962a41e2afa894c@mail.gmail.com>
	<27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>
	<dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com>
	<21403.1209759217@sss.pgh.pa.us>
	<72E02D29-848B-467A-AE6B-401568010254@gmail.com>
	<dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
	<481B8160.2090106@emolecules.com>
	<C25E5DBA-B649-4483-B1A2-2A881522B891@gmail.com>

On Fri, 2 May 2008, Alexy Khrabrov wrote:

> I created several indices for the primary table, yes.

That may be part of your problem.  All of the indexes all are being 
updated along with the main data in the row each time you touch a record. 
There's some optimization there in 8.3 but it doesn't make index overhead 
go away completely.  As mentioned already, the optimal solution to 
problems in this area is to adjust table normalization as much as feasible 
to limit what you're updating.

> Basically, the derived data is not critical at all, -- can I turn (1) 
> off transactional behavior for an UPDATE,

What you can do is defer transaction commits to only happen periodically 
rather than all the time by turning off syncronous_commit and increasing 
wal_writer_delay; see 
http://www.postgresql.com.cn/docs/8.3/static/wal-async-commit.html

> (2) should I care about vacuuming being done on the fly when saving RAM, 
> or need I defer it/manage it manually?

It's hard to speculate from here about what optimal vacuum behavior will 
be.  You might find it more efficient to turn autovacuum off when doing 
these large updates.  The flip side is that you'll be guaranteed to end up 
with more dead rows in the table and that has its own impact later.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD



view thread (21+ messages)  latest in thread

Message-ID: <Pine.GSO.4.64.0805021714580.2455@westnet.com>
Permalink:  ../Pine.GSO.4.64.0805021714580.2455@westnet.com/
Also on:    postgresql.org/message-id/Pine.GSO.4.64.0805021714580.2455@westnet.com

 · 

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: gsmith@gregsmith.com, deliverable@gmail.com
  Subject: Re: two memory-consuming postgres processes
  In-Reply-To: <Pine.GSO.4.64.0805021714580.2455@westnet.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