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

Στις 9/11/24 16:05, ο/η Achilleas Mantzios έγραψε:
>
> Στις 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 ) .
Meaning there are CPP directives in the tds_fdw source making it behave 
differently according to the Postgresql version. The two tds_fdw's have 
the same version.
>>
>> regards
>>
>
>