We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this. ps aux --sort=-%mem | head -30 USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND postgres 23438 41.0 6.2 78075200 8258220 ? Ssl Aug09 525:40 postgres: xxxxx: xxxxx(60028) idle postgres 4225 31.7 5.9 77797888 7903220 ? Ssl Aug09 438:58 postgres: xxxxx: xxxxx(45012) idle postgres 118999 39.3 4.8 76290020 6395136 ? Ssl Aug09 306:52 postgres: xxxxx: xxxxx(50026) idle free -g total used free shared buff/cache available Mem: 125 36 35 3 54 84 Swap: 19 0 19 System RAM : 128 GB CPU(s) : 32 Shared_buffer : 32 GB DB Size : 24 GB Max Connections : 200 Average Connections: 50 Effective_Cache_Size : 64 GB Work_mem : 41MB PG Version : 11 OS : Ubuntu A lot of the idle connections are getting reused despite it being closed from the app side. I have tuned the database by changing Shared_buffer, work_mem to values as per PostgreSQL expectations, but still getting these issues. PS : We have setup HAProxy between App and DB.

I have setup a master-slave replication and added a haproxy before them. #HA-Proxy version 1.4.24 global log /dev/log local0 log /dev/log local1 notice chroot /var/lib/haproxy user haproxy group haproxy daemon defaults log global mode http option httplog option dontlognull contimeout 5000 clitimeout 50000 srvtimeout 50000 errorfile 400 /etc/haproxy/errors/400.http errorfile 403 /etc/haproxy/errors/403.http errorfile 408 /etc/haproxy/errors/408.http errorfile 500 /etc/haproxy/errors/500.http errorfile 502 /etc/haproxy/errors/502.http errorfile 503 /etc/haproxy/errors/503.http errorfile 504 /etc/haproxy/errors/504.http frontend ft_readonly_mysql mode tcp timeout client 50000 option clitcpka bind *:3308 default_backend bk_readonly_mysql backend bk_readonly_mysql mode tcp option srvtcpka timeout server 50000 option mysql-check user haproxy_check server mysql1 127.0.0.1:3306 check server mysql2 172.16.19.250:3306 check If my pymysql client connect to mysql via 3306 directly, it works well. If connect to mysql via haproxy 3308 port, it will throw OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') every several minutes. I doubted it was due to haproxy config so that I have added clitcpka, srvtcpka, timeout client, timeout server. But it still throws Lost connection. Update 0: mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+

We have implemented master-master setup in maria-db. Master A is being used for read\write and Master B is used for backups and MIS operations. We have built this setup so that we do not need to promote slave in case of failure. Now we want to implement HA PROXY for automatic fail over. In case Master A goes down application should switch automatically to Master B. Now my question is that if Master A goes down and applications start serving from Master B. But when Master A comes online and is not in sync with Master B what will HA PROXY do, will applications get connected to Master A instantly or HA Proxy will check synch status and let Master A synch up with Master B and then allow applications to connect with Master A. What parameters need to be added in order to achieve this. HA PROXY Configuration:

I have PostgreSQL 15.2 from the Percona repo, along with Patroni 3.0.1 as management tool, HAProxy 2.5.11 for proxying and PGBouncer 1.18.0 as server side pool and keepalived 2.1. for the virtual IP. I have 3 nodes, I will use these IPs for the sake of this post: 10.0.1.1 is the primary, node1 10.0.1.2 is node2 10.0.1.3 is node3 HAProxy is set up to do a health check every 3 seconds:

listen primary
    bind *:9999
    option httpchk /primary
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 10.1.0.1:6432 check port 8008
    server node2 10.1.0.2:6432 check port 8008
    server node3 10.1.0.3:6432 check port 8008

6432 is the PGBouncer port which will actually receive the SQL traffic, but health check is done over http on port 8008 with Patroni. This causes Patroni to each time make a TCP ping to each PostgreSQL node, resulting in the following very ugly PostgreSQL logs every 3 seconds on every node:

