阿里云主机的pgbench测试

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

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:

-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

number of clients: 40

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)

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

number of transactions actually processed: 8621

latency average: 92.797 ms

tps = 429.793392 (including connections establishing)

tps = 433.587615 (excluding connections establishing)

-bash-4.1$ pgbench -S -n -T 20

scaling factor: 10

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

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

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

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)

数据库内存:1024M

最大IOPS:400

RDS:PostgreSQL 9.4.4

通过前面的ECS连RDS

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分钟。

-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.

下面是测试结果

number of transactions actually processed: 5075

latency average: 3.941 ms

tps = 253.728116 (including connections establishing)

tps = 253.951345 (excluding connections establishing)

number of transactions actually processed: 119146

latency average: 6.714 ms

tps = 5872.307162 (including connections establishing)

tps = 6024.203600 (excluding connections establishing)

number of transactions actually processed: 776

latency average: 25.773 ms

tps = 38.773930 (including connections establishing)

tps = 38.805683 (excluding connections establishing)

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。

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