Thanks for the reply Steiner,
On Feb 2, 2007, at 8:41 AM, Steinar H. Gunderson wrote:
On Fri, Feb 02, 2007 at 07:52:48AM -0600, Kirk Wythers wrote:
psql(15811) malloc: *** vm_allocate(size=8421376) failed (error
code=3)
psql(15811) malloc: *** error: can't allocate region
psql(15811) malloc: *** set a breakpoint in szone_error to debug
It sounds like you are out of memory. Have you tried reducing
work_mem?
Actually, what does your postgresql.conf look like with regard to
memory
settings?
I have not altered postgresql.conf. I assume these are the defaults:
# - Memory -
shared_buffers = 300 # min 16 or
max_connections*2, 8KB each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of
shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1024 # min 64, size in KB
#maintenance_work_mem = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
What about altering the sysctl values in /etc/rc to:
sysctl -w kern.sysv.shmmax=167772160
sysctl -w kern.sysv.shmmin=1
sysctl -w kern.sysv.shmmni=32
sysctl -w kern.sysv.shmseg=8
sysctl -w kern.sysv.shmall=65536
RIght now they are:
sysctl -w kern.sysv.shmmax=4194304 kern.sysv.shmmin=1
kern.sysv.shmmni=32 kern.s
ysv.shmseg=8 kern.sysv.shmall=1024
This a a dual G5 box with 6 gigs of ram running postgresql 8.1. I
have not tired altering kernel resources (as described in http://
www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SHARED-
MEMORY-PARAMETERS), or compiling for 64 bit. I'm just not sure what
to try next. Does anyone have any suggestions?
Compiling for 64 bit might very well help you, but it sounds odd to
use
several gigabytes of RAM for a sort.
Could you post EXPLAIN ANALYZE for the query with only one row, as
well
as your table schema?
met_data=# EXPLAIN ANALYSE SELECT sites.station_id, sites.longname,
sites.lat, sites.lon, sites.thepoint_meter, weather.date,
weather.year, weather.month, weather.day, weather.doy,
weather.precip, weather.tmin, weather.tmax, weather.snowfall,
weather.snowdepth, weather.tmean FROM sites LEFT OUTER JOIN weather
ON sites.station_id = weather.station_id WHERE weather.station_id =
210018 AND weather.year = 1893 AND weather.doy = 365;
QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------------
Nested Loop (cost=0.00..33426.63 rows=1 width=96) (actual
time=2.140..101.122 rows=1 loops=1)
-> Index Scan using sites_pkey on sites (cost=0.00..5.25 rows=1
width=60) (actual time=0.106..0.111 rows=1 loops=1)
Index Cond: (210018 = station_id)
-> Index Scan using weather_pkey on weather
(cost=0.00..33421.37 rows=1 width=40) (actual time=2.011..100.983
rows=1 loops=1)
Index Cond: (station_id = 210018)
Filter: (("year" = 1893) AND (doy = 365))
Total runtime: 101.389 ms
(7 rows)
The schema is public, but I'm not sure how to do an EXPAIN ANALYSE on
a schema.
/* Steinar */
--
Homepage: http://www.sesse.net/
