[PERFORM] Performance issue in PostgreSQL server...

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