pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIncreasing maximum connection for postgresql 9.2
10+ messages / 7 participants
[nested] [flat]
* Increasing maximum connection for postgresql 9.2
@ 2014-02-21 02:35 Amardeep Kaur <amardeepkaur88@gmail.com>
0 siblings, 4 replies; 10+ messages in thread
From: Amardeep Kaur @ 2014-02-21 02:35 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 03:34 Jorge Torralba <jorge.torralba@gmail.com>
parent: Amardeep Kaur <amardeepkaur88@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: Jorge Torralba @ 2014-02-21 03:34 UTC (permalink / raw)
To: Amardeep Kaur <amardeepkaur88@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 03:39 David Johnston <polobo@yahoo.com>
parent: Amardeep Kaur <amardeepkaur88@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: David Johnston @ 2014-02-21 03:39 UTC (permalink / raw)
To: pgsql-admin
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-tp5792978p5...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 07:41 om prash jaiswal <op12om@yahoo.co.in>
parent: Amardeep Kaur <amardeepkaur88@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: om prash jaiswal @ 2014-02-21 07:41 UTC (permalink / raw)
To: Amardeep Kaur <amardeepkaur88@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 11:16 Amardeep Kaur <amardeepkaur88@gmail.com>
parent: Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Amardeep Kaur @ 2014-02-21 11:16 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 13:20 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Amardeep Kaur <amardeepkaur88@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: Albe Laurenz @ 2014-02-21 13:20 UTC (permalink / raw)
To: Amardeep Kaur *EXTERN* <amardeepkaur88@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 13:37 Amardeep Kaur <amardeepkaur88@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 10+ messages in thread
From: Amardeep Kaur @ 2014-02-21 13:37 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-21 14:05 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Amardeep Kaur <amardeepkaur88@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Scott Ribe @ 2014-02-21 14:05 UTC (permalink / raw)
To: Amardeep Kaur <amardeepkaur88@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-27 01:03 Venkata Balaji Nagothi <vbnpgc@gmail.com>
parent: Amardeep Kaur <amardeepkaur88@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Venkata Balaji Nagothi @ 2014-02-27 01:03 UTC (permalink / raw)
To: Amardeep Kaur <amardeepkaur88@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Increasing maximum connection for postgresql 9.2
@ 2014-02-27 12:53 Amardeep Kaur <amardeepkaur88@gmail.com>
parent: Venkata Balaji Nagothi <vbnpgc@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Amardeep Kaur @ 2014-02-27 12:53 UTC (permalink / raw)
To: Venkata Balaji Nagothi <vbnpgc@gmail.com>; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2014-02-27 12:53 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-21 02:35 Increasing maximum connection for postgresql 9.2 Amardeep Kaur <amardeepkaur88@gmail.com>
2014-02-21 03:34 ` Jorge Torralba <jorge.torralba@gmail.com>
2014-02-21 11:16 ` Amardeep Kaur <amardeepkaur88@gmail.com>
2014-02-21 03:39 ` David Johnston <polobo@yahoo.com>
2014-02-21 07:41 ` om prash jaiswal <op12om@yahoo.co.in>
2014-02-21 13:20 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-02-21 13:37 ` Amardeep Kaur <amardeepkaur88@gmail.com>
2014-02-21 14:05 ` Scott Ribe <scott_ribe@elevated-dev.com>
2014-02-27 01:03 ` Venkata Balaji Nagothi <vbnpgc@gmail.com>
2014-02-27 12:53 ` Amardeep Kaur <amardeepkaur88@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox