Debugging slow connection open to PgCat

On a project I'm working on we are using PgCat as the PostgreSQL frontend. We chose it mainly based on gut feeling as pgbouncer seems a bit dated, although it would have arguably been the safe choice.

I was looking into the connection times using our tracing tool (Sentry) and noticed that establishing connections takes about 50ms.

That is a bit slow, right?

It was easy enough to confirm that it is indeed very slow. Establishing a direct connection to the mostly idle Postgres is in the sub-5ms range.

I quickly found a ticket about connection slowness, hinting that the problem could be related to TCP_NODELAY.

Essentially, it disables Nagle's algorithm, which batches small packets together. I guess that establishing connections from the client to PgCat is such a light process that the extra buffering is actively harmful.

And sure enough, after upgrading PgCat, we see sub 5ms connection times.

So why use PgCat at all? For us, it is for scaling purposes but not for load distribution. Our applications do not bombard the DB with a massive amount of queries but open long-lasting connections that might do only a few short ones. Pooling those together not only saves resources on the PG side but also enables us to sidestep the issue of maximum connections, which we want to keep low.

Comments

RocksDB data recovery

I recently needed to do some maintenance on a RocksDB key-value store. The task was simple enough, just delete some keys as the db served as a cache and did not contain any permanent data. I used the RocksDB cli administration tool ldb to erase the keys. After running a key scan with it, I got this error Failed: Corruption: Snappy not supported or corrupted Snappy compressed block contents So a damaged database. Fortunately, there's a tool to fix it, and after running it, I had access to the db via the admin tool. All the data was lost though. Adding and removing keys worked fine but all the old keys were gone. It turned out that the corrupted data was all the data there was. The recovery tool made a backup folder, and I recovered the data by taking the files from the backup folder and manually changing the CURRENT file to point to the old MANIFEST file which is apparently how RocksDB knows which sst (table) files to use. I could not access the data with the admin tool, ...

I'm not a passionate developer

A family friend of mine is an airlane pilot. A dream job for most, right? As a child, I certainly thought so. Now that I can have grown-up talks with him, I have discovered a more accurate description of his profession. He says that the truth about the job is that it is boring. To me, that is not that surprising. Airplanes are cool and all, but when you are in the middle of the Atlantic sitting next to the colleague you have been talking to past five years, how stimulating can that be? When he says the job is boring, it is not a bad kind of boring. It is a very specific boring. The "boring" you would want as a passenger. Uneventful. Yet, he loves his job. According to him, an experienced pilot is most pleased when each and every tiny thing in the flight plan - goes according to plan. Passengers in the cabin of an expert pilot sit in the comfort of not even noticing who is flying. As someone employed in a field where being boring is not exactly in high demand, this sounds pro...

Careful with externalTrafficPolicy

On a project I am working on is hosted in an EKS cluster with the NGINX ingress controller (the one maintained by Kubernetes). It is deployed using it's official official Helm chart, which I realized, after a lengthy debugging session, was a mistake. The initial setup I aimed to improve had several flaws. Firstly, we were using the AWS Classic Load Balancer in front of the nginx ingress in the cluster, which has been deprecated for some time (years?). Continuing to use it makes little sense to us. The second issue was that we were only running one(!) nginx pod, which is quite sketchy since the exposed web services had essentially no high availability. I switched to the Network Load Balancer (NLB), which was straightforward - I just needed to change the ingress-nginx service annotation to specify the load balancer type as NLB: service.beta.kubernetes.io/aws-load-balancer-type: nlb However, increasing the replica count turned out to be tricky. When I bumped it up to two, I began to ...

Koodihommia

Search This Blog