BUG #14101: Postgres Service Crashes With Memory Error And Does Not Recover

nathanmascitelli@geotab.com writes:

I am running Postgres 9.4 on Windows Server 2012. I have had postgres crash
a few times with the following error:
FATAL: could not reattach to shared memory (key=00000000000000D0,
addr=00000001405E0000): error code 1455

Hm, that's an odd one. According to
https://msdn.microsoft.com/en-us/library/windows/desktop/ms681385(v=vs.85).aspx
that is

ERROR_COMMITMENT_LIMIT
1455 (0x5AF)
The paging file is too small for this operation to complete.

which suggests that this is a resource-exhaustion type of problem.
How many backend processes are you trying to run concurrently,
and what else is running on the machine?

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Tom,​

The machine has the postgres server and our application server on it. There
are ​around 300 databases and typically ~1000 connections. At the time of
the crash there was free RAM and disk space on the server.

Is there any other information I can give you?

Thanks,

*Nathan Mascitelli*
Geotab Inc.
Software Developer | B. Eng Engineering Physics

[Direct] *+1 (289) 681-1005*
[Toll-Free] *+1 (877) 436-8221*
[Visit] www.geotab.com
Twitter <http://twitter.com/geotab&gt; | Facebook
<http://www.facebook.com/geotab&gt; | YouTube <http://www.youtube.com/mygeotab&gt;
| LinkedIn
<http://www.linkedin.com/company/102661?trk=tyah&amp;trkInfo=tarId:1403199250031,tas:geotab,idx:2-1-3&gt;

On Tue, Apr 19, 2016 at 11:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nathan Mascitelli <nathanmascitelli@geotab.com> writes:

The machine has the postgres server and our application server on it. There
are ​around 300 databases and typically ~1000 connections. At the time of
the crash there was free RAM and disk space on the server.

You're a braver man than I, to trust Windows with a 1000-connection
server. But anyway, if the backend count is that high it's far from
surprising that you hit some Windows resource limit or other. It's
widely considered best practice to use a connection pooler to limit
the number of backends to something a lot less than that, regardless
of platform.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 4/20/2016 7:09 AM, Tom Lane wrote:

Nathan Mascitelli<nathanmascitelli@geotab.com> writes:

The machine has the postgres server and our application server on it. There
are ​around 300 databases and typically ~1000 connections. At the time of
the crash there was free RAM and disk space on the server.

You're a braver man than I, to trust Windows with a 1000-connection
server. But anyway, if the backend count is that high it's far from
surprising that you hit some Windows resource limit or other. It's
widely considered best practice to use a connection pooler to limit
the number of backends to something a lot less than that, regardless
of platform.

with 300 databases in use, a pooler would not be much help. 1000
connections is an average of 3 connections per database.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

We are using a connection pooler. On average we see 2-5 connections per
database. But it sounds like we would need to either collect connections
more aggressively or lower the number of databases/server correct?

*Nathan Mascitelli*
Geotab Inc.
Software Developer | B. Eng Engineering Physics

[Direct] *+1 (289) 681-1005*
[Toll-Free] *+1 (877) 436-8221*
[Visit] www.geotab.com
Twitter <http://twitter.com/geotab&gt; | Facebook
<http://www.facebook.com/geotab&gt; | YouTube <http://www.youtube.com/mygeotab&gt;
| LinkedIn
<http://www.linkedin.com/company/102661?trk=tyah&amp;trkInfo=tarId:1403199250031,tas:geotab,idx:2-1-3&gt;

On Wed, Apr 20, 2016 at 10:31 AM, John R Pierce <pierce@hogranch.com> wrote:

That's a good idea, I'll have to play around and see what we can do.

In your opinion would a Linux server be able to handle this setup? Would
1000 connections/processes be a problem on Linux?

Thanks,

*Nathan Mascitelli*
Geotab Inc.
Software Developer | B. Eng Engineering Physics

[Direct] *+1 (289) 681-1005*
[Toll-Free] *+1 (877) 436-8221*
[Visit] www.geotab.com
Twitter <http://twitter.com/geotab&gt; | Facebook
<http://www.facebook.com/geotab&gt; | YouTube <http://www.youtube.com/mygeotab&gt;
| LinkedIn
<http://www.linkedin.com/company/102661?trk=tyah&amp;trkInfo=tarId:1403199250031,tas:geotab,idx:2-1-3&gt;

On Wed, Apr 20, 2016 at 11:21 AM, John R Pierce <pierce@hogranch.com> wrote:

On Wed, 20 Apr 2016 12:37:55 -0400
Nathan Mascitelli <nathanmascitelli@geotab.com> wrote:

That's a good idea, I'll have to play around and see what we can do.

In your opinion would a Linux server be able to handle this setup? Would
1000 connections/processes be a problem on Linux?

As long as the machine has the resources, this is not a problem. I have
clients with 1800 to 2000 connections. It will help a bit if you configure
huge pages and you will possibly need to configure more semaphores depending
on what linux version you are on. You can contact me for details if you
actually do this.

-dg

--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs