Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cjkV9-0002UJ-2u for pgsql-performance@arkaria.postgresql.org; Fri, 03 Mar 2017 10:31:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cjkV8-0008Bi-KR for pgsql-performance@arkaria.postgresql.org; Fri, 03 Mar 2017 10:31:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cjkTJ-0004iB-0H for pgsql-performance@postgresql.org; Fri, 03 Mar 2017 10:30:01 +0000 Received: from mxgate01.infotech-enterprises.com ([110.76.168.168] helo=mxgate01.cyient.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cjkTF-0002TV-6c for pgsql-performance@postgresql.org; Fri, 03 Mar 2017 10:29:59 +0000 X-IronPort-AV: E=Sophos;i="5.35,236,1483986600"; d="scan'208,217";a="63888418" Received: from unknown (HELO INFHYDEX10AHT2.corp.infotech-enterprises.com) ([172.17.0.115]) by mxgate01.cyient.com with ESMTP/TLS/DHE-RSA-AES256-SHA; 03 Mar 2017 15:46:05 +0530 Received: from CYINMBX2.CORP.CYIENT.COM (172.17.4.162) by INFHYDEX10AHT2.corp.infotech-enterprises.com (172.17.0.115) with Microsoft SMTP Server (TLS) id 14.3.158.1; Fri, 3 Mar 2017 15:59:51 +0530 Received: from CYINMBX4.CORP.CYIENT.COM (172.17.4.164) by CYINMBX2.CORP.CYIENT.COM (172.17.4.162) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.544.27; Fri, 3 Mar 2017 15:59:48 +0530 Received: from CYINMBX4.CORP.CYIENT.COM ([fe80::e40c:d4fe:9e0c:f465]) by CYINMBX4.CORP.CYIENT.COM ([fe80::e40c:d4fe:9e0c:f465%19]) with mapi id 15.01.0544.027; Fri, 3 Mar 2017 15:59:49 +0530 From: Dinesh Chandra 12108 To: "pgsql-performance@postgresql.org" Subject: Performance issue in PostgreSQL server... Thread-Topic: Performance issue in PostgreSQL server... Thread-Index: AdKUCRR1R30VhxuMT2utF4ZoBbTRiA== Date: Fri, 3 Mar 2017 10:29:49 +0000 Message-ID: <0796d1d53d3e4aa692cfdc329f86b399@cyient.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.17.4.81] Content-Type: multipart/alternative; boundary="_000_0796d1d53d3e4aa692cfdc329f86b399cyientcom_" MIME-Version: 1.0 X-Pg-Spam-Score: -3.5 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_0796d1d53d3e4aa692cfdc329f86b399cyientcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Dear Experts, I need your suggestions to resolve the performance issue reported on our Po= stgreSQL9.1 production database having 1.5 TB Size. I have observed that, s= ome 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 =3D 32212254720 kernel.shmall =3D 1073741824 Values of PostgreSQL.conf parameters are : shared_buffers =3D 10GB temp_buffers =3D 32MB work_mem =3D 512MB maintenance_work_mem =3D 2048MB max_files_per_process =3D 2000 checkpoint_segments =3D 200 max_wal_senders =3D 5 wal_buffers =3D -1 # min 32kB, -1 sets based on shared= _buffers Queries taking lot of time are: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D 2017-03-02 00:46:50 IST LOG: duration: 2492951.927 ms execute : = SELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observ= ation_evidence oe ON p.feature_id =3D oe.evd_feature_id WHERE p.domain_clas= s_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 : S= ELECT DISTINCT feature_id FROM evidence.point p INNER JOIN evidence.observa= tion_evidence oe ON p.feature_id =3D 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.m= odification_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 t= hem. 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. --_000_0796d1d53d3e4aa692cfdc329f86b399cyientcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Dear Experts,

 

I need your suggestions to resolve the perfor= mance 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 t= ime in execution and forcing applications to give slow response.

 

The configuration of database server is :

 

Architecture:     &n= bsp;   x86_64  

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

CPU’s : 8

Core(s) per socket:    4

Socket(s):      = ;       2

Model name:     &nbs= p;      Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz

 

RAM : 32 GB

SWAP :8 Gb

 

Kernel parameter:=

 

kernel.shmmax =3D 32212254720

kernel.shmall =3D 1073741824

 

 

Values of PostgreSQL.conf parameters are :

 

shared_buffers =3D 10GB

temp_buffers =3D 32MB

work_mem =3D 512MB    &nb= sp;            =             

maintenance_work_mem =3D 2048MB

max_files_per_process =3D 2000

checkpoint_segments =3D 200

max_wal_senders =3D 5   

wal_buffers =3D -1    &nb= sp;            =      # min 32kB, -1 sets based on shared_buffers=

 

 

Queries taking lot of time are:=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

 

 

2017-03-02 00:46:50 IST LOG:  duration: = 2492951.927 ms  execute <unnamed>: SELECT DISTINCT feature_id FR= OM evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id =3D 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 FRO= M evidence.point p INNER JOIN evidence.observation_evidence oe ON p.feature_id =3D oe.evd_feature_id WHERE p.domain_class_id IN (3) AND (p.m= odification_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 tot= al, 32142596k used,   687420k free,    7= 7460k buffers

Swap:  8190972k total,   20419= 6k used,  7986776k free, 27981268k cached

 

  PID USER     = PR  NI  VIRT  RES  SHR S %CPU %MEM    T= IME+  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 6= 03m 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&nb= sp;  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&n= bsp;  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&n= bsp;  0  416m  10m 4892 S  0.7  0.0 305:25.71 pgad= min3

2680 root      20&nb= sp;  0     0    0   = 0 S  0.3  0.0 103:38.54 nfsiod

3558 root      20&nb= sp;  0 13688 1100  992 S  0.3  0.0  45:00.36 gam_s= erver

15576 root      20&n= bsp;  0     0    0   = ; 0 S  0.3  0.0   0:01.16 flush-253:1

18430 postgres  20   0 10.5g&n= bsp; 18m  13m S  0.3  0.1   0:00.64 postgres

20083 postgres  20   0  1= 05m 1852 1416 S  0.3  0.0   0:00.01 bash

24188 postgres  20   0  1= 02m 1856  832 S  0.3  0.0   0:23.39 sshd

28250 postgres  20   0  1= 56m 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 ini= t

 

Regards,

Dinesh Chandra

|Database administrator (Oracle/Pos= tgreSQL)|

 



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 a= ttachments 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.
 --_000_0796d1d53d3e4aa692cfdc329f86b399cyientcom_--