FW: max_connections and shared_buffers

Hi Scott,

Sorry for the long mail and Thank you for the quick response,
My problem is that our server shuts down unexpectedly, the settings are
max_connections=1024,shared_buffers = 32MB for the machine. The
following is the log snippet
2007-08-15 15:44:10 LOG: could not receive data from client: No
connection could be made because the target machine actively refused it.
2007-08-15 15:44:10 LOG: unexpected EOF on client connection
2007-08-15 15:44:17 LOG: received fast shutdown request
2007-08-15 15:44:17 LOG: aborting any active transactions
2007-08-15 15:44:17 FATAL: terminating connection due to administrator
command
2007-08-15 15:44:17 FATAL: the database system is shutting down
2007-08-15 15:44:17 LOG: shutting down

Any clues?

Regards,
Anoo S
-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: 03 September 2007 09:49
To: Anoo Sivadasan Pillai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] max_connections and shared_buffers

>
> Hi,
>
> 1) I saw a comment from experts-exchage regarding
shared_buffers,
> where max_connections were 600
>
> "2000 shared buffers were for 40 connections
> For 600 connections it looks more like 30000 shared buffers - to
prevent
> weekly slowdown.
> i.e 8KB*30000 = 240MB
>
> 16M work_mem * 600 = 9600MB maximum when everyone is connected. "
>
> Can anybody explain the logic behind the calculation?

those two things are not really directly related.

The minimum number of shared buffers determined by the max connections
is just that. A minimum. Not the recommended value, which can often
be much higher, especially on a large machine with lots of RAM.

the 16M work mem * 600 has to do with the fact that if you had 600
clients connected, and they all ran a query with 1 sort (a query can
have > 1 sort by the way) then it would require 600*sort_mem (now
work_mem) memory for the server to handle all these sorts, on top of
the memory being used for other things. sort_mem (now work_mem) is
NOT allocated from already allocated memory, like shared buffers, it
is allocated new from free memory. Using up too much of your free
memory will make your server start swapping and slow down to a crawl.

> Why 30000 shared buffers is suggested for 600 connections.

Not sure. We don't have the context here. For that load, on that
machine, that's what they needed.

> Postgresql help says "This setting must be at least 128 kilobytes and
at
> least 16 kilobytes times max_connections."

Right. 2 blocks (8k each) for each connection is the minimum.

> 2) I want to set max_connections=1024
>
> Can anybody help to suggest a proper value for shared buffers for the
> settings ( if no other settings are counted )

Why do you want to set max connections to 1024? I would strongly
suggest using some kind of connection pooling rather than trying to
run 1k connections at once.

If you need to have 100 or so connections active at once, setup a
connection pool (pgpool or pgbouncer or java connection pooling etc)
for that many connections to the db, and use that. Connections aren't
free, they require some memory and some interaction with the other
backends, and they WILL slow down your db server unnecessarily.

And please, if you can, shorten your sig. It's way too long. I know,
some half baked lawyer somewhere in the company told you you have to
do it, but for a public mailing list it seems kinda overblown.
Visit our Website at http://www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

On 9/3/07, Anoo Sivadasan Pillai <aspillai(at)in(dot)rm(dot)com> wrote:
> Hi Scott,
>
> Sorry for the long mail and Thank you for the quick response,
> My problem is that our server shuts down unexpectedly, the settings are
> max_connections=1024,shared_buffers = 32MB for the machine. The
> following is the log snippet
> 2007-08-15 15:44:10 LOG: could not receive data from client: No
> connection could be made because the target machine actively refused it.
> 2007-08-15 15:44:10 LOG: unexpected EOF on client connection
> 2007-08-15 15:44:17 LOG: received fast shutdown request
> 2007-08-15 15:44:17 LOG: aborting any active transactions
> 2007-08-15 15:44:17 FATAL: terminating connection due to administrator
> command
> 2007-08-15 15:44:17 FATAL: the database system is shutting down
> 2007-08-15 15:44:17 LOG: shutting down
>
> Any clues?

Sounds like you're being hit by the Out of Memory (oom) killer in your
kernel maybe? how many connections do you have when this happens?

I think you might need to look at connection pooling. 1024
connections is a LOT of connections. How much memory does your server
have, btw, and what version of pgsql and what os are you running this
on?

Hi Scott,
We will be putting connection pooling, But as of now we have to find
some cure before implementing pooling.

Memory - 2GB
Postgres - PostgreSQL 8.2.4
OS - Windows 2003 server installation: ( Service
Pack 2 )

I am not sure how to get the number of connections while this happens.
How this can be taken?

Thanks and Regards,
Anoo S

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: 03 September 2007 12:17
To: Anoo Sivadasan Pillai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: FW: [ADMIN] max_connections and shared_buffers

On 9/3/07, Anoo Sivadasan Pillai <aspillai(at)in(dot)rm(dot)com> wrote:
> Hi Scott,
>
> Sorry for the long mail and Thank you for the quick response,
> My problem is that our server shuts down unexpectedly, the settings
are
> max_connections=1024,shared_buffers = 32MB for the machine. The
> following is the log snippet
> 2007-08-15 15:44:10 LOG: could not receive data from client: No
> connection could be made because the target machine actively refused
it.
> 2007-08-15 15:44:10 LOG: unexpected EOF on client connection
> 2007-08-15 15:44:17 LOG: received fast shutdown request
> 2007-08-15 15:44:17 LOG: aborting any active transactions
> 2007-08-15 15:44:17 FATAL: terminating connection due to
administrator
> command
> 2007-08-15 15:44:17 FATAL: the database system is shutting down
> 2007-08-15 15:44:17 LOG: shutting down
>
> Any clues?

Sounds like you're being hit by the Out of Memory (oom) killer in your
kernel maybe? how many connections do you have when this happens?

I think you might need to look at connection pooling. 1024
connections is a LOT of connections. How much memory does your server
have, btw, and what version of pgsql and what os are you running this
on?
Visit our Website at http://www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

On 9/3/07, Anoo Sivadasan Pillai <aspillai(at)in(dot)rm(dot)com> wrote:
> Hi Scott,
> We will be putting connection pooling, But as of now we have to find
> some cure before implementing pooling.
>
> Memory - 2GB
> Postgres - PostgreSQL 8.2.4
> OS - Windows 2003 server installation: ( Service
> Pack 2 )
>
> I am not sure how to get the number of connections while this happens.
> How this can be taken?

Not sure with Windows. I'm strictly a unix type of guy. I'm guessing
that Windows is detecting too many connections / out of memory and
shutting down the service.

Is there anything in the system logs that might give you a hint of why
pgsql is being shut down by the OS?

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> Not sure with Windows. I'm strictly a unix type of guy. I'm guessing
> that Windows is detecting too many connections / out of memory and
> shutting down the service.

The whole thing is pretty strange. "received fast shutdown request"
means that the postmaster got SIGINT --- a moment's look at the code
proves there is no other possibility. Now what sent it SIGINT?
AFAICS there are only two possible paths: "pg_ctl stop -m fast" or
this little bit of code in win32/signal.c:

/* Console control handler will execute on a thread created
by the OS at the time of invocation */
static BOOL WINAPI
pg_console_handler(DWORD dwCtrlType)
{
if (dwCtrlType == CTRL_C_EVENT ||
dwCtrlType == CTRL_BREAK_EVENT ||
dwCtrlType == CTRL_CLOSE_EVENT ||
dwCtrlType == CTRL_SHUTDOWN_EVENT)
{
pg_queue_signal(SIGINT);
return TRUE;
}
return FALSE;
}

Can any Windows hackers speculate on causes of this?

regards, tom lane