开发者社区> 问答> 正文

RDS PostgreSQL性能和ECS自建数据库的性能有什么差别?

德哥 2015-12-22 12:55:19 4798
弹性计算 关系型数据库 数据库 PostgreSQL RDS
分享到
取消 提交回答
全部回答(3)
  • 不算帅
    2019-07-17 18:20:36

    应该是CPU核心数差异造成的吧。
    你RDS那套配置应该是8核16G的配置,你选的ECS是4核16G;另外ECS上不知道你数据存在哪块盘上的,看你的选择有三块盘 系统盘、100G SSD、800G SSD;只有 800G这块的IO效率才是最高的。

    另外除去性能上的差异,就是成本上的差异了;RDS按量计费一年是 524365 = 43800 (包年28000);而同等配置的ECS (8核16G 800G SSD数据) 只要 18000 左右。

    0 0
  • 德哥
    2019-07-17 18:20:36

    可以解释一下吗?
    对小并发场景,由于你测试AliCloudDB PgSQL是经过了PROXY的网络和RDS的网络,走了2跳,来回4跳,而你测试ECS自建是本地测试,所以就网络RT而言,两个对比不公平,这方面AliCloudDB PgSQL吃了大亏。
    为什么后面AliCloudDB PgSQL性能会高过你的环境?
    因为AliCloudDB PgSQL做了大量优化。比社区版本要好。

    2 0
  • dbfans
    2019-07-17 18:20:36

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


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

    Snip20151222_14

    Snip20151222_10

    Snip20151222_8

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

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


    测试结果

    Snip20151222_15

    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倍,是不是有什么猫腻啊?

    Snip20151225_25

    screenshot

    测试方法在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

    5 0
添加回答
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

推荐文章
相似问题
推荐课程