Re: 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> 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.
>