Re: Enable huge_pages in PostgreSQL server version 11.2

it's a thread on the mailing list

On Tue, Aug 13, 2019 at 10:48 AM John Nieblas <johnnieblas(at)hotmail(dot)com>
wrote:

> I am not Amar.
>
> John Nieblas
> 951.545.4885
>
> ------------------------------
> *From:* Bikram MAJUMDAR <Bikram(dot)Majumdar(at)aqsacom(dot)com>
> *Sent:* Monday, August 12, 2019 7:26:55 AM
> *To:* Amar <amarno1(at)gmail(dot)com>; Suresh Seema <suresh(dot)seema(at)opsveda(dot)com>
> *Cc:* pgsql-admin(at)postgresql(dot)org <pgsql-admin(at)postgresql(dot)org>
> *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
>
>
>
> <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 <amarno1(at)gmail(dot)com>
> *Sent:* Monday, August 12, 2019 9:19 AM
> *To:* Suresh Seema <suresh(dot)seema(at)opsveda(dot)com>
> *Cc:* pgsql-admin(at)postgresql(dot)org
> *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 <suresh(dot)seema(at)opsveda(dot)com>
> 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
>