Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAgG7-0007Ug-2i for pgsql-performance@arkaria.postgresql.org; Thu, 20 Jan 2022 22:50:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nAgG5-00061G-W7 for pgsql-performance@arkaria.postgresql.org; Thu, 20 Jan 2022 22:50:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAgG5-000617-Kd for pgsql-performance@lists.postgresql.org; Thu, 20 Jan 2022 22:50:21 +0000 Received: from us-smtp-delivery-117.mimecast.com ([170.10.129.117]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAgFx-0002fy-AN for pgsql-performance@lists.postgresql.org; Thu, 20 Jan 2022 22:50:20 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=clackamas.us; s=mimecast20210408; t=1642719011; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type; bh=2aFioPABCaZgp4sp2J9zEsdM33wf0I6/xrhQdajAgbM=; b=MgcaOwuLnz0olDk/Zwj+qc3nAMt9fMqX1I36BC7Dir9yVgX9Lif6cT0bQFOmjEv42dmT28 BWk73qph2MQ5d4fHsRFDylpesGEq6Ym6rL7PIZiJhwwc2MoSpPVzVATkm7YFs4OKMWdrih 2s+3f2GcVegbDYaZC57fLePQeHqVtDE= Received: from GCC02-DM3-obe.outbound.protection.outlook.com (mail-dm3gcc02lp2109.outbound.protection.outlook.com [104.47.65.109]) by relay.mimecast.com with ESMTP with STARTTLS (version=TLSv1.2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id us-mta-191-ck8wgp6qP3CQpB5we7ePXA-1; Thu, 20 Jan 2022 17:50:09 -0500 X-MC-Unique: ck8wgp6qP3CQpB5we7ePXA-1 Received: from BN6PR09CA0075.namprd09.prod.outlook.com (2603:10b6:404:dc::13) by DM8PR09MB6024.namprd09.prod.outlook.com (2603:10b6:5:2ee::16) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.4909.7; Thu, 20 Jan 2022 22:50:08 +0000 Received: from DM3GCC02FT034.eop-gcc02.prod.protection.outlook.com (2a01:111:f400:7d04::202) by BN6PR09CA0075.outlook.office365.com (2603:10b6:404:dc::13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.4909.7 via Frontend Transport; Thu, 20 Jan 2022 22:50:08 +0000 X-MS-Exchange-Authentication-Results: spf=pass (sender IP is 198.245.132.32) smtp.mailfrom=clackamas.us; dkim=none (message not signed) header.d=none;dmarc=pass action=none header.from=clackamas.us Received: from Exch02.county.ds.clackamas.us (198.245.132.32) by DM3GCC02FT034.mail.protection.outlook.com (10.97.8.200) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.4888.11 via Frontend Transport; Thu, 20 Jan 2022 22:50:08 +0000 Received: from Exch01.county.ds.clackamas.us (10.32.2.129) by Exch02.county.ds.clackamas.us (10.32.2.133) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.858.5; Thu, 20 Jan 2022 14:50:07 -0800 Received: from Exch01.county.ds.clackamas.us ([fe80::4cf8:d71:78c5:f7]) by Exch01.county.ds.clackamas.us ([fe80::4cf8:d71:78c5:f7%7]) with mapi id 15.02.0858.002; Thu, 20 Jan 2022 14:50:07 -0800 From: "Lugosi, Jim" To: "pgsql-performance@lists.postgresql.org" Subject: Poor performance PostgreSQL 13/ PostGIS 3.x Thread-Topic: Poor performance PostgreSQL 13/ PostGIS 3.x Thread-Index: AdgOT+lC0Osg2RDRTM6oKlr4GCd1HA== Date: Thu, 20 Jan 2022 22:50:07 +0000 Message-ID: Accept-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.38.18.20] MIME-Version: 1.0 X-EOPAttributedMessage: 0 X-MS-PublicTrafficType: Email X-MS-Office365-Filtering-Correlation-Id: 71f69ffa-fad2-4655-ab0a-08d9dc673527 X-MS-TrafficTypeDiagnostic: DM8PR09MB6024:EE_ X-Microsoft-Antispam-PRVS: X-MS-Oob-TLC-OOBClassifiers: OLM:9508 X-MS-Exchange-SenderADCheck: 1 X-MS-Exchange-AntiSpam-Relay: 0 X-Microsoft-Antispam: BCL:0 X-Microsoft-Antispam-Message-Info: RwXgUtRRrol8C52Pnoi3lr2UJSWsVHEz7nH31mtAk5v/HojAfqlU0JOkdidjDnSc9NutmLLfv3uhE71Z3vUuz3hWXbVMjt/dxmQc4XwxN3NdJKXGygquDnF6rPzobXTNP+XdRE4ktJvLMTgBmUtGJ7q0ri2BLAxw0ho0eyim3SsGSO+O/p2MrtlbNfpoyBi2C8QkMXbbmxsGGmjVMTLB4BdYRBjrEtxAAlv5dcEzm1GZDVd+KPjYrnl1Ht207GtEq7PQRD9BwYyB8u9b0O/Wt4SLBpPRlDLtxB0KmuNrK5UgjH2MkR7pBJ7iIcP+HXW/2rwvuu7gPSZYHMV9dJhSf5IA7kC9vc6l8Bv3JHpr/Q97fKKRFwXWY6CcvSv4SlHYh1o/92SxHcr4l2fo6OOC0olgyPfMuvHoQX3/ivZHmGrjpAqhTayyeNmEFPRIre+fFCQh0BJHlHODi9d5ZJCZgJEk6q1bILju7Se3yVHWy+U6T9qoZmT47o7A4KUBkWG+N8OtAtgGbBB69Kv0FMKpTGTyIIHUiHTu7+Og862yvIuD1mvSauUUrrBirCmqB40I0kihitBiMPstkHQEY0BvSLgHFZcHl6P4GOMPfUhthZK7nDbqPn8b9rS4Uw5TZLcVg+8z23dd8qTcs644HFGUenhc8RxwGz29pFjo31IdegZgR94EfSndi7OWE5wshFa61VAlFIu60pxK7LtgP1n6b+o02iZzW75kUFcU7oVesKgEQ8J8Aud8392LZLU1vqPyyZiZjYwZmTZEwyU4WdWZ8hohQtguKGAVgRvsUuuIiuXrAf7i09uc2w97tWJLj6JJ8Gfhg2ybCxSpskqZr73rRuP5YHxY6glFqhO7OhOYjzMiJ57C3KQrLuxgh7P0SCxZSmUzQYVueCgBByNPNQna+LdEqVRevanSByGIr5m0RGc= X-Forefront-Antispam-Report: CIP:198.245.132.32;CTRY:US;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:Exch02.county.ds.clackamas.us;PTR:InfoDomainNonexistent;CAT:NONE;SFS:(4636009)(46966006)(40470700002)(36840700001)(8936002)(2906002)(36860700001)(316002)(508600001)(108616005)(8676002)(24736004)(2616005)(426003)(5660300002)(70206006)(70586007)(7696005)(81166007)(83380400001)(186003)(40460700001)(36756003)(82310400004)(356005)(86362001)(336012)(40140700001)(6916009)(26005)(47076005)(11771555001)(110056007)(80162009)(36900700001)(75396010);DIR:OUT;SFP:1102 X-OriginatorOrg: clackamas.us X-MS-Exchange-CrossTenant-OriginalArrivalTime: 20 Jan 2022 22:50:08.0076 (UTC) X-MS-Exchange-CrossTenant-Network-Message-Id: 71f69ffa-fad2-4655-ab0a-08d9dc673527 X-MS-Exchange-CrossTenant-Id: 11a5f385-e251-4bf1-b266-597e782882a0 X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=11a5f385-e251-4bf1-b266-597e782882a0;Ip=[198.245.132.32];Helo=[Exch02.county.ds.clackamas.us] X-MS-Exchange-CrossTenant-AuthSource: DM3GCC02FT034.eop-gcc02.prod.protection.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM8PR09MB6024 Authentication-Results: relay.mimecast.com; auth=pass smtp.auth=CUSA17A671 smtp.mailfrom=jimlug@clackamas.us X-Mimecast-Spam-Score: 0 X-Mimecast-Originator: clackamas.us Content-Language: en-US Content-Type: multipart/alternative; boundary="_000_c9b875ea99eb4e8387a2d2faf54ab7e3clackamasus_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_c9b875ea99eb4e8387a2d2faf54ab7e3clackamasus_ Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Hi folks, We are struggling to figure out what is going on. We are migrating from Pos= tgreSQL 9.6 to PostgreSQL 13 w/ PostGIS. Our 9.6 version was compiled from = source and the new version (13) was installed using Yum. BTW, the new versi= on is on a VM that has 16GB of memory, two cores, and 500 GB of disk. In ad= dition, we are using MapServer as our mapping engine and OpenLayers as the = client side interface. Once we switch over to the new version of PostgreSQL= , the performance takes a big nose dive. We have being tweaking and tuning = the database and it appears to be happy but the response times from mapfile= requests are 3 -7 seconds. Previously, the response time was below a secon= d. Another point is that we populated the new database from the old (9.6), usi= ng pg_dump. Could this be causing issues? Should we load the data from scra= tch? We use ogr2ogr (GDAL) to help assist with loading of spatial data. Any= way, not really sure what the problem is. Lastly, why am I seeing so many requests as to the PostGIS version. It appe= ars that every map request sends the following query "SELECT PostGIS_Versio= n();", which in turn takes up a connection. Any help would be greatly appreciated. Thanks __:) _ \<,_ (*)/ (*) James Lugosi Clackamas County GISP IS Software Specialist, Senior 121 Library Court, Oregon City OR 97045 503-723-4829 --_000_c9b875ea99eb4e8387a2d2faf54ab7e3clackamasus_ Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable

Hi folks,

 

We are struggling to figure out what is going on. We= are migrating from PostgreSQL 9.6 to PostgreSQL 13 w/ PostGIS. Our 9.6 ver= sion was compiled from source and the new version (13) was installed using = Yum. BTW, the new version is on a VM that has 16GB of memory, two cores, and 500 GB of disk. In addition, we= are using MapServer as our mapping engine and OpenLayers as the client sid= e interface. Once we switch over to the new version of PostgreSQL, the perf= ormance takes a big nose dive. We have being tweaking and tuning the database and it appears to be happy but= the response times from mapfile requests are 3 -7 seconds. Previously, the= response time was below a second.

 

Another point is that we populated the new database = from the old (9.6), using pg_dump. Could this be causing issues? Should we = load the data from scratch? We use ogr2ogr (GDAL) to help assist with loadi= ng of spatial data. Anyway, not really sure what the problem is.

Lastly, why am I seeing so many requests as to the P= ostGIS version. It appears that every map request sends the following query= "SELECT PostGIS_Version();", which in turn takes up a connection= . 

 

Any help would be greatly appreciated.

 

Thanks

 

&nbs= p;    __J
   _ \<,_
  (*)/ (*)

James Lugosi

Clackamas County GISP

IS Software Specialist, Senior

121 Library Court, Oregon City OR 97045

503-723-4829

 

--_000_c9b875ea99eb4e8387a2d2faf54ab7e3clackamasus_--