Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VtkeQ-0008WG-BS for pgsql-admin@arkaria.postgresql.org; Thu, 19 Dec 2013 20:56:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VtkeP-0003Co-HV for pgsql-admin@arkaria.postgresql.org; Thu, 19 Dec 2013 20:56:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VtkeN-00039K-2S for pgsql-admin@postgresql.org; Thu, 19 Dec 2013 20:56:55 +0000 Received: from mail-lb0-f174.google.com ([209.85.217.174]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VtkeJ-0000MR-RS for pgsql-admin@postgresql.org; Thu, 19 Dec 2013 20:56:54 +0000 Received: by mail-lb0-f174.google.com with SMTP id y6so710550lbh.19 for ; Thu, 19 Dec 2013 12:56:50 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:date:from:to:message-id:subject:mime-version :content-type; bh=TnACuz9AyWMQ2RjCyPJxOULpnU1EMC0UmitbV1tuKVw=; b=FJBqF1v5JwSY7LmTF3mmfeRx9yBB0Kp6xS4UD6f94E5wtFwnRgpRuQguuPQ6Cdsztd g5OLhRGe8UbSenPwwlz7fWynmT1bIx5IyaQ3U2CcGhK9LR7eyFkU+Kt/0zkWPZiSpLr/ GdwOwTagTt2azizlgj7vxsRzLqN9GZPQbHkN8l+XzQVNzOQs9rOTeaTr/yg7gISt4QUp UPTswBJcL9bAHZsSUQ+vSJ3zcu4UpxU67sJzM018fbcLYWuHr1AB/5K63NQp6vq9sibT EvRjrjXYgMoOJPsA5Iz2+p4Rplcx3AzfmxNNpylGY57+Kxb22gWBTDa4jqDi4JdMElY/ mOnQ== X-Gm-Message-State: ALoCoQn0/tEK1TbEE9FbkZhhNcwsxT9mH2VKSxa4wdtPZo/n/UtTs7XY1BCeFw/C51U13mU6n3E9 X-Received: by 10.112.218.74 with SMTP id pe10mr1743191lbc.19.1387486610728; Thu, 19 Dec 2013 12:56:50 -0800 (PST) Received: from [192.168.1.5] (host-87-255-14-144.bigtelecom.ru. [87.255.14.144]) by mx.google.com with ESMTPSA id di11sm4045077lac.0.2013.12.19.12.56.48 for (version=TLSv1 cipher=RC4-SHA bits=128/128); Thu, 19 Dec 2013 12:56:49 -0800 (PST) Date: Fri, 20 Dec 2013 00:56:47 +0400 From: Vasiliy I Ozerov To: pgsql-admin@postgresql.org Message-ID: <0ECD0DAFC1A24867A84CBF6C25C372F9@2reallife.com> Subject: Postgres 9.2 CPU Usage X-Mailer: sparrow 1.6.4 (build 1176) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="52b35d8f_75c6c33a_95f0" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --52b35d8f_75c6c33a_95f0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Good day=21 Sometimes ago we order new Dell (Dell PowerEdge T720 DX290) server, with = this configuration: 1. 128 Gb DDR3 ECC RAM 2. Dual Intel Xeon E5-2620 Hexa Core incl. Hyper-Thrreading Tecknology 3. 2x 600Gb SSD With PERC H710 mini RAID 1 We store a not big database (around 50 Gb) at postgresql, config: archive=5Fcommand =3D 'cp %p /var/lib/postgresql/9.2/main/archive/%f' archive=5Fmode =3D on autovacuum =3D on autovacuum=5Fmax=5Fworkers =3D '1' checkpoint=5Fcompletion=5Ftarget =3D '0.75' checkpoint=5Fsegments =3D '256' checkpoint=5Ftimeout =3D '45min' constraint=5Fexclusion =3D on data=5Fdirectory =3D '/var/lib/postgresql/9.2/main' datestyle =3D 'iso, mdy' default=5Fstatistics=5Ftarget =3D '50' default=5Ftext=5Fsearch=5Fconfig =3D 'pg=5Fcatalog.english' effective=5Fcache=5Fsize =3D '72GB' enable=5Fseqscan =3D off external=5Fpid=5Ffile =3D '/var/run/postgresql/9.2-main.pid' hba=5Ffile =3D '/etc/postgresql/9.2/main/pg=5Fhba.conf' ident=5Ffile =3D '/etc/postgresql/9.2/main/pg=5Fident.conf' lc=5Fmessages =3D 'en=5FUS.UT=46-8' lc=5Fmonetary =3D 'en=5FUS.UT=46-8' lc=5Fnumeric =3D 'en=5FUS.UT=46-8' lc=5Ftime =3D 'en=5FUS.UT=46-8' listen=5Faddresses =3D '*' log=5Fcheckpoints =3D on log=5Fconnections =3D on log=5Fdirectory =3D '/var/log/postgresql/' log=5Fduration =3D off log=5Fline=5Fprefix =3D '%t ' log=5Flock=5Fwaits =3D on log=5Fmin=5Fduration=5Fstatement =3D '50ms' log=5Fmin=5Ferror=5Fstatement =3D 'notice' log=5Fmin=5Fmessages =3D 'notice' log=5Fstatement =3D 'none' log=5Ftimezone =3D 'GMT-4' logging=5Fcollector =3D on maintenance=5Fwork=5Fmem =3D '1GB' max=5Fconnections =3D '150' max=5Fwal=5Fsenders =3D '3' port =3D '56432' shared=5Fbuffers =3D '24GB' ssl =3D off timezone =3D 'GMT-4' track=5Factivities =3D on track=5Fcounts =3D on track=5Ffunctions =3D 'all' unix=5Fsocket=5Fdirectory =3D '/var/run/postgresql' wal=5Fbuffers =3D '8MB' wal=5Flevel =3D 'hot=5Fstandby' work=5Fmem =3D =E2=80=9832MB' But we have a strange cpu usage by postgresql (LA about 4). But there is = no a lot of requests or heavy requests. We use pgbouncer, so postgresql d= oesn=E2=80=99t accept connections from users, only from pgbouncer. And th= e number of active requests per second is about 5: postgres=3D=23 select count(datid) from pg=5Fstat=5Factivity where state=3D= 'active'; count ------- 5 (1 row) postgres=3D=23 But the time spent to request is about 500 ms (from postgresql log). I tried to strace postgresql and I see the following: root=40server:/var/lib/postgresql=23 strace -c -f -p 41236 Process 41236 attached - interrupt to quit =5ECProcess 41236 detached % time seconds usecs/call calls errors syscall ------ ----------- ----------- --------- --------- ---------------- 50.80 0.000508 3 201 fdatasync 25.40 0.000254 0 82860 lseek 14.60 0.000146 0 1525 sendto 9.20 0.000092 0 737 recvfrom 0.00 0.000000 0 12 read 0.00 0.000000 0 321 write 0.00 0.000000 0 19 3 open 0.00 0.000000 0 17 close 0.00 0.000000 0 2 brk 0.00 0.000000 0 2 setitimer 0.00 0.000000 0 1 kill 0.00 0.000000 0 153 semop ------ ----------- ----------- --------- --------- ---------------- 100.00 0.001000 85850 3 total I think it is some misconfiguration issue, so can you help me with some c= onfig/sysctl options for such server=3F Thank you=21 -- =20 Vasiliy I Ozerov --52b35d8f_75c6c33a_95f0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Good day=21

Sometimes a= go we order new Dell (Dell PowerEdge T720 DX290)&nbs= p;server, with this configuration:

=
1. 128 Gb DDR3 ECC RAM
2. Dual Intel Xeon E5-2620 Hexa Cor= e incl. Hyper-Thrreading Tecknology
3. 2x 600Gb SSD With PERC H= 710 mini RAID 1

We store a not big database (aro= und 50 Gb) at postgresql, config:


archive=5Fcommand =3D 'cp %p /var/lib/postgresql/9.2/main/archive/%= f'
archive=5Fmode =3D on
autovacuum =3D on
= autovacuum=5Fmax=5Fworkers =3D '1'
checkpoint=5Fcompletion=5Fta= rget =3D '0.75'
checkpoint=5Fsegments =3D '256'
check= point=5Ftimeout =3D '45min'
constraint=5Fexclusion =3D on
=
data=5Fdirectory =3D '/var/lib/postgresql/9.2/main'
datest= yle =3D 'iso, mdy'
default=5Fstatistics=5Ftarget =3D '50'
=
default=5Ftext=5Fsearch=5Fconfig =3D 'pg=5Fcatalog.english'
effective=5Fcache=5Fsize =3D '72GB'
enable=5Fseqscan =3D off<= /div>
external=5Fpid=5Ffile =3D '/var/run/postgresql/9.2-main.pid'
hba=5Ffile =3D '/etc/postgresql/9.2/main/pg=5Fhba.conf'
ident=5Ffile =3D '/etc/postgresql/9.2/main/pg=5Fident.conf'
lc= =5Fmessages =3D 'en=5FUS.UT=46-8'
lc=5Fmonetary =3D 'en=5FUS.UT= =46-8'
lc=5Fnumeric =3D 'en=5FUS.UT=46-8'
lc=5Ftime =3D= 'en=5FUS.UT=46-8'
listen=5Faddresses =3D '*'
log=5Fc= heckpoints =3D on
log=5Fconnections =3D on
log=5Fdire= ctory =3D '/var/log/postgresql/'
log=5Fduration =3D off
log=5Fline=5Fprefix =3D '%t '
log=5Flock=5Fwaits =3D on
log=5Fmin=5Fduration=5Fstatement =3D '50ms'
log=5Fmin=5Fe= rror=5Fstatement =3D 'notice'
log=5Fmin=5Fmessages =3D 'notice'=
log=5Fstatement =3D 'none'
log=5Ftimezone =3D 'GMT-4= '
logging=5Fcollector =3D on
maintenance=5Fwork=5Fmem= =3D '1GB'
max=5Fconnections =3D '150'
max=5Fwal=5Fse= nders =3D '3'
port =3D '56432'
shared=5Fbuffers =3D '= 24GB'
ssl =3D off
timezone =3D 'GMT-4'
trac= k=5Factivities =3D on
track=5Fcounts =3D on
track=5Ff= unctions =3D 'all'
unix=5Fsocket=5Fdirectory =3D '/var/run/post= gresql'
wal=5Fbuffers =3D '8MB'
wal=5Flevel =3D 'hot=5F= standby'
work=5Fmem =3D =E2=80=9832MB'

But we have a strange cpu usage by postgresql (LA about 4). But th= ere is no a lot of requests or heavy requests. We use pgbouncer, so postg= resql doesn=E2=80=99t accept connections from users, only from pgbouncer.= And the number of active requests per second is about 5:

<= /div>
postgres=3D=23 select count(datid) from pg=5Fstat=5Factivi= ty where state=3D'active';
 count
-------
<= div>     5
(1 row)

post= gres=3D=23

But the time spent to request i= s about 500 ms (from postgresql log).

I tried to= strace postgresql and I see the following:

root=40server:/var/lib/postgresql=23 strace -c -f -p 41236
Pro= cess 41236 attached - interrupt to quit
=5ECProcess 41236 detac= hed
% time     seconds  usecs/call    = calls    errors syscall
------ ----------- ---------= -- --------- --------- ----------------
 50.80   &nbs= p;0.000508           3       201 =           fdatasync
 25.40  =  0.000254           0     82860 =           lseek
 14.60   &nb= sp;0.000146           0      1525=           sendto
  9.20   &= nbsp;0.000092           0       7= 37           recvfrom
  0.00 &nbs= p;  0.000000           0     &nbs= p;  12           read
  0.00=    0.000000           0    =   321           write
  0.0= 0    0.000000           0    = ;    19         3 open
  0.0= 0    0.000000           0    = ;    17           close
&nbs= p; 0.00    0.000000           0  =       2           brk
=   0.00    0.000000           0 &n= bsp;       2           setitimer<= /div>
  0.00    0.000000         &= nbsp; 0         1           = kill
  0.00    0.000000       &nb= sp;   0       153           = semop
------ ----------- ----------- --------- --------- ------= ----------
100.00    0.001000       &n= bsp;         85850         3 tota= l

I think it is some misconfiguration= issue, so can you help me with some config/sysctl options for such serve= r=3F

Thank you=21


-- 
Vasiliy I Oze= rov

--52b35d8f_75c6c33a_95f0--