Re: Performance issue in PostgreSQL server...

Dear Nur,

The below is the output for 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

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
Unique (cost=1679730.32..1679837.46 rows=21428 width=8) (actual time=154753.528..155657.818 rows=1607489 loops=1)
-> Sort (cost=1679730.32..1679783.89 rows=21428 width=8) (actual time=154753.514..155087.734 rows=4053270 loops=1)
Sort Key: p.feature_id
Sort Method: quicksort Memory: 288302kB
-> Hash Join (cost=1501657.09..1678188.87 rows=21428 width=8) (actual time=144146.620..152050.311 rows=4053270 loops=1)
Hash Cond: (oe.evd_feature_id = p.feature_id)
Join Filter: ((p.modification_time > '2015-05-10 03:36:56.056+05:30'::timestamp with time zone) OR (oe.modification_time > '2015-05-10 03:36:5
6.056+05:30'::timestamp with time zone))
-> Seq Scan on observation_evidence oe (cost=0.00..121733.18 rows=5447718 width=16) (actual time=0.007..1534.905 rows=5434406 loops=1)
-> Hash (cost=1483472.70..1483472.70 rows=1454751 width=16) (actual time=144144.653..144144.653 rows=1607491 loops=1)
Buckets: 262144 Batches: 1 Memory Usage: 75352kB
-> Index Scan using point_domain_class_id_index on point p (cost=0.00..1483472.70 rows=1454751 width=16) (actual time=27.265..142101.1
59 rows=1607491 loops=1)
Index Cond: (domain_class_id = 11)
Total runtime: 155787.379 ms
(13 rows)

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
------------------------------------------------------------------
Mobile: +91-9953975849 | Ext 1078 |dinesh(dot)chandra(at)cyient(dot)com<mailto:%7Cdinesh(dot)chandra(at)cyient(dot)com>
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.

From: Nur Agus [mailto:nuragus(dot)linux(at)gmail(dot)com]
Sent: 03 March, 2017 5:54 PM
To: Dinesh Chandra 12108 <Dinesh(dot)Chandra(at)cyient(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Performance issue in PostgreSQL server...

Hello Dinesh,

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(dot)Chandra(at)cyient(dot)com<mailto:Dinesh(dot)Chandra(at)cyient(dot)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.