Re: trouble with a join on OS X

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux