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

I'm getting infrequent backend crashes on a windows instance of PostgreSQL. The error I get is in the log below. It seems to relate to the share memory each time. Does anyone have any ideas what the problem is here, or what additional things I can do to get more information out next time the backend crashes?

I'm running PostgreSQL 8.4.4 on Windows server 2003 Standard.

Much Appreciated,
Jeremy

Postgresql.log:

2010-08-17 16:57:22 NZSTLOG: unexpected EOF on client connection
TopMemoryContext: 268428304 total in 26 blocks; 5528 free (22 chunks); 268422776 used
Local Buffer Lookup Table: 2088960 total in 8 blocks; 501416 free (22 chunks); 1587544 used
PostGIS PROJ4 Backend projPJ MemoryContext Hash: 8192 total in 1 blocks; 5384 free (0 chunks); 2808 used
PL/PgSQL function context: 57344 total in 3 blocks; 22264 free (16 chunks); 35080 used
PL/PgSQL function context: 57344 total in 3 blocks; 28696 free (27 chunks); 28648 used
PLpgSQL function cache: 24328 total in 2 blocks; 5904 free (0 chunks); 18424 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used
TopTransactionContext: 24576 total in 2 blocks; 9768 free (8 chunks); 14808 used
ExecutorState: 253952 total in 5 blocks; 83656 free (9 chunks); 170296 used
Combo CIDs: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
MessageContext: 253952 total in 5 blocks; 125592 free (1175 chunks); 128360 used
smgr relation table: 24576 total in 2 blocks; 15072 free (4 chunks); 9504 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 936 free (0 chunks); 88 used
ExecutorState: 2097192 total in 9 blocks; 1996680 free (1470 chunks); 100512 used
2010-08-17 19:19:32 NZSTLOG: server process (PID 6684) exited with exit code 128
2010-08-17 19:19:32 NZSTLOG: terminating any other active server processes
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTWARNING: terminating connection because of crash of another server process
2010-08-17 19:19:32 NZSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2010-08-17 19:19:32 NZSTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2010-08-17 19:19:32 NZSTLOG: all server processes terminated; reinitializing
2010-08-17 19:19:42 NZSTFATAL: pre-existing shared memory block is still in use
2010-08-17 19:19:42 NZSTHINT: Check if there are any old server processes still running, and terminate them.

______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________

On Wed, Aug 18, 2010 at 04:59, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
>
> I'm getting infrequent backend crashes on a windows instance of PostgreSQL. The error I get is in the log below. It seems to relate to the share memory each time. Does anyone have any ideas what the problem is here, or what additional things I can do to get more information out next time the backend crashes?
>
> I'm running PostgreSQL 8.4.4 on Windows server 2003 Standard.

Are there any running postgres.exe processes still present on the
machine? (Other than the postmaster itself, that is)

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

No they all got killed off.

-----Original Message-----
From: Magnus Hagander [mailto:magnus(at)hagander(dot)net]
Sent: Wednesday, August 18, 2010 8:06 PM
To: Jeremy Palmer
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

On Wed, Aug 18, 2010 at 04:59, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
>
> I'm getting infrequent backend crashes on a windows instance of PostgreSQL. The error I get is in the log below. It seems to relate to the share memory each time. Does anyone have any ideas what the problem is here, or what additional things I can do to get more information out next time the backend crashes?
>
> I'm running PostgreSQL 8.4.4 on Windows server 2003 Standard.

Are there any running postgres.exe processes still present on the
machine? (Other than the postmaster itself, that is)

--
 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.
______________________________________________________________________________________________________

Excerpts from Jeremy Palmer's message of mar ago 17 22:59:08 -0400 2010:
>
> I'm getting infrequent backend crashes on a windows instance of PostgreSQL. The error I get is in the log below. It seems to relate to the share memory each time. Does anyone have any ideas what the problem is here, or what additional things I can do to get more information out next time the backend crashes?
>
> I'm running PostgreSQL 8.4.4 on Windows server 2003 Standard.

