Re: Help！Why CPU Usage and LoadAverage Jump up Suddenly

吕晓旭 <lxxstcno1@xxxxxxxxx> · Thu, 5 Dec 2013 17:05:37 +0800

one more word.
high light somthting

performance weird
	: 
...
pg_config:  
CFLAGS = -m64 -march=native -O3 -mavx -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing 
...

2013/12/5 吕晓旭 <lxxstcno1@xxxxxxxxx>

Hi, all
    We find so weird problem on our productive PostgreSQL system. And I don't know how could I do to resolve this problem.

 We deployed PostgreSQL 9.2.4 on two system environments,  and the 
performances between them are absolutely different. one of them it's 
perfect, and the other one lets me down, CPU Usage and LoadAverage 
Jumped up Suddenly when concurrency smoothly rising up, simultaneously, 
average response time become unacceptable. 

    Anyone, who could give me some advice?

    The parameters of system environment and PotgreSQL listed below:
QPS Chart

performance perfect on
	system environment

$ uname -a
Linux l-interdb3.f.cn1 2.6.18-238.19.1.el5 #1 SMP Fri Jul 15 07:31:24 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

$ cat /etc/redhat-release 
CentOS release 5.6 (Final)

$ free -m
             total       used       free     shared    buffers     cached
Mem:         96678      95620       1058          0        443      84607
-/+ buffers/cache:      10568      86109
Swap:        49151          0      49151

$ cat /proc/cpuinfo
processor       : 23
vendor_id       : GenuineIntel
cpu family      : 6
model           : 44
model name      : Intel(R) Xeon(R) CPU           E5645  @ 2.40GHz
stepping        : 2
cpu MHz         : 2400.146
cache size      : 12288 KB
physical id     : 1
siblings        : 12
core id         : 9
cpu cores       : 6
apicid          : 51
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx pdpe1gb rdtscp lm constant_tsc ida nonstop_tsc arat pni monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 4800.10
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: [8]

pg_config

BINDIR = /opt/pg92/bin
DOCDIR = /opt/pg92/share/doc/postgresql
HTMLDIR = /opt/pg92/share/doc/postgresql
INCLUDEDIR = /opt/pg92/include
PKGINCLUDEDIR = /opt/pg92/include/postgresql
INCLUDEDIR-SERVER = /opt/pg92/include/postgresql/server
LIBDIR = /opt/pg92/lib
PKGLIBDIR = /opt/pg92/lib/postgresql
LOCALEDIR = /opt/pg92/share/locale
MANDIR = /opt/pg92/share/man
SHAREDIR = /opt/pg92/share/postgresql
SYSCONFDIR = /opt/pg92/etc/postgresql
PGXS = /opt/pg92/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/opt/pg92' '--with-perl' '--with-libxml' '--with-libxslt' '--with-ossp-uuid' 'CFLAGS= -march=core2 -O2 '
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -march=core2 -O2  -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/opt/pg92/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgport -lxslt -lxml2 -lz -lreadline -ltermcap -lcrypt -ldl -lm 
VERSION = PostgreSQL 9.2.4

performace

performance weird
	system environment(kernel 2.6.32-220 is also tested, no different on performance)
$ uname -a
Linux l-interdb11.f.cn1 3.2.34-1.el6.x86_64 #1 SMP Mon Jan 14 18:23:19 CST 2013 x86_64 x86_64 x86_64 GNU/Linux

$ cat /etc/redhat-release 
CentOS release 6.2 (Final)

$ free -m
             total       used       free     shared    buffers     cached
Mem:         64387      62907       1479          0        182      56524
-/+ buffers/cache:       6200      58186
Swap:        49151        494      48657

$ cat /proc/cpuinfo
processor	: 23
vendor_id	: GenuineIntel
cpu family	: 6
model		: 45
model name	: Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz
stepping	: 7
microcode	: 0x70d
cpu MHz		: 2300.020
cache size	: 15360 KB
physical id	: 1
siblings	: 12
core id		: 5
cpu cores	: 6
apicid		: 43
initial apicid	: 43
fpu		: yes
fpu_exception	: yes
cpuid level	: 13
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx lahf_lm ida arat xsaveopt pln pts dtherm tpr_shadow vnmi flexpriority ept vpid
bogomips	: 4599.34
clflush size	: 64
cache_alignment	: 64
address sizes	: 46 bits physical, 48 bits virtual

pg_config

$ pg_config 
BINDIR = /opt/pg92/bin
DOCDIR = /opt/pg92/share/doc/postgresql
HTMLDIR = /opt/pg92/share/doc/postgresql
INCLUDEDIR = /opt/pg92/include
PKGINCLUDEDIR = /opt/pg92/include/postgresql
INCLUDEDIR-SERVER = /opt/pg92/include/postgresql/server
LIBDIR = /opt/pg92/lib
PKGLIBDIR = /opt/pg92/lib/postgresql
LOCALEDIR = /opt/pg92/share/locale
MANDIR = /opt/pg92/share/man
SHAREDIR = /opt/pg92/share/postgresql
SYSCONFDIR = /opt/pg92/etc/postgresql
PGXS = /opt/pg92/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/opt/pg92' '--with-perl' '--with-libxml' '--with-libxslt' '--with-ossp-uuid' 'CFLAGS= -m64 -march=native -O3 -mavx'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -m64 -march=native -O3 -mavx -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
CFLAGS_SL = -fpic
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/opt/pg92/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgport -lxslt -lxml2 -lz -lreadline -lcrypt -ldl -lm 
VERSION = PostgreSQL 9.2.4

performace
Average Response Time:

Thanks~~