Re: Is there a significant difference in Memory settings between 9.5 and 12

On Mon, May 11, 2020 at 2:08 PM Tory M Blue <tmblue(at)gmail(dot)com> wrote:

>
>
> On Mon, May 11, 2020 at 1:36 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Tory M Blue <tmblue(at)gmail(dot)com> writes:
>> > 12 will not start at 10GB, even though it's the same hardware, same
>> config
>> > file, same physical box, same everything, just version 12 vs 9.5
>>
>> For me, using all-default settings (in particular, shared_buffers =
>> 128MB), the shared memory block is about 141.6MB using 9.5 and 142.1MB
>> using 12. So there's half a meg or so of additional data in v12, but
>> certainly not gigabytes worth.
>>
>> Are you trying to start both postmasters concurrently? Maybe you're
>> hitting some kernel limit on the total amount of shared memory in the
>> system.
>>
>> regards, tom lane
>>
>
> Okay the one difference I see in settings is this little gem in 12..
>
> shared_memory_type mmap
>
> Otherwise i'm not seeing a ton of other settings not common between them,.
>
> This is the only major difference I'm seeing, as it's really not an option
> in 9.5..... Appears 9.5 was using
>
> shared_memory_type (enum)
>
> Specifies the shared memory implementation that the server should use for
> the main shared memory region that holds PostgreSQL's shared buffers and
> other shared data. Possible values are mmap (for anonymous shared memory
> allocated using mmap), sysv (for System V shared memory allocated via
> shmget) and windows (for Windows shared memory). Not all values are
> supported on all platforms; the first supported option is the default for
> that platform. The use of the sysv option, which is not the default on
> any platform, is generally discouraged because it typically requires
> non-default kernel settings to allow for large allocations (see
> Section 18.4.1
> <https://www.postgresql.org/docs/12/kernel-resources.html#SYSVIPC>).
>
>

That didn't help.

ay 11 19:46:13 qdb03.prod.ca. postmaster[31048]: < 2020-05-11 19:46:13.026
PDT >FATAL: could not create shared memory segment: Cannot allocate memory
May 11 19:46:13 qdb03.prod.ca postmaster[31048]: < 2020-05-11 19:46:13.026
PDT >DETAIL: Failed system call was shmget(key=5432001, size=11026235392,
03600).
May 11 19:46:13 qdb03.prod.ca postmaster[31048]: < 2020-05-11 19:46:13.026
PDT >HINT: This error usually means that PostgreSQL's request for a
shared memory segm
May 11 19:46:13 qdb03.prod.ca postmaster[31048]: The PostgreSQL
documentation contains more information about shared memory configuration.

Attempted to change ;
#shared_memory_type = 'sysv'

It took the change but didn't help. So 10GB of shared_buffers in 12 is
still a no go. I'm down to 5GB and it works, but this is the same hardware,
the same exact 9.5 configuration. So I'm missing something. WE have not had
to mess with kernel memory settings since 9.4, so this is an odd one.

I'll keep digging, but i'm hesitant to do my multiple TB db's with half of
their shared buffer configs, until I understand what 12 is doing
differently than 9.5

Thanks again for the ideas

Tory