Re: Win32 Backend Cash - pre-existing shared memory block is still in use

Bugger I got another crash on the server today even after setting the temp_buffers to 512MB. Has anyone got any suggestions to fix this issue?

Should I just compile the source using MS visual studio, then debug and get a stack trace for someone to diagnose on this list?

Thanks
Jeremy

-----Original Message-----
From: Jeremy Palmer
Sent: Friday, August 20, 2010 1:52 PM
To: 'Magnus Hagander'; Tom Lane
Cc: pgsql-general(at)postgresql(dot)org; Alvaro Herrera; Chris Crook
Subject: RE: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

Yes I do realise that temp_buffers is per backend. I set it like this because we only have a few simultaneous clients connecting, and these clients generally run large analysis queries that usually create big temp tables.

I turned on extra logging and I have tracked down the query that is crashing the backend. The query was making a really big temp table. By setting the temp_buffers to 512MB the queries no longer crashes the backend.

My question is what is a safe value for the temp_buffers parameter on a win32 system? Also how can we stop PostgreSQL crashing because of this issue? I'm willing provide more information to help diagnose this.

Regards,
Jeremy

-----Original Message-----
From: Magnus Hagander [mailto:magnus(at)hagander(dot)net]
Sent: Friday, August 20, 2010 1:46 AM
To: Tom Lane
Cc: Jeremy Palmer; pgsql-general(at)postgresql(dot)org; Alvaro Herrera
Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

On Thu, Aug 19, 2010 at 15:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> writes:
>> Could it be that I have too much memory allocated for postgresql? My resource settings are:
>> shared_buffers = 94952
>> temp_buffers = 1GB
>> work_mem = 19339
>> maintenance_work_mem = 191845
>> max_stack_depth = 2MB
>
> 1GB for temp_buffers is a *LOT*.  You do realize that's per backend?
> Those other settings don't look too unreasonable.

Definitely - particularly since this is a 32-bit version, that's
getting very close to the address space limits...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
______________________________________________________________________________________________________

This message contains information, which is confidential and may be subject to legal privilege.
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info(at)linz(dot)govt(dot)nz) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________