Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0ooj-0001vj-OK for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2019 15:16:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i0ooi-0004UC-AZ for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2019 15:16:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0ooh-0004Qq-TY for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2019 15:15:59 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0oof-0003Dd-2u for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2019 15:15:59 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id C9C55BE0A81 for ; Thu, 22 Aug 2019 17:15:55 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at rs.plausibolo.de Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id xdW6tzvWDjOW for ; Thu, 22 Aug 2019 17:15:53 +0200 (CEST) Received: from [10.54.127.245] (x59cc9bf5.dyn.telefonica.de [89.204.155.245]) by rs.plausibolo.de (Postfix) with ESMTPSA id 23215BE017C for ; Thu, 22 Aug 2019 17:15:53 +0200 (CEST) Date: Thu, 22 Aug 2019 17:15:51 +0200 User-Agent: K-9 Mail for Android In-Reply-To: <2A91BEF8171A5349931391E0C721CC5359E750C4@CPEMS-KPN301.KPNCNL.LOCAL> References: <2A91BEF8171A5349931391E0C721CC5359E73FC3@CPEMS-KPN301.KPNCNL.LOCAL> <9840f6c7-5644-1928-e224-b06ecd9297eb@gmail.com> <2A91BEF8171A5349931391E0C721CC5359E75079@CPEMS-KPN301.KPNCNL.LOCAL> <71b2a6be-9aae-04bb-d006-c46ded2ab243@gmail.com> <2A91BEF8171A5349931391E0C721CC5359E750C4@CPEMS-KPN301.KPNCNL.LOCAL> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----LX1GE4QJDJEL56IS7MBT1BALKHPTTD" Content-Transfer-Encoding: 7bit Subject: RE: Postgres DB Slowness To: pgsql-admin@lists.postgresql.org From: Holger Jakobs Message-ID: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------LX1GE4QJDJEL56IS7MBT1BALKHPTTD Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Could it be that you are comparing the time until first row to time until a= ll rows have been transferred? Otherwise the times don't seem sensible=2E Am 22=2E August 2019 17:04:19 MESZ schrieb soumik=2Ebhattacharjee@kpn=2Eco= m: >From: Ron >Sent: Thursday, August 22, 2019 4:26 PM >To: >pgsql-admin@lists=2Epostgresql=2Eorg >Subject: Re: Postgres DB Slowness > >On 8/22/19 9:04 AM, >soumik=2Ebhattacharjee@kpn=2Ecom >wrote: > > >Hello Members, > >We have the below PostgreSQL database recently migrated from Oracle=2E > >The postgres DB parameters are attached here=2E > > ># DB Version: 10 > ># OS Type: Linux > ># Total Memory (RAM): 30 GB >#CPU >nproc --all >2 > >There is huge slowness in the database now with any queries=2E > >Oracle - Select * from TABLENAME- in takes 0=2E009 milliseconds >PostgreSQL - Same query takes more than 2 minutes=2E > >Have you validated that the Postgres server has the same indexes? > >If so, did you ANALYZE all the tables? > >Yes Ron, we migrated from Oracle as per same structure and indexes=2E > >I did the analyze for 2-3 tables now as per my SQL query I wrote and >also ran for one particular table below =E2=80=93 > >VACUUM (FULL,ANALYZE) table_name=2E > >What does the query plan for that query look like? > >What's the table definition? > >Is the hardware comparable? (Might it be a SAN or VM issue?) > >Table definition is attached here=2E > >EXPLAIN SELECT * > FROM npcurren=2Enum_aangesloten_nr; > >"Seq Scan on num_aangesloten_nr (cost=3D0=2E00=2E=2E268192=2E46 rows=3D9= 649046 >width=3D113)" > > > >- Oracle 11gR2 was hosted on physical HP-UX server last >updated 12 years back- legacy system > >- Current postgres server- physical > >$ cat /etc/redhat-release > >Red Hat Enterprise Linux Server release 7=2E6 (Maipo) > >$ uname -a > >Linux slnc7r1513=2Edb=2Egen=2Elocal 3=2E10=2E0-957=2E1=2E3=2Eel7=2Ex86_64= #1 SMP Thu Nov >15 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux > >[postgres@slnc7r1513 ~]$ --=20 Holger Jakobs, Bergisch Gladbach +49 178 9759012 - sent from mobile, therefore short - ------LX1GE4QJDJEL56IS7MBT1BALKHPTTD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Cou= ld it be that you are comparing the time until first row to time until all = rows have been transferred?

Otherwise the times don't seem sensible= =2E


Am 22=2E August 2019 17:04:19 MES= Z schrieb soumik=2Ebhattacharjee@kpn=2Ecom:

From: Ron <ronljohnsonjr@gmail=2Ecom= >
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-a= dmin@lists=2Epostgresql=2Eorg
Subject: Re: Postgres DB Slowness<= /o:p>

 

On 8/22/19 9:04 AM, soumik=2Ebhattacharjee@kpn=2Ecom wrote:


Hello Members,

 

We have the below PostgreSQL d= atabase recently migrated from Oracle=2E

 

The postgres DB parameters are= attached here=2E

 

# DB Version: 10

# OS Type: Linux

# Total Memory (RAM): 30 GB=

#CPU

nproc --all<= /p>

2

 

There is huge slowness in the = database now with any queries=2E

 

Oracle - Select * from TABLENAME- in takes  0=2E009 milliseconds<= /span>

PostgreSQL - Same query takes more than 2 minutes=2E=


Have you validated that the Postgres server has the same indexes?

If so, did you ANALYZE all the tables?

 <= /span>

Yes Ron, we migrated from Oracle as per same structure an= d indexes=2E

 

I did the analyze for 2-3 tables now as per my SQL query = I wrote and also ran for one particular table below =E2=80=93

 

VACUUM (FULL,ANALYZE) table_name=2E


What does the query plan for that query look like?

What's the table definition?

Is the hardware comparable?  (Might it be a SAN or VM issue?)

&n= bsp;

Table definition is attached here=2E=

 

EXPLAIN SELECT *

         =        FROM npcurren=2Enum_aangesloten_nr;

 

"Seq Scan on num_aangesloten_nr  (cost=3D0=2E00= =2E=2E268192=2E46 rows=3D9649046 width=3D113)"

 

 

-    &nb= sp;     Oracle 11gR2  was hosted on physical = HP-UX server last updated 12 years back- legacy system

-    &nb= sp;     Current postgres server- physical

$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7=2E6 (= Maipo)

$ uname -a

Linux slnc7r1513=2Edb=2Egen=2Elocal 3=2E10=2E0-= 957=2E1=2E3=2Eel7=2Ex86_64 #1 SMP Thu Nov 15 17:36:42 UTC 2018 x86_64 x86_6= 4 x86_64 GNU/Linux

[postgres@slnc7r1513 ~]$

 

 

 

 

 

 

 

 


--
Holger Jakobs, Bergisch Gladbach
+49 178 = 9759012
- sent from mobile, therefore short -
------LX1GE4QJDJEL56IS7MBT1BALKHPTTD--