数年前に購入したDELLのノートでも4桁tps出せることだけを示した乱暴なメモですのでご容赦願います。
・Red Hat Enterprise Linux release 8.1 (Ootpa)
・PostgreSQL x86_64 10.6（仮想マシンで論理コア数4のスペック確保。物理コア数は半分）
実行環境
[root@localhost ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 94
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz
stepping : 3
microcode : 0xc6
cpu MHz : 2592.001
cache size : 6144 KB
physical id : 0
siblings : 1
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 22
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 invpcid rtm mpx rdseed adx smap clflushopt xsaveopt xsavec xsaves arat flush_l1d arch_capabilities
bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs
bogomips : 5184.00
clflush size : 64
cache_alignment : 64
address sizes : 43 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 94
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz
stepping : 3
microcode : 0xc6
cpu MHz : 2592.001
cache size : 6144 KB
physical id : 2
siblings : 1
core id : 0
cpu cores : 1
apicid : 2
initial apicid : 2
fpu : yes
fpu_exception : yes
cpuid level : 22
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 invpcid rtm mpx rdseed adx smap clflushopt xsaveopt xsavec xsaves arat flush_l1d arch_capabilities
bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs
bogomips : 5184.00
clflush size : 64
cache_alignment : 64
address sizes : 43 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 94
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz
stepping : 3
microcode : 0xc6
cpu MHz : 2592.001
cache size : 6144 KB
physical id : 4
siblings : 1
core id : 0
cpu cores : 1
apicid : 4
initial apicid : 4
fpu : yes
fpu_exception : yes
cpuid level : 22
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 invpcid rtm mpx rdseed adx smap clflushopt xsaveopt xsavec xsaves arat flush_l1d arch_capabilities
bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs
bogomips : 5184.00
clflush size : 64
cache_alignment : 64
address sizes : 43 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 94
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz
stepping : 3
microcode : 0xc6
cpu MHz : 2592.001
cache size : 6144 KB
physical id : 6
siblings : 1
core id : 0
cpu cores : 1
apicid : 6
initial apicid : 6
fpu : yes
fpu_exception : yes
cpuid level : 22
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 invpcid rtm mpx rdseed adx smap clflushopt xsaveopt xsavec xsaves arat flush_l1d arch_capabilities
bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs
bogomips : 5184.00
clflush size : 64
cache_alignment : 64
address sizes : 43 bits physical, 48 bits virtual
power management:
pgbenchのインストール
pgbenchの説明
pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.
[root@localhost ~]# yum install postgresql-contrib.x86_64
以下の結果はpgbenchとDB同一環境であることに注意。（オーバーヘッド軽いのでただの追い風参考記録）
DELLのノートPCでも条件次第では4桁tpsに達するという数値感覚だけ記憶に留めておくレベルのメモ。
[postgres@localhost ~]$ pgbench -c 2 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 20000/20000
latency average = 1.267 ms
tps = 1578.613372 (including connections establishing)
tps = 1578.920760 (excluding connections establishing)
[postgres@localhost ~]$ pgbench -c 3 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 3
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 30000/30000
latency average = 1.493 ms
tps = 2009.387942 (including connections establishing)
tps = 2009.626273 (excluding connections establishing)
[postgres@localhost ~]$
[postgres@localhost ~]$ pgbench -c 4 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 2.250 ms
tps = 1777.985566 (including connections establishing)
tps = 1778.128090 (excluding connections establishing)
[postgres@localhost ~]$ pgbench -c 10 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 5.688 ms
tps = 1758.066690 (including connections establishing)
tps = 1758.133300 (excluding connections establishing)
[postgres@localhost ~]$ pgbench -c 50 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 500000/500000
latency average = 41.989 ms
tps = 1190.778208 (including connections establishing)
tps = 1190.784000 (excluding connections establishing)
[postgres@localhost ~]$ pgbench -c 90 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 90
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 900000/900000
latency average = 102.581 ms
tps = 877.355151 (including connections establishing)
tps = 877.356902 (excluding connections establishing)
WindowsのpgbenchとLinuxのPostgreSQLのマシン内のリモート検証結果
DBクライアント側のリソースネックのリソース解析結果については後日update予定。（素直にAWS上でやったほうがよい感触）
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 1 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 10000/10000
latency average = 3.257 ms
tps = 307.070428 (including connections establishing)
tps = 307.118656 (excluding connections establishing)
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 2 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 20000/20000
latency average = 4.284 ms
tps = 466.861238 (including connections establishing)
tps = 466.956161 (excluding connections establishing)
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 3 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 3
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 30000/30000
latency average = 5.480 ms
tps = 547.398552 (including connections establishing)
tps = 547.469908 (excluding connections establishing)
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 4 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 3.983 ms
tps = 1004.240360 (including connections establishing)
tps = 1004.332917 (excluding connections establishing)
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 5 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 50000/50000
latency average = 5.147 ms
tps = 971.520008 (including connections establishing)
tps = 971.666508 (excluding connections establishing)
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 5 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 5
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 50000/50000
latency average = 6.050 ms
tps = 826.439951 (including connections establishing)
tps = 826.526860 (excluding connections establishing)
C:\Program Files\PostgreSQL\10\bin>pgbench -h 192.168.80.131 -p 5432 -U postgres -c 10 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 21.874 ms
tps = 457.154765 (including connections establishing)
tps = 457.165835 (excluding connections establishing)