Read-only Replica setup problem

Dear All,
Setup:
We have configured 1 master and 3 read-only replica databasse in GCP CloudSQL (postgre) for our DHIS2 implementation. We have configure the read replicas in dhis.conf in all the application servers.

Problem:
However, based on our monitoring, most of the read queries are still directed toward the master database. This created high CPU usage only on our master, and almost no activities in the replicas, thus negating our objective to scale out the database.

Question:
Is there any other settings need to be configured in order to use the read replicas more effectively?

1 Like

Hey @Kresna_Adiprawira, welcome to the community! I hope that others will join the discussion; however, while asking for support, I got some info from @bobj and would like to quote his words:

It is a vexing issue. Many have tried and not succeeded. I believe the only successful implementation of replicated read only database is BAO. I have asked them before about whether they have any performance metrics from that. @Lars do you have any wisdom to add? (generally when people ask me about load balancing postgres I tell them not to do it. Would be great to be more constructive than that).

Hi guys,

We tried replication in South Africa using one master(bare-metal) and one read-only replica(VM) and we found the opposite.
All read queries to the database were being directed exclusively to the read-only replica even when the the master database server was idle. This caused high CPU usage on the read-only.

We’d hoped that the read-only would carry the bulk of the read-only queries and still dedicate a few to the master database server when the master is idle. Sadly this was not the case.

We are also very interested to see or hear what metrics BAO could come up with.

I hope this helps.