Thread: Increasing maximum connection for postgresql 9.2

Increasing maximum connection for postgresql 9.2

From
Amardeep Kaur
Date:
Hey Guys

I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed kernel parameters according to
http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.
When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my database enters into recovery mode and it fails the request. For my stress test i am trying to get data from one table.
Do you guys have any idea what is causing the problem or if i am missing some other configuration?

--
Amardeep Kaur

Re: Increasing maximum connection for postgresql 9.2

From
Jorge Torralba
Date:
How much memory does your server have.What is your work_mem set to ?


On Thu, Feb 20, 2014 at 6:35 PM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:
Hey Guys

I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed kernel parameters according to
http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.
When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my database enters into recovery mode and it fails the request. For my stress test i am trying to get data from one table.
Do you guys have any idea what is causing the problem or if i am missing some other configuration?

--
Amardeep Kaur




--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.

Re: Increasing maximum connection for postgresql 9.2

From
David Johnston
Date:
Amardeep Kaur wrote
> Hey Guys
>
> I am trying to increase the max_connection from 100 to 5000. I modified
> shared_buffer and changed kernel parameters according to
> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.
> When i stress test it with 500 concurrent request, then after 150 - 160
> concurrent connections my database enters into recovery mode and it fails
> the request. For my stress test i am trying to get data from one table.
> Do you guys have any idea what is causing the problem or if i am missing
> some other configuration?
>
> --
> Amardeep Kaur

Methinks you should probably "configure" a connection pooler.

Otherwise provide complete and detailed specifics on hardware, software, and
actual configuration values if you expect to get meaningful help on using
such an large max connections setting.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Increasing-maximum-connection-for-postgresql-9-2-tp5792978p5792989.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: Increasing maximum connection for postgresql 9.2

From
om prash jaiswal
Date:
Increase your kenal level parameter.
in the /etc/sysctl.conf file
kernal.shmmax=
kernal.shmin =
based on your RAM memory.
Give half of RAM size to these parameter.
calculate the value of kernal.shmmax, kernal.shmin from the given link.
http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.



On Friday, 21 February 2014 8:06 AM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:
Hey Guys

I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed kernel parameters according to
http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.
When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my database enters into recovery mode and it fails the request. For my stress test i am trying to get data from one table.
Do you guys have any idea what is causing the problem or if i am missing some other configuration?

--
Amardeep Kaur



Re: Increasing maximum connection for postgresql 9.2

From
Amardeep Kaur
Date:
Hey Jorge

My work_mem is 1 MB. I only modified max_connection and shared_buffer. Can you tell me how should I modify this? Is there any rule based on your RAM to decide these parameters?

-Amardeep


On Fri, Feb 21, 2014 at 12:34 AM, Jorge Torralba <jorge.torralba@gmail.com> wrote:
How much memory does your server have.What is your work_mem set to ?


On Thu, Feb 20, 2014 at 6:35 PM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:
Hey Guys

I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed kernel parameters according to
http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.
When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my database enters into recovery mode and it fails the request. For my stress test i am trying to get data from one table.
Do you guys have any idea what is causing the problem or if i am missing some other configuration?

--
Amardeep Kaur




--
Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.



--
Amardeep Kaur
8130553685

Re: Increasing maximum connection for postgresql 9.2

From
Albe Laurenz
Date:
Amardeep Kaur wrote:
> I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed
> kernel parameters according to
> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.

As has been mentioned, that is too much.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my
> database enters into recovery mode and it fails the request. For my stress test i am trying to get
> data from one table.
> 
> Do you guys have any idea what is causing the problem or if i am missing some other configuration?

The database does not go into recovery mode just like that.
Look into the database log and see if you can find the appropriate messages, then
we can figure out what caused the problem.

Yours,
Laurenz Albe

Re: Increasing maximum connection for postgresql 9.2

From
Amardeep Kaur
Date:
The error is
 could not receive data from client: Connection reset by peer
followed by
incomplete startup packet

-Amardeep


