Обсуждение: max_worker_processer configuration for DWH databases?

Поиск
Список
Период
Сортировка

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.parametercurrent valueexpected Valuecurrent valueexpected Value
1shared_buffers547475416GB26944088GB
2effective_cache_size547475448GB269440824GB
3work_mem131072605kb1310721210KB
4maintenance_work_mem10639362GB5283842GB
5max_connections3466346634663466
6checkpoint_completion_target0.50.90.50.9
7wal_buffers204816MB204816MB
8default_statistics_target256500256500
9random_page_cost11.111.1
10effective_io_concurrency256200256200
11min_wal_size804GB804GB
12max_wal_size102416GB102416GB
13max_worker_processes8482
14max_parallel_workers_per_gather0201
15max_parallel_workers1412


 

 

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

Вложения

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 <dbatocloud17@gmail.com> 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.parametercurrent valueexpected Valuecurrent valueexpected Value
1shared_buffers547475416GB26944088GB
2effective_cache_size547475448GB269440824GB
3work_mem131072605kb1310721210KB
4maintenance_work_mem10639362GB5283842GB
5max_connections3466346634663466
6checkpoint_completion_target0.50.90.50.9
7wal_buffers204816MB204816MB
8default_statistics_target256500256500
9random_page_cost11.111.1
10effective_io_concurrency256200256200
11min_wal_size804GB804GB
12max_wal_size102416GB102416GB
13max_worker_processes8482
14max_parallel_workers_per_gather0201
15max_parallel_workers1412


 

 

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

Вложения

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: dbatocloud17@gmail.com



On Fri, Jan 22, 2021 at 7:46 PM Bk B <rbbalakumaran@gmail.com> 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 <dbatocloud17@gmail.com> 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.parametercurrent valueexpected Valuecurrent valueexpected Value
1shared_buffers547475416GB26944088GB
2effective_cache_size547475448GB269440824GB
3work_mem131072605kb1310721210KB
4maintenance_work_mem10639362GB5283842GB
5max_connections3466346634663466
6checkpoint_completion_target0.50.90.50.9
7wal_buffers204816MB204816MB
8default_statistics_target256500256500
9random_page_cost11.111.1
10effective_io_concurrency256200256200
11min_wal_size804GB804GB
12max_wal_size102416GB102416GB
13max_worker_processes8482
14max_parallel_workers_per_gather0201
15max_parallel_workers1412


 

 

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

Вложения