B&R v12 with PostgreSQL - connection pooling?

[eider]

Is it normal for B&R to not use any connection pooling when configuration database is connected to a PostgreSQL server? I have observed over 100 processes spawned (each process is single connection), which actually makes running PostgreSQL more resource intensive than MSSQL due to penalty inquired for context switches between concurrent queries on multiple connections. The actual % difference is nearing additional 7% on my virtual machine running B&R and I refuse to believe this is intended behavior.

It's also interesting to note that loading console increases number of connections but does not decrease it significantly after closing it, though it seems to be mostly first-run thing.

[HannesK]

Hello,
that's correct. We have connection pooling in our software "since always" and continued using the approach for Postgres. For V12 we decided against additional connection pooling on Postgres side, although that might happen in a future version. If you like to install pgBouncer to reduce connections, you can do that.

Best regards,
Hanes

[Gostev]

Actually, I remember seeing in dev reports that some intermediary V12 builds did not use connection pooling, instead opening and closing connections as needed, and that was really bad in terms of performance. So in the grand scheme of things, connection pooling is actually a good thing.

As Hannes mentioned, we also use it for SQL Server, which is of course much more polished than PostgreSQL in general so I'm not particularly surprised about slightly higher CPU load. Although it would be unfair not to highlight that we have previously received a completely opposite report. But perhaps this was due to light overall load of a tiny environment.

I would also add that there is SqlMaxConnectionPoolSize registry value allows to reduce the default value of 80 if needed. Be aware that in our testing this did not impact database load all that much whether it was set to a few hundred or a few dozen, however it was definitely impact overall Veeam performance in large environments if you go into either extreme.

[eider]

Sorry for the confusion but you both answered as if I asked why B&R uses connection pooling (which would be quite normal), but my actual question was why it seems not to use it.

It seems that you suggest that B&R indeed does use them, but with a quite high number. Unlike MSSQl, PostgreSQL spawns new process (not thread) for each connection, so 80 processes is a quite a lot in my opinion. Is B&R workload so SQL-heavy to warrant it? As far as I could glance it uses it purely as configuration database with basic metadata, with actual block metadata for .vbk and stuff being on disk (or object storage). Given that, I'd expect 80 connections to be a configuration designed for a setup with hundreds of concurrent jobs running at the same time and few console connections open.

I'll try lowering SqlMaxConnectionPoolSize value to see what effect it has. I'm not so much concerned about database load (this instance is mostly idling with less than 10 agents connected and jobs running once per day at maximum and spread across multiple hours during night) as it is with CPU time lost to context switches between 80+ of PostgreSQL processes.

[Gostev]

Yes, VBR workload is very heavy in large environments, especially depending on workloads you're protecting. For example, CDP and database transaction log backup processes run 24/7 and require constant database interaction to register newly created restore points. Also UI is very heavy on the database, with all of its views and real-time updates.

I believe the plan is to eventually make this connection pool dynamic depending on how actively a given environment uses the configuration database, but for now reducing the registry value is the way to go for tiny environments.

[eider]

Okay. Reducing connection pool size from 80 to 40 seem to bring PostgreSQL usage more in-line with MSSQL, however it still has its spikes which increase overall average usage by 2 to 3%, though nothing as dramatic as before. The spiky load is most likely more natural to PostgreSQL, and It seems that B&R does indeed maintain quite a few queries running 24/7 even with console closed (such as looking for jobs with specific status - shouldn't service know it already?). If not for these spikes though it would end up faring a little bit better than MSSQL.

I'll try to monitor it for a bit longer than 40 minutes but I'll most likely stick with MSSQL for the time being; I am actually amazed at the performance it presents in my tiny environment. Different workloads will present different results of course, so this might not be universal.

Incidentally, it seems that v12 improved on drilling down MSSQL quite a bit, if it was a contest between v11 MSSQL and v12 PostgreSQL then v12 would win.

[gschaffer]

Hello,
that's correct. We have connection pooling in our software "since always" and continued using the approach for Postgres. For V12 we decided against additional connection pooling on Postgres side, although that might happen in a future version. If you like to install pgBouncer to reduce connections, you can do that.

Best regards,
Hanes

Is it safe to use pgBouncer in transaction mode with Veeam VBR?

[gschaffer]

We have a dedicated postgres server running on linux for Veeam VBR. And in the VBR that connect to these postgres, we have only CDP jobs.
The Postgres server has 8vCPU and is a vsphere VM. Postgres is on xfs filesystem.
vCenter reports this VM is using 100%of CPU.
Inside VM, we have about of 30% of idle CPU but load in 12 or more.
iostat reposts 26% of disk utilization.
I tried to investigate why this behavior and found nothing.
Filesystem metadata is also not causing this high load. So I googled about Postgres Pooling found this forum. I believed that pgbouncer could help.
But with pgbouncer (session mode), the postgres server got load about 23.
I tested transaction mode, but Veeam Backup Server didn't even start.

[HannesK]

Hello,

Inside VM, we have about of 30% of idle CPU but load in 12 or more.

that sounds high depending on how many CDP policies / VMs / disks you replicate. The reason could be "IO wait". Did you run the Set-VBRPSQLDatabaseServerLimits? If yes, then I suggest contacting support to figure out why the load is so high. Please post the case number for reference.

But with pgbouncer (session mode), the postgres server got load about 23.

not a good sign for 8 CPUs

Best regards,
Hannes

[JaySt]

i've monitored the postgres.exe process count on a Veeam Cloud Connect VBR server (Windows) during the last week. Its specced with 4 cores and 12 GB RAM. I dont have that much tenants coming in on this environment (15 or so), maxing out at 2 sessions per tenants at any point in time. I was quite suprised on the number of processes spawned during peak hours (night)....

matching cpu usage:

[Gostev]

It is for this reason that migrating Veeam Cloud Connect server to PosgreSQL is explicitly not recommended at this time. We just did not have time to optimize VCC for PostgreSQL in time for V12 release.

[JaySt]

Any optimizations coming in the short term (coming months)? Or is more time/data needed? I can migrate back to mssql perhaps depending on how things are looking on Veeam side of things on finding optimzations.

[Gostev]

My gut feeling is, this is more of a V13 type of situation.

[JaySt]

Oh wow
I can throw some more compute resources at it off course, but next major is too far away to be in a poorly scalable situation in the mean time. I guess i’ll hit a certain point soon when i have to migrate back to mssql. Interesting to see how this goes, but somewhat disappointed as well tbh.