PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab

2025-04-14 技术教程

背景环境

阿里云虚拟机

[root@pg11-test~]#lscpuArchitecture:x86_64CPUop-mode(s):32-bit,64-bitByteOrder:LittleEndianCPU(s):64On-lineCPU(s)list:0-63Thread(s)percore:2Core(s)persocket:32Socket(s):1NUMAnode(s):1VendorID:GenuineIntelCPUfamily:6Model:85Modelname:Intel(R)Xeon(R)Platinum8163CPU@2.50GHzStepping:4CPUMHz:2500.008BogoMIPS:5000.01Hypervisorvendor:KVMVirtualizationtype:fullL1dcache:32KL1icache:32KL2cache:1024KL3cache:33792KNUMAnode0CPU(s):0-63Flags:fpuvmedepsetscmsrpaemcecx8apicsepmtrrpgemcacmovpatpse36clflushmmxfxsrssesse2sshtsyscallnxpdpe1gbrdtscplmconstant_tscrep_goodnopleagerfpupnipclmulqdqssse3fmacx16pcidsse4_1sse4_2x2apicmovbepopcnttsc_deadline_timeraesxsaveavxf16crdrandhypervisorlahf_lmabm3dnowprefetchfsgsbasetsc_adjustbmi1hleavx2smepbmi2ermsinvpcidrtmmpxavx512favx512dqrdseedadxsmapavx512cdavx512bwavx512vlxsaveoptxsavecxgetbv1

[root@pg11-test~]#free-gtotalusedfreesharedbuff/cacheavailableMem:5031221665274423Swap:000

uname-aLinuxpg11-test3.10.0-693.2.2.el7.x86_64#1SMPTueSep1222:26:13UTC2017x86_64x86_64x86_64GNU/Linux配置ECS SSD盘存储

1、卷管理

ddif=/dev/zeroof=/dev/vdbbs=1024count=1024ddif=/dev/zeroof=/dev/vdcbs=1024count=1024ddif=/dev/zeroof=/dev/vddbs=1024count=1024ddif=/dev/zeroof=/dev/vdebs=1024count=1024ddif=/dev/zeroof=/dev/vdfbs=1024count=1024ddif=/dev/zeroof=/dev/vdgbs=1024count=1024ddif=/dev/zeroof=/dev/vdhbs=1024count=1024ddif=/dev/zeroof=/dev/vdibs=1024count=1024pvcreate/dev/vd[b-i]vgcreate-Ay-s128Mvgdata01/dev/vd[b-i]lvcreate-Ay-i8-I8-L4096GiB-nlv01vgdata01lvcreate-Ay-i8-I8-L4096GiB-nlv02vgdata01lvcreate-Ay-i8-I8-L4096GiB-nlv03vgdata01

2、文件系统条带

mkfs.ext4/dev/mapper/vgdata01-lv01-m0-Oextent,uninit_bg-Elazy_itable_init=1,stride=2,stripe_width=16-b4096-Tlargefile-Llv01mkfs.ext4/dev/mapper/vgdata01-lv02-m0-Oextent,uninit_bg-Elazy_itable_init=1,stride=2,stripe_width=16-b4096-Tlargefile-Llv02mkfs.ext4/dev/mapper/vgdata01-lv03-m0-Oextent,uninit_bg-Elazy_itable_init=1,stride=2,stripe_width=16-b4096-Tlargefile-Llv03

3、mount

vi/etc/fstabLABEL=lv01/data01ext4defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback00LABEL=lv02/data02ext4defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback00LABEL=lv03/data03ext4defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback00mkdir/data01mkdir/data02mkdir/data03mount-a配置ECS虚拟机OS参数

1、内核参数

