本着怀疑的精神，用公司资源买了个ECS和RDS实例，测试两天，有结果发上来秀一下！也正要验一下阿里的RDS for PostgreSQL到底好不好使！

抄袭一下德哥的文章风格 :p
准备用PostgreSQL的pgbench测试一下ECS的TPS性能租了个号称20000IOPS的SSD云盘

ECS主机测试环境：
内存
postgres@iZ23r14an4lZ-> free

         total       used       free     shared    buffers     cached

Mem: 16330912 11332764 4998148 0 31992 10714396
-/+ buffers/cache: 586376 15744536
Swap: 0 0 0

CPU
[root@iZ23r14an4lZ ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 4
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 62
Stepping: 4
CPU MHz: 2600.048
BogoMIPS: 5198.01
Hypervisor vendor: Xen
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 20480K
NUMA node0 CPU(s): 0-3

块设备
[root@iZ23r14an4lZ ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 20G 0 disk
└─xvda1 202:1 0 20G 0 part /
xvdb 202:16 0 800G 0 disk
└─xvdb1 202:17 0 800G 0 part /alidata1
xvdc 202:32 0 100G 0 disk
└─xvdc1 202:33 0 100G 0 part /alidata2

mount参数
[root@iZ23r14an4lZ ~]# mount
/dev/xvda1 on / type ext4 (rw,barrier=0)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
none on /proc/xen type xenfs (rw)
/dev/xvdb1 on /alidata1 type ext4 (rw,noatime,nodiratime,discard,data=writeback,nodelalloc,barrier=0)
/dev/xvdc1 on /alidata2 type ext4 (rw,noatime,nodiratime,discard,data=writeback,nodelalloc,barrier=0)

postgres@iZ23r14an4lZ-> df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 20G 1.6G 18G 9% /
tmpfs 7.8G 4.0K 7.8G 1% /dev/shm
/dev/xvdb1 788G 68G 680G 10% /alidata1
/dev/xvdc1 99G 24G 71G 25% /alidata2

确认pg_xlog分布在不同盘
postgres@iZ23r14an4lZ-> cd $PGDATA
postgres@iZ23r14an4lZ-> ll
total 124K
drwx------ 7 postgres root 4.0K Dec 22 03:23 base
drwx------ 2 postgres root 4.0K Dec 22 11:31 global
drwx------ 2 postgres root 4.0K Dec 22 03:39 pg_clog
drwx------ 2 postgres root 4.0K Dec 22 03:23 pg_dynshmem
-rw------- 1 postgres root 4.4K Dec 22 03:23 pg_hba.conf
-rw------- 1 postgres root 1.6K Dec 22 03:23 pg_ident.conf
drwx------ 2 postgres root 12K Dec 22 10:20 pg_log
drwx------ 4 postgres root 4.0K Dec 22 03:23 pg_logical
drwx------ 4 postgres root 4.0K Dec 22 03:23 pg_multixact
drwx------ 2 postgres root 4.0K Dec 22 11:31 pg_notify
drwx------ 2 postgres root 4.0K Dec 22 03:23 pg_replslot
drwx------ 2 postgres root 4.0K Dec 22 03:23 pg_serial
drwx------ 2 postgres root 4.0K Dec 22 03:23 pg_snapshots
drwx------ 2 postgres root 4.0K Dec 22 13:03 pg_stat
drwx------ 2 postgres root 4.0K Dec 22 13:03 pg_stat_tmp
drwx------ 2 postgres root 4.0K Dec 22 12:52 pg_subtrans
drwx------ 2 postgres root 4.0K Dec 22 03:23 pg_tblspc
drwx------ 2 postgres root 4.0K Dec 22 03:23 pg_twophase
-rw------- 1 postgres root 4 Dec 22 03:23 PG_VERSION
lrwxrwxrwx 1 root root 21 Dec 22 11:30 pg_xlog -> /alidata2/pg_xlog_now
-rw------- 1 postgres root 88 Dec 22 03:23 postgresql.auto.conf
-rw-r--r-- 1 postgres postgres 21K Dec 22 12:47 postgresql.conf
-rw------- 1 postgres root 1.9K Dec 22 03:23 postgresql.conf.old
-rw------- 1 postgres root 28 Dec 22 11:31 postmaster.opts

参数配置
postgres@iZ23r14an4lZ-> grep "^[a-z]" postgresql.conf
max_connections = 200 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
unix_socket_permissions = 0700 # begin with 0 to use octal notation
shared_buffers = 4GB # min 128kB
huge_pages = try # on, off, or try
maintenance_work_mem = 512MB # min 1MB
bgwriter_delay = 10ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
wal_level = logical # minimal, archive, hot_standby, or logical
synchronous_commit = off # synchronization level;
full_page_writes = on # recover from partial page writes
wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
checkpoint_segments = 256 # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.00001 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 12GB
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_checkpoints = off
log_connections = off
log_disconnections = off
log_error_verbosity = default # terse, default, or verbose messages
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and

测试模型TPC-B, 产生1亿条测试记录。
postgres@iZ23r14an4lZ-> pgbench -s 1000 -i
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 100000000 tuples (0%) done (elapsed 0.21 s, remaining 210.90 s).
200000 of 100000000 tuples (0%) done (elapsed 0.44 s, remaining 218.91 s).
......

数据生成中。。。。待续。。。

测试结果

20000这么高的IOPS才只达到1000TPS。。。无心纠缠了，有空再测RDS

测试方法：

postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 8 -j 8 -T 10
postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 64 -j 64 -T 20
postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 96 -j 48 -T 20
postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 128 -j 64 -T 20
postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 160 -j 80 -T 20

每次测试前执行以下SQL，确保每次测试的数据规整。

postgres=# checkpoint;
CHECKPOINT
postgres=# explain analyze select count(*) from pgbench_accounts ;

QUERY PLAN

Aggregate (cost=2858130.08..2858130.08 rows=1 width=0) (actual time=99066.042..99066.042 rows=1 loops=1)
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2607293.00 rows=100334829 width=0) (actual time=1.378..90633.294 rows=100000000 loops=1)

     Heap Fetches: 19954600

Planning time: 0.507 ms
Execution time: 99066.096 ms
(5 rows)

RDS for PostgreSQL上pg_bench的测试结果！！是不是有猫腻！！

IOPS在RDS中明显低于ECS，但多用户操作时性能高了近4倍，是不是有什么猫腻啊？

测试方法在ECS上连接RDS进行以下操作：

[postgres@iZ23xxxxp2tZ ~]$ pgbench -n -r -P 1 -c 8 -j 8 -T 20 -h 100.xx.xx.xx -p 3432 -U dbfans postgres
[postgres@iZ23xxxxp2tZ ~]$ pgbench -n -r -P 1 -c 64 -j 64 -T 20 -h 100.xx.xx.xx -p 3432 -U dbfans postgres
[postgres@iZ23xxxxp2tZ ~]$ pgbench -n -r -P 1 -c 96 -j 48 -T 20 -h 100.xx.xx.xx -p 3432 -U dbfans postgres
[postgres@iZ23xxxxp2tZ ~]$ pgbench -n -r -P 1 -c 128 -j 64 -T 20 -h 100.xx.xx.xx -p 3432 -U dbfans postgres
[postgres@iZ23xxxxp2tZ ~]$ pgbench -n -r -P 1 -c 160 -j 80 -T 20 -h 100.xx.xx.xx -p 3432 -U dbfans postgres

看来看去生态还是太弱了，观望中！看到有年会的分享虽好，但业内资料太少，问题解答渠道也不够！