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 1pWP5J-0003ba-RP for pgsql-admin@arkaria.postgresql.org; Sun, 26 Feb 2023 22:01:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pWP5H-0001gH-Fk for pgsql-admin@arkaria.postgresql.org; Sun, 26 Feb 2023 22:01:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pWP5G-0001g8-QN for pgsql-admin@lists.postgresql.org; Sun, 26 Feb 2023 22:01:31 +0000 Received: from mail-qv1-xf33.google.com ([2607:f8b0:4864:20::f33]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pWP5C-0005JK-W4 for pgsql-admin@lists.postgresql.org; Sun, 26 Feb 2023 22:01:29 +0000 Received: by mail-qv1-xf33.google.com with SMTP id nv15so3311724qvb.7 for ; Sun, 26 Feb 2023 14:01:26 -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=nmuqVj9PITjFariqbXs6DpGQJN6JSaQ7Rx5NfDBs/Mo=; b=qzO0F+kumnOh2J2f2IPUzMDZ6616p+Apc/HrU7t/u/MDa7ZwYawvip52NxTp0IwKpN DOT1xDnrWyUiG5Pu0vHZF2fGnAv7JkzhCoV2E23KrRoXzZ1zQZTlQQy1ZkuKYdepar/C 3fcGxTWO7qFb7jzT7ID/TZWKKUPdUIyoawhdlMLVW2lFuvqAiO8O5KORcjoWUpatwB1f VDLm8uAvMLV1sTrw+g3vcT5f4adrwymaYdkebwPkA3IXx+SJvtUeQu68WRXTuHUNCPKS PxuQ0Vjb3AohKstjo3bNtUS33I7hZK2rfRKoYEavQv8ATF60tev4gze0i2OIXgyEmeaz QOmA== 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=nmuqVj9PITjFariqbXs6DpGQJN6JSaQ7Rx5NfDBs/Mo=; b=RoqNMXBfthcsLKN0fdPeQfeqoupyXew1i+l41+nyWYWTscoFuJVwRjU33xhuqmZwtZ +Y63mp7JZAcdYOppNo6kvAskWa+AvV8aa4Y8Z2yZsqJUxILGP/PwM0smUwsgfycqUpBQ 74xrwx6Ho6FZrpb6YLX3WJlWDF4pl2Y3ja8ucbGp05lThhDIODa2anV6uQONGztV1kSq HWZgl71P2atS9yplMFrMtoch7QvSZ1cUC03pN1hBv+Z15dZ3nuvKMuWiBFUNCJ/DFtN0 kKDxHWjsFmSQvV5SCIVRIp45z2d5eH4hLb8NqSNEGt7/CIDyPR0QcQbsTZ7GqxkY/dU4 DhPQ== X-Gm-Message-State: AO0yUKXTCfVqNq3+Kust6eF/Ijz1Ows/uawfpJuNM33gYGnb++WltAMV a4bQ0idgLKPRM+IuuqyiN0mc/Q== X-Google-Smtp-Source: AK7set9PNMEg4MYQPqd0Vy5//Bcxvd1YA2fEy/j6bW9+rPOk3UsctLA+ldXo/DCKZYBtpbEm/lqHrQ== X-Received: by 2002:a05:6214:2b0a:b0:56f:8b5:3e94 with SMTP id jx10-20020a0562142b0a00b0056f08b53e94mr13589221qvb.14.1677448885944; Sun, 26 Feb 2023 14:01:25 -0800 (PST) Received: from smtpclient.apple (vpn-a.aweberprod.com. [204.194.222.13]) by smtp.gmail.com with ESMTPSA id bm26-20020a05620a199a00b007424376ca4bsm3746239qkb.18.2023.02.26.14.01.24 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 26 Feb 2023 14:01:25 -0800 (PST) From: Chris Hoover Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_C58D2374-CE6C-442D-A0AF-0351E512BA79" 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: Sun, 26 Feb 2023 17:01:13 -0500 In-Reply-To: <1177969.1677342917@sss.pgh.pa.us> Cc: Evan Rempel , pgsql-admin@lists.postgresql.org To: Tom Lane References: <4b583c4a-022f-f452-c0c8-0a932a6ee411@gmail.com> <4a41387e-516e-0782-6110-89c05a99d020@sqlexec.com> <20230225144930.GS9791@aart.rice.edu> <8b47c295-4b16-1a00-32e4-807103816475@uvic.ca> <1177969.1677342917@sss.pgh.pa.us> 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=_C58D2374-CE6C-442D-A0AF-0351E512BA79 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Tom, Got a chance to work on this today. Here is what I=E2=80=99m getting. = I used the same command that is used when the config has small memory. $ /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/ = --config-file=3D/etc/postgresql/14/appdb/postgresql.conf = --listen_addresses=3D0.0.0.0 --port=3D5432 =E2=80=94cluster_name=3D = --wal_level=3Dlogical --hot_standby=3Don --max_connections=3D150 = --max_wal_senders=3D10 --max_prepared_transactions=3D0 = --max_locks_per_transaction=3D64 --track_commit_timestamp=3Doff = --max_replication_slots=3D10 --max_worker_processes=3D12 = --wal_log_hints=3Don 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 21967716352 bytes), reduce = PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or = max_connections. LOG: database system is shut down $ /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/ = --config-file=3D/etc/postgresql/14/appdb/postgresql.conf = --listen_addresses=3D0.0.0.0 --port=3D5432 =E2=80=94cluster_name=3D = --wal_level=3Dlogical --hot_standby=3Don --max_connections=3D150 = --max_wal_senders=3D10 --max_prepared_transactions=3D0 = --max_locks_per_transaction=3D64 --track_commit_timestamp=3Doff = --max_replication_slots=3D10 --max_worker_processes=3D12 = --wal_log_hints=3Don FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=3D2359323, size=3D21967716352, = 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. The PostgreSQL documentation contains more information about = shared memory configuration. LOG: database system is shut down $ ulimit -v unlimited I=E2=80=99m not sure about the cgroup stuff. I installed lscgroup and = this is what is shows. (BTW, we are using Patroni to control = PostgreSQL, but it does not appear to be part of the problem since = I=E2=80=99ve duplicated the errors without it): $ lscgroup devices:/ devices:/user.slice devices:/system.slice devices:/system.slice/irqbalance.service devices:/system.slice/system-systemd\x2dfsck.slice devices:/system.slice/syslog-ng.service devices:/system.slice/systemd-networkd.service devices:/system.slice/systemd-udevd.service devices:/system.slice/cron.service devices:/system.slice/oddjobd.service devices:/system.slice/sys-fs-fuse-connections.mount devices:/system.slice/sys-kernel-config.mount devices:/system.slice/networkd-dispatcher.service devices:/system.slice/sys-kernel-debug.mount devices:/system.slice/certmonger.service devices:/system.slice/accounts-daemon.service devices:/system.slice/swapfile.swap devices:/system.slice/numad.service devices:/system.slice/systemd-journald.service devices:/system.slice/unattended-upgrades.service devices:/system.slice/sensu-client.service devices:/system.slice/ssh.service devices:/system.slice/dev-mqueue.mount devices:/system.slice/rpc-gssd.service devices:/system.slice/vnstat.service devices:/system.slice/var-lib-postgresql.mount devices:/system.slice/rpcbind.service devices:/system.slice/chrony.service devices:/system.slice/sssd.service devices:/system.slice/proc-sys-fs-binfmt_misc.mount devices:/system.slice/run-rpc_pipefs.mount devices:/system.slice/autofs.service devices:/system.slice/patroni.service devices:/system.slice/consul.service devices:/system.slice/telegraf.service devices:/system.slice/dev-hugepages.mount devices:/system.slice/dbus.service devices:/system.slice/system-getty.slice devices:/system.slice/systemd-logind.service cpuset:/ cpu,cpuacct:/ cpu,cpuacct:/user.slice cpu,cpuacct:/user.slice/user-5088.slice cpu,cpuacct:/system.slice memory:/ pids:/ pids:/user.slice pids:/user.slice/user-5088.slice pids:/user.slice/user-5088.slice/user@5088.service pids:/user.slice/user-5088.slice/session-267.scope pids:/user.slice/user-5088.slice/session-269.scope pids:/user.slice/user-5088.slice/session-88.scope pids:/system.slice pids:/system.slice/irqbalance.service pids:/system.slice/system-systemd\x2dfsck.slice pids:/system.slice/syslog-ng.service pids:/system.slice/systemd-networkd.service pids:/system.slice/systemd-udevd.service pids:/system.slice/cron.service pids:/system.slice/oddjobd.service pids:/system.slice/sys-fs-fuse-connections.mount pids:/system.slice/sys-kernel-config.mount pids:/system.slice/networkd-dispatcher.service pids:/system.slice/sys-kernel-debug.mount pids:/system.slice/certmonger.service pids:/system.slice/accounts-daemon.service pids:/system.slice/swapfile.swap pids:/system.slice/numad.service pids:/system.slice/systemd-journald.service pids:/system.slice/unattended-upgrades.service pids:/system.slice/sensu-client.service pids:/system.slice/ssh.service pids:/system.slice/dev-mqueue.mount pids:/system.slice/rpc-gssd.service pids:/system.slice/vnstat.service pids:/system.slice/var-lib-postgresql.mount pids:/system.slice/rpcbind.service pids:/system.slice/chrony.service pids:/system.slice/sssd.service pids:/system.slice/proc-sys-fs-binfmt_misc.mount pids:/system.slice/run-rpc_pipefs.mount pids:/system.slice/autofs.service pids:/system.slice/patroni.service pids:/system.slice/consul.service pids:/system.slice/telegraf.service pids:/system.slice/dev-hugepages.mount pids:/system.slice/dbus.service pids:/system.slice/system-getty.slice pids:/system.slice/system-getty.slice/getty@tty1.service pids:/system.slice/systemd-logind.service rdma:/ hugetlb:/ perf_event:/ blkio:/ freezer:/ net_cls,net_prio:/ Thanks, Chris Hoover Senior DBA AWeber.com Cell: (803) 528-2269 Email: chrish@aweber.com > On Feb 25, 2023, at 11:35 AM, Tom Lane wrote: >=20 > Evan Rempel writes: >> Bear in mind that if you are using systemd to start postgres, then = these=20 >> user limits may not apply. >=20 > 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.) >=20 > 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. >=20 > I'm still wondering about cgroups, too. >=20 > regards, tom lane --Apple-Mail=_C58D2374-CE6C-442D-A0AF-0351E512BA79 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Tom,

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

$ = /usr/lib/postgresql/14/bin/postgres -D = /var/lib/postgresql/14/<dir> = --config-file=3D/etc/postgresql/14/appdb/postgresql.conf = --listen_addresses=3D0.0.0.0 --port=3D5432 =E2=80=94cluster_name=3D<nam= e> --wal_level=3Dlogical --hot_standby=3Don --max_connections=3D150 = --max_wal_senders=3D10 --max_prepared_transactions=3D0 = --max_locks_per_transaction=3D64 --track_commit_timestamp=3Doff = --max_replication_slots=3D10 --max_worker_processes=3D12 = --wal_log_hints=3Don
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 21967716352 bytes), reduce PostgreSQL's shared = memory usage, perhaps by reducing shared_buffers or = max_connections.
LOG:  database system is shut = down

