Feedback-ID: i76984098:Fastmail
Message-ID: <d372707b-6758-8696-3f50-cd4fe4501b66@aklaver.com>
Date: Tue, 5 Sep 2023 11:22:52 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.13.0
Subject: Re: CPU usage for queries, psycopg 2 vs 3
To: David Raymond <David.Raymond@tomtom.com>,
 "psycopg@postgresql.org" <psycopg@postgresql.org>
References: 
 <DB7PR07MB3916A230837909499A87EF3287E8A@DB7PR07MB3916.eurprd07.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <DB7PR07MB3916A230837909499A87EF3287E8A@DB7PR07MB3916.eurprd07.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/d372707b-6758-8696-3f50-cd4fe4501b66%40aklaver.com>
Precedence: bulk


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 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?

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 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,
>
>