Обсуждение: New PG14 server won't start with >2GB shared_buffers

Hello All,

Have a strange issue that I can not solve.

I have a new server that has 1TB ram (not a typo).  I have been fighting with this evening to start up with a shared_buffer setting over 2GB.  When the memory allocation  breaks 2GB, the database won’t start.  I’ve dropped it down to 500M to get the database online.  We’ve tried both SysV memory and mmap memory.

Any ideas on what is going on and how to resolve?  I’m sure I’m missing something obvious, but it is totally eluding me.

OS: Ubuntu 18.04

Memory:  1007GB

CPU: Intel(R) Xeon(R) CPU           X7550  @ 2.00GHz (64 cores total)

Kernel Memory:

kernel.shmmax = 274877906944

kernel.shmall = 17179869184

Postgres Config:

                  name                                  |                  setting

 autovacuum_max_workers                 | 3

 autovacuum_work_mem                     | -1

 effective_cache_size                           | 98992128

 fsync                                                    | on

 full_page_writes                                  | on

 maintenance_work_mem                    | 2097152

 shared_buffers                                    | 64000

 shared_memory_type                         | sysv

 temp_buffers                                       | 4096

 wal_buffers                                         | 2048

 work_mem                                          | 119990

If you need additional parameters, please ask

SysV Error:

Feb 25 01:55:16 appdb-server01.production.aweberint.com postgres[34680]: FATAL:  could not create shared memory segment: Cannot allocate memory

DETAIL:  Failed system call was shmget(key=2359323, size=2063564800, 03600).

HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMALL parameter.  You might need to reconfigure the kernel with larger SHMALL.

Mmap Error:

FATAL:  could not map anonymous shared memory: Cannot allocate memory

HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 1635737600 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

Thanks,

Chris Hoover

Senior DBA

AWeber.com

Cell: (803) 528-2269

Email: chrish@aweber.com

On Feb 25, 2023, at 5:25 AM, Ron <ronljohnsonjr@gmail.com> wrote:

Why did you have to drop shared buffers to 500MB, instead of something just below 2GB?

And what's "your kernel's SHMALL parameter"?

On 2/24/23 20:08, Chris Hoover wrote:

Hello All,

Have a strange issue that I can not solve.

I have a new server that has 1TB ram (not a typo).  I have been fighting with this evening to start up with a shared_buffer setting over 2GB.  When the memory allocation  breaks 2GB, the database won’t start.  I’ve dropped it down to 500M to get the database online.  We’ve tried both SysV memory and mmap memory.

Any ideas on what is going on and how to resolve?  I’m sure I’m missing something obvious, but it is totally eluding me.

OS: Ubuntu 18.04

Memory:  1007GB

CPU: Intel(R) Xeon(R) CPU           X7550  @ 2.00GHz (64 cores total)

Kernel Memory:

kernel.shmmax = 274877906944

kernel.shmall = 17179869184

Postgres Config:

                  name                                  |                  setting

 autovacuum_max_workers                 | 3

 autovacuum_work_mem                     | -1

 effective_cache_size                           | 98992128

 fsync                                                    | on

 full_page_writes                                  | on

 maintenance_work_mem                    | 2097152

 shared_buffers                                    | 64000

 shared_memory_type                         | sysv

 temp_buffers                                       | 4096

 wal_buffers                                         | 2048

 work_mem                                          | 119990

If you need additional parameters, please ask

SysV Error:

Feb 25 01:55:16 appdb-server01.production.aweberint.com postgres[34680]: FATAL:  could not create shared memory segment: Cannot allocate memory

DETAIL:  Failed system call was shmget(key=2359323, size=2063564800, 03600).

HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMALL parameter.  You might need to reconfigure the kernel with larger SHMALL.

Mmap Error:

FATAL:  could not map anonymous shared memory: Cannot allocate memory

HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 1635737600 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

Thanks,

Chris Hoover

Senior DBA

AWeber.com

Cell: (803) 528-2269

Email: chrish@aweber.com

--
Born in Arizona, moved to Babylonia.

On Feb 25, 2023, at 9:05 AM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

I just don't get what all this fuss is about trying to get PG up on the smallest possible values for shared_buffers, especially considering this person has 1 TB of memory?

Chris Hoover wrote on 2/25/2023 8:07 AM:

Ron,

Honestly, I grabbed 500MB to get the database up, by this time I was very frustrated and just wanted it up.  :)  I think I was at 1500MB and it still would not start.  So to save what was left of my evening out, I just picked 500.  

Here is shmall:

kernel.shmall = 17179869184

Thanks,

Chris Hoover

Senior DBA

AWeber.com

Cell: (803) 528-2269

Email: chrish@aweber.com

On Feb 25, 2023, at 5:25 AM, Ron <ronljohnsonjr@gmail.com> wrote:

Why did you have to drop shared buffers to 500MB, instead of something just below 2GB?

And what's "your kernel's SHMALL parameter"?

On 2/24/23 20:08, Chris Hoover wrote:

Hello All,

Have a strange issue that I can not solve.

I have a new server that has 1TB ram (not a typo).  I have been fighting with this evening to start up with a shared_buffer setting over 2GB.  When the memory allocation  breaks 2GB, the database won’t start.  I’ve dropped it down to 500M to get the database online.  We’ve tried both SysV memory and mmap memory.

Any ideas on what is going on and how to resolve?  I’m sure I’m missing something obvious, but it is totally eluding me.

OS: Ubuntu 18.04

Memory:  1007GB

CPU: Intel(R) Xeon(R) CPU           X7550  @ 2.00GHz (64 cores total)

Kernel Memory:

kernel.shmmax = 274877906944

kernel.shmall = 17179869184