$ /usr/lib/postgresql/14/bin/postgres -D = /var/lib/postgresql/14/<dir> = --config-file=3D/etc/postgresql/14/appdb/postgresql.conf = --listen_addresses=3D0.0.0.0 --port=3D5432 =E2=80=94cluster_name=3D<nam= e> --wal_level=3Dlogical --hot_standby=3Don --max_connections=3D150 = --max_wal_senders=3D10 --max_prepared_transactions=3D0 = --max_locks_per_transaction=3D64 --track_commit_timestamp=3Doff = --max_replication_slots=3D10 --max_worker_processes=3D12 = --wal_log_hints=3Don
FATAL:  could not create = shared memory segment: Cannot allocate memory
DETAIL: =  Failed system call was shmget(key=3D2359323, size=3D21967716352, = 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.
The PostgreSQL documentation = contains more information about shared memory = configuration.
LOG:  database system is shut = down

$ ulimit = -v
unlimited

I=E2=80=99m not sure = about the cgroup stuff.  I installed lscgroup and this is what is = shows.  (BTW, we are using Patroni to control PostgreSQL, but it = does not appear to be part of the problem since I=E2=80=99ve duplicated = the errors without it):
$ = lscgroup
devices:/
devices:/user.slice
devic= es:/system.slice
devices:/system.slice/irqbalance.service
<= div>devices:/system.slice/system-systemd\x2dfsck.slice
devices:/= system.slice/syslog-ng.service
devices:/system.slice/systemd-net= workd.service
devices:/system.slice/systemd-udevd.service
<= div>devices:/system.slice/cron.service
devices:/system.slice/odd= jobd.service
devices:/system.slice/sys-fs-fuse-connections.mount=
devices:/system.slice/sys-kernel-config.mount
devices= :/system.slice/networkd-dispatcher.service
devices:/system.slice= /sys-kernel-debug.mount
devices:/system.slice/certmonger.service=
devices:/system.slice/accounts-daemon.service
devices= :/system.slice/swapfile.swap
devices:/system.slice/numad.service=
devices:/system.slice/systemd-journald.service
device= s:/system.slice/unattended-upgrades.service
devices:/system.slic= e/sensu-client.service
devices:/system.slice/ssh.service
devices:/system.slice/dev-mqueue.mount
devices:/system.slice/= rpc-gssd.service
devices:/system.slice/vnstat.service
= devices:/system.slice/var-lib-postgresql.mount
devices:/system.s= lice/rpcbind.service
devices:/system.slice/chrony.service
<= div>devices:/system.slice/sssd.service
devices:/system.slice/pro= c-sys-fs-binfmt_misc.mount
devices:/system.slice/run-rpc_pipefs.= mount
devices:/system.slice/autofs.service
devices:/sy= stem.slice/patroni.service
devices:/system.slice/consul.service<= /div>
devices:/system.slice/telegraf.service
devices:/system= .slice/dev-hugepages.mount
devices:/system.slice/dbus.service
devices:/system.slice/system-getty.slice
devices:/system= .slice/systemd-logind.service
cpuset:/
cpu,cpuacct:/
cpu,cpuacct:/user.slice
cpu,cpuacct:/user.slice/user-50= 88.slice
cpu,cpuacct:/system.slice
memory:/
= pids:/
pids:/user.slice
pids:/user.slice/user-5088.sli= ce
pids:/user.slice/user-5088.slice/user@5088.service
= pids:/user.slice/user-5088.slice/session-267.scope
pids:/user.sl= ice/user-5088.slice/session-269.scope
pids:/user.slice/user-5088= .slice/session-88.scope
pids:/system.slice
pids:/syste= m.slice/irqbalance.service
pids:/system.slice/system-systemd\x2d= fsck.slice
pids:/system.slice/syslog-ng.service
pids:/= system.slice/systemd-networkd.service
pids:/system.slice/systemd= -udevd.service
pids:/system.slice/cron.service
pids:/s= ystem.slice/oddjobd.service
pids:/system.slice/sys-fs-fuse-conne= ctions.mount
pids:/system.slice/sys-kernel-config.mount
pids:/system.slice/networkd-dispatcher.service
pids:/system.sl= ice/sys-kernel-debug.mount
pids:/system.slice/certmonger.service=
pids:/system.slice/accounts-daemon.service
pids:/syst= em.slice/swapfile.swap
pids:/system.slice/numad.service
pids:/system.slice/systemd-journald.service
pids:/system.slice= /unattended-upgrades.service
pids:/system.slice/sensu-client.ser= vice
pids:/system.slice/ssh.service
pids:/system.slice= /dev-mqueue.mount
pids:/system.slice/rpc-gssd.service
= pids:/system.slice/vnstat.service
pids:/system.slice/var-lib-pos= tgresql.mount
pids:/system.slice/rpcbind.service
pids:= /system.slice/chrony.service
pids:/system.slice/sssd.service
pids:/system.slice/proc-sys-fs-binfmt_misc.mount
pids:/sy= stem.slice/run-rpc_pipefs.mount
pids:/system.slice/autofs.servic= e
pids:/system.slice/patroni.service
pids:/system.slic= e/consul.service
pids:/system.slice/telegraf.service
p= ids:/system.slice/dev-hugepages.mount
pids:/system.slice/dbus.se= rvice
pids:/system.slice/system-getty.slice
pids:/syst= em.slice/system-getty.slice/getty@tty1.service
pids:/system.slic= e/systemd-logind.service
rdma:/
hugetlb:/
pe= rf_event:/
blkio:/
freezer:/
net_cls,net_pri= o:/


Thanks,


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



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

= --Apple-Mail=_C58D2374-CE6C-442D-A0AF-0351E512BA79--