Re: [PERFORM] Performance issue in PostgreSQL server...

You can try the EXPLAIN tool

psql=> EXPLAIN ANALYZE SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (11) AND (p.modification_time > '2015-05-10 00:06:56.056 IST' OR oe.modification_time > '2015-05-10 00:06:56.056 IST') ORDER BY feature_id

Then paste here the result.

Thanks

On Fri, Mar 3, 2017 at 5:29 PM, Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com> wrote:

Dear Experts,

 

I need your suggestions to resolve the performance issue reported on our PostgreSQL9.1 production database having 1.5 TB Size. I have observed that, some select queries with order by clause are taking lot of time in execution and forcing applications to give slow response.

 

The configuration of database server is :

 

Architecture:         x86_64  

CPU op-mode(s):        32-bit, 64-bit

CPU’s : 8

Core(s) per socket:    4

Socket(s):             2

Model name:            Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz

 

RAM : 32 GB

SWAP :8 Gb

 

Kernel parameter:

 

kernel.shmmax = 32212254720

kernel.shmall = 1073741824

 

 

Values of PostgreSQL.conf parameters are :

 

shared_buffers = 10GB

temp_buffers = 32MB

work_mem = 512MB                             

maintenance_work_mem = 2048MB

max_files_per_process = 2000

checkpoint_segments = 200

max_wal_senders = 5   

wal_buffers = -1                      # min 32kB, -1 sets based on shared_buffers

 

 

Queries taking lot of time are:

==================================

 

 

2017-03-02 00:46:50 IST LOG:  duration: 2492951.927 ms  execute <unnamed>: SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (11) AND (p.modification_time > '2015-05-10 00:06:56.056 IST' OR oe.modification_time > '2015-05-10 00:06:56.056 IST') ORDER BY feature_id

 

 

2017-03-02 01:05:16 IST LOG:  duration: 516250.512 ms  execute <unnamed>: SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id = oe.evd_feature_id WHERE p.domain_class_id IN (3) AND (p.modification_time > '2015-05-10 01:22:59.059 IST' OR oe.modification_time > '2015-05-10 01:22:59.059 IST') ORDER BY feature_id

 

 

Top command output:

 

top - 15:13:15 up 66 days,  3:45,  8 users,  load average: 1.84, 1.59, 1.57

Tasks: 830 total,   1 running, 828 sleeping,   0 stopped,   1 zombie

Cpu(s):  3.4%us,  0.7%sy,  0.0%ni, 81.7%id, 14.2%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:  32830016k total, 32142596k used,   687420k free,    77460k buffers

Swap:  8190972k total,   204196k used,  7986776k free, 27981268k cached

 

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

30639 postgres  20   0 10.5g 4.7g 4.7g S 13.5 14.9  10:20.95 postgres

18185 postgres  20   0 10.5g 603m 596m S  4.9  1.9   2:51.16 postgres

16543 postgres  20   0 10.5g 2.8g 2.8g S  4.3  8.8   1:34.04 postgres

14710 postgres  20   0 10.5g 2.9g 2.9g S  3.9  9.2   1:20.84 postgres

1214 root      20   0 15668 1848  896 S  1.0  0.0 130:46.43 top

13462 postgres  20   0 10.5g 1.4g 1.3g S  1.0  4.3   0:25.56 postgres

20081 root      20   0 15668 1880  936 R  1.0  0.0   0:00.12 top

13478 postgres  20   0 10.5g 2.1g 2.1g S  0.7  6.9   0:56.43 postgres

41107 root      20   0  416m  10m 4892 S  0.7  0.0 305:25.71 pgadmin3

2680 root      20   0     0    0    0 S  0.3  0.0 103:38.54 nfsiod

3558 root      20   0 13688 1100  992 S  0.3  0.0  45:00.36 gam_server

15576 root      20   0     0    0    0 S  0.3  0.0   0:01.16 flush-253:1

18430 postgres  20   0 10.5g  18m  13m S  0.3  0.1   0:00.64 postgres

20083 postgres  20   0  105m 1852 1416 S  0.3  0.0   0:00.01 bash

24188 postgres  20   0  102m 1856  832 S  0.3  0.0   0:23.39 sshd

28250 postgres  20   0  156m 1292  528 S  0.3  0.0   0:46.86 postgres

1 root      20   0 19356 1188  996 S  0.0  0.0   0:05.00 init

 

Regards,

Dinesh Chandra

|Database administrator (Oracle/PostgreSQL)|

 

 

---

DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Check all attachments for viruses before opening them. All views or opinions presented in this e-mail are those of the author and may not reflect the opinion of Cyient or those of our affiliates.