Postgres Config:

                  name                                  |                  setting

 autovacuum_max_workers                 | 3

 autovacuum_work_mem                     | -1

 effective_cache_size                           | 98992128

 fsync                                                    | on

 full_page_writes                                  | on

 maintenance_work_mem                    | 2097152

 shared_buffers                                    | 64000

 shared_memory_type                         | sysv

 temp_buffers                                       | 4096

 wal_buffers                                         | 2048

 work_mem                                          | 119990

If you need additional parameters, please ask

SysV Error:

Feb 25 01:55:16 appdb-server01.production.aweberint.com postgres[34680]: FATAL:  could not create shared memory segment: Cannot allocate memory

DETAIL:  Failed system call was shmget(key=2359323, size=2063564800, 03600).

HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMALL parameter.  You might need to reconfigure the kernel with larger SHMALL.

Mmap Error:

FATAL:  could not map anonymous shared memory: Cannot allocate memory

HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 1635737600 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

Thanks,

Chris Hoover

Senior DBA

AWeber.com

Cell: (803) 528-2269

Email: chrish@aweber.com

-- 
Born in Arizona, moved to Babylonia.

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

  
<pgadvanced3.jpg>

On Feb 25, 2023, at 9:49 AM, Kenneth Marshall <ktm@rice.edu> wrote:

Chris Hoover wrote on 2/25/2023 8:07 AM:

Ron,

Honestly, I grabbed 500MB to get the database up, by this time I
was very frustrated and just wanted it up.  :)  I think I was at
1500MB and it still would not start.  So to save what was left of
my evening out, I just picked 500.

Here is shmall:
kernel.shmall = 17179869184

Thanks,


Chris Hoover
Senior DBA
AWeber.com
Cell: (803) 528-2269
Email: chrish@aweber.com

Hi Chris,

What are your limits set to for the account that the database runs as?

Regards,
Ken

On Feb 25, 2023, at 9:53 AM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:

I sincerely apologize.  I should have read this stuff more closely.

Jeff Janes wrote on 2/25/2023 9:51 AM:

On Sat, Feb 25, 2023 at 9:05 AM MichaelDBA <MichaelDBA@sqlexec.com> wrote:

I just don't get what all this fuss is about trying to get PG up on the smallest possible values for shared_buffers, especially considering this person has 1 TB of memory?

The fuss is why he can't make it bigger.  No one wants to make it as small as possible.

Chris Hoover wrote on 2/25/2023 8:07 AM:

Here is shmall:

kernel.shmall = 17179869184

How did that get so small?  On my Ubuntu18.04, it comes out of the box at 2^64 - 1.  Why would anyone have set about making it smaller?  Now the current value should still be big enough, but I wonder what else got changed while someone was monkeying with things that didn't need to be monkeyed with.

Cheers,

Jeff

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343

  
<pgadvanced3.jpg>

On Feb 25, 2023, at 11:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Evan Rempel <erempel@uvic.ca> writes:

Bear in mind that if you are using systemd to start postgres, then these
user limits may not apply.

Yeah, it seems likely that the PG server is being started under
more-restrictive limits than what these manual reports suggest.
It would be useful to try logging in as the Postgres OS user and
manually starting the server -- just do "postgres &" and see what
happens.  (If it does start, "pg_ctl stop" can be used to shut it
down again, or you can manually send SIGTERM to the postmaster
process.)

I tried to reproduce the problem by intentionally setting
"ulimit -v" too small for my PG settings, and I got error messages
that were similar but not identical to what Chris reported.
(I think the sysv case failed at shmat() not shmget().)  So it's
probably not ulimit per se that's responsible.  But if the
server is being started under systemd, then I can entirely
believe that systemd has some poorly-documented feature that
sets additional limits for daemon processes.

I'm still wondering about cgroups, too.

regards, tom lane

On Feb 26, 2023, at 7:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Chris Hoover <chrish@aweber.com> writes:

Got a chance to work on this today.  Here is what I’m getting.  I used the same command that is used when the config has small memory.

OK, so it still happens when interactive, which seems to exclude systemd.

Have you tried setting huge_pages = off, as somebody suggested upthread?
(The default setting of "try" ought to work anyway, but we've heard a few
reports suggesting sometimes it doesn't.)

Googling for ways to limit resources under Linux led me to mentions of
/etc/security/limits.conf, which might be worth checking.  The comments
in that say it only applies to logins via PAM, which should include your
interactive session and I'm not too sure about Patroni.

Otherwise I'm kind of running out of ideas :-(.  *Something* is clearly
interfering with your resource limits, but I don't know what.

regards, tom lane

On Feb 27, 2023, at 11:25 AM, Chris Hoover <chrish@aweber.com> wrote:

Tom and everyone,

We are leaning towards some sort of hardware/physical memory issue.  Our server admins are going to take the server and do some in depth testing of the hardware.  

Thanks again for all the assistance over the weekend.

Thanks,

Chris Hoover

Senior DBA

AWeber.com

Cell: (803) 528-2269

Email: chrish@aweber.com

On Feb 26, 2023, at 7:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Chris Hoover <chrish@aweber.com> writes:

Got a chance to work on this today.  Here is what I’m getting.  I used the same command that is used when the config has small memory.

OK, so it still happens when interactive, which seems to exclude systemd.

Have you tried setting huge_pages = off, as somebody suggested upthread?
(The default setting of "try" ought to work anyway, but we've heard a few
reports suggesting sometimes it doesn't.)

Googling for ways to limit resources under Linux led me to mentions of
/etc/security/limits.conf, which might be worth checking.  The comments
in that say it only applies to logins via PAM, which should include your
interactive session and I'm not too sure about Patroni.

Otherwise I'm kind of running out of ideas :-(.  *Something* is clearly
interfering with your resource limits, but I don't know what.

regards, tom lane