vi/etc/sysctl.conf#addbydigoal.zhoufs.aio-max-nr=1048576fs.file-max=76724600#可选：kernel.core_pattern=/data01/corefiles/core_%e_%u_%t_%s.%p#/data01/corefiles事先建好，权限777，如果是软链接，对应的目录修改为777kernel.sem=409621474836472147483646512000#信号量,ipcs-l或-u查看，每16个进程一组，每组信号量需要17个信号量。kernel.shmall=107374182#所有共享内存段相加大小限制(建议内存的80%)，单位为页。kernel.shmmax=274877906944#最大单个共享内存段大小(建议为内存一半),>9.2的版本已大幅降低共享内存的使用，单位为字节。kernel.shmmni=819200#一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段net.core.netdev_max_backlog=10000net.core.rmem_default=262144#Thedefaultsettingofthesocketreceivebufferinbytes.net.core.rmem_max=4194304#Themaximumreceivesocketbuffersizeinbytesnet.core.wmem_default=262144#Thedefaultsetting(inbytes)ofthesocketsendbuffer.net.core.wmem_max=4194304#Themaximumsendsocketbuffersizeinbytes.net.core.somaxconn=4096net.ipv4.tcp_max_syn_backlog=4096net.ipv4.tcp_keepalive_intvl=20net.ipv4.tcp_keepalive_probes=3net.ipv4.tcp_keepalive_time=60net.ipv4.tcp_mem=83886081258291216777216net.ipv4.tcp_fin_timeout=5net.ipv4.tcp_synack_retries=2net.ipv4.tcp_syncookies=1#开启SYNCookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN攻击net.ipv4.tcp_timestamps=1#减少time_waitnet.ipv4.tcp_tw_recycle=0#如果=1则开启TCP连接中TIME-WAIT套接字的快速回收，但是NAT环境可能导致连接失败，建议服务端关闭它net.ipv4.tcp_tw_reuse=1#开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接net.ipv4.tcp_max_tw_buckets=262144net.ipv4.tcp_rmem=81928738016777216net.ipv4.tcp_wmem=81926553616777216net.nf_conntrack_max=1200000net.netfilter.nf_conntrack_max=1200000vm.dirty_background_bytes=409600000#系统脏页到达这个值，系统后台刷脏页调度进程pdflush（或其他）自动将(dirty_expire_centisecs/100）秒前的脏页刷到磁盘#默认为10%，大内存机器建议调整为直接指定多少字节vm.dirty_expire_centisecs=3000#比这个值老的脏页，将被刷到磁盘。3000表示30秒。vm.dirty_ratio=95#如果系统进程刷脏页太慢，使得系统脏页超过内存95%时，则用户进程如果有写磁盘的操作（如fsync,fdatasync等调用），则需要主动把系统脏页刷出。#有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。vm.dirty_writeback_centisecs=100#pdflush（或其他）后台刷脏页进程的唤醒间隔，100表示1秒。vm.swappiness=0#不使用交换分区vm.mmap_min_addr=65536vm.overcommit_memory=0#在分配内存时，允许少量overmalloc,如果设置为1,则认为总是有足够的内存，内存较少的测试环境可以使用1.vm.overcommit_ratio=90#当overcommit_memory=2时，用于参与计算允许指派的内存大小。vm.swappiness=0#关闭交换分区vm.zone_reclaim_mode=0#禁用numa,或者在vmlinux中禁止.net.ipv4.ip_local_port_range=4000065535#本地自动分配的TCP,UDP端口号范围fs.nr_open=20480000#单个进程允许打开的文件句柄上限#以下参数请注意#vm.extra_free_kbytes=4096000#vm.min_free_kbytes=2097152#如果是小内存机器，以上两个值不建议设置#vm.nr_hugepages=66536#建议sharedbuffer设置超过64GB时使用大页，页大小/proc/meminfoHugepagesize#vm.lowmem_reserve_ratio=111#对于内存大于64G时，建议设置，否则建议默认值25625632

2、资源限制

vi/etc/security/limits.conf#nofile超过1048576的话，一定要先将sysctl的fs.nr_open设置为更大的值，并生效后才能继续设置nofile.*softnofile1024000*hardnofile1024000*softnprocunlimited*hardnprocunlimited*softcoreunlimited*hardcoreunlimited*softmemlockunlimited*hardmemlockunlimited

3、关闭透明大页

vi/etc/rc.localtouch/var/lock/subsys/localiftest-f/sys/kernel/mm/transparent_hugepage/enabled;thenechonever>/sys/kernel/mm/transparent_hugepage/enabledfisu-postgres-c"pg_ctlstart"部署PostgreSQL 11

https://yum.postgresql.org/repopackages.php#pg11

