Обсуждение: Tuning Linux for Postgresql - Database failed to start

Поиск
Список
Период
Сортировка

Tuning Linux for Postgresql - Database failed to start

От
Albin Ary
Дата:
Hello, 
I have a Postgresql Database cluster. 
Version: 14.2
OS: Oracle Linux 8

I tried to follow the link to optimize the server.

Unfortunately, after rebooting the database is not starting anymore. I know the HINT is straightforward but I am not able to see what the reason could be. 

Any advice would be appreciated.

-- Unit postgresql-14.service has begun starting up.
May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.047 CEST [1612] FATAL:  could not map anonymous shared memory: Cannot allocate memory
May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.047 CEST [1612] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory,>
May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.048 CEST [1612] LOG:  database system is shut down
May 10 22:10:40 postgersdb.localdomain systemd[1]: postgresql-14.service: Main process exited, code=exited, status=1/FAILURE
May 10 22:10:40 postgersdb.localdomain systemd[1]: postgresql-14.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: https://support.oracle.com

Re: Tuning Linux for Postgresql - Database failed to start

От
Scott Ribe
Дата:
> On May 10, 2022, at 2:23 PM, Albin Ary <albinary10@gmail.com> wrote:
>
> Hello,
> I have a Postgresql Database cluster.
> Version: 14.2
> OS: Oracle Linux 8
>
> I tried to follow the link to optimize the server.
> https://www.enterprisedb.com/blog/tuning-red-hat-enterprise-linux-family-postgresql
>
> Unfortunately, after rebooting the database is not starting anymore. I know the HINT is straightforward but I am not
ableto see what the reason could be.  
>
> Any advice would be appreciated.
>
> -- Unit postgresql-14.service has begun starting up.
> May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.047 CEST [1612] FATAL:  could not map
anonymousshared memory: Cannot allocate memory 
> May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.047 CEST [1612] HINT:  This error
usuallymeans that PostgreSQL's request for a shared memory segment exceeded available memory,> 
> May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.048 CEST [1612] LOG:  database system is
shutdown 
> May 10 22:10:40 postgersdb.localdomain systemd[1]: postgresql-14.service: Main process exited, code=exited,
status=1/FAILURE
> May 10 22:10:40 postgersdb.localdomain systemd[1]: postgresql-14.service: Failed with result 'exit-code'.
> -- Subject: Unit failed
> -- Defined-By: systemd
> -- Support: https://support.oracle.com

This is a decent quick guide to the parameters affecting this:

https://docs.oracle.com/cd/E19879-01/821-0182/fxxua/index.html

And here is the explanation as to how they related to postgres:

https://www.postgresql.org/docs/14/kernel-resources.html

Re: Tuning Linux for Postgresql - Database failed to start

От
Albin Ary
Дата:
Hello, 

Thanks a lot for your inputs. 

I only changed the section related to HugePages. 

This server has 4 GB RAM.

shared_buffers = '1GB' 
vm.nr_hugepages=650

tuned profile:



[main]
summary=Tuned profile for EDB PostgreSQL Instances
[bootloader]
cmdline=transparent_hugepage=never
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
[sysctl]
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.overcommit_memory=0
net.ipv4.tcp_timestamps=0

[vm]
transparent_hugepages=never
vm.nr_hugepages=605

On Tue, 10 May 2022 at 22:29, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On May 10, 2022, at 2:23 PM, Albin Ary <albinary10@gmail.com> wrote:
>
> Hello,
> I have a Postgresql Database cluster.
> Version: 14.2
> OS: Oracle Linux 8
>
> I tried to follow the link to optimize the server.
> https://www.enterprisedb.com/blog/tuning-red-hat-enterprise-linux-family-postgresql
>
> Unfortunately, after rebooting the database is not starting anymore. I know the HINT is straightforward but I am not able to see what the reason could be.
>
> Any advice would be appreciated.
>
> -- Unit postgresql-14.service has begun starting up.
> May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.047 CEST [1612] FATAL:  could not map anonymous shared memory: Cannot allocate memory
> May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.047 CEST [1612] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory,>
> May 10 22:10:40 postgersdb.localdomain postmaster[1612]: 2022-05-10 22:10:40.048 CEST [1612] LOG:  database system is shut down
> May 10 22:10:40 postgersdb.localdomain systemd[1]: postgresql-14.service: Main process exited, code=exited, status=1/FAILURE
> May 10 22:10:40 postgersdb.localdomain systemd[1]: postgresql-14.service: Failed with result 'exit-code'.
> -- Subject: Unit failed
> -- Defined-By: systemd
> -- Support: https://support.oracle.com

This is a decent quick guide to the parameters affecting this:

https://docs.oracle.com/cd/E19879-01/821-0182/fxxua/index.html

And here is the explanation as to how they related to postgres:

https://www.postgresql.org/docs/14/kernel-resources.html

Re: Tuning Linux for Postgresql - Database failed to start

От
Kenneth Marshall
Дата:
On Tue, May 10, 2022 at 10:39:09PM +0200, Albin Ary wrote:
> Hello,
> 
> Thanks a lot for your inputs.
> 
> I only changed the section related to HugePages.
> 
> This server has 4 GB RAM.
> 
> shared_buffers = '1GB'
> vm.nr_hugepages=650
> 
> tuned profile:
> 
Hi Albin,

1GB shared_buffers + 1.3GB huge pages on a 4GB system = don't have
enough resources. Please remove the huge pages request or add additional
memory to your system.

Regards,
Ken

Re: Tuning Linux for Postgresql - Database failed to start

От
Albin Ary
Дата:
Ok, will do that. Thanks a lot. 

It is clear now why it fails to start. 

Actually, the blog author mentions that setting it high might cause the database failing to start, anyway a calculation is provided and I followed that calculation. 


The ideal number of huge pages is just a bit higher than this -- just a bit. If you increase this value too much, processes that need small pages that also need space in the OS will fail to start. This may even end up with the operating system failing to boot or other PostgreSQL instances on the same server failing to start.




On Tue, 10 May 2022 at 22:48, Kenneth Marshall <ktm@rice.edu> wrote:
On Tue, May 10, 2022 at 10:39:09PM +0200, Albin Ary wrote:
> Hello,
>
> Thanks a lot for your inputs.
>
> I only changed the section related to HugePages.
>
> This server has 4 GB RAM.
>
> shared_buffers = '1GB'
> vm.nr_hugepages=650
>
> tuned profile:
>
Hi Albin,

1GB shared_buffers + 1.3GB huge pages on a 4GB system = don't have
enough resources. Please remove the huge pages request or add additional
memory to your system.

Regards,
Ken