Re: Postgres DB Slowness

Can you please paste explain analyze SELECT *
FROM npcurren.num_aangesloten_nr output?

On Thu, Aug 22, 2019 at 8:57 PM <soumik(dot)bhattacharjee(at)kpn(dot)com> wrote:

>
>
>
>
> *From:* Bhattacharjee, Soumik
> *Sent:* Thursday, August 22, 2019 5:05 PM
> *To:* 'Ron' <ronljohnsonjr(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* RE: Postgres DB Slowness
>
>
>
> *From:* Ron <ronljohnsonjr(at)gmail(dot)com> <ronljohnsonjr(at)gmail(dot)com>
> *Sent:* Thursday, August 22, 2019 4:26 PM
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Postgres DB Slowness
>
>
>
> On 8/22/19 9:04 AM, soumik(dot)bhattacharjee(at)kpn(dot)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?)
>
>
>
> 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)"
>
>
>
>
>
> - 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.6 (Maipo)
>
> $ uname -a
>
> Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15
> 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
>
>
>
>
>
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>