rpm-ivhhttps://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpmrpm-ivhhttps://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpmyum-yinstallcoreutilsglib2lrzszdstatsysstate4fsprogsxfsprogsntpreadline-develzlib-developenssl-develpam-devellibxml2-devellibxslt-develpython-develtcl-develgccgcc-c++makesmartmontoolsflexbisonperl-develperl-ExtUtils*openldap-develjadetexopenjadebzip2yuminstall-ypostgresql11*

环境变量

su-postgresvi.bash_profileexportPS1="$USER@`/bin/hostname-s`->"exportPGPORT=1921exportPGDATA=/data01/pg11/pg_root$PGPORTexport.utf8exportPGHOME=/usr/pgsql-11exportLD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexportDATE=`date+"%Y%m%d%H%M"`exportPATH=$PGHOME/bin:$PATH:.exportMANPATH=$PGHOME/share/man:$MANPATHexportPGHOST=$PGDATAexportPGUSER=postgresexportPGDATABASE=postgresaliasrm='rm-i'aliasll='ls-lh'unaliasvi初始化PostgreSQL 11数据库

mkdir/data01/pg11mkdir/data02/pg11chownpostgres:postgres/data01/pg11chownpostgres:postgres/data02/pg11

su-postgresinitdb-D$PGDATA-X/data02/pg11/pg_wal1921-Upostgres-ESQL_ASCII--locale=C配置PostgreSQL 11数据库参数

vi$PGDATA/postgresql.auto.conflisten_addresses='0.0.0.0'port=1921max_connections=2000superuser_reserved_connections=3unix_socket_directories='.,/var/run/postgresql,/tmp'tcp_keepalives_idle=60tcp_keepalives_interval=10tcp_keepalives_count=10shared_buffers=64GBmax_prepared_transactions=2000work_mem=8MBmaintenance_work_mem=2GBdynamic_shared_memory_type=posixvacuum_cost_delay=0bgwriter_delay=10msbgwriter_lru_maxpages=1000bgwriter_lru_multiplier=10.0effective_io_concurrency=0max_worker_processes=128max_parallel_maintenance_workers=6max_parallel_workers_per_gather=0parallel_leader_participation=onmax_parallel_workers=32wal_level=minimalsynchronous_commit=offwal_writer_delay=10mscheckpoint_timeout=35minmax_wal_size=128GBmin_wal_size=32GBcheckpoint_completion_target=0.1max_wal_senders=0effective_cache_size=400GBlog_destination='csvlog'logging_collector=onlog_directory='log'log_filename='postgresql-%a.log'log_truncate_on_rotation=onlog_rotation_age=1dlog_rotation_size=0log_checkpoints=onlog_connections=onlog_disconnections=onlog_error_verbosity=verboselog_line_prefix='%m[%p]'log_timezone='PRC'log_autovacuum_min_duration=0autovacuum_max_workers=16autovacuum_freeze_max_age=1200000000autovacuum_multixact_freeze_max_age=1400000000autovacuum_vacuum_cost_delay=0msvacuum_freeze_table_age=1150000000vacuum_multixact_freeze_table_age=1150000000datestyle='iso,mdy'timezone='PRC'lc_messages='C'lc_monetary='C'lc_numeric='C'lc_time='C'default_text_search_config='pg_catalog.english'jit=offcpu_tuple_cost=0.00018884145574257426cpu_index_tuple_cost=0.00433497085216479990cpu_operator_cost=0.00216748542608239995seq_page_cost=0.014329random_page_cost=0.016

启动数据库

pg_ctlstart部署sysbench 1.x1 推荐使用yum 安装

https://github.com/akopytov/sysbench

curl-shttps://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh|sudobashsudoyum-yinstallsysbench2 编译安装

yum-yinstallmakeautomakelibtoolpkgconfiglibaio-develgitclonehttps://github.com/akopytov/sysbenchcdsysbench./autogen.sh./configure--prefix=/var/lib/pgsql/sysbench--with-pgsql--without-mysql--with-pgsql-includes=/usr/pgsql-11/include--with-pgsql-libs=/usr/pgsql-11/libmake-j128makeinstalltpc-c 测试(本例使用prepared statement版本lua以降低SQL硬解析开销)

可以把sysbench部署在本地或异机进行测试。本例使用本机测试。

1、准备tpcc lua脚本(使用如下git ps版本，由于PostgreSQL优化器与Oracle类似都较为复杂，建议使用prepared statement降低CPU开销)

