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

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(at)gmail(dot)com>

>
>
> 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
> - [image: 内嵌图片 1]
>
>
> - 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
> - [image: 内嵌图片 2]
> - [image: 内嵌图片 3]
>
>
> - 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
> - [image: 内嵌图片 4]
> - [image: 内嵌图片 5]
> - Average Response Time:
> - [image: 内嵌图片 1]
>
>
> Thanks~~
>