Message-ID: <cd2aa7ec-cfe5-447d-ac13-31f2cb65a1c4@cloud.gatewaynet.com>
Date: Sun, 10 Nov 2024 11:45:41 +0200
MIME-Version: 1.0
Subject: Re: tds_fdw : Severe performance degradation from postgresql 10.23 to
 16.4
To: Tomas Vondra <tomas@vondra.me>, pgsql-performance@lists.postgresql.org
References: <f43721f9-e628-4a22-b84d-e0ccc75b50cc@cloud.gatewaynet.com>
 <75b1aafc-bf5f-48fe-a495-fba11d8f0b05@vondra.me>
 <9640ce5b-3128-4c3c-b901-65747bc67b83@cloud.gatewaynet.com>
 <6797bc15-486b-48c3-9e89-cf85ae9a4043@vondra.me>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <6797bc15-486b-48c3-9e89-cf85ae9a4043@vondra.me>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/cd2aa7ec-cfe5-447d-ac13-31f2cb65a1c4%40cloud.gatewaynet.com>
Precedence: bulk


Στις 9/11/24 17:41, ο/η Tomas Vondra έγραψε:
>
> On 11/9/24 15:05, Achilleas Mantzios wrote:
>> Στις 9/11/24 12:49, ο/η Tomas Vondra έγραψε:
>>> On 11/8/24 20:32, Achilleas Mantzios wrote:
>>>> Dear All,
>>>>
>>>> we have hit a serious performance regression going from 10.23 → 16.4 as
>>>> far as tds_fdw (MS SQL) FDW is concerned. To cut the long story short, I
>>>> recreated the good fast “old” (pgsql 10) setup on the same vm as the
>>>> slow “new” (pgsql 16). Here is the bug report on github :
>>>>
>>>> https://github.com/tds-fdw/tds_fdw/issues/371
>>>>
>>>> All environment on the two pgsql clusters is shared (freetds version,
>>>> tds_fdw, gcc, llvm). Only thing differs are pgsql versions. The speed on
>>>> the old pgsql 10.23 is about 10 to 20 times higher than pgsql 16.4 . |
>>>> Setting client_min_messages TO debug3 does yield identical output on the
>>>> two systems.
>>>> |
>>>>
>>>> The new pgsql 16.4 shows 100% CPU usage during the query execution.
>>>>
>>>> I know we are pretty much alone with this, but it would be very nice if
>>>> anyone could help, see smth we are missing or guide us via the right
>>>> path.
>>>>
>>> I have little experience with tds_fdw, and can't investigate that
>>> locally. But it might be interesting to compare CPU profiles for the two
>>> (slow and fast) cases. Chances are the difference will be an indication
>>> regarding what got that slower. It might be something in PG or in the
>>> FDW, hard to say.
>> Thank you Tomas, do you have linux-perf in mind? How could we get the
>> CPU profiles of the two ?  Just in case it was not clear, the tests were
>> done on the same VM, at the same time, having the two DBs listening on
>> different ports. Kinda spooky from my part to do that on the production
>> system, but I wanted everything to be the same (latency to/from the ms
>> sql, etc) except the pgsql version, to prove my assumption, and it seems
>> pgsql version is what makes the difference (in conjunction of course
>> with tds_fdw ) .
> Yes, I mean linux-perf. There's a wikipage with some basic instructions:
>
>    https://wiki.postgresql.org/wiki/Profiling_with_perf
>
> But in short, I'd do this:
>
> 1) use pg_backend_pid() to get PID of the backend
>
>      SELECT pg_backend_pid();
>
> 2) start recording profile for that PID, say for 1 minute
>
>      perf record -g -p $PID -- sleep 60
>
> 3) run the query in a loop
>
>      SELECT count(*) as foo FROM mssql_bdynacom."ACDOC" \watch 1
>
> 4) once the recording stops, generate report
>
>      perf report > report.txt
>
>
> Do this for both versions, share the output txt files. You may need to
> install additional packages with debug symbols to get better profiles,
> and stuff like that.
>
> Also, don't forget to share the explain plans.
Thank you Tomas, as soon as I get my hands on the new VM I will do as 
you suggested and report back.
>
> regard
>