https://github.com/digoal/sysbench-tpcc

gitclonehttps://github.com/digoal/sysbench-tpcc

2、数据库连接示例

unixsocket_dir='/tmp'port=1921user=postgresdbname=postgres测试1 PostgreSQL: prepare data and tables

cdsysbench-tpccchmod700*.lua

./tpcc.lua--pgsql-host=/tmp--pgsql-port=1921--pgsql-user=postgres--pgsql-db=postgres--threads=64--tables=10--scale=100--trx_level=RC--db-ps-mode=auto--db-driver=pgsqlprepare

目前sysbench insert数据已使用bulk insert

insertintotbl(x,x,..)values(),(),(),...();2 PostgreSQL: Run benchmark

./tpcc.lua--pgsql-host=/tmp--pgsql-port=1921--pgsql-user=postgres--pgsql-db=postgres--threads=64--tables=10--scale=100--trx_level=RC--db-ps-mode=auto--db-driver=pgsql--time=3000--report-interval=1run3 PostgreSQL: Cleanup

./tpcc.lua--pgsql-host=/tmp--pgsql-port=1921--pgsql-user=postgres--pgsql-db=postgres--threads=64--tables=10--scale=100--trx_level=RC--db-driver=pgsqlcleanupPostgreSQL 11 1000W(10*100W) tpcc 3000秒测试结果

1、测试开始

postgres@pg11-test->./tpcc.lua--pgsql-host=/tmp--pgsql-port=1921--pgsql-user=postgres--pgsql-db=postgres--time=3000--threads=64--report-interval=1--tables=10--scale=100--trx_level=RC--db-ps-mode=auto--db-driver=pgsqlrunsysbench1.0.15(usingbundledLuaJIT2.1.0-beta2)Runningthetestwithfollowingoptions:Numberofthreads:64Reportintermediateresultsevery1second(s)InitializingrandomnumbergeneratorfromcurrenttimeInitializingworkerthreads...Threadsstarted!

2、中间结果

