[postgis-users] AWS RDS performance

Tue Feb 25 09:28:17 PST 2020

Hi Alexander,

I'd suggest to check if there are differences in the execution of the query
in RDS or in the other box where the query is executed with higher
performances.
I'm specifically talking about to check the outputs of the EXPLAIN (BUFFER,
ANALYSE) of teh query executed in the two boxes.

Eventual differences could be related to different DB settings in RDS, so
aligning the configuration should also make the two executions more close.

If the plans are instead the same, well, here the hardware could be
involved, specially the underlying storage unit.

But I'd definitely start from a comparison of the outputs of EXPLAIN
(BUFFER, ANALYSE).

Giuseppe.

Il giorno mar 25 feb 2020 alle ore 17:04 Alexander Gataric <gataric at usa.net>
ha scritto:

> I've been using PostGIS on AWS with low CPU utilization. I tried 11.6 and
> 10.11 and RDS size db.m5.2xlarge (8 vCPU, 32 GB).
>
> Any suggestions? Performance is not what I'd expect from a box with those
> specs.
>
> Get BlueMail for Android <http://www.bluemail.me/r?b=15818>
> On Feb 25, 2020, at 10:34 AM, Alexander Gataric <gataric at usa.net> wrote:
>>
>> What parameter file settings are you using? I got low CPU utilization on
>> 11.6 with postgis 2.4.
>>
>> Get BlueMail for Android <http://www.bluemail.me/r?b=15818>
>> On Feb 25, 2020, at 3:31 AM, Stefan Duling < stefan.duling at mapz.com>
>> wrote:
>>>
>>> Sorry there is a major typor in my previous mail with the postgres version. Each time i wrote 10.4 it has to be 10.12. Corrected version:
>>>
>>>
>>>
>>> Hello everyone,
>>>
>>> I just want to report the outcomes of my latest performance research.
>>>
>>> Postgres 12.1 and Postgres 12.2 are both restrictively slow in performing our geometry processing queries - no matter what postgis version (3.0.0, 2.5) I use and how the raw data came into the db. Setting jit on or off and/or allow parallel workers and ANALYZE before the queries hasn’t a significant effect on my test query.
>>>
>>> I installed Postgres 10.12 with postgis 2.4 on the same machine on a separate cluster and it runs the queries in fraction of time. See explain analyze output below.
>>>
>>> I haven’t tested the newly released postgis 3.0.1 since there is no easy installation so far (as long as I see).
>>> As long as I don’t see a clear way to break it down further we will stay with postgres 10.12.
>>>
>>> Regards,
>>>
>>> Stefan
>>>
>>> —————————
>>> NEW SETUP WITH POSTGRES 10.12, POSTGIS 2.4
>>>
>>>
>>> EXPLAIN ANALYZE CREATE TABLE osm_admin_lines_split AS
>>> SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, ST_SubDivide(ST_Collect(ST_ExteriorRing(the_geom)),5000) AS geometry
>>> FROM (SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, (ST_Dump(geometry)).geom AS the_geom FROM osm_admin_areas) AS foo
>>> GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in;
>>>                                                                                                       QUERY PLAN
>>>
>>>    ------------------------------
>>>
>>>  ProjectSet  (cost=272668581.44..3513224466.44 rows=612003000000 width=65) (actual time=56997.276..91803.763 rows=739733 loops=1)
>>>    ->  GroupAggregate  (cost=272668581.44..297148701.44 rows=612003000 width=65) (actual time=56997.263..69736.892 rows=611704 loops=1)
>>>          Group Key: osm_admin_areas.osm_id, osm_admin_areas.name, osm_admin_areas.admin_level, osm_admin_areas.name_en, osm_admin_areas.name_fr, osm_admin_areas.area, osm_admin_areas.iso, osm_admin_areas.is_in
>>>          ->  Sort  (cost=272668581.44..274198588.94 rows=612003000 width=65) (actual time=56997.204..61484.384 rows=708178 loops=1)
>>>                Sort Key: osm_admin_areas.osm_id, osm_admin_areas.name, osm_admin_areas.admin_level, osm_admin_areas.name_en, osm_admin_areas.name_fr, osm_admin_areas.area, osm_admin_areas.iso, osm_admin_areas.is_in
>>>                Sort Method: external merge  Disk: 4087800kB
>>>                ->  Result  (cost=0.00..162527467.79 rows=612003000 width=65) (actual time=6.103..44923.073 rows=708178 loops=1)
>>>                      ->  ProjectSet  (cost=0.00..3406687.79 rows=612003000 width=65) (actual time=6.095..44633.240 rows=708178 loops=1)
>>>                            ->  Seq Scan on osm_admin_areas  (cost=0.00..190612.03 rows=612003 width=6983) (actual time=0.103..4156.204 rows=611707 loops=1)
>>>  Planning time: 4.031 ms
>>>  Execution time: 193917.392 ms
>>> (11 rows)
>>>
>>>
>>>    ------------------------------
>>>
>>> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200225/aa731dd7/attachment.html>