On Fri, Feb 21, 2014 at 10:20 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Amardeep Kaur wrote:
> I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed
> kernel parameters according to
> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.

As has been mentioned, that is too much.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my
> database enters into recovery mode and it fails the request. For my stress test i am trying to get
> data from one table.
>
> Do you guys have any idea what is causing the problem or if i am missing some other configuration?

The database does not go into recovery mode just like that.
Look into the database log and see if you can find the appropriate messages, then
we can figure out what caused the problem.

Yours,
Laurenz Albe



--
Amardeep Kaur
8130553685

Re: Increasing maximum connection for postgresql 9.2

From
Scott Ribe
Date:
On Feb 21, 2014, at 6:37 AM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:

> The error is
>  could not receive data from client: Connection reset by peer
> followed by
> incomplete startup packet

Which has nothing to do with recovery mode. What makes you think the server is in recovery mode?

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: Increasing maximum connection for postgresql 9.2

From
Venkata Balaji Nagothi
Date:


On Sat, Feb 22, 2014 at 12:37 AM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:
The error is
 could not receive data from client: Connection reset by peer
followed by
incomplete startup packet

True. As mentioned up the thread, this does not mean that the database is in the recovery mode. If the database is in recovery mode, you would see a straight forward message in the logs saying that the "database is in recovery mode".

Please help us know how the connections or requests are sent to database from Application. Is there anything you could see in Application/client site logs ?

Things to be identified : If there are any unexpected connection drops/hangs ? do you see any unexpected load spike on DB server ? Network level issues etc.

Venkata Balaji N

Sr. Database Administrator
Fujitsu Australia 


-Amardeep


On Fri, Feb 21, 2014 at 10:20 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Amardeep Kaur wrote:
> I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed
> kernel parameters according to
> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.

As has been mentioned, that is too much.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my
> database enters into recovery mode and it fails the request. For my stress test i am trying to get
> data from one table.
>
> Do you guys have any idea what is causing the problem or if i am missing some other configuration?

The database does not go into recovery mode just like that.
Look into the database log and see if you can find the appropriate messages, then
we can figure out what caused the problem.

Yours,
Laurenz Albe



--
Amardeep Kaur
8130553685

Re: Increasing maximum connection for postgresql 9.2

From
Amardeep Kaur
Date:
Hi Guys

I actually saw the straight message "database is in recovery mode" in the logs.  I checked syslog and the problem was we were running out of RAM.

Thanks again for the help.

-Amardeep



On Wed, Feb 26, 2014 at 10:03 PM, Venkata Balaji Nagothi <vbnpgc@gmail.com> wrote:


On Sat, Feb 22, 2014 at 12:37 AM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:
The error is
 could not receive data from client: Connection reset by peer
followed by
incomplete startup packet

True. As mentioned up the thread, this does not mean that the database is in the recovery mode. If the database is in recovery mode, you would see a straight forward message in the logs saying that the "database is in recovery mode".

Please help us know how the connections or requests are sent to database from Application. Is there anything you could see in Application/client site logs ?

Things to be identified : If there are any unexpected connection drops/hangs ? do you see any unexpected load spike on DB server ? Network level issues etc.

Venkata Balaji N

Sr. Database Administrator
Fujitsu Australia 


-Amardeep


On Fri, Feb 21, 2014 at 10:20 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Amardeep Kaur wrote:
> I am trying to increase the max_connection from 100 to 5000. I modified shared_buffer and changed
> kernel parameters according to
> http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC.

As has been mentioned, that is too much.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> When i stress test it with 500 concurrent request, then after 150 - 160 concurrent connections my
> database enters into recovery mode and it fails the request. For my stress test i am trying to get
> data from one table.
>
> Do you guys have any idea what is causing the problem or if i am missing some other configuration?

The database does not go into recovery mode just like that.
Look into the database log and see if you can find the appropriate messages, then
we can figure out what caused the problem.

Yours,
Laurenz Albe



--
Amardeep Kaur
8130553685




--
Amardeep Kaur
8130553685