psycopg 3.2 causing SEVERE performance degredation

[davidgrohmann]

We recently pulled in psycopg3.2 through a regular pipenv upgrade cycle and it has killed performance across a few apps. Unfortunately I don't have any exact measurements to point to, but something has changed, and reverting to 3.1.x solved the problem.

I don't know if this is the queries themselves returning slower or if it is establishing a connection or what.

I'm posting this here as a warning and hopefully if others see same problem and can post more details.

I apologize for not bringing any evidence

[dvarrazzo]

Compared to which version?

[jameschristopher]

This may or may not be related but I've noticed a new error connection timeout expired in pure python psycopg >3.1.19. This occurs when running under gevent. Pinning to ==3.1.19 does not cause this error. In my environment it's connecting to pgbouncer so I doubt it's a problem on that end. Thank you @dvarrazzo for your continue work on this project.

[mtvx]

I'm witnessing similar(ish) behavior bumping 3.1 -> 3.2, seeing a bunch of randomish connection timeout expired.

The problem should be along the lines

psycopg/psycopg/psycopg/connection.py

Lines 101 to 103 in 00c6625

	conninfo = make_conninfo("", **attempt)
	gen = cls._connect_gen(conninfo, timeout=timeout)
	rv = waiting.wait_conn(gen, interval=_WAIT_INTERVAL)

which got refactored in #766.

I added timing information to the lines before and after the connection attempts, and the code got into

psycopg/psycopg/psycopg/connection.py

Lines 118 to 119 in 00c6625

	assert last_ex
	raise last_ex.with_traceback(None)

without actually waiting for the given connect_timeout. For example, with 10s given as connect timeout, the code got into raising state in just some milliseconds.

Didn't go deeper as I'm not really familiar with that code.

Anyway, something flawed in the new waiting logic?

(Running psycopg[c], if that matters.)

[jameschristopher]

That's interesting, I have also found it to be random. I thought maybe their was truly some kind of constraint on my pgBouncer instance, but it is managed by Heroku, so that seemed unlikely. And it wasn't during any kind of high load either.

[jameschristopher]

As a follow up, after more analysis I determined that the connection timeout expired errors were occurring when my app was over my Heroku dyno's memory soft limit. I was able to solve the problem by increasing my Heroku dyno size to get more memory. Not sure what the underlying mechanics are of this, but incase anyone stumbles across this issue I want them to have a solution, such as it is.

[jhrr]

I've found a similar extreme performance regression but it doesn't seem to be from simply moving between 3.1 -> 3.2.

I was seeing very slow response times (with a Django app) using Python 3.11 and psycopg==3.2.3. But then I turned on pooling with psycopg-pool==3.2.4 and this fixed everything. Things were very snappy.

Then I upgraded everything to Python 3.12 and even with pooling the performance was even slower than I was experiencing in the first situation.

I've downgraded to Python 3.11 with psycopg==3.2.3, psycopg-binary==3.2.3, psycopg-pool==3.2.4 and performance is good again.

[dvarrazzo]

@jhrr I am afraid your report is not very useful. You don't specify on which platform it happens, for instance. Apart from that, on which operation is the slower version slower?

So far, "performance degradation" has meant "I have a misconfigured macOS and GSSAPI wastes time on connection". I haven't seen anything measurable so far.

Which operation is slower? Connection? Querying? Can you make a repro?

So far I have left this ticket open for the benefit of the doubt, but I need to see a shred of evidence, otherwise I'll have to close it.

[jhrr]

@jhrr I am afraid your report is not very useful. You don't specify on which platform it happens, for instance. Apart from that, on which operation is the slower version slower?

So far, "performance degradation" has meant "I have a misconfigured macOS and GSSAPI wastes time on connection". I haven't seen anything measurable so far.

Which operation is slower? Connection? Querying? Can you make a repro?

So far I have left this ticket open for the benefit of the doubt, but I need to see a shred of evidence, otherwise I'll have to close it.

Yep, sure thing, understood.

I will try and make a more accurate and isolated reproduction when I get a chance to do that. Currently sprinting on something and I've been able to just revert to 3.11 for now so it's not a blocking issue for me. I appreciate you need additional detail so I will attempt to get into the weeds when I have a spare moment.

I am using MacOS right now so I will look into GSSAPI as you mention, but I also have a Linux box here so I will do a test on that as well and see what comes out.

[taobojlen]

More anecdotal evidence: I tried to update our Django 5.1 app to psycopg 3.2, and after running our test suite (in parallel) for a few seconds, I started seeing connection timeout errors from basically every single test. We have connect_timeout set to 3 seconds. This happens locally on MacOS 15.1, and it was also happening in our Ubuntu-based CI runners.

By using psycopg 3.1 instead, I no longer see this problem. 3.1.20 works, but with 3.2.1 I hit the error (I wasn't able to install 3.2.0 to test because of some broken dependencies). So it appears that the issue was introduced in 3.2.

I've tried to create a small reproduction but I haven't been able to. It seems like it isn't enough to just run a lot of database queries in parallel. I will share more here if I am able to isolate the problem.

[mtvx]

@taobojlen That sounds a bit like what I experienced. For me it was that the connect_timeout was not really waited for, but connection failed right away. See my post above, might want to check the same.

[akrherz]

I assume for folks that don't need it, have gssencmode=disable set? For my lame environment, this has always trouble for connection timeouts and latency.

[taobojlen]

@akrherz yep, I'm seeing this issue both with and without gssencmode=disable set.

[dvarrazzo]

@taobojlen provide a repro