[postgis-users] Status of PostGIS 3 and PostgreSQL Yum Repos?

Paul Ramsey pramsey at cleverelephant.ca
Wed Nov 6 19:20:36 PST 2019 

92566 page faults vs 2221...

On Wed, Nov 6, 2019 at 6:46 PM Daryl Herzmann <akrherz at gmail.com> wrote:
>
> And yikes, this appears to be the source of the troubles here.
>
> On RHEL7:
>
> echo "0 0" | time /usr/proj62/bin/cs2cs +proj=longlat +datum=WGS84 +to
> +init=epsg:2163
> 9473741.42 1181205.06 0.00
> 3.04user 0.11system 0:03.15elapsed 99%CPU (0avgtext+0avgdata 14744maxresident)k
> 0inputs+0outputs (0major+92566minor)pagefaults 0swaps
>
> On RHEL8:
>
> echo "0 0" | time /usr/proj62/bin/cs2cs +proj=longlat +datum=WGS84 +to
> +init=epsg:2163
> 9473741.42 1181205.06 0.00
> 0.06user 0.00system 0:00.07elapsed 97%CPU (0avgtext+0avgdata 13228maxresident)k
> 0inputs+0outputs (0major+2221minor)pagefaults 0swaps
>
> So that's 3.04 seconds vs 0.06 seconds.  Will do some more checking
> and engage the proj folks perhaps.
>
> daryl
>
> On Wed, Nov 6, 2019 at 8:23 PM Daryl Herzmann <akrherz at gmail.com> wrote:
> >
> > Greetings,
> >
> > Thanks for the continued support. I am using the RPM provided by
> > https://yum.postgresql.org/.
> >
> > On RHEL7:
> >
> > #  ldd /usr/proj62/lib/libproj.so
> > linux-vdso.so.1 =>  (0x00007ffcf7deb000)
> > libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x00007ff7a3f82000)
> > libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ff7a3d66000)
> > libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007ff7a3a5f000)
> > libm.so.6 => /lib64/libm.so.6 (0x00007ff7a375d000)
> > libc.so.6 => /lib64/libc.so.6 (0x00007ff7a338f000)
> > libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007ff7a3179000)
> > libdl.so.2 => /lib64/libdl.so.2 (0x00007ff7a2f75000)
> > /lib64/ld-linux-x86-64.so.2 (0x00007ff7a46c3000)
> >
> >
> > On RHEL8:
> >
> > # ldd /usr/proj62/lib/libproj.so
> > linux-vdso.so.1 (0x00007ffcea502000)
> > libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x00007fd3334b0000)
> > libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd333290000)
> > libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fd332efb000)
> > libm.so.6 => /lib64/libm.so.6 (0x00007fd332b79000)
> > libc.so.6 => /lib64/libc.so.6 (0x00007fd3327b5000)
> > libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fd33259d000)
> > libdl.so.2 => /lib64/libdl.so.2 (0x00007fd332399000)
> > libz.so.1 => /lib64/libz.so.1 (0x00007fd332182000)
> > /lib64/ld-linux-x86-64.so.2 (0x00007fd333c54000)
> >
> > The byte sizes of the .so files are different.  It appears they both
> > come from the same spec file
> >
> > https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/master/proj62/master/proj62.spec
> >
> > I have sqlite-libs-3.26.0-3.el8.x86_64 on RHEL8 and
> > sqlite-3.7.17-8.el7.x86_64 on RHEL7.
> >
> > daryl
> >
> > On Wed, Nov 6, 2019 at 3:35 PM Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> > >
> > > 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
> > >
> > > _______________________________________________
> > > 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