[postgis-users] Status of PostGIS 3 and PostgreSQL Yum Repos?
Paul Ramsey
pramsey at cleverelephant.ca
Wed Nov 6 13:35:15 PST 2019
Are the contents of the proj-6.2 packages the same on both systems? Like we’re looking mostly for build difference here, since the code versions are all the same…
P
> On Nov 6, 2019, at 1:31 PM, Daryl Herzmann <akrherz at gmail.com> wrote:
>
> Hi Paul,
>
> Thanks again for your response. I ran valgrind like so:
>
> echo "explain analyze select st_transform(geom, 2163) from stations;"
> | valgrind --tool=callgrind -v --dump-every-bb=100000000
> /usr/pgsql-12/bin/postgres --single -D 12/data asos
>
> which resulted in the following log:
>
> https://mesonet.agron.iastate.edu/pickup/callgrind.zip
>
> I then attempted to look at the file in kcachegrind and am unsure what
> I am looking at :)
>
> It seems to show a lot of time being spent in sqlite3 functions. 38
> million calls to sqlite3VdbeSerialType ?
>
> daryl
>
> On Tue, Nov 5, 2019 at 9:45 PM Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>>
>> Do you have access to cachegrind or some other sampling profiler in
>> your environment? Maybe you can get a feel for where the cycles are
>> different? Also, does your proj install include a pgk-config? You
>> could check and see if the build flags differ between systems.
>>
>> On Tue, Nov 5, 2019 at 7:32 PM Daryl Herzmann <akrherz at gmail.com> wrote:
>>>
>>> Greetings,
>>>
>>> I have created a reproducer in development and can run whatever
>>> command necessary to help debug this problem. I can't resolve what I
>>> should try though :) Any suggestions?
>>>
>>> daryl
>>>
>>> On Mon, Nov 4, 2019 at 9:34 PM Daryl Herzmann <akrherz at gmail.com> wrote:
>>>>
>>>> Good evening Paul,
>>>>
>>>> Thanks for the reply. Some more details:
>>>>
>>>> RHEL7
>>>>
>>>> # ldd /usr/pgsql-12/lib/postgis-3.so
>>>> linux-vdso.so.1 => (0x00007ffc7ef0b000)
>>>> libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fbdc45b5000)
>>>> libgeos_c.so.1 => /usr/geos38/lib64/libgeos_c.so.1 (0x00007fbdc4378000)
>>>> libproj.so.15 => /usr/proj62/lib/libproj.so.15 (0x00007fbdc3eef000)
>>>> libjson-c.so.2 => /lib64/libjson-c.so.2 (0x00007fbdc3ce4000)
>>>> libprotobuf-c.so.1 => /lib64/libprotobuf-c.so.1 (0x00007fbdc3adb000)
>>>> libxml2.so.2 => /lib64/libxml2.so.2 (0x00007fbdc3771000)
>>>> libm.so.6 => /lib64/libm.so.6 (0x00007fbdc346f000)
>>>> libSFCGAL.so.1 => /lib64/libSFCGAL.so.1 (0x00007fbdc29ac000)
>>>> libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fbdc2796000)
>>>> libc.so.6 => /lib64/libc.so.6 (0x00007fbdc23c8000)
>>>> /lib64/ld-linux-x86-64.so.2 (0x00007fbdc4beb000)
>>>> libgeos-3.8.0.so => /usr/geos38/lib64/libgeos-3.8.0.so (0x00007fbdc2000000)
>>>> libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x00007fbdc1d4b000)
>>>> libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fbdc1b2f000)
>>>> libdl.so.2 => /lib64/libdl.so.2 (0x00007fbdc192b000)
>>>> libz.so.1 => /lib64/libz.so.1 (0x00007fbdc1715000)
>>>> liblzma.so.5 => /lib64/liblzma.so.5 (0x00007fbdc14ef000)
>>>> libCGAL.so.11 => /usr/lib64/libCGAL.so.11 (0x00007fbdc12c7000)
>>>> libCGAL_Core.so.11 => /usr/lib64/libCGAL_Core.so.11 (0x00007fbdc108e000)
>>>> libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x00007fbdc0e33000)
>>>> libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x00007fbdc0bbb000)
>>>> libboost_date_time-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_date_time-mt.so.1.53.0 (0x00007fbdc09aa000)
>>>> libboost_thread-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_thread-mt.so.1.53.0 (0x00007fbdc0793000)
>>>> libboost_system-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_system-mt.so.1.53.0 (0x00007fbdc058f000)
>>>> libboost_serialization-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_serialization-mt.so.1.53.0 (0x00007fbdc0323000)
>>>> librt.so.1 => /usr/lib64/librt.so.1 (0x00007fbdc011b000)
>>>>
>>>> RHEL8
>>>>
>>>> # ldd /usr/pgsql-12/lib/postgis-3.so
>>>> linux-vdso.so.1 (0x00007ffcb9f66000)
>>>> libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007ff53a841000)
>>>> libgeos_c.so.1 => /usr/geos38/lib64/libgeos_c.so.1 (0x00007ff53a602000)
>>>> libproj.so.15 => /usr/proj62/lib/libproj.so.15 (0x00007ff53a175000)
>>>> libjson-c.so.4 => /lib64/libjson-c.so.4 (0x00007ff539f65000)
>>>> libprotobuf-c.so.1 => /lib64/libprotobuf-c.so.1 (0x00007ff539d5c000)
>>>> libxml2.so.2 => /lib64/libxml2.so.2 (0x00007ff5399f4000)
>>>> libm.so.6 => /lib64/libm.so.6 (0x00007ff539672000)
>>>> libSFCGAL.so.1 => /lib64/libSFCGAL.so.1 (0x00007ff53897b000)
>>>> libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ff538763000)
>>>> libc.so.6 => /lib64/libc.so.6 (0x00007ff53839f000)
>>>> /lib64/ld-linux-x86-64.so.2 (0x00007ff53af0e000)
>>>> libgeos-3.8.0.so => /usr/geos38/lib64/libgeos-3.8.0.so (0x00007ff537fcc000)
>>>> libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x00007ff537cb9000)
>>>> libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ff537a99000)
>>>> libdl.so.2 => /lib64/libdl.so.2 (0x00007ff537895000)
>>>> libz.so.1 => /lib64/libz.so.1 (0x00007ff53767e000)
>>>> liblzma.so.5 => /lib64/liblzma.so.5 (0x00007ff537457000)
>>>> libCGAL_Core.so.13 => /usr/lib64/libCGAL_Core.so.13 (0x00007ff5371b7000)
>>>> libboost_thread.so.1.66.0 => /usr/lib64/libboost_thread.so.1.66.0
>>>> (0x00007ff536f8b000)
>>>> libboost_system.so.1.66.0 => /usr/lib64/libboost_system.so.1.66.0
>>>> (0x00007ff536d86000)
>>>> libboost_serialization.so.1.66.0 =>
>>>> /usr/lib64/libboost_serialization.so.1.66.0 (0x00007ff536b45000)
>>>> libboost_chrono.so.1.66.0 => /usr/lib64/libboost_chrono.so.1.66.0
>>>> (0x00007ff53693c000)
>>>> libboost_date_time.so.1.66.0 =>
>>>> /usr/lib64/libboost_date_time.so.1.66.0 (0x00007ff536729000)
>>>> libboost_atomic.so.1.66.0 => /usr/lib64/libboost_atomic.so.1.66.0
>>>> (0x00007ff536527000)
>>>> libCGAL.so.13 => /usr/lib64/libCGAL.so.13 (0x00007ff536308000)
>>>> libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x00007ff5360a4000)
>>>> libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x00007ff535e0e000)
>>>> librt.so.1 => /usr/lib64/librt.so.1 (0x00007ff535c05000)
>>>>
>>>> So RHEL8 has CGAL-4.14-1.rhel8.x86_64, whereas RHEL7 has
>>>> CGAL-4.7-1.rhel7.1.x86_64
>>>>
>>>> This RHEL7 host was performing fine with previous PostgreSQL + Postgis
>>>> releases. Wonder what I could have fouled up....
>>>>
>>>> daryl
>>>>
>>>> On Mon, Nov 4, 2019 at 9:18 PM Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>>>>>
>>>>> 40x is shocking and it looks like the only difference is red hat 7 vs 8.?
>>>>>
>>>>>> On Nov 4, 2019, at 6:47 PM, Daryl Herzmann <akrherz at gmail.com> wrote:
>>>>>>
>>>>>> Thanks for the response Raúl,
>>>>>>
>>>>>> I tried PostGIS 3.0 GA now on RHEL7 and am still seeing the same
>>>>>> puzzling slow behaviour. My RHEL8 development laptop crunches the
>>>>>> same data through ST_Transform() in excellent time. Here's a
>>>>>> comparison of the explain analyze on both hosts
>>>>>>
>>>>>> RHEL7
>>>>>>
>>>>>> POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120"
>>>>>> GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.0" LIBXML="2.9.1" LIBJSON="0.11"
>>>>>> LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)"
>>>>>>
>>>>>> explain analyze select st_transform(geom, 2163) from stations;
>>>>>> QUERY
>>>>>> PLAN
>>>>>> -------------------------------------------------------------------------------------------------------------------------------------
>>>>>> Gather (cost=1000.00..113124.15 rows=69980 width=32) (actual
>>>>>> time=3535.920..4624.442 rows=70025 loops=1)
>>>>>> Workers Planned: 1
>>>>>> Workers Launched: 1
>>>>>> -> Parallel Seq Scan on stations (cost=0.00..105126.15 rows=41165
>>>>>> width=32) (actual time=4061.506..4104.824 rows=35012 loops=2)
>>>>>> Planning Time: 0.057 ms
>>>>>> Execution Time: 4627.474 ms
>>>>>>
>>>>>>
>>>>>> RHEL8
>>>>>>
>>>>>> POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120"
>>>>>> GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.0" LIBXML="2.9.7" LIBJSON="0.13.1"
>>>>>> LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)"
>>>>>>
>>>>>> explain analyze select st_transform(geom, 2163) from stations;
>>>>>> QUERY PLAN
>>>>>> ---------------------------------------------------------------------------------------------------------------------------------
>>>>>> Gather (cost=1000.00..112529.32 rows=69618 width=32) (actual
>>>>>> time=46.818..90.248 rows=69618 loops=1)
>>>>>> Workers Planned: 1
>>>>>> Workers Launched: 1
>>>>>> -> Parallel Seq Scan on stations (cost=0.00..104567.52 rows=40952
>>>>>> width=32) (actual time=49.211..72.335 rows=34809 loops=2)
>>>>>> Planning Time: 0.036 ms
>>>>>> Execution Time: 91.871 ms
>>>>>>
>>>>>>
>>>>>> I don't have an exact copy of the data between the two hosts, but it is close.
>>>>>>
>>>>>> thanks
>>>>>> daryl
>>>>>>
>>>>>>> On Wed, Oct 30, 2019 at 8:58 AM <rmrodriguez at carto.com> wrote:
>>>>>>>
>>>>>>> The are waiting for GDAL 3.0.2 to be out to package them together.
>>>>>>> Source: https://twitter.com/DevrimGunduz/status/1189216750895882243
>>>>>>>
>>>>>>> In any case, ST_Transform is slower because PROJ6 is slower, but it
>>>>>>> shouldn't be that slow.
>>>>>>>
>>>>>>>> On Wed, Oct 30, 2019 at 2:54 PM Daryl Herzmann <akrherz at gmail.com> wrote:
>>>>>>>>
>>>>>>>> Greetings,
>>>>>>>>
>>>>>>>> Does anybody know what's going on with the PostgreSQL Yum repos
>>>>>>>> supporting PostGIS 3? The repos continue to be stuck with 3.0 alpha4
>>>>>>>>
>>>>>>>> https://yum.postgresql.org/12/redhat/rhel-7-x86_64/
>>>>>>>>
>>>>>>>> I am having issues with performance with that alpha4 release and am
>>>>>>>> hoping they magically go away with the GA release of version 3 :)
>>>>>>>>
>>>>>>>> https://redmine.postgresql.org/issues/4826
>>>>>>>>
>>>>>>>> Is there some public interface to see the build status results of these RPMs?
>>>>>>>>
>>>>>>>> thanks
>>>>>>>> daryl
>>>>>>>> _______________________________________________
>>>>>>>> postgis-users mailing list
>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Raúl Marín Rodríguez
>>>>>>> carto.com
>>>>>>> _______________________________________________
>>>>>>> postgis-users mailing list
>>>>>>> postgis-users at lists.osgeo.org
>>>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org
>>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list