Excessive CPU usage on Windows

[dvarrazzo]

Reported in the mailing list by David Raymond:

---

I've noticed that CPU usage for queries seems to have drastically increased going from psycopg2 to psycopg3.

In psycopg2 a connection would sit quietly while waiting for the results, using next to no CPU.
In psycopg it looks like the threads are using their core at full power, continuously checking for the result as fast as possible, and never sleeping or relaxing.

See below for a simple example script. During the psycopg2 part while the 8 threads are waiting on results my processor usage sits at the normal idling 2-3%. But in the psycopg3 part, while the 8 threads are waiting on results my processor usage jumps up to 62-63% or so.

Is there a setting or configuration I'm missing?
This is on Windows, where I install with "pip install psycopg2-binary" and "pip install psycopg[binary]"
Current versions shown by pip list:
psycopg 3.1.10 (This gets installed even when telling it to install psycopg[binary])
psycopg-binary 3.1.10
psycopg2-binary 2.9.7

And their reported .version
psycopg.version: 3.1.10
psycopg2.version: 2.9.7 (dt dec pq3 ext lo64)

I've got several scripts where I open up multiple connections to multiple servers and run a number of queries on them, where 98% of my machine's time should be just relaxing and waiting for the server to finish the next 1 to 15 minute long query. I don't need to continue the very microsecond the query completes. Is there anything I can do to bring CPU usage back down with psycopg (3) ?

Thank you,

import sys
import threading


import psycopg
import psycopg2


def connectAndRunSleep3():
    conn = psycopg.connect(host = "localhost", dbname = "...", user = "...", password = "...", autocommit = True)
    with conn.cursor() as cur:
        cur.execute("select pg_sleep(15);")
    conn.close()


def connectAndRunSleep2():
    conn = psycopg2.connect(host = "localhost", dbname = "...", user = "...", password = "...")
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute("select pg_sleep(15);")
    conn.close()


if __name__ == "__main__":
    print("Python version:", sys.version)
    print("psycopg.__version__:", psycopg.__version__)
    print("psycopg2.__version__:", psycopg2.__version__)
    
    numThreads = 8
    
    print("Check your CPU usage during this run")
    
    print(f"Querying with {numThreads:,d} threads in psycopg2...")
    threads = []
    for n in range(numThreads):
        thread = threading.Thread(target = connectAndRunSleep2)
        threads.append(thread)
        thread.start()
    for thread in threads:
        thread.join()
    print("All joined.")
    
    print(f"Querying with {numThreads:,d} threads in psycopg3...")
    threads = []
    for n in range(numThreads):
        thread = threading.Thread(target = connectAndRunSleep3)
        threads.append(thread)
        thread.start()
    for thread in threads:
        thread.join()
    print("All joined.")

The slowness appears related to the wait_c function on Windows, because it was reproduced with 3.1.5 but not 3.1.4.

Couldn't reproduce the issue on Linux, will need a Windows machine.

As a stop-gap measure, next 3.1.11 will be released using wait_select on Windows instead.

[dvarrazzo]

I might have found the problem in this snippet:

psycopg/psycopg_c/psycopg_c/_psycopg/waiting.pyx

Lines 113 to 117 in 8d2ba2d

	else {
	tv.tv_sec = (int)timeout;
	tv.tv_usec = (int)(((long)timeout * SEC_TO_US) % SEC_TO_US);
	tvptr = &tv;
	}

The usec conversion is wrong: the (long) cast is applied to the timeout in sec, not in usec, therefore ending up being 0 and causing a busy loop instead of a polite waiting.

     else {
         tv.tv_sec = (int)timeout;
-        tv.tv_usec = (int)(((long)timeout * SEC_TO_US) % SEC_TO_US);
+        tv.tv_usec = (int)((long)(timeout * SEC_TO_US) % SEC_TO_US);
         tvptr = &tv;
     }