pg93@db-172-16-3-150-> numactl --showpolicy: defaultpreferred node: currentphyscpubind: 0 1 2 3 4 5 6 7 cpubind: 0 nodebind: 0 membind: 0 pg93@db-172-16-3-150-> cat /proc/cpuinfo processor       : 0vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 1siblings        : 4core id         : 0cpu cores       : 4apicid          : 16initial apicid  : 16fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.98clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 1vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 0siblings        : 4core id         : 0cpu cores       : 4apicid          : 0initial apicid  : 0fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.50clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 2vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 1siblings        : 4core id         : 1cpu cores       : 4apicid          : 18initial apicid  : 18fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.98clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 3vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 0siblings        : 4core id         : 1cpu cores       : 4apicid          : 2initial apicid  : 2fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.50clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 4vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 1siblings        : 4core id         : 2cpu cores       : 4apicid          : 20initial apicid  : 20fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.98clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 5vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 0siblings        : 4core id         : 2cpu cores       : 4apicid          : 4initial apicid  : 4fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.50clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 6vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 1siblings        : 4core id         : 3cpu cores       : 4apicid          : 22initial apicid  : 22fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.98clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:processor       : 7vendor_id       : GenuineIntelcpu family      : 6model           : 26model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHzstepping        : 5cpu MHz         : 1595.992cache size      : 4096 KBphysical id     : 0siblings        : 4core id         : 3cpu cores       : 4apicid          : 6initial apicid  : 6fpu             : yesfpu_exception   : yescpuid level     : 11wp              : yesflags           : 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 rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 popcnt lahf_lm dts tpr_shadow vnmi flexpriority ept vpidbogomips        : 3191.50clflush size    : 64cache_alignment : 64address sizes   : 40 bits physical, 48 bits virtualpower management:

[root@db-172-16-3-150 ~]# dmidecode -t processor# dmidecode 2.11SMBIOS 2.6 present.Handle 0x0400, DMI type 4, 40 bytesProcessor Information        Socket Designation: CPU1        Type: Central Processor        Family: Xeon        Manufacturer: Intel        ID: A5 06 01 00 FF FB EB BF        Signature: Type 0, Family 6, Model 26, Stepping 5        Flags:                FPU (Floating-point unit on-chip)                VME (Virtual mode extension)                DE (Debugging extension)                PSE (Page size extension)                TSC (Time stamp counter)                MSR (Model specific registers)                PAE (Physical address extension)                MCE (Machine check exception)                CX8 (CMPXCHG8 instruction supported)                APIC (On-chip APIC hardware supported)                SEP (Fast system call)                MTRR (Memory type range registers)                PGE (Page global enable)                MCA (Machine check architecture)                CMOV (Conditional move instruction supported)                PAT (Page attribute table)                PSE-36 (36-bit page size extension)                CLFSH (CLFLUSH instruction supported)                DS (Debug store)                ACPI (ACPI supported)                MMX (MMX technology supported)                FXSR (FXSAVE and FXSTOR instructions supported)                SSE (Streaming SIMD extensions)                SSE2 (Streaming SIMD extensions 2)                SS (Self-snoop)                HTT (Multi-threading)                TM (Thermal monitor supported)                PBE (Pending break enabled)        Version: Intel(R) Xeon(R) CPU           E5504  @ 2.00GHz        Voltage: 1.2 V        External Clock: 4800 MHz        Max Speed: 3600 MHz        Current Speed: 1600 MHz        Status: Populated, Enabled        Upgrade: Socket LGA1366        L1 Cache Handle: 0x0700        L2 Cache Handle: 0x0701        L3 Cache Handle: 0x0702        Serial Number: Not Specified        Asset Tag: Not Specified        Part Number: Not Specified        Core Count: 4        Core Enabled: 4        Thread Count: 4        Characteristics:                64-bit capableHandle 0x0401, DMI type 4, 40 bytesProcessor Information        Socket Designation: CPU2        Type: Central Processor        Family: Xeon        Manufacturer: Intel        ID: A5 06 01 00 FF FB EB BF        Signature: Type 0, Family 6, Model 26, Stepping 5        Flags:                FPU (Floating-point unit on-chip)                VME (Virtual mode extension)                DE (Debugging extension)                PSE (Page size extension)                TSC (Time stamp counter)                MSR (Model specific registers)                PAE (Physical address extension)                MCE (Machine check exception)                CX8 (CMPXCHG8 instruction supported)                APIC (On-chip APIC hardware supported)                SEP (Fast system call)                MTRR (Memory type range registers)                PGE (Page global enable)                MCA (Machine check architecture)                CMOV (Conditional move instruction supported)                PAT (Page attribute table)                PSE-36 (36-bit page size extension)                CLFSH (CLFLUSH instruction supported)                DS (Debug store)                ACPI (ACPI supported)                MMX (MMX technology supported)                FXSR (FXSAVE and FXSTOR instructions supported)                SSE (Streaming SIMD extensions)                SSE2 (Streaming SIMD extensions 2)                SS (Self-snoop)                HTT (Multi-threading)                TM (Thermal monitor supported)                PBE (Pending break enabled)        Version: Intel(R) Xeon(R) CPU           E5504  @ 2.00GHz        Voltage: 1.2 V        External Clock: 4800 MHz        Max Speed: 3600 MHz        Current Speed: 1600 MHz        Status: Populated, Idle        Upgrade: Socket LGA1366        L1 Cache Handle: 0x0703        L2 Cache Handle: 0x0704        L3 Cache Handle: 0x0705        Serial Number: Not Specified        Asset Tag: Not Specified        Part Number: Not Specified        Core Count: 4        Core Enabled: 4        Thread Count: 4        Characteristics:                64-bit capable[root@db-172-16-3-150 ~]# cat /proc/cpuinfo |grep "core id"core id         : 0core id         : 0core id         : 1core id         : 1core id         : 2core id         : 2core id         : 3core id         : 3[root@db-172-16-3-150 ~]# cat /proc/cpuinfo |grep "physical id"physical id     : 1physical id     : 0physical id     : 1physical id     : 0physical id     : 1physical id     : 0physical id     : 1physical id     : 0

