Re: Postgres DB Slowness - Mailing list pgsql-admin

From Jehan-Guillaume (ioguix) de Rorthais
Subject Re: Postgres DB Slowness
Date
Msg-id 20190822223028.5d1a30f3@firost
Whole thread Raw
In response to RE: Postgres DB Slowness  (<soumik.bhattacharjee@kpn.com>)
List pgsql-admin
On Thu, 22 Aug 2019 15:25:50 +0000
<soumik.bhattacharjee@kpn.com> wrote:
> On 8/22/19 9:04 AM,
> soumik.bhattacharjee@kpn.com<mailto: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.
>
[...]

> Table definition is attached here.
>
> EXPLAIN SELECT *
>                 FROM npcurren.num_aangesloten_nr;
>
> "Seq Scan on num_aangesloten_nr  (cost=0.00..268192.46 rows=9649046
> width=113)"

First, this query can not use any kind of index as it just returns the whole
table.

Second, does your table really have about 10 million rows? If yes, do not
expect to have a result in 0.009 milliseconds. As Holger Jakobs wrote, maybe
you compare the time to return the very first row? I can't believe Oracle can
provide 10Mo rows in 9µs.

[...]
>
> The select * from query takes all of the 2 CPU’s.
>
>
> top - 17:24:33 up 42 days,  5:12,  1 user,  load average: 0.43, 0.54, 0.57
> Tasks: 227 total,   4 running, 223 sleeping,   0 stopped,   0 zombie
> %Cpu(s): 35.2 us,  6.9 sy,  0.0 ni, 51.9 id,  0.0 wa,  0.0 hi,  6.0 si,  0.0
> st KiB Mem : 32947032 total, 29998788 free,   375068 used,  2573176 buff/cache
> KiB Swap:  2097148 total,  1953380 free,   143768 used. 30803956 avail Mem
>
>   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
> 11331 postgres  20   0 5976024  16036  13432 R  92.1  0.0   0:13.98 postgres
> 11163 postgres  20   0 5981000  16764   9088 S   1.3  0.1   0:01.24 postgres

No. it takes one core. In top 100% means "one core is burning somewhere". On
your "2 CPU" server, %CPU can go as high as 200%.

Regards,



pgsql-admin by date:

Previous
From: Adarsh Sharma
Date:
Subject: Re: Postgres DB Slowness
Next
From: Jeff Janes
Date:
Subject: Re: Postgres DB Slowness