Обсуждение: Increasing maximum connection for postgresql 9.2
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.
Do you guys have any idea what is causing the problem or if i am missing some other configuration?
--
Amardeep Kaur
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:
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.Hey GuysI 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.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 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.
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.
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.
--
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.
Do you guys have any idea what is causing the problem or if i am missing some other configuration?
--
Amardeep Kaur
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?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: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.Hey GuysI 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.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
8130553685Amardeep 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
The error is
could not receive data from client: Connection reset by peer
followed by could not receive data from client: Connection reset by peer
incomplete startup packet
On Fri, Feb 21, 2014 at 10:20 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Amardeep Kaur wrote:As has been mentioned, that is too much.
> 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.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_ConnectionsThe database does not go into recovery mode just like that.
> 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?
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
8130553685On 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
On Sat, Feb 22, 2014 at 12:37 AM, Amardeep Kaur <amardeepkaur88@gmail.com> wrote:
The error isfollowed by
could not receive data from client: Connection reset by peer
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
-AmardeepOn Fri, Feb 21, 2014 at 10:20 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:Amardeep Kaur wrote:As has been mentioned, that is too much.
> 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.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_ConnectionsThe database does not go into recovery mode just like that.
> 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?
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 Kaur8130553685
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 isfollowed by
could not receive data from client: Connection reset by peer
incomplete startup packetTrue. 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 NSr. Database AdministratorFujitsu Australia-AmardeepOn Fri, Feb 21, 2014 at 10:20 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:Amardeep Kaur wrote:As has been mentioned, that is too much.
> 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.
There is also an interesting article on the Wiki:
http://wiki.postgresql.org/wiki/Number_Of_Database_ConnectionsThe database does not go into recovery mode just like that.
> 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?
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