2024-11-26 20:45:04.200 CET - Process=, Application=[[unknown]], User=[[unknown]]@[10.0.1.1(36024)]/[[unknown]], SessionID=[67462540.4d387], Session start=[2024-11-26 20:45:04 CET], Command tag=[]: LOG:  could not receive data from client: Connection reset by peer
2024-11-26 20:45:05.152 CET - Process=, Application=[[unknown]], User=[[unknown]]@[10.0.1.2(48138)]/[[unknown]], SessionID=[67462541.4d391], Session start=[2024-11-26 20:45:05 CET], Command tag=[]: LOG:  could not receive data from client: Connection reset by peer
2024-11-26 20:45:06.202 CET - Process=, Application=[[unknown]], User=[[unknown]]@[10.0.1.3(51212)]/[[unknown]], SessionID=[67462542.4d39d], Session start=[2024-11-26 20:45:06 CET], Command tag=[]: LOG:  could not receive data from client: Connection reset by peer

On a network level it is a simple 3-way TCP handshake, closed immediately: I didn't find any way to hide this in the PostgreSQL log. Any idea? It is unnecessary and misleading log message.

I have a server with two instances of PG Bouncer running on different ports. Both are using session mode to pool. I set up HA Proxy to load balance incoming connections between these two processes to distribute the load. I get "server closed the connection unexpectedly" intermittently when connecting through HA Proxy. If I connect directly to one of the PG Bouncer instances I do not receive these errors. Has anyone setup something similar to this?

I am currently trying to deploy Load Balancing using HAProxy for my PostgreSQL cluster (used Patroni HA solution). Now, I have been able to successfully implement the setup and Load Balancing is working to an extent. My HAProxy configuration : global maxconn 100 defaults log global mode tcp retries 2 timeout client 30m timeout connect 4s timeout server 30m timeout check 5s listen stats mode http bind *:7000 stats enable stats uri / listen read_write bind *:5004 option httpchk OPTIONS /master http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 IP:5432 maxconn 100 check port 8008 server pg2 IP:5432 maxconn 100 check port 8009 listen read_only bind *:5005 mode tcp balance roundrobin http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pg1 IP:5432 maxconn 100 check port 8008 server pg2 IP:5432 maxconn 100 check port 8009 However, my question is how will the application interact with HAProxy for read only requests. Does the application need to connect using port 5005 or is there any internal logic applied by HAProxy to do this. This is my first time implementing a Load Balancing setup, hence would appreciate your patience. :)

I have created a mongod replica cluster of 2 nodes , mongo1 and mongo2. to load balance I have added following rule in my haproxy listen mongo_replica_cluster bind *:27017 mode tcp balance roundrobin server mongo1 10.2.0.12:27017 check inter 10s fall 3 rise 99999999 server mongo2 10.2.0.11:27017 check backup but if the mongo1 goes down mongo2 becomes the primary but even if mongo1 comes up mongo2 remains primary but ha redirect all traffic to mongo1 which is slave now. I want to redirect all my traffic to only the primary node.

We have 3 nodes of MariaDB Galera Cluster runing with 2 HAproxy for loadbalancing. Recently, all 3 nodes of MariaDB crashed . I noticed that CPU was really high like 99% or 100% on the 3 nodes before the crash. Is it possible that all nodes crashed because of high utilisation of CPU ? Who has already seen a similar case before please ? Thank you.

I'm trying to get a solid MySQL group replication load balancing / failover set-up. Currently I use keepalived to share one private IP to connect to the loadbalanced MySQL GROUP REPLICATION cluster, balanced/failover arranged through haproxy (tcp checks on port 33061) which works great. However, once a node get's in an unreachable state (because of network issues) and eventually goes offline for the cluster we will have to join the node back to the cluster, which is all fine and works. However, during the recovery phase (state RECOVERING), the node is already listening on port 33061, enabling the node for loadbalancing and failover. However, the cluster is not operational yet. Is there any check I can add to prevent the node from being online while the node is still in RECOVERING state, joining the cluster? Usually this process is rather quick, but it also happend a few times that it may take up to 15 minutes, causing database errors during this phase. Many thanks!