pg93@db-172-16-3-150-> taskset -c 0 /home/pg93/pgsql9.3.1/bin/postgres >/dev/null 2>&1

pg93@db-172-16-3-150-> taskset -c 1 psqlpsql (9.3.1)Type "help" for help.digoal=# select pg_backend_pid(); pg_backend_pid ----------------          24781(1 row)

[root@db-172-16-3-150 ~]# taskset -c 3 stap -e 'global aprobe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start") {  delete a  println("query__start ", user_string($arg1), "pid:", pid())}probe vfs.read.return {  t = gettimeofday_ns() - @entry(gettimeofday_ns())  # if (execname() == "postgres" && devname != "N/A")  # 这句判断也有一定的影响, 所以不用. 我们使用target模式.    a[pid()] <<< t}probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__done") {  printdln("**", pid(), @count(a[pid()]), @avg(a[pid()]))  println("query__done ", user_string($arg1), "pid:", pid())  delete a}' -x 24781

digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.023..3154.782 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared read=94393 Total runtime: 4710.788 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.040..3151.680 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=32 read=94361 Total runtime: 4696.888 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.035..3158.134 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=64 read=94329 Total runtime: 4706.135 ms(4 rows)

query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2478124781**94417**5461query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24781query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2478124781**94361**5429query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24781query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2478124781**94329**5529query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24781

pg93@db-172-16-3-150-> taskset -c 3 /home/pg93/pgsql9.3.1/bin/postgres >/dev/null 2>&1pg93@db-172-16-3-150-> taskset -c 3 psqlpsql (9.3.1)Type "help" for help.digoal=# select pg_backend_pid(); pg_backend_pid ----------------          24829(1 row)[root@db-172-16-3-150 ~]# taskset -c 3 stap -e 'global aprobe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__start") {  delete a  println("query__start ", user_string($arg1), "pid:", pid())}probe vfs.read.return {  t = gettimeofday_ns() - @entry(gettimeofday_ns())  # if (execname() == "postgres" && devname != "N/A")  # 这句判断也有一定的影响, 所以不用. 建议使用target模式.    a[pid()] <<< t}probe process("/home/pg93/pgsql9.3.1/bin/postgres").mark("query__done") {  printdln("**", pid(), @count(a[pid()]), @avg(a[pid()]))  println("query__done ", user_string($arg1), "pid:", pid())  delete a}' -x 24829digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.023..2039.248 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared read=94393 Total runtime: 3065.989 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.036..2039.643 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=32 read=94361 Total runtime: 3066.841 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.034..2039.005 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=64 read=94329 Total runtime: 3066.746 ms(4 rows)query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2482924829**94417**3277query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24829query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2482924829**94361**3272query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24829query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2482924829**94329**3273query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24829

digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.055..2032.953 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=96 read=94297 Total runtime: 3059.635 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.044..2034.114 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=128 read=94265 Total runtime: 3060.832 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.043..2038.548 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=160 read=94233 Total runtime: 3065.674 ms(4 rows)

query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2482924829**94297**3194query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24829query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2482924829**94265**3200query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24829query__start explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:2482924829**94233**3195query__done explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;pid:24829

digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.037..1812.712 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=192 read=94201 Total runtime: 2833.760 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.038..1814.074 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=224 read=94169 Total runtime: 2835.363 ms(4 rows)digoal=# explain (analyze,verbose,costs,buffers,timing) select * from tbl_cost_align;                                                               QUERY PLAN                                                               ---------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on postgres.tbl_cost_align  (cost=0.00..195393.00 rows=10100000 width=45) (actual time=0.039..1814.641 rows=10100000 loops=1)   Output: id, info, crt_time   Buffers: shared hit=256 read=94137 Total runtime: 2840.508 ms(4 rows)

digoal=# select 220/94137.0;        ?column?        ------------------------ 0.00233701945037551653(1 row)0.002337毫秒. 2337纳秒.