The first entries are unrelated.

> 2010-08-17 16:57:22 NZSTLOG: unexpected EOF on client connection

This is a memory dump and could be unrelated (or maybe not).

> TopMemoryContext: 268428304 total in 26 blocks; 5528 free (22 chunks); 268422776 used
> Local Buffer Lookup Table: 2088960 total in 8 blocks; 501416 free (22 chunks); 1587544 used
[snip some more]

The real problem is here:

> 2010-08-17 19:19:32 NZSTLOG: server process (PID 6684) exited with exit code 128
> 2010-08-17 19:19:32 NZSTLOG: terminating any other active server processes

The rest is just noise about other processes being killed by postgres to
reset memory to a known-good state.

What you need to investigate is what caused that process to exit with
code 128.

PS: it'd be a good idea to append some whitespace to log_line_prefix, to
ensure that timezone NZST is separate from the "LOG" tag.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> This is a memory dump and could be unrelated (or maybe not).

>> TopMemoryContext: 268428304 total in 26 blocks; 5528 free (22 chunks); 268422776 used

That's an unreasonably large amount of stuff in TopMemoryContext :-(.
I wonder what caused that? It's not clear that'd have led to the crash
though.

regards, tom lane

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

I'm running on a server with 3.7GB of RAM.

I will adjust the logging level and wait for another crash. Should I set the level to 'debug5'? The cluster is for development purposes, so I don't mind the overhead.

Cheers,
Jeremy

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, August 19, 2010 4:07 AM
To: Alvaro Herrera
Cc: Jeremy Palmer; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> This is a memory dump and could be unrelated (or maybe not).

>> TopMemoryContext: 268428304 total in 26 blocks; 5528 free (22 chunks); 268422776 used

That's an unreasonably large amount of stuff in TopMemoryContext :-(.
I wonder what caused that? It's not clear that'd have led to the crash
though.

regards, tom lane
______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________

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.

I looked into the code and noticed that temp buffers are allocated
directly in TopMemoryContext, so this setting doubtless explains
the large amount of stuff in TopMemoryContext in your memory map.
I'm thinking we should push those buffers into a context of their own,
just so that it's clearer from the map where the memory went.

regards, tom lane

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/

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.
______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________

512M is still REALLY high for a 32 bit postgresql. Have you tried
something in the 16Meg range?

On Tue, Aug 24, 2010 at 10:31 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
> 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.
> ______________________________________________________________________________________________________
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
To understand recursion, one must first understand recursion.

Thanks. So can you explain why 512mb is bad decision here given that I only have 3.7GB of RAM?

The reason why I want the temp_buffers set so high is because this server is used for large data warehousing type queries. The server has very few sessions simultaneously running on it, but each session can create large temp tables.

Thanks
Jeremy

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Wednesday, August 25, 2010 5:11 PM
To: Jeremy Palmer
Cc: Magnus Hagander; Tom Lane; pgsql-general(at)postgresql(dot)org; Alvaro Herrera
Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

512M is still REALLY high for a 32 bit postgresql. Have you tried
something in the 16Meg range?

On Tue, Aug 24, 2010 at 10:31 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
> 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.
> ______________________________________________________________________________________________________
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
To understand recursion, one must first understand recursion.
______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> 512M is still REALLY high for a 32 bit postgresql. Have you tried
> something in the 16Meg range?

Cutting his value for shared_buffers (currently about 800MB) might be
wise too. I'm not sure what the effectively available address space
for a win32 process is, but if there's any inefficiency in the way
the address space is laid out, those numbers could be enough to be
trouble.

regards, tom lane

On Tue, Aug 24, 2010 at 11:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>> 512M is still REALLY high for a 32 bit postgresql.  Have you tried
>> something in the 16Meg range?
>
> Cutting his value for shared_buffers (currently about 800MB) might be
> wise too.  I'm not sure what the effectively available address space
> for a win32 process is, but if there's any inefficiency in the way
> the address space is laid out, those numbers could be enough to be
> trouble.