[1s]thds:64tps:10848.89qps:338713.05(r/w/o:139980.50/145087.73/53644.82)lat(ms,95%):15.00err/s54.69reconn/s:0.00[2s]thds:64tps:14145.17qps:405842.53(r/w/o:185145.95/192406.24/28290.34)lat(ms,95%):11.24err/s58.22reconn/s:0.00[3s]thds:64tps:15556.07qps:437546.11(r/w/o:199541.96/206892.00/31112.15)lat(ms,95%):10.09err/s69.00reconn/s:0.00[4s]thds:64tps:15749.37qps:443252.50(r/w/o:202278.79/209473.96/31499.75)lat(ms,95%):9.91err/s73.00reconn/s:0.00[5s]thds:64tps:15919.52qps:455430.67(r/w/o:207796.44/215797.19/31837.04)lat(ms,95%):9.73err/s57.98reconn/s:0.00[6s]thds:64tps:15992.56qps:458874.12(r/w/o:209319.81/217568.19/31986.11)lat(ms,95%):9.56err/s79.03reconn/s:0.00[7s]thds:64tps:16176.76qps:461788.59(r/w/o:210620.85/218814.23/32353.51)lat(ms,95%):9.56err/s53.00reconn/s:0.00[8s]thds:64tps:16450.87qps:467366.17(r/w/o:213272.25/221191.19/32902.73)lat(ms,95%):9.39err/s80.00reconn/s:0.00[9s]thds:64tps:15862.17qps:452484.21(r/w/o:206426.15/214335.73/31722.33)lat(ms,95%):9.73err/s64.00reconn/s:0.00[10s]thds:64tps:16492.73qps:472949.95(r/w/o:215464.54/224497.94/32987.46)lat(ms,95%):9.39err/s54.00reconn/s:0.00[11s]thds:64tps:16660.95qps:475135.93(r/w/o:216847.28/224967.76/33320.89)lat(ms,95%):9.39err/s73.00reconn/s:0.00[12s]thds:64tps:16775.06qps:475670.07(r/w/o:217046.72/225072.23/33551.12)lat(ms,95%):9.22err/s80.01reconn/s:0.00[13s]thds:64tps:16906.01qps:477886.83(r/w/o:218090.01/225984.81/33812.02)lat(ms,95%):9.22err/s66.96reconn/s:0.00[14s]thds:64tps:16908.88qps:482703.49(r/w/o:220326.70/228559.03/33817.76)lat(ms,95%):9.22err/s78.04reconn/s:0.00[15s]thds:64tps:17015.05qps:479408.28(r/w/o:218798.81/226580.37/34029.10)lat(ms,95%):9.39err/s65.00reconn/s:0.00[16s]thds:64tps:16834.45qps:480909.86(r/w/o:219310.87/227931.10/33667.90)lat(ms,95%):9.22err/s60.00reconn/s:0.00[17s]thds:64tps:17083.44qps:485663.48(r/w/o:221645.70/229846.91/34170.88)lat(ms,95%):9.06err/s66.00reconn/s:0.00[18s]thds:64tps:17195.95qps:487005.36(r/w/o:221943.49/230672.96/34388.91)lat(ms,95%):9.06err/s78.00reconn/s:0.00..............................[2990s]thds:64tps:17542.80qps:496350.64(r/w/o:226691.34/234572.70/35086.60)lat(ms,95%):9.06err/s78.00reconn/s:0.00[2991s]thds:64tps:17309.25qps:496647.58(r/w/o:226609.23/235418.85/34619.51)lat(ms,95%):9.06err/s81.00reconn/s:0.00[2992s]thds:64tps:16584.81qps:472614.59(r/w/o:215667.53/223777.44/33169.62)lat(ms,95%):9.22err/s81.00reconn/s:0.00[2993s]thds:64tps:17224.50qps:495915.40(r/w/o:226332.57/235134.83/34448.00)lat(ms,95%):9.22err/s62.00reconn/s:0.00[2994s]thds:64tps:17445.25qps:497437.54(r/w/o:226739.24/235807.81/34890.50)lat(ms,95%):9.22err/s66.99reconn/s:0.00[2995s]thds:64tps:17554.56qps:498410.55(r/w/o:227378.10/235922.34/35110.11)lat(ms,95%):8.90err/s75.01reconn/s:0.00[2996s]thds:64tps:17495.41qps:493823.41(r/w/o:225246.43/233586.15/34990.82)lat(ms,95%):9.06err/s70.00reconn/s:0.00[2997s]thds:64tps:17510.83qps:493703.35(r/w/o:225073.65/233608.05/35021.66)lat(ms,95%):9.06err/s61.00reconn/s:0.00[2998s]thds:64tps:17393.22qps:496557.75(r/w/o:226414.86/235357.44/34785.45)lat(ms,95%):9.06err/s68.99reconn/s:0.00[2999s]thds:64tps:17533.23qps:502571.93(r/w/o:229456.19/238048.28/35067.46)lat(ms,95%):8.90err/s79.01reconn/s:0.00[3000s]thds:64tps:17632.80qps:495850.27(r/w/o:226004.39/234582.29/35263.59)lat(ms,95%):9.06err/s78.00reconn/s:0.00

详情

3、统计结果

SQLstatistics:queriesperformed:read:669057449write:694400833other:103206774total:1466665056transactions:51587354(17194.37persec.)queries:1466665056(488848.22persec.)ignorederrors:223749(74.58persec.)reconnects:0(0.00persec.)Generalstatistics:totaltime:3000.2437stotalnumberofevents:51587354Latency(ms):min:0.29avg:3.72max:1007.8595thpercentile:9.22sum:191863975.40Threadsfairness:events(avg/stddev):806052.4062/4085.80executiontime(avg/stddev):2997.8746/0.05资源消耗

1、top

