From: "Lugosi, Jim" <JimLug@clackamas.us>
To: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Subject: Poor performance PostgreSQL13/PostGIS 3.x
Thread-Topic: Poor performance PostgreSQL13/PostGIS 3.x
Thread-Index: AdgOS8QTMwRl+hFvSvq6+q2D9L4wyw==
Date: Thu, 20 Jan 2022 22:31:15 +0000
Message-ID: <792d4b48d5b2404cb1a521b25536eb49@clackamas.us>
Accept-Language: en-US
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 20 Jan 2022 22:31:16.0055
 (UTC)
X-MS-Exchange-CrossTenant-Network-Message-Id: 3c064f76-ed47-42a3-da50-08d9dc64926d
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=[Exch01.county.ds.clackamas.us]
X-MS-Exchange-CrossTenant-AuthSource: DM3GCC02FT033.eop-gcc02.prod.protection.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Anonymous
X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem
X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH0PR09MB8028
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_792d4b48d5b2404cb1a521b25536eb49clackamasus_"
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/792d4b48d5b2404cb1a521b25536eb49%40clackamas.us>
Precedence: bulk

--_000_792d4b48d5b2404cb1a521b25536eb49clackamasus_
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 Po=
stgreSQL 9.6 to PostgreSQL 13 w/ PostGIS.  Our 9.6 version was compiled fro=
m source and the new version (13) was installed using Yum.  BTW, the new ve=
rsion is on a VM that has 16GB of memory, two cores, and 500 GB of disk.  I=
n addition, we are using MapServer as our mapping engine and OpenLayers as =
the client side interface.  Once we switch over to the new version of Postg=
reSQL, the performance takes a big nose dive.  We have being tweaking and t=
uning the database and it appears to be happy but the response times from m=
apfile 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), usi=
ng pg_dump.  Could this be causing issues?  Should we load the data from sc=
ratch?  We use ogr2ogr (GDAL) to help assist with loading of spatial data. =
 Anyway, not really sure what the problem is.

Lastly, why am I seeing so many requests as to the PostGIS version.  It app=
ears that every map request sends the following query "SELECT PostGIS_Versi=
on();", which in turn takes up a connection.

Any suggestions would be greatly appreciated?

     __:)
   _ \<,_
  (*)/ (*)
James Lugosi
Clackamas County GISP
IS Software Specialist, Senior
121 Library Court, Oregon City OR 97045
503-723-4829


--_000_792d4b48d5b2404cb1a521b25536eb49clackamasus_
Content-Type: text/html; charset=WINDOWS-1252
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
=09{font-family:Wingdings;
=09panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
=09{font-family:"Cambria Math";
=09panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
=09{font-family:Calibri;
=09panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
=09{margin:0in;
=09margin-bottom:.0001pt;
=09font-size:11.0pt;
=09font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
=09{mso-style-priority:99;
=09color:#0563C1;
=09text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
=09{mso-style-priority:99;
=09color:#954F72;
=09text-decoration:underline;}
span.EmailStyle17
=09{mso-style-type:personal-compose;
=09font-family:"Calibri",sans-serif;
=09color:windowtext;}
.MsoChpDefault
=09{mso-style-type:export-only;
=09font-family:"Calibri",sans-serif;}
@page WordSection1
=09{size:8.5in 11.0in;
=09margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
=09{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hi folks,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">We are struggling to figure out what is going on.&nb=
sp; We are migrating from PostgreSQL 9.6 to PostgreSQL 13 w/ PostGIS.&nbsp;=
 Our 9.6 version was compiled from source and the new version (13) was inst=
alled using Yum.&nbsp; BTW, the new version is on
 a VM that has 16GB of memory, two cores, and 500 GB of disk.&nbsp; In addi=
tion, we are using MapServer as our mapping engine and OpenLayers as the cl=
ient side interface.&nbsp; Once we switch over to the new version of Postgr=
eSQL, the performance takes a big nose dive.&nbsp;
 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.&nbsp; Previo=
usly, the response time was below a second.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Another point is that we populated the new database =
from the old (9.6), using pg_dump.&nbsp; Could this be causing issues?&nbsp=
; Should we load the data from scratch?&nbsp; We use ogr2ogr (GDAL) to help=
 assist with loading of spatial data.&nbsp; Anyway, not
 really sure what the problem is.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Lastly, why am I seeing so many requests as to the P=
ostGIS version.&nbsp; It appears that every map request sends the following=
 query &#8220;SELECT PostGIS_Version();&#8221;, which in turn takes up a co=
nnection.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Any suggestions would be greatly appreciated?&nbsp; =
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal" style=3D"mso-margin-top-alt:auto;margin-bottom:6.0pt=
"><span style=3D"font-size:10.0pt;font-family:&quot;Courier New&quot;">&nbs=
p;&nbsp;&nbsp;&nbsp; __</span><span style=3D"font-size:7.5pt;font-family:Wi=
ngdings;color:#333399">J</span><span style=3D"font-size:12.0pt;font-family:=
&quot;Times New Roman&quot;,serif"><br>
</span><span style=3D"font-size:10.0pt;font-family:&quot;Courier New&quot;"=
>&nbsp;&nbsp; _ \&lt;,_</span><span style=3D"font-size:12.0pt;font-family:&=
quot;Times New Roman&quot;,serif"><br>
</span><span style=3D"font-size:10.0pt;font-family:&quot;Courier New&quot;"=
>&nbsp; (<u>*</u>)/ (<u>*</u>)</span><span style=3D"font-size:12.0pt;font-f=
amily:&quot;Times New Roman&quot;,serif">
<o:p></o:p></span></p>
<p class=3D"MsoNormal">James Lugosi<o:p></o:p></p>
<p class=3D"MsoNormal">Clackamas County GISP<o:p></o:p></p>
<p class=3D"MsoNormal">IS Software Specialist, Senior<o:p></o:p></p>
<p class=3D"MsoNormal">121 Library Court, Oregon City OR 97045<o:p></o:p></=
p>
<p class=3D"MsoNormal">503-723-4829<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_792d4b48d5b2404cb1a521b25536eb49clackamasus_--