I believe it's limited to 3Gigs.

Yeah that's what stated here: http://technet.microsoft.com/en-us/library/ms189334.aspx

But only if I add /3gb switch to the kernel boot parameters

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Wednesday, August 25, 2010 5:28 PM
To: Tom Lane
Cc: Jeremy Palmer; Magnus Hagander; pgsql-general(at)postgresql(dot)org; Alvaro Herrera
Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

On Tue, Aug 24, 2010 at 11:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>> 512M is still REALLY high for a 32 bit postgresql.  Have you tried
>> something in the 16Meg range?
>
> Cutting his value for shared_buffers (currently about 800MB) might be
> wise too.  I'm not sure what the effectively available address space
> for a win32 process is, but if there's any inefficiency in the way
> the address space is laid out, those numbers could be enough to be
> trouble.

I believe it's limited to 3Gigs.
______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________

On Tue, Aug 24, 2010 at 11:21 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
> Thanks. So can you explain why 512mb is bad decision here given that I only have 3.7GB of RAM?

Because it's per session. Even with just a hand ful of processes
running you can chew up most of your memory real fast. Also, win32
pgsql is limited to 3Gig total memory in use.

> The reason why I want the temp_buffers set so high is because this server is used for large data warehousing type queries. The server has very few sessions simultaneously running on it, but each session can create large temp tables.

Start low. Test, benchmark. Increase in steps, benchmark again. See
where performance stops getting better, or the server starts crashing,
back off. It's pretty standard testing methodology.

It's a very common mistake for people to try to get postgresql to use
as much memory as possible, when quite often letting the OS use a fair
chunk for caching gives as good or better performance.

I would be WAY cheaper in time and effort, to just build a simple 64
bit linux box, toss 16 or 32 Gigs in it, and put your reporting db on
there. Trying to do "big memory" things in windows is, at least for
now, a net loss.

On Tue, Aug 24, 2010 at 11:30 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
> Yeah that's what stated here: http://technet.microsoft.com/en-us/library/ms189334.aspx
>
> But only if I add /3gb switch to the kernel boot parameters
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> Sent: Wednesday, August 25, 2010 5:28 PM
> To: Tom Lane
> Cc: Jeremy Palmer; Magnus Hagander; pgsql-general(at)postgresql(dot)org; Alvaro Herrera
> Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use
>
> On Tue, Aug 24, 2010 at 11:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>>> 512M is still REALLY high for a 32 bit postgresql.  Have you tried
>>> something in the 16Meg range?
>>
>> Cutting his value for shared_buffers (currently about 800MB) might be
>> wise too.  I'm not sure what the effectively available address space
>> for a win32 process is, but if there's any inefficiency in the way
>> the address space is laid out, those numbers could be enough to be
>> trouble.
>
> I believe it's limited to 3Gigs.
> ______________________________________________________________________________________________________
>
> 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.
> ______________________________________________________________________________________________________
>

--
To understand recursion, one must first understand recursion.

I wrote:
> Cutting his value for shared_buffers (currently about 800MB) might be
> wise too. I'm not sure what the effectively available address space
> for a win32 process is, but if there's any inefficiency in the way
> the address space is laid out, those numbers could be enough to be
> trouble.

Actually, a bit of googling turns up this:

http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx

which says that the available userspace address range for a win32
process is only *two* gig (although you can get to three using tricks
that I doubt are in his PG build). Take 800M+500M off the top, and it's
not too hard to credit that it might be tricky to swing a cat in the
remainder; especially given that it sounds like he's running complex
queries that could want to eat a lot of working RAM themselves.

IOW, these numbers are too big for your system.

regards, tom lane