top-c-upostgrestop-15:11:18up1day,2:56,4users,loadaverage:54.04,22.42,31.39Tasks:604total,56running,548sleeping,0stopped,0zombie%Cpu(s):65.7us,19.3sy,0.0ni,14.9id,0.1wa,0.0hi,0.0si,0.0stKiBMem:52819500+total,18716296+free,13114144used,32791788+buff/cacheKiBSwap:0total,0free,0used.44386240+availMemPIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND54047postgres2004399344713843104S893.80.014:53.76sysbench./tpcc.lua--pgsql-host=/tmp--pgsql-port=1921--pgsql-user=postgres--pgsql-db=postgres--time=3000--threads=64--report-interval=1--tables=10--scale=100+54126postgres20065.867g9.910g9.892gR81.22.01:13.00postgres:postgrespostgres[local]idleintransaction54129postgres20065.867g9.872g9.854gR81.22.01:12.65postgres:postgrespostgres[local]idleintransaction54143postgres20065.867g9.933g9.915gR81.22.01:12.74postgres:postgrespostgres[local]EXECUTE54150postgres20065.867g9.887g9.869gR81.22.01:12.57postgres:postgrespostgres[local]idleintransaction54154postgres20065.867g0.010t0.010tR81.22.01:12.70postgres:postgrespostgres[local]idleintransaction54158postgres20065.867g0.010t0.010tR81.22.01:13.61postgres:postgrespostgres[local]EXECUTE54168postgres20065.867g0.010t9.991gR81.22.01:13.47postgres:postgrespostgres[local]EXECUTE54174postgres20065.867g9.879g9.861gS81.22.01:12.61postgres:postgrespostgres[local]idleintransaction53350postgres20065.846g0.060t0.060tR75.012.236:43.87postgres:backgroundwriter54113postgres20065.867g0.010t9.995gR75.02.01:12.65postgres:postgrespostgres[local]idle54118postgres20065.867g9.889g9.871gR75.02.01:12.83postgres:postgrespostgres[local]EXECUTE54119postgres20065.867g0.010t9.987gR75.02.01:12.96postgres:postgrespostgres[local]EXECUTE54120postgres20065.867g9.968g9.950gR75.02.01:13.02postgres:postgrespostgres[local]idleintransaction54121postgres20065.867g9.938g9.920gR75.02.01:12.96postgres:postgrespostgres[local]EXECUTE54123postgres20065.867g0.010t9.992gR75.02.01:12.97postgres:postgrespostgres[local]EXECUTE54131postgres20065.867g9.915g9.897gS75.02.01:12.78postgres:postgrespostgres[local]EXECUTE54133postgres20065.867g0.010t0.010tR75.02.01:12.74postgres:postgrespostgres[local]EXECUTE54134postgres20065.867g9.957g9.939gR75.02.01:13.17postgres:postgrespostgres[local]EXECUTE54135postgres20065.867g9.986g9.968gR75.02.01:12.87postgres:postgrespostgres[local]idleintransaction54139postgres20065.867g0.010t0.010tR75.02.01:13.60postgres:postgrespostgres[local]EXECUTE54140postgres20065.867g0.010t0.010tS75.02.01:12.91postgres:postgrespostgres[local]idleintransaction54141postgres20065.867g9.926g9.908gS75.02.01:12.86postgres:postgrespostgres[local]EXECUTE54142postgres20065.867g9.979g9.961gR75.02.01:12.74postgres:postgrespostgres[local]EXECUTEtransaction54144postgres20065.867g9.966g9.947gR75.02.01:12.94postgres:postgrespostgres[local]idleintransaction

2、iotop

