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.
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:
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
- system environment
- 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:
- system environment(kernel 2.6.32-220 is also tested, no different on performance)
