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 1i0oTL-0000we-6E for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2019 14:53:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1i0oTK-0002ce-1x for pgsql-admin@arkaria.postgresql.org; Thu, 22 Aug 2019 14:53:54 +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 1i0oTJ-0002PD-HL for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2019 14:53:53 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1i0oTH-0002mU-6w for pgsql-admin@lists.postgresql.org; Thu, 22 Aug 2019 14:53:53 +0000 Received: by mail-oi1-x235.google.com with SMTP id y8so4553083oih.10 for ; Thu, 22 Aug 2019 07:53:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=xzS2nSVncC86e558HgzViMwU8VCAzWZHZqxARic/+9A=; b=iHfckpk435N1rpLuRrslKim4mfjh3R2IG50rwpU/3239JZMRjVIs+jbCfhAdFgWwgw 9iBcqe6JDBDzYOtPbmNNyy2+KE3Gxzik69+cy0zBzgk5ikWnCT9m8ZHBSTlUPUUXq8o4 J+mhLjZro4SUdXypbkZwwmtih0b0y1gLuN4Ys/evl9cr1+LRnXczsCpDhnZDJS7lwQou 322NoWkC3+D/IoJnrsdGh2pWNZlpJxlnWq4VkXvkGALmmCgIIAp353/Ud2bWxqy8pNnr +PxDPCKCy+7c/pvzDai4k6R07D3g8+Sbfx+LxDXy2m+cOzuPmimq6PN/IhoMHApEd7LU Hxig== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=xzS2nSVncC86e558HgzViMwU8VCAzWZHZqxARic/+9A=; b=cR0Umnm/wf375r6PQ3fmIcocugpGtWHwPzPRsT3nmGF/ofAn8osAjqczAeD+zE3Pt4 C1YAwTLQ5IvgOY0NS4eXkdZ1fUuetY+gNGxqrqNFmpnu/GwBmOo/gMuPmk3Lsomxpene eNfza0PVZvsScvqs27CRity+RCi7Lu286z3/QQjmkrCK3BraCkM3Ybfb/CuqyRejM1C8 1lkgZQ5VEfJODmU9Y6eCFIX4JI9KqcF1OYwjVgM8JX40LX3X2UBacCOq75zL9erduLrk 9muFjSDtXPt8LeHb2K3j407i6bUfvNBgDPrpBszJlMkOkR0wft9eI6K+cqCtpeIxDzz7 Xayw== X-Gm-Message-State: APjAAAVh6KPOmkmDc+A2nqaaxgqtN3c/EGOA7ZZoeGqn6yxqAfE6BN6v cF0iS/TQxPTeopFi8y7rw+iMIdU0/zkANg== X-Google-Smtp-Source: APXvYqzEJfx7PzArBFAIYMOQrH61z2KQ5NbmU240SEAGEjbdKmmhs2vjBjkrPnDiEhcn6VHCksOZjg== X-Received: by 2002:a54:4f8a:: with SMTP id g10mr4057322oiy.63.1566485215189; Thu, 22 Aug 2019 07:46:55 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id r13sm5922895oic.56.2019.08.22.07.46.53 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 22 Aug 2019 07:46:54 -0700 (PDT) Subject: Re: Postgres DB Slowness To: pgsql-admin@lists.postgresql.org References: <2A91BEF8171A5349931391E0C721CC5359E73FC3@CPEMS-KPN301.KPNCNL.LOCAL> <9840f6c7-5644-1928-e224-b06ecd9297eb@gmail.com> <2A91BEF8171A5349931391E0C721CC5359E75079@CPEMS-KPN301.KPNCNL.LOCAL> From: Ron Message-ID: <71b2a6be-9aae-04bb-d006-c46ded2ab243@gmail.com> Date: Thu, 22 Aug 2019 09:46:53 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: <2A91BEF8171A5349931391E0C721CC5359E75079@CPEMS-KPN301.KPNCNL.LOCAL> Content-Type: multipart/alternative; boundary="------------5754EE4E75822BA0B4C82DC1" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------5754EE4E75822BA0B4C82DC1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 8/22/19 9:35 AM, soumik.bhattacharjee@kpn.com wrote: > > *From:*Ron > *Sent:* Thursday, August 22, 2019 4:26 PM > *To:* pgsql-admin@lists.postgresql.org > *Subject:* Re: Postgres DB Slowness > > On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com > wrote: > > Hello Members, > > We have the below PostgreSQL database recently migrated from Oracle. > > The postgres DB parameters are *_attached_* here. > > # 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. > > *Oracle*- Select * from TABLENAME- in takes  0.009 milliseconds > > *PostgreSQL*- Same query takes more than 2 minutes. > > > 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. > > I did the analyze for 2-3 tables now as per my SQL query I wrote and also > ran for one particular table below – > > VACUUM (FULL,ANALYZE) table_name. > 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?) -- Angular momentum makes the world go 'round. --------------5754EE4E75822BA0B4C82DC1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 8/22/19 9:35 AM, soumik.bhattacharjee@kpn.com wrote:

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

On 8/22/19 9:04 AM, soumik.bhattacharjee@kpn.com wrote:

Hello Members,

 

We have the below PostgreSQL database recently migrated from Oracle.

 

The postgres DB parameters are attached here.

 

# 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.

 

Oracle - Select * from TABLENAME- in takes  0.009 milliseconds

PostgreSQL - Same query takes more than 2 minutes.


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.

 

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

 

VACUUM (FULL,ANALYZE) table_name.


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?)

--
Angular momentum makes the world go 'round.
--------------5754EE4E75822BA0B4C82DC1--