dbfans_个人页

本着怀疑的精神，用公司资源买了个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 15744536Swap: 0 0 0 CPU[root@iZ23r14an4lZ ~]# lscpuArchitecture: x86_64CPU op-mode(s): 32-bit, 64-bitByte Order: Little EndianCPU(s): 4On-line CPU(s) list: 0-3Thread(s) per core: 1Core(s) per socket: 1Socket(s): 4NUMA node(s): 1Vendor ID: GenuineIntelCPU family: 6Model: 62Stepping: 4CPU MHz: 2600.048BogoMIPS: 5198.01Hypervisor vendor: XenVirtualization type: fullL1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 20480KNUMA node0 CPU(s): 0-3 块设备[root@iZ23r14an4lZ ~]# lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINTxvda 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 /alidata1xvdc 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 -hFilesystem 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 $PGDATApostgres@iZ23r14an4lZ-> lltotal 124Kdrwx------ 7 postgres root 4.0K Dec 22 03:23 basedrwx------ 2 postgres root 4.0K Dec 22 11:31 globaldrwx------ 2 postgres root 4.0K Dec 22 03:39 pg_clogdrwx------ 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.confdrwx------ 2 postgres root 12K Dec 22 10:20 pg_logdrwx------ 4 postgres root 4.0K Dec 22 03:23 pg_logicaldrwx------ 4 postgres root 4.0K Dec 22 03:23 pg_multixactdrwx------ 2 postgres root 4.0K Dec 22 11:31 pg_notifydrwx------ 2 postgres root 4.0K Dec 22 03:23 pg_replslotdrwx------ 2 postgres root 4.0K Dec 22 03:23 pg_serialdrwx------ 2 postgres root 4.0K Dec 22 03:23 pg_snapshotsdrwx------ 2 postgres root 4.0K Dec 22 13:03 pg_statdrwx------ 2 postgres root 4.0K Dec 22 13:03 pg_stat_tmpdrwx------ 2 postgres root 4.0K Dec 22 12:52 pg_subtransdrwx------ 2 postgres root 4.0K Dec 22 03:23 pg_tblspcdrwx------ 2 postgres root 4.0K Dec 22 03:23 pg_twophase-rw------- 1 postgres root 4 Dec 22 03:23 PG_VERSIONlrwxrwxrwx 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.confmax_connections = 200 # (change requires restart)unix_socket_directories = '.' # comma-separated list of directoriesunix_socket_permissions = 0700 # begin with 0 to use octal notationshared_buffers = 4GB # min 128kBhuge_pages = try # on, off, or trymaintenance_work_mem = 512MB # min 1MBbgwriter_delay = 10ms # 10-10000ms between roundsbgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/roundwal_level = logical # minimal, archive, hot_standby, or logicalsynchronous_commit = off # synchronization level;full_page_writes = on # recover from partial page writeswal_buffers = 16MB # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms # 1-10000 millisecondscheckpoint_segments = 256 # in logfile segments, min 1, 16MB eachcheckpoint_completion_target = 0.00001 # checkpoint target duration, 0.0 - 1.0effective_cache_size = 12GBlog_destination = 'csvlog' # Valid values are combinations oflogging_collector = on # Enable capturing of stderr and csvloglog_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 thelog_rotation_age = 1d # Automatic rotation of logfiles willlog_rotation_size = 0 # Automatic rotation of logfiles willlog_checkpoints = offlog_connections = offlog_disconnections = offlog_error_verbosity = default # terse, default, or verbose messageslog_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and 测试模型TPC-B, 产生1亿条测试记录。postgres@iZ23r14an4lZ-> pgbench -s 1000 -iNOTICE: table 'pgbench_history' does not exist, skippingNOTICE: table 'pgbench_tellers' does not exist, skippingNOTICE: table 'pgbench_accounts' does not exist, skippingNOTICE: table 'pgbench_branches' does not exist, skippingcreating 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 10postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 64 -j 64 -T 20postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 96 -j 48 -T 20postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 128 -j 64 -T 20postgres@iZ23r14an4lZ-> pgbench -n -r -P 1 -c 160 -j 80 -T 20 每次测试前执行以下SQL，确保每次测试的数据规整。 postgres=# checkpoint;CHECKPOINTpostgres=# 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

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