TotalDISKREAD:0.00B/s|TotalDISKWRITE:1110.48M/sActualDISKREAD:0.00B/s|ActualDISKWRITE:1087.77M/sTIDPRIOUSERDISKREADDISKWRITESWAPINIO>COMMAND53351be/4postgres0.00B/s495.03M/s0.00%14.21%postgres:walwriter54165be/4postgres0.00B/s5.68M/s0.00%0.03%postgres:postgrespostgres[local]idleintransaction54127be/4postgres0.00B/s6.57M/s0.00%0.02%postgres:postgrespostgres[local]EXECUTEwaiting51622be/4root0.00B/s0.00B/s0.00%0.02%[kworker/u128:0]54167be/4postgres0.00B/s5.32M/s0.00%0.02%postgres:postgrespostgres[local]idleintransaction54120be/4postgres0.00B/s6.10M/s0.00%0.02%postgres:postgrespostgres[local]idleintransaction54135be/4postgres0.00B/s6.17M/s0.00%0.01%postgres:postgrespostgres[local]idleintransaction54128be/4postgres0.00B/s6.09M/s0.00%0.01%postgres:postgrespostgres[local]EXECUTE54157be/4postgres0.00B/s6.14M/s0.00%0.01%postgres:postgrespostgres[local]EXECUTE54145be/4postgres0.00B/s6.23M/s0.00%0.01%postgres:postgrespostgres[local]idleintransaction54132be/4postgres0.00B/s5.38M/s0.00%0.01%postgres:postgrespostgres[local]idleintransaction54122be/4postgres0.00B/s5.64M/s0.00%0.01%postgres:postgrespostgres[local]EXECUTE54151be/4postgres0.00B/s6.55M/s0.00%0.01%postgres:postgrespostgres[local]EXECUTE54174be/4postgres0.00B/s7.20M/s0.00%0.01%postgres:postgrespostgres[local]idleintransaction54118be/4postgres0.00B/s5.81M/s0.00%0.01%postgres:postgrespostgres[local]EXECUTE54114be/4postgres0.00B/s5.31M/s0.00%0.01%postgres:postgrespostgres[local]idleintransaction54142be/4postgres0.00B/s5.56M/s0.00%0.00%postgres:postgrespostgres[local]idleintransaction54117be/4postgres0.00B/s5.94M/s0.00%0.00%postgres:postgrespostgres[local]idleintransaction54158be/4postgres0.00B/s6.15M/s0.00%0.00%postgres:postgrespostgres[local]EXECUTE54161be/4postgres0.00B/s5.72M/s0.00%0.00%postgres:postgrespostgres[local]EXECUTE54124be/4postgres0.00B/s5.67M/s0.00%0.00%postgres:postgrespostgres[local]EXECUTE54113be/4postgres0.00B/s5.49M/s0.00%0.00%postgres:postgrespostgres[local]EXECUTE53350be/4postgres0.00B/s236.05M/s0.00%0.00%postgres:backgroundwriter54164be/4postgres0.00B/s5.44M/s0.00%0.00%postgres:postgrespostgres[local]idleintransaction54115be/4postgres0.00B/s5.86M/s0.00%0.00%postgres:postgrespostgres[local]idleintransaction54149be/4postgres0.00B/s5.49M/s0.00%0.00%postgres:postgrespostgres[local]idleintransaction

3、io统计

avg-cpu:%user%nice%system%iowait%steal%idle71.360.0015.570.000.0013.07Device:rrqm/swrqm/sr/sw/srkB/swkB/savgrq-szavgqu-szawaitr_awaitw_awaitsvctm%utilvda0.000.000.000.000.000.000.000.000.000.000.000.000.00vdb0.00417.000.0012851.000.00106080.0016.512.850.220.000.220.0449.40vdc0.00439.000.0013091.000.00108164.0016.523.000.230.000.230.0449.60vdd0.00434.000.0012941.000.00106964.0016.533.110.240.000.240.0450.10vde0.00433.001.0013040.004.00107736.0016.523.230.250.000.250.0450.50vdf0.00434.000.0013040.000.00107760.0016.533.280.250.000.250.0451.10vdg0.00448.000.0012806.000.00105996.0016.553.470.270.000.270.0452.20vdh0.00438.000.0013179.000.00108896.0016.533.760.290.000.290.0452.30vdi0.00459.000.0012933.000.00107072.0016.563.750.290.000.290.0451.80dm-00.000.001.00107284.004.00857876.0015.9927.500.260.000.260.0162.60dm-10.000.000.000.000.000.000.000.000.000.000.000.000.00dm-20.000.000.0099.000.00792.0016.000.000.020.000.020.030.30附录

PostgreSQL 12可能会支持force prepare，类似Oracle force cursor的特性

wgethttps://www.postgresql.org/message-id/attachment/64449/autoprepare-11.patchpatch-p1<../autoprepare-11.patchmakemakeinstall

patchingfiledoc/src/sgml/autoprepare.sgmlpatchingfiledoc/src/sgml/catalogs.sgmlHunk#1succeededat8223(offset-8lines).Hunk#2succeededat9539(offset-8lines).patchingfiledoc/src/sgml/filelist.sgmlpatchingfiledoc/src/sgml/postgres.sgmlpatchingfilesrc/backend/catalog/system_views.sqlpatchingfilesrc/backend/commands/prepare.cpatchingfilesrc/backend/nodes/nodeFuncs.cpatchingfilesrc/backend/tcop/postgres.cHunk#9succeededat4616(offset6lines).Hunk#10succeededat5441(offset6lines).patchingfilesrc/backend/tcop/utility.cpatchingfilesrc/backend/utils/cache/inval.cpatchingfilesrc/backend/utils/misc/guc.cHunk#1succeededat475(offset-8lines).Hunk#2succeededat2126(offset-8lines).patchingfilesrc/include/catalog/pg_proc.datpatchingfilesrc/include/commands/prepare.hpatchingfilesrc/include/nodes/nodeFuncs.hpatchingfilesrc/include/tcop/pquery.hpatchingfilesrc/include/utils/guc.hpatchingfilesrc/test/regress/expected/autoprepare.outpatchingfilesrc/test/regress/expected/date_1.outpatchingfilesrc/test/regress/expected/rules.outpatchingfilesrc/test/regress/parallel_schedulepatchingfilesrc/test/regress/serial_schedulepatchingfilesrc/test/regress/sql/autoprepare.sql

