0

I am currently managing a 3-node PostgreSQL asynchronous streaming replication cluster setup, which includes one master node and two standby nodes, all sitting behind a pgpool load balancer. During high write transactions (approximately 90% write and 10% read), I'm encountering issues that cause my application to get stuck and generate errors.

Issue:

  • Scenario 1: When I direct both read and write traffic solely to the primary node, everything functions correctly, and the application runs smoothly.
  • Scenario 2: When I distribute the load across the standby nodes (writes to the primary, reads to the standbys), I start seeing a failure rate of about 3% to 5%, causing the application logs to get stuck with the following error:
java.lang.RuntimeException: org.postgresql.util.PSQLException: The connection attempt failed.
Caused by: org.postgresql.util.PSQLException: The connection attempt failed
20230402 184393.543 Master-slave integrity test hit SQL problem: Database NOT ok! org.postgresql.util.PSQLException I/O error occurred while sending to the backend.
WARNING: Failed to check connection: java.net.ConnectException: Connection refused(Connection refused)

  • Despite these issues, the CPU, RAM, and load average metrics on all database nodes appear normal.However, the load balancer logs consistently show the following error in both scenarios (primary-only and primary+standby):

    SQLUtilsMDS/SAS pid 6354333: ERROR: unable to read data from frontend.

Question:

What could be causing these connection issues when distributing the read load across the standby nodes? Could this be an application-side issue, or is there something I need to tweak in my pgpool configuration or PostgreSQL settings to handle the high write transaction load better?

Any insights or suggestions would be greatly appreciated.

Improve this question
4
  • i think this is more of a problem with pg-pool routing connections. How the pg-pool resource usage during this period?
    – goodfella
    Commented Aug 26 at 3:09
  • node resource is very log 2% CPU and load avg. like 5
    – Sheikh Wasiu Al Hasib
    Commented Aug 26 at 19:33
  • And this is not an issue with pgpool, pgpool working well.
    – Sheikh Wasiu Al Hasib
    Commented Aug 26 at 19:58
  • load avg 5 per core? Did you check I/O wait? As you mentioned in the post your workload is not high enough to saturate a single master server. Hence when you use standbys in addition it definitely not cause a problem in db cluster unless you having long running transactions or very slow replication n/w.
    – goodfella
    Commented Aug 27 at 4:18

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.