10

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).

Manivannan Thirugnanam
  • 827
  • 12
  • 18

1 Answers1

15

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

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 4
    I advise leaving the existing format and update the `32768` by `54613` (100/15*8*1024). It will be 15% even if you up or downgrade the instance class – Antoine Lizée Sep 21 '20 at 08:05
  • 1
    Forgive my ignorance here, but wouldn't changing {DBInstanceClassMemory/32768} to {DBInstanceClassMemory/54613} change the percentage of cache memory available from 25% to 15% and therefore reduce database performance? Does the OP realize they're shrinking the cache? – sage88 Aug 19 '22 at 20:10