On 04/05/11 2:50 AM, Jeremy Palmer wrote:
I've been having repeated troubles trying to get a PostgreSQL app to play
nicely on Ubuntu. I recently posted a message on this list about an out of
memory error and got a resolution by reducing the work_mem setting. However I'm
now getting further out of memory issues during the same stage of plpgsql
function as mentioned before.
The function itself is run as part of larger transaction which does the
following:
1/ Maintains 104 tables (15 PostGIS tables), by loading and applying
incremental table changes. A typical incremental load with maintain about
10,000 rows.
2/ When each one of these tables is updated an after trigger is fired that
maintains an associated table revision table.
3/ After all of the tables are maintained a plpgsql function is called to
build/maintain a set of de-normalised tables. These tables total about 20GB.
Each one of these tables is compared against the previous table revision to
determine its row changes. It's in this function that the out of memory
exception is occurring.
a few random questions...
Does that all really have to be a single transaction?
Do you really need to use triggers for your revision tracking, and can't
rely on your daily update cycle to manually set the revision information?
Is it really necessary to generate massive denormalized tables, rather
than using view's to join the data?
shared_buffers = 512MB
maintenance_work_mem = 512MB
temp_buffers = 256MB
work_mem = 1MB
wal_buffers = 16MB
effective_cache_size = 4094MB
The size of the database is 350GB. The typical number of users connected to the
database is 1 or 2. This database is used for loading external data, managing
revision table information and generating and outputting de-normalised
datasets, so it does not have a high number of transactions running. Typically
1 large one per day.
with only 1-2 connections, you certainly could increase the work_mem.
Alternately, this single giant transaction could manually set a larger
work_mem which would only apply to it. Personally, given your 8gb
system and what you've described, I think I'd set the tuning parameters
something like...
shared_buffers = 1GB
maintenance_work_mem = 128MB
temp_buffers = 64MB
work_mem = 16MB
wal_buffers = 16MB
effective_cache_size = 4094MB
adjust effective_cache_size to somewhat less than the 'cached' value
shown in `free -m` after your system has been running for awhile.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
