From: | Kirk Wythers <kwythers(at)umn(dot)edu> |
---|---|
To: | "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: trouble with a join on OS X |
Date: | 2007-02-02 15:59:38 |
Message-ID: | 006150BD-B5E9-4D06-B4E0-76A1C76A72CE@umn.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Kirk Wythers | 2007-02-02 16:05:29 | Re: trouble with a join on OS X |
Previous Message | Tom Lane | 2007-02-02 15:46:53 | Re: trouble with a join on OS X |