Re: Enable huge_pages in PostgreSQL server version 11.2

I am not Amar.

John Nieblas
951.545.4885

________________________________
From: Bikram MAJUMDAR <[email protected]>
Sent: Monday, August 12, 2019 7:26:55 AM
To: Amar <[email protected]>; Suresh Seema <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Enable huge_pages in PostgreSQL server version 11.2

Hi Amar,

Introducing myself – my name is Bikram. I am new in postgres world.
Trying to download/install and configure pgwatch2 for monitoring.
Do you have any guidance or any  pointers on how would we install and run it for our single (no cluster) postgres database.

Thanks and regards,
Bikram Majumdar
Sr Software Developer/DBA, Aqsacom Inc.
c. 1.972.365.3737

[cid:[email protected]]<http://www.aqsacom.com/>

Disclaimer. This electronic mail message contains CONFIDENTIAL information which is (a) CUSTOMER / SUPPLIER / PARTNER PRIVILEGED COMMUNICATION, WORK PRODUCT, PROPRIETARY IN NATURE, OR OTHERWISE PROTECTED BY LAW FROM DISCLOSURE, and (b) intended only for the use of the Addressee(s) named herein. If you are not an Addressee, or the person responsible for delivering this to an Addressee, you are hereby notified that reading, copying, or distributing this message is prohibited. If you have received this electronic mail message in error, please reply to the sender and take the steps necessary to delete the message completely from your computer system. Statement and opinions expressed in this e-mail are those of the sender, and do not necessarily reflect those of AQSACOM. AQSACOM accepts no liability for any damage caused by any virus/worms transmitted by this email.

From: Amar <[email protected]>
Sent: Monday, August 12, 2019 9:19 AM
To: Suresh Seema <[email protected]>
Cc: [email protected]
Subject: Re: Enable huge_pages in PostgreSQL server version 11.2

Try reducing shared_buffers to 6GB

On Mon, 12 Aug 2019 at 14:48, Suresh Seema <[email protected]<mailto:[email protected]>> wrote:
Hi Everyone,

We have installed PostgresSQL 11.2 version on Centos 7  and we want perform  PG benck mark with huge_pages with size of  2MB and 1GB.
We have modified required kernel parameters ,grub configuration and set the huge page count in OS Level .But Postgresql instance is not starting with   huge_pages = on parameters but working with  huge_pages = try. When we run test with huge_pages = try db parameter huge pages not getting utilized.

Please find PG instance configuration below :
-------------------------
max_connections = 200
shared_buffers = 60GB
effective_cache_size = 70GB
maintenance_work_mem = 1024MB
default_statistics_target = 500
random_page_cost = 1.2
effective_io_concurrency = 1000
work_mem = 256MB
max_worker_processes = 32
max_parallel_workers_per_gather = 16
max_parallel_workers = 32
enable_parallel_append = on
enable_parallel_hash = on
force_parallel_mode = off
min_parallel_index_scan_size = 64
min_parallel_table_scan_size = 512
parallel_leader_participation = off
parallel_setup_cost = 100
parallel_tuple_cost = 0.1
cpu_tuple_cost = 0.01
temp_buffers = 512MB
huge_pages = on                 # on, off, or try

Error:
# systemctl status postgresql
● postgresql.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2019-08-12 11:52:19 UTC; 10min ago
     Docs: https://www.postgresql.org/docs/11/static/
  Process: 6882 ExecStart=/usr/pgsql-11/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 6868 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 6882 (code=exited, status=1/FAILURE)

Aug 12 11:52:19 ip-10-1-4-103.ec2.internal postmaster[6882]: 2019-08-12 11:52:19.433 UTC [6882] LOG:  listening on IPv6 addres... 5432
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal postmaster[6882]: 2019-08-12 11:52:19.434 UTC [6882] LOG:  listening on Unix socket...5432"
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal postmaster[6882]: 2019-08-12 11:52:19.438 UTC [6882] LOG:  listening on Unix socket...5432"
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal postmaster[6882]: 2019-08-12 11:52:19.441 UTC [6882] FATAL:  could not map anonymou...emory
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal postmaster[6882]: 2019-08-12 11:52:19.441 UTC [6882] HINT:  This error usually means tha...
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal postmaster[6882]: 2019-08-12 11:52:19.442 UTC [6882] LOG:  database system is shut down
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal systemd[1]: postgresql.service: main process exited, code=exited, status=1/FAILURE
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal systemd[1]: Failed to start PostgreSQL 11 database server.
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal systemd[1]: Unit postgresql.service entered failed state.
Aug 12 11:52:19 ip-10-1-4-103.ec2.internal systemd[1]: postgresql.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

Please suggest me on this .


--
Regards,
Suresh Seema


--
~Amar