Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Resize Shared buffer size in Postgresql hosted in AWS RDS

We are facing performance issue on Production Postgresql Database server which is hosted in AWS RDS server. So that we installed postgresql in EC2 instance Linux server for pre-production and tried with shared buffer value 15% of RAM value we get some positive response.

So that, Definitely we can assign shared buffer value 15% to 30 % of RAM value. But when i trying to resize shared buffer value in Production DB server which i hosted in AWS RDS. it says invalid parameter value. By default following value is assigned for shared buffer parameter. shared buffer = {DBInstanceClassMemory/32768}
Please help me to resize shared buffer value is min 15 % of RAM memory. My Instance specification is : 2 CPU, 7.5 GB RAM (db.m3.large).

like image 647
Manivannan Thirugnanam Avatar asked Feb 27 '17 05:02

Manivannan Thirugnanam


People also ask

How can I increase the number of shared_buffers in PostgreSQL?

This is because Aurora PostgreSQL eliminates double buffering and doesn't utilize file system cache. As a result, Aurora PostgreSQL can increase shared_buffers to improve performance. It's a best practice to use the default value of 75% for the shared_buffers DB parameter when using Aurora PostgreSQL.

Is PostgreSQL running in AWS RDS with performance issues?

We are facing performance issue on Production Postgresql Database server which is hosted in AWS RDS server. So that we installed postgresql in EC2 instance Linux server for pre-production and tried with shared buffer value 15% of RAM value we get some positive response.

How much RAM should be allocated to shared_buffers in RDS?

As a result, allocating more than 40% of RAM to shared_buffers is unlikely to perform better than a smaller value. For RDS DB instances, the default value of the DB parameter group is set to 25% of total memory.

What is the recommended default value for the shared_buffers DB parameter?

It's a best practice to use the default value of 75% for the shared_buffers DB parameter when using Aurora PostgreSQL. A smaller value can degrade performance by reducing the available memory to the data pages while also increasing I/O on the Aurora storage subsystem. For more information, see Amazon Aurora PostgreSQL Reference.


1 Answers

If you want to set shared_buffers to 15% of RAM, put 147456 as value instead of {DBInstanceClassMemory/32768}...

shared buffers is set as number of 8kB blocks => calculating is such:

postgres=> select (15*7.5*1024*1024 /100)/8;
      ?column?
---------------------
 147456.000000000000
(1 row)

where:

  • 15 is 15%
  • 7.5 is 7.5 GB of RAM
  • 1024*1024 to convert GB to KB (to unify against shared_buffers units
  • 8 is 8kB

then you have to reboot your RDS instance and check value with:

show shared_buffers; in psql

like image 113
Vao Tsun Avatar answered Nov 15 '22 06:11

Vao Tsun