Enable huge_pages in PostgreSQL server version 11.2

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Enable huge_pages in PostgreSQL server version 11.2

Suresh Seema
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
Reply | Threaded
Open this post in threaded view
|

Re: Enable huge_pages in PostgreSQL server version 11.2

Amar
Try reducing shared_buffers to 6GB

On Mon, 12 Aug 2019 at 14:48, Suresh Seema <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

RE: Enable huge_pages in PostgreSQL server version 11.2

Bikram MAJUMDAR

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

 

 

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 <[hidden email]>
Sent: Monday, August 12, 2019 9:19 AM
To: Suresh Seema <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Enable huge_pages in PostgreSQL server version 11.2

John Nieblas
I am not Amar.

John Nieblas
951.545.4885


From: Bikram MAJUMDAR <[hidden email]>
Sent: Monday, August 12, 2019 7:26:55 AM
To: Amar <[hidden email]>; Suresh Seema <[hidden email]>
Cc: [hidden email] <[hidden email]>
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

 

 

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 <[hidden email]>
Sent: Monday, August 12, 2019 9:19 AM
To: Suresh Seema <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Enable huge_pages in PostgreSQL server version 11.2

Avin Kavish
it's a thread on the mailing list

On Tue, Aug 13, 2019 at 10:48 AM John Nieblas <[hidden email]> wrote:
I am not Amar.

John Nieblas
951.545.4885


From: Bikram MAJUMDAR <[hidden email]>
Sent: Monday, August 12, 2019 7:26:55 AM
To: Amar <[hidden email]>; Suresh Seema <[hidden email]>
Cc: [hidden email] <[hidden email]>
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

 

 

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 <[hidden email]>
Sent: Monday, August 12, 2019 9:19 AM
To: Suresh Seema <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Enable huge_pages in PostgreSQL server version 11.2

Purav Chovatia
In reply to this post by Suresh Seema
You havent mentioned what is the amount of huge pages that you have configured. Make sure that is slightly higher than the shared_buffer value. We usually set shared_buffer to 8GB and set the huge pages to 5120 (i.e. 5120 pages of 2mb so a total of 10gb). Setting it exactly to 4096 does not work.


On Mon, 12 Aug 2019 at 19:18, Suresh Seema <[hidden email]> 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