Re: postgresql 18 and shm

[Greg Troxel <gdt%lexort.com@localhost>]

Marc Baudoin <babafou%babafou.eu.org@localhost> writes:

>> [pgsql 18 wants lots of semaphore resources]

> Have a look at this thread from last year:
>
> https://mail-index.netbsd.org/pkgsrc-users/2025/10/25/msg042112.html
>
> The setting that works for me is:
>
> kern.ipc.semmni=20
> kern.ipc.semmns=200

What is kern.posix.semmax?  For me, it's 128, netbsd
9/10/11. i386/earmv7hf-el/amd64/aarch64.

> I still think changing the default values or, at least,
> displaying a warning when installing the package or starting
> PostgreSQL with insufficient values would be nice.

We should not warn at install time; we don't do that, for resource
issues, and install is typically automatic via pkgin.  This is about
resources at start time, and postgresql is itself failing.   So the real
questions are how to set up the package so that it does not fail, and/or
to give a better error message.

It does seem like pgsql is giving a pretty reasonable error message, but
the bit about sets is not explained.

It sounds like we should do some combination of:

  0) file a bug upstream that the error is not as specific as it could
  be, and does not point to the specific call that failed

  1) increase the defaults in NetBSD (and pull up to 11, 10, and, until
     11 is released, 9 :-) to be high enough.

  2) In /etc/rc.d/pgsql, check the semaphore limits and for each that is
     not enough *for the default pgsql config*
     a) print a warning
     b) print a warning and fail
     c) increase the limit

  3) Change the defaults in the installed pgsql config files.


The big questions in my mind are:

  What are the resource usage considerations of increased semaphore
  limits?  I would guess they are pretty low, dating from a VAX with 1MB
  of RAM, and just haven't gotten increased, and that the proposed
  larger values would not hurt, except on people trying to serious
  retrocomputing on 32MB machines.

  Is this a change  in resource usage by pgsql18 for the same configured
  connection limit, is it a change in the default connection limit, or
  is it something else?


Looking on netbsd-10/amd64 with pgsql 16, I see

  --- /usr/pkg/share/postgresql/postgresql.conf.sample    2026-05-15 15:28:51.000000000 -0400
  +++ postgresql.conf     2024-09-03 18:40:11.938840152 -0400
  @@ -64,3 +64,3 @@
   #port = 5432                           # (change requires restart)
  -#max_connections = 100                 # (change requires restart)
  +max_connections = 20                   # (change requires restart)
   #reserved_connections = 0              # (change requires restart)

and it's unclear how postgresql.conf.sample flows to postgresql.conf.


Martin's numbers

> # The PostgresSQL server may need additional semmaphore resources
> kern.posix.semmax=256
> kern.ipc.semmns=256
> kern.ipc.semmni=64

             kern.ipc.semmni
                     Max number of number of semaphore identifiers.

             kern.ipc.semmns
                     Max number of number of semaphores in system.

and I am having trouble finding docs for kern.posix.semmax.

On a machine that is running pgsql server, I see

  kern.posix.semmax = 128
  kern.posix.semcnt = 0

so it seems that is POSIX semaphores vs SysV semaphores?

I see 3 ids with 17 semaphores each (??) in use by pgsql.

  Semaphores:
  T        ID     KEY        MODE       OWNER    GROUP  CREATOR   CGROUP NSEMS    OTIME    CTIME
  s     65536    6321331 --rw-------    pgsql    pgsql    pgsql    pgsql    17 20:31:43  7:54:54
  s     65537    6321332 --rw-------    pgsql    pgsql    pgsql    pgsql    17 20:31:43  7:54:54
  s     65538    6321333 --rw-------    pgsql    pgsql    pgsql    pgsql    17  7:59:54  7:54:54


plus one that I think is unrelated.