Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pVvtZ-0003TP-Re for pgsql-admin@arkaria.postgresql.org; Sat, 25 Feb 2023 14:51:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pVvtV-0006oB-Po for pgsql-admin@arkaria.postgresql.org; Sat, 25 Feb 2023 14:51:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pVvtV-0006lY-7s for pgsql-admin@lists.postgresql.org; Sat, 25 Feb 2023 14:51:25 +0000 Received: from mail-qv1-xf29.google.com ([2607:f8b0:4864:20::f29]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pVvtS-0007qp-VS for pgsql-admin@lists.postgresql.org; Sat, 25 Feb 2023 14:51:24 +0000 Received: by mail-qv1-xf29.google.com with SMTP id y12so1488267qvt.8 for ; Sat, 25 Feb 2023 06:51:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aweber.com; s=2018090500; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=LeRN/QOmwWsUAMOB6+ShZvQPyYYF3PfIAgRHIG4w4vw=; b=ZRwURWmwD9ziDHxjSXsK/xhhSJrEiEalWyTqbjbMAlrT4/Gww3Tpqc26f2L4uv1DQa ABzIhXLGCWmOlsNUW/lq9ooo84lVxUqV7flGEyAPkD7mQO6zniY7G1f7P543UJN4iJsg 415qtQJIF+rQmy9lNj7+1/37YM2jvGEphXZXlyWqiEmS9vv0qli0E79jpO5w4bYM5Oug W0aHdt/PBWuYAwgLo3XhkL3PmxeJtW+yXn8Dkec4LwXqP7bcz+FbM08qt5uxQ8wRp2L3 lbrBBA1Ahee5z72W1NkV8Ear+EiKhupGz3v0+EDz5M5nzyZHc82nJvUtx3yHmNEcF2/5 d4Gw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=LeRN/QOmwWsUAMOB6+ShZvQPyYYF3PfIAgRHIG4w4vw=; b=gvxLOvENhfgcogdRl9bPj5foh0KBTk34vV67nBcM2XyFcKuhDsVQlMvRz0NZzTjH7y bA51DxMQJauh78bcYZLE2g31DY7jcMHn7c3NFnImQ+nApKd3Dy+fE5qHqC2cduMgz25j RLZkt6l+S9nN7fotf+yi0Yp2bPkC2dESJVl0ya7vpegA+S+rTvlHHISWEwPU6LbU+lC5 ao1tNRdxpXuyooO7G1O/ZhNqa6dhh7QlwTBDL9vwINEOHLSkLWTNURHlbuzW3133rVYd qIcUyXDnipL/0aUKOyWryzC9WUgq/1KjsQplWmvwfyRS3gveGjGSr8G6DZgn/btX/rdc 5xGg== X-Gm-Message-State: AO0yUKVCf0W84V27gX/0ven9o+1hFK5fS/3KZVKxFaQ3yPF7MufGhvLx rPbazsmayq8e6X9Wk4lUost5uQ== X-Google-Smtp-Source: AK7set9a/J7NQzgeadgeXrX7N5yU1Nvu+Vu492dWr60smK1wn6ws4r7JOgU635s6yJegrf1L2aNWEw== X-Received: by 2002:a05:6214:e8d:b0:56e:a756:91d with SMTP id hf13-20020a0562140e8d00b0056ea756091dmr38809636qvb.50.1677336681032; Sat, 25 Feb 2023 06:51:21 -0800 (PST) Received: from smtpclient.apple (vpn-a.aweberprod.com. [204.194.222.13]) by smtp.gmail.com with ESMTPSA id q18-20020ac87352000000b003bfaff2a6b9sm1444957qtp.10.2023.02.25.06.51.20 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 25 Feb 2023 06:51:20 -0800 (PST) From: Chris Hoover Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_E5313349-05FB-4D97-BD90-2217FB9A5ECF" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.400.51.1.1\)) Subject: Re: New PG14 server won't start with >2GB shared_buffers Date: Sat, 25 Feb 2023 09:51:09 -0500 In-Reply-To: <4a41387e-516e-0782-6110-89c05a99d020@sqlexec.com> Cc: Ron , pgsql-admin@lists.postgresql.org To: MichaelDBA References: <4b583c4a-022f-f452-c0c8-0a932a6ee411@gmail.com> <4a41387e-516e-0782-6110-89c05a99d020@sqlexec.com> X-Mailer: Apple Mail (2.3731.400.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_E5313349-05FB-4D97-BD90-2217FB9A5ECF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 MichaelDBA, The issue is PostgreSQL won=E2=80=99t start with a large shared_buffer. = If I try to start using more than ~2GB, it won=E2=80=99t start (See = errors in original post). That=E2=80=99s the issue we are trying to = resolve. =20 This is a large server running on bare metal that won=E2=80=99t let PG = start with a large shared_buffer setting. Until we can resolve this, we = can=E2=80=99t upgrade to this server. Thanks, Chris Hoover Senior DBA AWeber.com Cell: (803) 528-2269 Email: chrish@aweber.com > On Feb 25, 2023, at 9:05 AM, MichaelDBA = wrote: >=20 > 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? >=20 > Chris Hoover wrote on 2/25/2023 8:07 AM: >> Ron, >>=20 >> 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. =20 >>=20 >> Here is shmall: >> kernel.shmall =3D 17179869184 >>=20 >> Thanks, >>=20 >>=20 >> Chris Hoover >> Senior DBA >> AWeber.com >> Cell: (803) 528-2269 >> Email: chrish@aweber.com >>=20 >>=20 >>=20 >>> On Feb 25, 2023, at 5:25 AM, Ron = wrote: >>>=20 >>> Why did you have to drop shared buffers to 500MB, instead of = something just below 2GB? >>>=20 >>> And what's "your kernel's SHMALL parameter"? >>>=20 >>> On 2/24/23 20:08, Chris Hoover wrote: >>>> Hello All, >>>>=20 >>>> Have a strange issue that I can not solve. >>>>=20 >>>> 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=E2=80=99t = start. I=E2=80=99ve dropped it down to 500M to get the database online. = We=E2=80=99ve tried both SysV memory and mmap memory. >>>>=20 >>>> Any ideas on what is going on and how to resolve? I=E2=80=99m sure = I=E2=80=99m missing something obvious, but it is totally eluding me. >>>>=20 >>>> OS: Ubuntu 18.04 >>>> Memory: 1007GB >>>> CPU: Intel(R) Xeon(R) CPU X7550 @ 2.00GHz (64 cores = total) >>>>=20 >>>> Kernel Memory: >>>> kernel.shmmax =3D 274877906944 >>>> kernel.shmall =3D 17179869184 >>>>=20 >>>> 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 >>>>=20 >>>> If you need additional parameters, please ask >>>>=20 >>>> 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=3D2359323, = size=3D2063564800, 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. >>>>=20 >>>> 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. >>>>=20 >>>> Thanks, >>>>=20 >>>>=20 >>>> Chris Hoover >>>> Senior DBA >>>> AWeber.com >>>> Cell: (803) 528-2269 >>>> Email: chrish@aweber.com >>>>=20 >>>>=20 >>>>=20 >>>=20 >>> --=20 >>> Born in Arizona, moved to Babylonia. >>=20 >=20 >=20 > Regards, > Michael Vitale > Michaeldba@sqlexec.com > 703-600-9343 > =20 > --Apple-Mail=_E5313349-05FB-4D97-BD90-2217FB9A5ECF Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 MichaelDBA,

The issue is = PostgreSQL won=E2=80=99t start with a large shared_buffer.  If I = try to start using more than ~2GB, it won=E2=80=99t start (See errors in = original post).  That=E2=80=99s the issue we are trying to resolve. =  

This is a large server running on bare = metal that won=E2=80=99t let PG start with a large shared_buffer = setting.  Until we can resolve this, we can=E2=80=99t upgrade to = this server.

Thanks,


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



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 =3D 17179869184

Thanks,


Chris Hoover
Senior = DBA
AWeber.com
Cell: (803) = 528-2269



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=E2=80=99t start.  I=E2=80=99ve = dropped it down to 500M to get the database online.  We=E2=80=99ve = tried both SysV memory and mmap memory.

Any = ideas on what is going on and how to resolve?  I=E2=80=99m sure = I=E2=80=99m 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 =3D = 274877906944
kernel.shmall =3D = 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=3D2359323, size=3D2063564800, 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




-- 
Born in Arizona, moved = to = Babylonia.



Regards,
Michael Vitale
703-600-9343
  
<pgadvanced3.jpg>

= --Apple-Mail=_E5313349-05FB-4D97-BD90-2217FB9A5ECF--