Обсуждение: CPU usage for queries, psycopg 2 vs 3
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
aspossible, 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
usagesits at the normal idling 2-3%. But in the psycopg3 part, while the 8 threads are waiting on results my processor
usagejumps 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,
where98% 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
backdown 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.")
On 9/5/23 10:51 AM, David Raymond wrote: > 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 aspossible, 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 processorusage sits at the normal idling 2-3%. But in the psycopg3 part, while the 8 threads are waiting on results my processorusage jumps up to 62-63% or so. > > Is there a setting or configuration I'm missing? Take a look at this: https://github.com/psycopg/psycopg/issues/448 It seems to be related. > 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 minutelong query. I don't need to continue the very microsecond the query completes. Is there anything I can do to bringCPU usage back down with psycopg (3) ? > > Thank you, > >
On Tue, 5 Sept 2023 at 18:52, David Raymond <David.Raymond@tomtom.com> wrote: > > I've noticed that CPU usage for queries seems to have drastically increased going from psycopg2 to psycopg3. Hello David, thank you for the report. No, this is not expected: psycopg 3 uses I/O completion functions to wait idle for results from the server, so it shouldn't consume more cpu than psycopg2. I will investigate (but on Linux, I don't have a Windows machine handy to test with). There was a change in the waiting function in psycopg 3.1.5, related to <https://github.com/psycopg/psycopg/issues/414>. I wonder if it was a regression with that change? Could you please be so kind as to repeat your tests with psycopg 3.1.4? Also, did you try only to connect on localhost? Are you able to run the same test with a server running on a different network location? Thank you very much -- Daniele
> Could you please be so kind as to repeat your tests with psycopg 3.1.4? Tried with 3.1.4 and it has idle processor usage while waiting for the 8 results, just like psycopg2. Tried with 3.1.5 and it spikes up to 62% reported CPU usage while waiting for the 8 results, just like the current release. > Also, did you try only to connect on localhost? Are you able to run the same test with a server running on a differentnetwork location? Have now tried with both a local host, and spreading the connections out over a number of remote servers, and the resultsare the same for both situations.
On Thu, 7 Sept 2023 at 13:17, David Raymond <David.Raymond@tomtom.com> wrote: > > > Could you please be so kind as to repeat your tests with psycopg 3.1.4? > Tried with 3.1.4 and it has idle processor usage while waiting for the 8 results, just like psycopg2. > Tried with 3.1.5 and it spikes up to 62% reported CPU usage while waiting for the 8 results, just like the current release. Thank you, this gives a good indication about where to look. -- Daniele
Hello David, On Tue, 5 Sept 2023 at 18:52, David Raymond <David.Raymond@tomtom.com> wrote: > > I've noticed that CPU usage for queries seems to have drastically increased going from psycopg2 to psycopg3. > [...] > This is on Windows, where I install with "pip install psycopg2-binary" and "pip install psycopg[binary]" I have tested your script on Linux and on this platform I can't reproduce the issue. Psycopg 3 CPU usage remains low. I will try to repeat your test on Windows. -- Daniele
Hello David, On Thu, 7 Sept 2023 at 14:17, David Raymond <David.Raymond@tomtom.com> wrote: > > > Could you please be so kind as to repeat your tests with psycopg 3.1.4? > Tried with 3.1.4 and it has idle processor usage while waiting for the 8 results, just like psycopg2. > Tried with 3.1.5 and it spikes up to 62% reported CPU usage while waiting for the 8 results, just like the current release. I am not able to quickly investigate this issue, because I don't have a Windows machine handy. However I would like to release 3.1.11 soon, so I would like to merge this changeset, which should revert the wait function to what was in 3.1.4 on Windows: https://github.com/psycopg/psycopg/commit/36f70532c4451d04c0052c6eac4d05380159e809 Are you able to test it to verify that CPU usage is regular with this changeset? Also please note that you sent your last, very informative, message, to me only: maybe you want to send it to the ML? If someone is able to look into this issue better, it would be some welcome help. FYI, I've reported it at https://github.com/psycopg/psycopg/issues/645 Cheers -- Daniele
Looks good. With the change it quietly idles while waiting for query results, with no CPU spike. -David > Hello David, > > On Thu, 7 Sept 2023 at 14:17, David Raymond <David.Raymond@tomtom.com> wrote: >> >> > Could you please be so kind as to repeat your tests with psycopg 3.1.4? >> Tried with 3.1.4 and it has idle processor usage while waiting for the 8 results, just like psycopg2. >> Tried with 3.1.5 and it spikes up to 62% reported CPU usage while waiting for the 8 results, just like the current release. > I am not able to quickly investigate this issue, because I don't have > a Windows machine handy. However I would like to release 3.1.11 soon, > so I would like to merge this changeset, which should revert the wait > function to what was in 3.1.4 on Windows: > Are you able to test it to verify that CPU usage is regular with this changeset? > Cheers > -- Daniele
On Wed, 20 Sept 2023 at 16:27, David Raymond <David.Raymond@tomtom.com> wrote: > > Looks good. With the change it quietly idles while waiting for query results, with no CPU spike. Thank you for confirming. I will release Psycopg 3.1.11 in the next few days, with this changeset included. Cheers -- Daniele