Re: two memory-consuming postgres processes - Mailing list pgsql-performance

From Greg Smith
Subject Re: two memory-consuming postgres processes
Date
Msg-id Pine.GSO.4.64.0805021714580.2455@westnet.com
Whole thread Raw
In response to Re: two memory-consuming postgres processes  (Alexy Khrabrov <deliverable@gmail.com>)
Responses Re: two memory-consuming postgres processes
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Alexy Khrabrov
Date:
Subject: Re: two memory-consuming postgres processes
Next
From: PFC
Date:
Subject: Re: two memory-consuming postgres processes