Re: tds_fdw : Severe performance degradation from postgresql 10.23 to 16.4

Στις 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
>