Thanks for the tips. I would move to LINUX if it was an option :(

I will bring the numbers down and do some benchmarking and tests to find out what I can get away with on this system.

Thanks again,
Jeremy

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Wednesday, August 25, 2010 5:34 PM
To: Jeremy Palmer
Cc: Tom Lane; Magnus Hagander; pgsql-general(at)postgresql(dot)org; Alvaro Herrera
Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use

I would be WAY cheaper in time and effort, to just build a simple 64
bit linux box, toss 16 or 32 Gigs in it, and put your reporting db on
there. Trying to do "big memory" things in windows is, at least for
now, a net loss.

On Tue, Aug 24, 2010 at 11:30 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
> Yeah that's what stated here: http://technet.microsoft.com/en-us/library/ms189334.aspx
>
> But only if I add /3gb switch to the kernel boot parameters
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> Sent: Wednesday, August 25, 2010 5:28 PM
> To: Tom Lane
> Cc: Jeremy Palmer; Magnus Hagander; pgsql-general(at)postgresql(dot)org; Alvaro Herrera
> Subject: Re: [GENERAL] Win32 Backend Cash - pre-existing shared memory block is still in use
>
> On Tue, Aug 24, 2010 at 11:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>>> 512M is still REALLY high for a 32 bit postgresql.  Have you tried
>>> something in the 16Meg range?
>>
>> Cutting his value for shared_buffers (currently about 800MB) might be
>> wise too.  I'm not sure what the effectively available address space
>> for a win32 process is, but if there's any inefficiency in the way
>> the address space is laid out, those numbers could be enough to be
>> trouble.
>
> I believe it's limited to 3Gigs.
> ______________________________________________________________________________________________________
>
> 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.
> ______________________________________________________________________________________________________
>

--
To understand recursion, one must first understand recursion.
______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________

On Tue, Aug 24, 2010 at 11:39 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
> Thanks for the tips. I would move to LINUX if it was an option :(

It's easier to ask for forgiveness than to ask for permission. Plus
once you've got a working, performant machine, the boss might turn a
blind eye.

> I will bring the numbers down and do some benchmarking and tests to find out what I can get away with on this system.

Best of luck. You may find you still get pretty good performance
with the lower settings.

On 25 Aug 2010, at 7:39, Jeremy Palmer wrote:

> Thanks for the tips. I would move to LINUX if it was an option :(

I'd say if your memory requirements are really as high as you suggest, then anything that allows you to run a 64-bit version of Postgres is a better option than 32-bit Windows. Be that Linux, FreeBSD, Solaris, etc, maybe even OS/400.

Especially if your database requires good performance, Windows is known to be a significantly non-optimal choice.

So saying Linux is not an option really reduces your problem to choosing between getting it to work with a lot lower memory footprint or not at all. Are those viable options?

<Windows rant>
I have to work on a Windows PC these days. For Christ's sake, how long have they had the opportunity to fix basic flaws like scroll-bars not working properly (if you drag one and slip off the bottom part of the window it's back on top again)? Or mouse scrolling when using the scroll wheel over a piece of user-interface that doesn't have focus (Answer; install katmouse)? Or basic file-system performance? Or detaching a USB-keyboard without halting the OS for a second?
</Windows-rant>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c74b77710405088597927!

On Wed, Aug 25, 2010 at 07:34, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> Cutting his value for shared_buffers (currently about 800MB) might be
>> wise too.  I'm not sure what the effectively available address space
>> for a win32 process is, but if there's any inefficiency in the way
>> the address space is laid out, those numbers could be enough to be
>> trouble.
>
> Actually, a bit of googling turns up this:
>
> http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx
>
> which says that the available userspace address range for a win32
> process is only *two* gig (although you can get to three using tricks
> that I doubt are in his PG build).  Take 800M+500M off the top, and it's

Correct, we don't set ourselves as large address aware.

Hmm. I wonder if we even do that with the 64-bit build. I'm pretty
sure I tried with shared_buffers > 4Gb, but now that i see that page,
I think I need to re-verify that :-)

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