PostgreSQL

31 Dec 2024 17:15

17
I am using pgbouncer in transaction mode & trying to allow close to 500 active transaction. The purpose is simply to stress test the setup
Current setup: [ 'n' clients --->1 pgbouncer ----> 1 postgres ]
I notice that my transaction/second (tps) decreases considerably when I use pgbouncer instead of a direct connection to postgres.
For the same set to transaction (via pgbench)
Direct connections => 10k (tps) appx
pgbouncer connection => 3k (tps) appx
Is there any configuration in pgbouncer that needs to be tuned to allow a better performance?
I understand pgbouncer is a single threaded application, but would like to tune it till its best. Following is my pgbouncer configuration:

PostgreSQL

30 Dec 2024 21:52

I didn't think so. Very weird situation where a lot of backends waiting on IO:DataFileRead correlates precisely with a lot of read I/O from the WAL volume.

PostgreSQL

30 Dec 2024 21:31

I believe you! It just puzzles me why...

PostgreSQL

30 Dec 2024 19:57

Impressive. Never heard of anyone getting better performance out of pgpool, but then again I've never looked back at that can of worms anyway 😏

PostgreSQL

30 Dec 2024 19:51

Oh, you're actually planning to use the built-in loadbalancing? Report on your TPS once you're done please (seriously interested)!

PostgreSQL

30 Dec 2024 19:47

What your opinion on my design?

PostgreSQL

30 Dec 2024 19:43

The picture is using "master/slave" terminology.

PostgreSQL

30 Dec 2024 19:35

Pgbouncer only connection pooler not load balancer

PostgreSQL

30 Dec 2024 19:32

And Primary and Replica ...

PostgreSQL

30 Dec 2024 19:26

I’m currently working on a PostgreSQL setup with the following design, and I’d love to hear your opinions and suggestions:

PostgreSQL

30 Dec 2024 17:41

Share it somewhere... https://imgbb.com/ or whatever

PostgreSQL

30 Dec 2024 16:58

I’m currently working on a PostgreSQL setup with the following design, and I’d love to hear your opinions and suggestions:

PostgreSQL

30 Dec 2024 09:30

If you shoot for the best, contact Markus Winand: https://use-the-index-luke.com/

PostgreSQL

30 Dec 2024 08:01

Hello and welcome to this chat. Here we discuss PostgreSQL and provide best-effort voluntary support on Postgresql issues.
If you have any question, try asking kindly, including log files, error descriptions and perhaps what you're trying to do.

- 🚫 photos and screenshots
- 🚫 spam and offtopic
- ✅ questions with log files and clear explanation of what's gone wrong

⬇️ Press the button down here ⬇️

PostgreSQL

28 Dec 2024 15:39

In witness server only install etcd without pg right ?

PostgreSQL

31 Dec 2024 00:25

Could it be Drive Saturation?

It's perfectly logical. When IO goes up (especially writes).
WAL IO goes up.

And Read IO goes up. (you have to read it before you write it (to copy it).
You have to read another block if this block is full. THEN you get to write).

Meanwhile, you have to write to WAL.

Are you seeing locks/waits?

PostgreSQL

30 Dec 2024 21:49

Do backends ever *read* from the WAL? The walsender does, of course, but do general backends ever?

PostgreSQL

30 Dec 2024 20:22

I can take a picture for tps 🥲🥲

PostgreSQL

30 Dec 2024 19:54

With pgpool load- balancer my hardware server are 16 core CPU , 64 ram , 1TB ssd , with pgpool load-balancer read are 107000 tps abd write 21000tps using pgbench for 100 client concurntly

PostgreSQL

30 Dec 2024 19:50

RO and RW from the applications should be clearly separated (e.g. by access IP).

All the unnatural intelligence in an attempt to divide it by statements is a complete and utter crap.

Also, there should be some repmgr(s) at application side. Probably those third witness should go exactly there (there is not a much of a use of the working primary server that doesn't have connectivity with the apps).

(Also, all those autofailovers, rafts and other "HA" stuff is a kind of ha-ha-ha on all the initial attempts to build postgres server setup.

You'd have to build a good monitoring, a good performance test stand, a good DBA team with understanding of long transactions/performance trashing/wal gathering/vacuum inefficiency problems and 24x7 response personnel to make your availability levels depend on network and hardware failures.
Before that... Well, autofailovers would not be able to increase availability. But, in fact, flapping would probably decrease it...

However, if you feel better with it — why not, go on. Not a much damage).

PostgreSQL

30 Dec 2024 19:46

https://www.postgresql.org/message-id/20170731211348.1449.52678%40wrigleys.postgresql.org

PostgreSQL

30 Dec 2024 19:36

I don't understand you

PostgreSQL

30 Dec 2024 19:34

I'm using pgbouncer but I have problem when use pgbouncer tps are 35K and without pgbouncer are 107k for read using pgbench

PostgreSQL

30 Dec 2024 19:31

Looks reasonable and pretty much a standard setup. I'd personally not use PGPool but pgbouncer. But that's my personal preference.

PostgreSQL

30 Dec 2024 19:26

https://ibb.co/k8483HX

PostgreSQL

30 Dec 2024 17:10

I want to send design picture

PostgreSQL

30 Dec 2024 16:16

How I can send pictures on this community

PostgreSQL

30 Dec 2024 08:51

Is there any sql trainer available for training .?

PostgreSQL

28 Dec 2024 21:58

Yes, check configuration file for option

PostgreSQL

28 Dec 2024 15:09

Hi, I have PG master and slave and I want to install patroni. Do I need 1 server for witness ?