Обсуждение: max_worker_processer configuration for DWH databases?
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: dbatocloud17@gmail.com
Вложения
Hi AllI 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.
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: dbatocloud17@gmail.com
Вложения
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: dbatocloud17@gmail.com
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.ThanksOn Fri, Jan 22, 2021, 6:43 PM dbatoCloud Solution <dbatocloud17@gmail.com> wrote:Hi AllI 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.
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: dbatocloud17@gmail.com