Re: trouble with a join on OS X

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/