shared_buffers and effective_cache_size not tuned for machine

[mthaddon]

Steps to reproduce

1. Deploy the postgresql machine charm (in our case revision 318 from 14/edge)

Expected behavior

Some tuning is done to PostgreSQL based on machine constraints.

Actual behavior

The PostgreSQL defaults are used.

root@juju-9431ad-prod-events-db-7:/var/snap/charmed-postgresql/common/var/lib/postgresql# grep -r shared_buffers *
postgresql.base.conf:shared_buffers = 128MB                     # min 128kB
postgresql.base.conf:#wal_buffers = -1                  # min 32kB, -1 sets based on shared_buffers
postgresql.base.conf.backup:shared_buffers = 128MB                      # min 128kB
postgresql.base.conf.backup:#wal_buffers = -1                   # min 32kB, -1 sets based on shared_buffers
root@juju-9431ad-prod-events-db-7:/var/snap/charmed-postgresql/common/var/lib/postgresql# grep -r effective_cache_size *
postgresql.base.conf:#effective_cache_size = 4GB
postgresql.base.conf.backup:#effective_cache_size = 4GB
root@juju-9431ad-prod-events-db-7:/var/snap/charmed-postgresql/common/var/lib/postgresql# free -m
               total        used        free      shared  buff/cache   available
Mem:            7937         369        4635          89        2933        7172
Swap:              0           0           0

Per https://www.postgresql.org/docs/14/runtime-config-resource.html "If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even larger settings for shared_buffers are effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Larger settings for shared_buffers usually require a corresponding increase in max_wal_size, in order to spread out the process of writing large quantities of new or changed data over a longer period of time."

I'm not a PostgreSQL expert, and I'm not saying these are the only options that matter, obviously all tuning options will need to be considered.

Versions

Operating system: jammy

Juju CLI: 2.9.44

Juju agent: 2.9.44

Charm revision: 318

LXD: N/A, this is on OpenStack.

Log output

N/A

Additional context

N/A

[github-actions]

https://warthogs.atlassian.net/browse/DPE-2494

[taurus-forever]

The latest charm (revision 330+) mimics the legacy charm behavior in the default profile production.
Documented:

• https://charmhub.io/postgresql/docs/r-profiles
• https://charmhub.io/postgresql-k8s/docs/r-profiles

Please check it from your side using:
juju deploy postgresql --config profile=testing --channel 14/beta

@paulomach please consider to resolve this issue and Jira ticket. Tnx!

[paulomach]

Done on PR #231