/**Thresholdforimplicitpreparingoffrequentlyexecutedqueries*/{{"autoprepare_threshold",PGC_USERSET,QUERY_TUNING_OTHER,gettext_noop("Thresholdforautopreparingquery."),gettext_noop("0valuedisablesautoprepare.")},&autoprepare_threshold,0,0,INT_MAX,NULL,NULL,NULL},{{"autoprepare_limit",PGC_USERSET,QUERY_TUNING_OTHER,gettext_noop("Maximalnumberofautopreparedqueries."),gettext_noop("0meansunlimitednumberofautopreparedqueries.Toolargenumberofpreparedqueriescancausebackendmemoryoverflowandslowdownexecutionspeed(becauseofincreasedlookuptime)")},&autoprepare_limit,113,0,INT_MAX,NULL,NULL,NULL},{{"autoprepare_memory_limit",PGC_USERSET,QUERY_TUNING_OTHER,gettext_noop("Maximalsizeofmemoryusedbyautopreparedqueries."),gettext_noop("0meansthatthereisnomemorylimit.Calculatingmemoryusedbypreparedqueriesaddssommeextraoverhead,""sonon-zerovalueofthisparametermaycausesomeslowdown.autoprepare_limitismuchfasterwaytolimitnumberofautopreparedstatements"),GUC_UNIT_KB},&autoprepare_memory_limit,0,0,INT_MAX,NULL,NULL,NULL},

如果支持内部ps，则测试时，不再需要使用prepare statement

./tpcc.lua--pgsql-host=/tmp--pgsql-port=8001--pgsql-user=postgres--pgsql-db=postgres--threads=64--tables=10--scale=100--db-driver=pgsqlprepare./tpcc.lua--pgsql-host=/tmp--pgsql-port=8001--pgsql-user=postgres--pgsql-db=postgres--threads=64--tables=10--scale=100--time=3000--report-interval=1--trx_level=RC--db-ps-mode=auto--db-driver=pgsqlrun./tpcc.lua--pgsql-host=/tmp--pgsql-port=8001--pgsql-user=postgres--pgsql-db=postgres--threads=64--tables=10--scale=100--db-driver=pgsqlcleanup小结

1、PostgreSQL 11 beta3版本，1000W tpc-c，在ECS虚拟机下测试，性能已达到 103万 tpmC。

2、PostgreSQL 11 beta3版本，200G tpc-h，在ECS虚拟机下测试(未过多优化)，总耗时在30分钟左右（还有巨大优化空间）。

《(TPC-H测试 SF=10,SF=200) PostgreSQL 11 vs 10 vs Deepgreen》

SF=10

数据库q1q2q3q4q5q6q7q8q9q10q11q12q13q14q15q16q17q18q19q20q21q22PostgreSQL 11beta3322534229312426102746210132

SF=200

数据库q1q2q3q4q5q6q7q8q9q10q11q12q13q14q15q16q17q18q19q20q21q22PostgreSQL 11beta3183825325785224663824269813581147325951221312414

PostgreSQL 在oltp, olap混合场景下有着出色的表现，再加上对Oracle兼容性的加持，以及阿里、平安集团、邮储、国家电网、中兴、华为、铁总、苏宁、去哪儿、探探等PG企业用户的背书，已成为市场去O的标志性替代产品，帮助企业实现几乎无痛的去O。

阿里云ADAM 去O 专版 PPAS(PostgreSQL高级版)提供免费去O评估。

Ora2pg开源去O产品.

《Oracle migration to Greenplum - (含 Ora2pg)》