max_worker_processer configuration for DWH databases?

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

max_worker_processer configuration for DWH databases?

dbatoCloud Solution
Hi All
I need to configure the psql parameter for the Dataware Warehousing Database. I have AWS Aurora PostgreSQL 10.11 with 2 nodes (1-RW,1R) databases.
Here the problem here in production node ( 64GB RAM/8vCPU) and standby node side ( 32GB RAM/4vCPU).

But as per the postgreSQL nodes says that max_worker_processes should be same as prod but my standby node configuration lessar than prod.

Please let me know if I can use the same value or different.

image.png

    production   standby  
    db.r5.2xlarge(64GB RAM/8vCPU) db.x5.xlarge (32GB RAM/4vCPU)
S.No. parameter current value expected Value current value expected Value
1 shared_buffers 5474754 16GB 2694408 8GB
2 effective_cache_size 5474754 48GB 2694408 24GB
3 work_mem 131072 605kb 131072 1210KB
4 maintenance_work_mem 1063936 2GB 528384 2GB
5 max_connections 3466 3466 3466 3466
6 checkpoint_completion_target 0.5 0.9 0.5 0.9
7 wal_buffers 2048 16MB 2048 16MB
8 default_statistics_target 256 500 256 500
9 random_page_cost 1 1.1 1 1.1
10 effective_io_concurrency 256 200 256 200
11 min_wal_size 80 4GB 80 4GB
12 max_wal_size 1024 16GB 1024 16GB
13 max_worker_processes 8 4 8 2
14 max_parallel_workers_per_gather 0 2 0 1
15 max_parallel_workers 1 4 1 2


 

 

Thanks & Best Wishes,

Ashok

 

---------------------------------------------------------------------------------------------------------------------

Ashokkumar Mani  (OCP12c/11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922

W: https://dbatocloudsolution.blogspot.in/ | E: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: max_worker_processer configuration for DWH databases?

Bk B
When you create an instance in RDS, you should keep the writer and reader instances with same instance type. Because, when the writer instance getting any issues or oom, RDS does automatic failover. In your case, your current reader instance will become writer. It would lead to inevitable spike or downtime.

Thanks

On Fri, Jan 22, 2021, 6:43 PM dbatoCloud Solution <[hidden email]> wrote:
Hi All
I need to configure the psql parameter for the Dataware Warehousing Database. I have AWS Aurora PostgreSQL 10.11 with 2 nodes (1-RW,1R) databases.
Here the problem here in production node ( 64GB RAM/8vCPU) and standby node side ( 32GB RAM/4vCPU).

But as per the postgreSQL nodes says that max_worker_processes should be same as prod but my standby node configuration lessar than prod.

Please let me know if I can use the same value or different.

image.png

    production   standby  
    db.r5.2xlarge(64GB RAM/8vCPU) db.x5.xlarge (32GB RAM/4vCPU)
S.No. parameter current value expected Value current value expected Value
1 shared_buffers 5474754 16GB 2694408 8GB
2 effective_cache_size 5474754 48GB 2694408 24GB
3 work_mem 131072 605kb 131072 1210KB
4 maintenance_work_mem 1063936 2GB 528384 2GB
5 max_connections 3466 3466 3466 3466
6 checkpoint_completion_target 0.5 0.9 0.5 0.9
7 wal_buffers 2048 16MB 2048 16MB
8 default_statistics_target 256 500 256 500
9 random_page_cost 1 1.1 1 1.1
10 effective_io_concurrency 256 200 256 200
11 min_wal_size 80 4GB 80 4GB
12 max_wal_size 1024 16GB 1024 16GB
13 max_worker_processes 8 4 8 2
14 max_parallel_workers_per_gather 0 2 0 1
15 max_parallel_workers 1 4 1 2


 

 

Thanks & Best Wishes,

Ashok

 

---------------------------------------------------------------------------------------------------------------------

Ashokkumar Mani  (OCP12c/11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922

W: https://dbatocloudsolution.blogspot.in/ | E: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: max_worker_processer configuration for DWH databases?

dbatoCloud Solution
Hi RB, 

Thanks. 
 

Thanks & Best Wishes,

Ashok

 

---------------------------------------------------------------------------------------------------------------------

Ashokkumar Mani  (OCP12c/11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922

W: https://dbatocloudsolution.blogspot.in/ | E: [hidden email]



On Fri, Jan 22, 2021 at 7:46 PM Bk B <[hidden email]> wrote:
When you create an instance in RDS, you should keep the writer and reader instances with same instance type. Because, when the writer instance getting any issues or oom, RDS does automatic failover. In your case, your current reader instance will become writer. It would lead to inevitable spike or downtime.

Thanks

On Fri, Jan 22, 2021, 6:43 PM dbatoCloud Solution <[hidden email]> wrote:
Hi All
I need to configure the psql parameter for the Dataware Warehousing Database. I have AWS Aurora PostgreSQL 10.11 with 2 nodes (1-RW,1R) databases.
Here the problem here in production node ( 64GB RAM/8vCPU) and standby node side ( 32GB RAM/4vCPU).

But as per the postgreSQL nodes says that max_worker_processes should be same as prod but my standby node configuration lessar than prod.

Please let me know if I can use the same value or different.

image.png

    production   standby  
    db.r5.2xlarge(64GB RAM/8vCPU) db.x5.xlarge (32GB RAM/4vCPU)
S.No. parameter current value expected Value current value expected Value
1 shared_buffers 5474754 16GB 2694408 8GB
2 effective_cache_size 5474754 48GB 2694408 24GB
3 work_mem 131072 605kb 131072 1210KB
4 maintenance_work_mem 1063936 2GB 528384 2GB
5 max_connections 3466 3466 3466 3466
6 checkpoint_completion_target 0.5 0.9 0.5 0.9
7 wal_buffers 2048 16MB 2048 16MB
8 default_statistics_target 256 500 256 500
9 random_page_cost 1 1.1 1 1.1
10 effective_io_concurrency 256 200 256 200
11 min_wal_size 80 4GB 80 4GB
12 max_wal_size 1024 16GB 1024 16GB
13 max_worker_processes 8 4 8 2
14 max_parallel_workers_per_gather 0 2 0 1
15 max_parallel_workers 1 4 1 2


 

 

Thanks & Best Wishes,

Ashok

 

---------------------------------------------------------------------------------------------------------------------

Ashokkumar Mani  (OCP12c/11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922

W: https://dbatocloudsolution.blogspot.in/ | E: [hidden email]