|
Hi
>From: Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> >Sent: Wednesday, February 19, 2020 4:23 PM >To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no> >Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org> >Subject: Re: SubtransControlLock and performance problems > >On 2020-Feb-19, Lars Aksel Opsahl wrote: > >> With the values above I did see same performance problems and we ended >> with a lot of subtransControlLock. >> >> So I started to change the code based on your feedbacks. >> >> - What seems to work very good in combination with a catch exception >> and retry pattern is to insert the data in to separate table for each >> job. (I the current testcase we reduced the number of >> subtransControlLock from many hundreds to almost none.) > >I think at this point your only recourse is to start taking profiles to >see where the time is going. Without that, you're just flying blind and >whatever you do will not necessarily move your needle at all. Hi Yes I totally agree with you and yes I have tried to do some profiling and testing while developing. >From the worst case to best case the time is reduced 15 times (from 300 minutes to 20 minutes) when testing a small dataset for with 619230 surface (25909671 total line points) with the test below “resolve_overlap_gap_run('org_jm.jm_ukomm_flate','figurid','geo',4258,false,'test_topo_jm',0.000001,31,3000); “ The reason for this seems to be related to the problems described by Laurenz Albe related to how Postgres handles try and catch and sub transactions, which I did not know about. If we don't have this is mind and we start to get subtranslocks it seems to kill the performance in some cases. In this test I ran with 31 parallel threads which is very high on a server with only 32 cores and maybe not realistic. I just did this now see what happens when I try to push a server to it’s limits and maximise the performance increase. If I reduce this to 1 single thread, there should be now difference and if run on 16 threads the difference would much much smaller. I will now start to run on datasets which are 10 times bigger to check how thing scales, but then run with around maybe 28 parallel jobs.
The two branches I have tested on now which should show the main difference are here. https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_using_TopoGeo_addLinestringwhich is the faster one. https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology which is slower one, but here I have now added a check on number of subtranslocks before I kick of new jobs and that reduced time form 9 hours to 3 hours.
Thanks.
Lars
|
