阿里云主机的pgbench测试

之前在青云的云主机上做过pgbench测试，这次在阿里云上再测一下，数据仅供参考

1.  ECS

1.1  环境

CPU： 1核    内存： 1024 MB
带宽：1Mbps（峰值）
OS:CentOS 6.5 x64
PostgresQL9.4.2(shared_buffers = 128MB,其它都是默认值)

[root@xxxxxxxx ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 62
model name : Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz
stepping : 4
cpu MHz : 2599.981
cache size : 20480 KB
physical id : 0
siblings : 1
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
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 clflush mmx fxsr sse sse2 ht syscall nx lm up rep_good unfair_spinlock pni ssse3 cx16 sse4_1 sse4_2 popcnt aes hypervisor lahf_lm
bogomips : 5199.96
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:

1.2 scale因子为100时pgbench的测试结果

-bash-4.1$ pgbench postgres -n -S -T 20
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 19892
latency average: 1.005 ms
tps = 994.338042 (including connections establishing)
tps = 994.576926 (excluding connections establishing)

-bash-4.1$ pgbench postgres -n -S -c 40 -T 20
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 40
number of threads: 1
duration: 20 s
number of transactions actually processed: 16596
latency average: 48.204 ms
tps = 826.941182 (including connections establishing)
tps = 834.350086 (excluding connections establishing)

-bash-4.1$ pgbench postgres -n -T 20
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 4840
latency average: 4.132 ms
tps = 241.357157 (including connections establishing)
tps = 241.411969 (excluding connections establishing)

-bash-4.1$ pgbench postgres -n -c 40 -T 20
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 40
number of threads: 1
duration: 20 s
number of transactions actually processed: 8621
latency average: 92.797 ms
tps = 429.793392 (including connections establishing)
tps = 433.587615 (excluding connections establishing)

1.3 scale因子为10时pgbench的测试结果

-bash-4.1$ pgbench -S -n -T 20
transaction type: SELECT only
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 178573
latency average: 0.112 ms
tps = 8928.611607 (including connections establishing)
tps = 8930.597763 (excluding connections establishing)

-bash-4.1$ pgbench -S -n -c 40 -T 20
transaction type: SELECT only
scaling factor: 10
query mode: simple
number of clients: 40
number of threads: 1
duration: 20 s
number of transactions actually processed: 152800
latency average: 5.236 ms
tps = 7620.938509 (including connections establishing)
tps = 8490.798375 (excluding connections establishing)

-bash-4.1$ pgbench -P 1 -n -c 40 -T 20
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 40
number of threads: 1
duration: 20 s
number of transactions actually processed: 10143
latency average: 78.727 ms
latency stddev: 318.873 ms
tps = 502.796187 (including connections establishing)
tps = 507.197524 (excluding connections establishing)

-bash-4.1$ pgbench -P 1 -n -T 20
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 8113
latency average: 2.462 ms
latency stddev: 5.968 ms
tps = 405.617044 (including connections establishing)
tps = 405.730862 (excluding connections establishing)

2. RDS

2.1 环境

数据库内存:1024M
最大IOPS:400
RDS:PostgreSQL 9.4.4
通过前面的ECS连RDS

2.2 scale因子为100时pgbench的测试结果

pgbench -s 100初始化到建索引的步骤时总是失败，并且收到了RDS发生主备切换的短信。 

postgres=> create index pgbench_accounts_pkey on pgbench_accounts(aid);
server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

另外。通过观察RDS的监控画面，发现初始化IOPS使用量已经达到400的上限。

2.3 scale因子为10时pgbench的测试结果

初始化花了2分钟。

-bash-4.1$ pgbench -i -s 10
NOTICE: table \"pgbench_history\" does not exist, skipping
NOTICE: table \"pgbench_tellers\" does not exist, skipping
NOTICE: table \"pgbench_accounts\" does not exist, skipping
NOTICE: table \"pgbench_branches\" does not exist, skipping
creating tables...
100000 of 1000000 tuples (10%) done (elapsed 1.83 s, remaining 16.48 s).
200000 of 1000000 tuples (20%) done (elapsed 19.65 s, remaining 78.58 s).
300000 of 1000000 tuples (30%) done (elapsed 27.08 s, remaining 63.18 s).
400000 of 1000000 tuples (40%) done (elapsed 43.28 s, remaining 64.92 s).
500000 of 1000000 tuples (50%) done (elapsed 62.33 s, remaining 62.33 s).
600000 of 1000000 tuples (60%) done (elapsed 64.12 s, remaining 42.75 s).
700000 of 1000000 tuples (70%) done (elapsed 84.43 s, remaining 36.19 s).
800000 of 1000000 tuples (80%) done (elapsed 89.05 s, remaining 22.26 s).
900000 of 1000000 tuples (90%) done (elapsed 104.94 s, remaining 11.66 s).
1000000 of 1000000 tuples (100%) done (elapsed 124.75 s, remaining 0.00 s).
vacuum...
set primary keys...
done.

下面是测试结果

-bash-4.1$ pgbench postgres -n -S -T 20
transaction type: SELECT only
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 5075
latency average: 3.941 ms
tps = 253.728116 (including connections establishing)
tps = 253.951345 (excluding connections establishing)

-bash-4.1$ pgbench postgres -n -S -c 40 -T 20
transaction type: SELECT only
scaling factor: 10
query mode: simple
number of clients: 40
number of threads: 1
duration: 20 s
number of transactions actually processed: 119146
latency average: 6.714 ms
tps = 5872.307162 (including connections establishing)
tps = 6024.203600 (excluding connections establishing)

-bash-4.1$ pgbench postgres -n -T 20
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 1
number of threads: 1
duration: 20 s
number of transactions actually processed: 776
latency average: 25.773 ms
tps = 38.773930 (including connections establishing)
tps = 38.805683 (excluding connections establishing)

-bash-4.1$ pgbench postgres -n -c 40 -T 20
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 40
number of threads: 1
duration: 20 s
number of transactions actually processed: 950
latency average: 842.105 ms
tps = 46.935648 (including connections establishing)
tps = 48.162375 (excluding connections establishing)

ECS和RDS分别在北京A区和B区，网络延时高达1ms，这可能是上面的单并发的Select Only 的tps很低的原因。除此以外，进行TPC-B 40并发测试时，监控画面上显示的IOPS峰值最高只达到了200。

3. 参考

http://blog.chinaunix.net/uid-20726500-id-5090498.html