I have a HAProxy + Patroni setup with split reads and writes. The problem is, when both my replicas go down, only my writes are supported, the reads stop because both replicas are down. How is this problem generally handled in this setup? Is there a way to have HAProxy direct the read traffic to the master in case no read servers are available?

We have an S1M2->S2 setup of MySQL replicated nodes. These are now to be brought to the back-end of an HA Proxy server to split read from writes. We also intend to achieve automatic fail-over with this. However, write requests to be routed to M2 only when M1 fails. In usual scenario, we will be good with not touching M2 at all. There seems to be no "balance" option in HA Proxy that switches to M2 only when M1 fails. Please suggest how this can be achieved using HA Proxy. Writing round-robin across M1 and M2 is a time consuming solution to be taken up at this point in time.

I am building a mysql cluster on local network. I have 3 nodes and would like to load balance them using HAProxy. It works fine when I place HAProxy on one of my nodes inside of the network. I bind its Ip (lets say 192.168.0.1) to the ips of the sql nodes (xxx.xxx.x.2 ,...3,...4). My question is: how do I bind HAProxy to my nodes if i want to place it outside of my local network, lets say, on AWS micro instance? I want to direct mysql pulls through aws to my 3 nodes that i have at home. I have already forwarded port 3306 and can access it from outside no problem. But I only have one public Ip. How can I bind haproxy's ip to my public ip so that it connects to all 3 nodes. That is the part I cant wrap my head around. Thank you for help in advance.

I am building an architechture consisting MySQL Master-Master replication with HAProxy in its front. For HAProxy i am using round_robin algorithm to distribute loads between two masters. My question is when there is a heavy write situation as Seconds_Behind_Master will be greater than 0, what should I do for the data inconsistency will arise when HAProxy will go divert to an unupdated Master?

I'm looking to deploy a MariaDB galera cluster across three servers. However, I've noticed that the documentation often references HAProxy or the like. Does this not cause a single point of failure situation? I guess the question I'm asking is - Do I need any sort of load-balancing software to use a MariaDB cluster? Or could I have support for the cluster built-in to the app? Apologies for the English.

I'm trying to load balance between servers running Postgresql (Master-Slave configuration with Streaming Replication). First I've tried setting up PgPool-II, and it worked fine when I connected some app we're using here, but we use Tableau (some tool for reporting) and Tableau can't connect correctly, so now I'm searching for alternatives to load balance, and I've read in many places that HAProxy is an good alternative to load balance, but can't find a good guide to configure it to function with Postgres. Following some articles I think I managed to configure something very basic, I managed to start HAProxy running in port 10001, but when I try to connect via psql, I get the error: psql: The server has closed the connection unexpectedly (I translated it from Spanish, so may not be the exact message in English). I get that error immediately, so I'm not sure if it's something in the server running HAProxy or in the server running Postgresql. At the moment, for testing, I'm running HAProxy in one server and configured to only connect to one server. My configuration in the HAProxy server is like this: global log 127.0.0.1 local2 maxconn 4000 defaults mode tcp log global option dontlognull retries 3 timeout connect 1m timeout client 1m timeout server 1m maxconn 3000 listen psql_cluster :10001 mode tcp server psql1 [ip_server_postgres]:5432 Both servers are running Centos 7, I'm running HAProxy 1.5.15 and Postgresql 9.4. For the record, the communication between the server HAProxy and the master with Postgres works fine with user Postgres... PS: While writing this, I thought that may be because the new user haproxy can't connect to the master server? Should I be able to ssh from one to another?. If I try "su haproxy" it says "This account is currently not available". Maybe is one of this things? **EDIT** I found in the logs of the HAProxy Server that indeed my connection is getting received by the server: psql_cluster postgres/psql1 1/-1/0 0 SC 0/0/0/0/3 0/0

Initially I have two database server (Master Master Replication config with Haproxy load balancing) A and B. And two application server say P and Q. Both P and Q is pointed towards A will move to B in case of failure. OLTP operations are huge in numbers per minute. Now we are adding two more app servers R and S. Just want to know experts opinion. Is it ok to point both R and S towards A? Or should I use B this time. Please suggest.