我的书签
添加书签
移除书签背景
PostgreSQL 已与2019.10.3正式发布，测试其tpcc性能。
环境
阿里云虚拟机
1. [root@PostgreSQL12 ~]# lscpu
2. Architecture: x86_64
3. CPU op-mode(s): 32-bit, 64-bit
4. Byte Order: Little Endian
5. CPU(s): 16
6. On-line CPU(s) list: 0-15
7. Thread(s) per core: 2
8. Core(s) per socket: 8
9. Socket(s): 1
10. NUMA node(s): 1
11. Vendor ID: GenuineIntel
12. CPU family: 6
13. Model: 85
14. Model name: Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
15. Stepping: 4
16. CPU MHz: 2500.014
17. BogoMIPS: 5000.02
18. Hypervisor vendor: KVM
19. Virtualization type: full
20. L1d cache: 32K
21. L1i cache: 32K
22. L2 cache: 1024K
23. L3 cache: 33792K
24. NUMA node0 CPU(s): 0-15
25. Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 spec_ctrl intel_stibp
27. [root@PostgreSQL12 ~]# lsblk
28. NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
29. vda 253:0 0 200G 0 disk
30. └─vda1 253:1 0 200G 0 part /
31. vdb 253:16 0 1.8T 0 disk
32. └─vdb1 253:17 0 1.8T 0 part /data01
33. vdc 253:32 0 1.8T 0 disk
34. └─vdc1 253:33 0 1.8T 0 part /data02
36. [root@PostgreSQL12 ~]# uname -a
37. Linux PostgreSQL12 3.10.0-957.21.3.el7.x86_64 #1 SMP Tue Jun 18 16:35:19 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
39. [root@PostgreSQL12 ~]# free -g
40. total used free shared buff/cache available
41. Mem: 125 5 79 19 39 91
42. Swap: 0 0 0
测试方法
1、快设备设置
1. parted -s /dev/vdb mklabel gpt
2. parted -s /dev/vdc mklabel gpt
3. parted -s /dev/vdb mkpart primary 1MiB 100%
4. parted -s /dev/vdc mkpart primary 1MiB 100%
2、文件系统设置
1. mkfs.ext4 /dev/vdb1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L data01
2. mkfs.ext4 /dev/vdc1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L data02
4. vi /etc/fstab
5. LABEL=data01 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0
6. LABEL=data02 /data02 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0
8. mkdir /data01
9. mkdir /data02
11. mount -a
3、系统内核设置
1. vi /etc/sysctl.conf
3. # add by digoal.zhou
4. fs.aio-max-nr = 1048576
5. fs.file-max = 76724600
7. # 可选：kernel.core_pattern = /data01/corefiles/core_%e_%u_%t_%s.%p
8. # /data01/corefiles 事先建好，权限777，如果是软链接，对应的目录修改为777
10. kernel.sem = 4096 2147483647 2147483646 512000
11. # 信号量, ipcs -l 或 -u 查看，每16个进程一组，每组信号量需要17个信号量。
13. kernel.shmall = 107374182
14. # 所有共享内存段相加大小限制 (建议内存的80%)，单位为页。
15. kernel.shmmax = 274877906944
16. # 最大单个共享内存段大小 (建议为内存一半), >9.2的版本已大幅降低共享内存的使用，单位为字节。
17. kernel.shmmni = 819200
18. # 一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段
20. net.core.netdev_max_backlog = 10000
21. net.core.rmem_default = 262144
22. # The default setting of the socket receive buffer in bytes.
23. net.core.rmem_max = 4194304
24. # The maximum receive socket buffer size in bytes
25. net.core.wmem_default = 262144
26. # The default setting (in bytes) of the socket send buffer.
27. net.core.wmem_max = 4194304
28. # The maximum send socket buffer size in bytes.
29. net.core.somaxconn = 4096
30. net.ipv4.tcp_max_syn_backlog = 4096
31. net.ipv4.tcp_keepalive_intvl = 20
32. net.ipv4.tcp_keepalive_probes = 3
33. net.ipv4.tcp_keepalive_time = 60
34. net.ipv4.tcp_mem = 8388608 12582912 16777216
35. net.ipv4.tcp_fin_timeout = 5
36. net.ipv4.tcp_synack_retries = 2
37. net.ipv4.tcp_syncookies = 1
38. # 开启SYN Cookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN攻击
39. net.ipv4.tcp_timestamps = 1
40. # 减少time_wait
41. net.ipv4.tcp_tw_recycle = 0
42. # 如果=1则开启TCP连接中TIME-WAIT套接字的快速回收，但是NAT环境可能导致连接失败，建议服务端关闭它
43. net.ipv4.tcp_tw_reuse = 1
44. # 开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接
45. net.ipv4.tcp_max_tw_buckets = 262144
46. net.ipv4.tcp_rmem = 8192 87380 16777216
47. net.ipv4.tcp_wmem = 8192 65536 16777216
49. net.nf_conntrack_max = 1200000
50. net.netfilter.nf_conntrack_max = 1200000
52. vm.dirty_background_bytes = 409600000
53. # 系统脏页到达这个值，系统后台刷脏页调度进程 pdflush（或其他） 自动将(dirty_expire_centisecs/100）秒前的脏页刷到磁盘
54. # 默认为10%，大内存机器建议调整为直接指定多少字节
56. vm.dirty_expire_centisecs = 3000
57. # 比这个值老的脏页，将被刷到磁盘。3000表示30秒。
58. vm.dirty_ratio = 95
59. # 如果系统进程刷脏页太慢，使得系统脏页超过内存 95 % 时，则用户进程如果有写磁盘的操作（如fsync, fdatasync等调用），则需要主动把系统脏页刷出。
60. # 有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。
62. vm.dirty_writeback_centisecs = 100
63. # pdflush（或其他）后台刷脏页进程的唤醒间隔， 100表示1秒。
65. vm.swappiness = 0
66. # 不使用交换分区
68. vm.mmap_min_addr = 65536
69. vm.overcommit_memory = 0
70. # 在分配内存时，允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存，内存较少的测试环境可以使用 1 .
72. vm.overcommit_ratio = 90
73. # 当overcommit_memory = 2 时，用于参与计算允许指派的内存大小。
74. vm.swappiness = 0
75. # 关闭交换分区
76. vm.zone_reclaim_mode = 0
77. # 禁用 numa, 或者在vmlinux中禁止.
78. net.ipv4.ip_local_port_range = 40000 65535
79. # 本地自动分配的TCP, UDP端口号范围
80. fs.nr_open=20480000
81. # 单个进程允许打开的文件句柄上限
83. # 以下参数请注意
84. vm.extra_free_kbytes = 4096000
85. vm.min_free_kbytes = 2097152 # vm.min_free_kbytes 建议每32G内存分配1G vm.min_free_kbytes
86. # 如果是小内存机器，以上两个值不建议设置
87. # vm.nr_hugepages = 66536
88. # 建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize
89. vm.lowmem_reserve_ratio = 1 1 1
90. # 对于内存大于64G时，建议设置，否则建议默认值 256 256 32
93. sysctl -p
4、系统资源限制设置
1. vi /etc/security/limits.conf
3. * soft nofile 1024000
4. * hard nofile 1024000
5. * soft nproc unlimited
6. * hard nproc unlimited
7. * soft core unlimited
8. * hard core unlimited
9. * soft memlock unlimited
10. * hard memlock unlimited
5、自启动
1. vi /etc/rc.local
3. if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
4. echo never > /sys/kernel/mm/transparent_hugepage/enabled
5. fi
6. su - postgres -c "pg_ctl start"
1. chmod +x /etc/rc.d/rc.local
6、EPEL包
1. rpm -ivh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
3. yum -y install coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex openjade bzip2 git iotop
7、PG 12包
1. yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
3. yum install -y postgresql12*
8、PG12 环境变量
1. su - postgres
3. vi .bash_profile
5. export PS1="$USER@`/bin/hostname -s`-> "
6. export PGPORT=1921
7. export PGDATA=/data01/pg12/pg_root$PGPORT
8. export LANG=en_US.utf8
9. export PGHOME=/usr/pgsql-12
10. export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
11. export DATE=`date +"%Y%m%d%H%M"`
12. export PATH=$PGHOME/bin:$PATH:.
13. export MANPATH=$PGHOME/share/man:$MANPATH
14. export PGHOST=$PGDATA
15. export PGUSER=postgres
16. export PGDATABASE=postgres
17. alias rm='rm -i'
18. alias ll='ls -lh'
19. unalias vi
9、部署PG12文件系统
1. mkdir /data01/pg12
2. mkdir /data02/pg12
3. chown postgres:postgres /data01/pg12
4. chown postgres:postgres /data02/pg12
10、初始化PG12数据库实例
1. su - postgres
3. initdb -D $PGDATA -X /data02/pg12/pg_wal1921 -U postgres -E SQL_ASCII --locale=C
11、数据库参数设置
1. vi $PGDATA/postgresql.auto.conf
3. listen_addresses = '0.0.0.0'
4. port = 1921
5. max_connections = 1000
6. superuser_reserved_connections = 13
7. unix_socket_directories = '., /var/run/postgresql, /tmp'
8. unix_socket_permissions = 0700
9. tcp_keepalives_idle = 60
10. tcp_keepalives_interval = 10
11. tcp_keepalives_count = 10
12. tcp_user_timeout = 60
13. shared_buffers = 32GB
14. maintenance_work_mem = 2GB
15. dynamic_shared_memory_type = posix
16. max_files_per_process = 2000
17. vacuum_cost_delay = 0
18. bgwriter_delay = 10ms
19. bgwriter_lru_maxpages = 1000
20. bgwriter_lru_multiplier = 10.0
21. effective_io_concurrency = 0
22. max_worker_processes = 8
23. max_parallel_maintenance_workers = 4
24. max_parallel_workers_per_gather = 0
25. max_parallel_workers = 8
26. wal_level = minimal
27. synchronous_commit = off
28. full_page_writes = off
29. wal_buffers = 16MB
30. wal_writer_delay = 10ms
31. checkpoint_timeout = 15min
32. max_wal_size = 128GB
33. min_wal_size = 16GB
34. checkpoint_completion_target = 0.1
35. max_wal_senders = 0
36. random_page_cost = 1.2
37. effective_cache_size = 128GB
38. jit = off
39. log_destination = 'csvlog'
40. logging_collector = on
41. log_directory = 'log'
42. log_filename = 'postgresql-%a.log'
43. log_truncate_on_rotation = on
44. log_rotation_age = 1d
45. log_rotation_size = 0
46. log_checkpoints = on
47. log_error_verbosity = verbose
48. log_line_prefix = '%m [%p] '
49. log_statement = 'ddl'
50. log_timezone = 'Asia/Shanghai'
51. autovacuum = on
52. log_autovacuum_min_duration = 0
53. autovacuum_max_workers = 3
54. autovacuum_vacuum_scale_factor = 0.02
55. autovacuum_analyze_scale_factor = 0.01
56. autovacuum_freeze_max_age = 800000000
57. autovacuum_multixact_freeze_max_age = 900000000
58. autovacuum_vacuum_cost_delay = 0ms
59. vacuum_freeze_min_age = 500000000
60. vacuum_freeze_table_age = 750000000
61. vacuum_multixact_freeze_min_age = 5000000
62. vacuum_multixact_freeze_table_age = 750000000
63. datestyle = 'iso, mdy'
64. timezone = 'Asia/Shanghai'
65. lc_messages = 'C'
66. lc_monetary = 'C'
67. lc_numeric = 'C'
68. lc_time = 'C'
69. default_text_search_config = 'pg_catalog.english'
12、数据库防火墙设置
1. vi $PGDATA/pg_hba.conf
3. host all all 192.168.0.0/24 trust
13、启动数据库
1. pg_ctl start
14、数据库表空间设置
1. mkdir /data01/pg12/tbs1
2. mkdir /data02/pg12/tbs2
4. psql
5. create tablespace tbs1 location '/data01/pg12/tbs1';
6. create tablespace tbs2 location '/data02/pg12/tbs2';
15、sysbench部署
1. curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
2. sudo yum -y install sysbench
5. su - postgres
6. git clone https://github.com/digoal/sysbench-tpcc
9. cd sysbench-tpcc
11. chmod 700 *.lua
16、清理数据方法
1. drop schema public cascade;
2. create schema public;
3. grant all on schema public to public;
17、初始化数据(装载速度约每秒37MB)
1. export pgsql_table_options="tablespace tbs1"
2. export pgsql_index_options="tablespace tbs2"
测1000个仓库(1套表，112GB)
1. nohup time ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=1 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql prepare >./out.log 2>&1 &
测10000个仓库(10套表，每套1000个仓库, 1120GB)
1. nohup time ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=10 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql prepare >./out.log 2>&1 &
18、压测
远程建议3倍cpu客户端，本地建议2倍cpu客户端。
run 时不调用purge
1. 测1000个仓库
2. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=1 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=3600 --report-interval=5 run
5. 测10000个仓库
6. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=10 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=3600 --report-interval=5 run
run 时调用purge
1. 测1000个仓库
2. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=1 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=3600 --report-interval=5 --enable_purge=yes run
5. 测10000个仓库
6. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=10 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=3600 --report-interval=5 --enable_purge=yes run
cleanup
1. 测1000个仓库
2. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=1 --scale=1000 --trx_level=RC --db-driver=pgsql cleanup
5. 测10000个仓库
6. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=64 --tables=10 --scale=1000 --trx_level=RC --db-driver=pgsql cleanup
结果解读
run 时不调用purge
1. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=1921 --pgsql-user=postgres --pgsql-db=postgres --threads=32 --tables=1 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=60 --report-interval=5 run
2. sysbench 1.0.17 (using system LuaJIT 2.0.4)
4. Running the test with following options:
5. Number of threads: 32
6. Report intermediate results every 5 second(s)
7. Initializing random number generator from current time
10. Initializing worker threads...
12. Threads started!
14. [ 5s ] thds: 32 tps: 3248.44 qps: 93258.54 (r/w/o: 42390.64/44038.35/6829.54) lat (ms,95%): 27.66 err/s 10.00 reconn/s: 0.00
15. [ 10s ] thds: 32 tps: 3626.37 qps: 102832.62 (r/w/o: 46883.60/48696.28/7252.74) lat (ms,95%): 23.52 err/s 14.00 reconn/s: 0.00
16. [ 15s ] thds: 32 tps: 3838.38 qps: 109478.46 (r/w/o: 49903.95/51897.94/7676.56) lat (ms,95%): 21.50 err/s 18.60 reconn/s: 0.00
17. [ 20s ] thds: 32 tps: 4006.41 qps: 114816.04 (r/w/o: 52365.11/54437.71/8013.22) lat (ms,95%): 20.00 err/s 19.20 reconn/s: 0.00
18. [ 25s ] thds: 32 tps: 4103.01 qps: 116394.38 (r/w/o: 53051.28/55137.28/8205.81) lat (ms,95%): 20.00 err/s 17.20 reconn/s: 0.00
19. [ 30s ] thds: 32 tps: 4115.59 qps: 116128.74 (r/w/o: 52981.68/54915.87/8231.18) lat (ms,95%): 20.00 err/s 15.20 reconn/s: 0.00
20. [ 35s ] thds: 32 tps: 4109.69 qps: 117433.18 (r/w/o: 53571.93/55641.86/8219.39) lat (ms,95%): 19.65 err/s 19.19 reconn/s: 0.00
21. [ 40s ] thds: 32 tps: 4169.11 qps: 118802.26 (r/w/o: 54157.77/56306.27/8338.22) lat (ms,95%): 19.65 err/s 15.81 reconn/s: 0.00
22. [ 45s ] thds: 32 tps: 4170.78 qps: 118412.12 (r/w/o: 53997.63/56072.92/8341.57) lat (ms,95%): 19.65 err/s 18.80 reconn/s: 0.00
23. [ 50s ] thds: 32 tps: 4225.57 qps: 120878.63 (r/w/o: 55162.50/57264.98/8451.15) lat (ms,95%): 19.65 err/s 22.20 reconn/s: 0.00
24. [ 55s ] thds: 32 tps: 4128.25 qps: 116929.64 (r/w/o: 53310.25/55362.88/8256.51) lat (ms,95%): 20.00 err/s 19.40 reconn/s: 0.00
25. [ 60s ] thds: 32 tps: 4096.19 qps: 116335.90 (r/w/o: 53103.86/55039.66/8192.38) lat (ms,95%): 20.37 err/s 18.00 reconn/s: 0.00
统计方法：
1. SQL statistics:
2. queries performed:
3. read: 3104738
4. write: 3224417
5. other: 480086 -- 统计 begin;commit;rollback;
6. total: 6809241 -- 统计所有请求，以上相加
7. transactions: 239227 (3973.25 per sec.) -- 统计每秒完成事务数(不包括rollback;) 使用这个计算 total tpmc = 3973.25*60 = 238395
8. queries: 6809241 (113092.77 per sec.) -- 所有请求
9. ignored errors: 1038 (17.24 per sec.)
10. reconnects: 0 (0.00 per sec.)
12. General statistics:
13. total time: 60.2077s
14. total number of events: 239227
16. Latency (ms):
17. min: 0.42
18. avg: 8.02 -- 平均事务处理时间
19. max: 329.15
20. 95th percentile: 20.37 -- 95% 的事务处理时间低于 20.37 ms
21. sum: 1919757.02 -- 总耗时= --threads=32 乘以 --time=60
23. Threads fairness:
24. events (avg/stddev): 7475.8438/78.44
25. execution time (avg/stddev): 59.9924/0.01
统计结果如下：
1. total tpmc= 3973.25*60=238395
3. new orders tpmc= (total tpmc)*(10/23) = 103650 # (取决于run时是否 ```--enable_purge=yes```) 或 调用purge : (total tpmc)*(10/24)
1. function event()
2. -- print( NURand (1023,1,3000))
3. local max_trx = sysbench.opt.enable_purge == "yes" and 24 or 23
4. local trx_type = sysbench.rand.uniform(1,max_trx)
5. if trx_type <= 10 then
6. trx="new_order"
7. elseif trx_type <= 20 then
8. trx="payment"
9. elseif trx_type <= 21 then
10. trx="orderstatus"
11. elseif trx_type <= 22 then
12. trx="delivery"
13. elseif trx_type <= 23 then
14. trx="stocklevel"
15. elseif trx_type <= 24 then
16. trx="purge"
17. end
32c64ht 512G 1000仓库 机器测试结果
2. Architecture: x86_64
3. CPU op-mode(s): 32-bit, 64-bit
4. Byte Order: Little Endian
5. CPU(s): 64
6. On-line CPU(s) list: 0-63
7. Thread(s) per core: 2
8. Core(s) per socket: 32
9. Socket(s): 1
10. NUMA node(s): 1
11. Vendor ID: GenuineIntel
12. CPU family: 6
13. Model: 85
14. Model name: Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
15. Stepping: 4
16. CPU MHz: 2500.008
17. BogoMIPS: 5000.01
18. Hypervisor vendor: KVM
19. Virtualization type: full
20. L1d cache: 32K
21. L1i cache: 32K
22. L2 cache: 1024K
23. L3 cache: 33792K
24. NUMA node0 CPU(s): 0-63
25. Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 spec_ctrl intel_stibp
27. [root@pg11-test ~]# free -g
28. total used free shared buff/cache available
29. Mem: 503 313 3 17 186 170
30. Swap: 0 0 0
32. [root@pg11-test ~]# lsblk
33. NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
34. vda 253:0 0 200G 0 disk
35. └─vda1 253:1 0 200G 0 part /
36. vdb 253:16 0 1.8T 0 disk
37. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
38. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
39. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
40. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
41. vdc 253:32 0 1.8T 0 disk
42. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
43. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
44. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
45. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
46. vdd 253:48 0 1.8T 0 disk
47. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
48. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
49. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
50. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
51. vde 253:64 0 1.8T 0 disk
52. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
53. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
54. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
55. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
56. vdf 253:80 0 1.8T 0 disk
57. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
58. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
59. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
60. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
61. vdg 253:96 0 1.8T 0 disk
62. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
63. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
64. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
65. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
66. vdh 253:112 0 1.8T 0 disk
67. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
68. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
69. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
70. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
71. vdi 253:128 0 1.8T 0 disk
72. ├─vgdata01-lv01 252:0 0 4T 0 lvm /data01
73. ├─vgdata01-lv02 252:1 0 4T 0 lvm /data02
74. ├─vgdata01-lv03 252:2 0 4T 0 lvm /data03
75. └─vgdata01-lv04 252:3 0 2T 0 lvm /data04
77. [root@pg11-test ~]# pvs
78. PV VG Fmt Attr PSize PFree
79. /dev/vdb vgdata01 lvm2 a-- <1.75t 0
80. /dev/vdc vgdata01 lvm2 a-- <1.75t 0
81. /dev/vdd vgdata01 lvm2 a-- <1.75t 0
82. /dev/vde vgdata01 lvm2 a-- <1.75t 0
83. /dev/vdf vgdata01 lvm2 a-- <1.75t 0
84. /dev/vdg vgdata01 lvm2 a-- <1.75t 0
85. /dev/vdh vgdata01 lvm2 a-- <1.75t 0
86. /dev/vdi vgdata01 lvm2 a-- <1.75t 0
87. [root@pg11-test ~]# vgs
88. VG #PV #LV #SN Attr VSize VFree
89. vgdata01 8 4 0 wz--n- <13.97t 0
90. [root@pg11-test ~]# lvs
91. LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
92. lv01 vgdata01 -wi-ao---- 4.00t
93. lv02 vgdata01 -wi-ao---- 4.00t
94. lv03 vgdata01 -wi-ao---- 4.00t
95. lv04 vgdata01 -wi-ao---- <1.97t
97. [root@pg11-test ~]# lvdisplay -vv
98. devices/global_filter not found in config: defaulting to global_filter = [ "a|.*/|" ]
99. Setting global/locking_type to 1
100. Setting global/use_lvmetad to 1
101. global/lvmetad_update_wait_time not found in config: defaulting to 10
102. Setting response to OK
103. Setting protocol to lvmetad
104. Setting version to 1
105. Setting global/use_lvmpolld to 1
106. Setting devices/sysfs_scan to 1
107. Setting devices/multipath_component_detection to 1
108. Setting devices/md_component_detection to 1
109. Setting devices/fw_raid_component_detection to 0
110. Setting devices/ignore_suspended_devices to 0
111. Setting devices/ignore_lvm_mirrors to 1
112. devices/filter not found in config: defaulting to filter = [ "a|.*/|" ]
113. Setting devices/cache_dir to /etc/lvm/cache
114. Setting devices/cache_file_prefix to
115. devices/cache not found in config: defaulting to /etc/lvm/cache/.cache
116. Setting devices/write_cache_state to 1
117. Setting global/use_lvmetad to 1
118. Setting activation/activation_mode to degraded
119. metadata/record_lvs_history not found in config: defaulting to 0
120. Setting activation/monitoring to 1
121. Setting global/locking_type to 1
122. Setting global/wait_for_locks to 1
123. File-based locking selected.
124. Setting global/prioritise_write_locks to 1
125. Setting global/locking_dir to /run/lock/lvm
126. Setting global/use_lvmlockd to 0
127. Setting response to OK
128. Setting token to filter:3239235440
129. Setting daemon_pid to 11015
130. Setting response to OK
131. Setting global_disable to 0
132. report/output_format not found in config: defaulting to basic
133. log/report_command_log not found in config: defaulting to 0
134. Obtaining the complete list of VGs before processing their LVs
135. Setting response to OK
136. Setting response to OK
137. Setting name to vgdata01
138. Processing VG vgdata01 jwrfAR-tEXe-qf6u-rd95-yhPW-O7Xw-JPUjyr
139. Locking /run/lock/lvm/V_vgdata01 RB
140. Reading VG vgdata01 jwrfAR-tEXe-qf6u-rd95-yhPW-O7Xw-JPUjyr
141. Setting response to OK
142. Setting response to OK
143. Setting name to vgdata01
144. Setting metadata/format to lvm2
145. Setting id to 8Wny3c-lLb1-27xY-9rFC-HCOc-XsaD-HmvN5l
146. Setting format to lvm2
147. Setting device to 64784
148. Setting dev_size to 3749707776
149. Setting label_sector to 1
150. Setting ext_flags to 1
151. Setting ext_version to 2
152. Setting size to 1044480
153. Setting start to 4096
154. Setting ignore to 0
155. Setting id to ClcfJi-9Omy-hZdN-ll46-B6J2-fAAL-MLrleE
156. Setting format to lvm2
157. Setting device to 64800
158. Setting dev_size to 3749707776
159. Setting label_sector to 1
160. Setting ext_flags to 1
161. Setting ext_version to 2
162. Setting size to 1044480
163. Setting start to 4096
164. Setting ignore to 0
165. Setting id to uFhANC-PCAV-JwJL-zSNn-O8np-I2Wi-ue8Vv1
166. Setting format to lvm2
167. Setting device to 64816
168. Setting dev_size to 3749707776
169. Setting label_sector to 1
170. Setting ext_flags to 1
171. Setting ext_version to 2
172. Setting size to 1044480
173. Setting start to 4096
174. Setting ignore to 0
175. Setting id to hKBbU0-a3gm-sHq1-eU7Q-ZJ3m-Iwoo-MuKzzj
176. Setting format to lvm2
177. Setting device to 64832
178. Setting dev_size to 3749707776
179. Setting label_sector to 1
180. Setting ext_flags to 1
181. Setting ext_version to 2
182. Setting size to 1044480
183. Setting start to 4096
184. Setting ignore to 0
185. Setting id to cOZaeJ-Drns-9BcP-5Aoq-oZ88-0hVs-M7K8SU
186. Setting format to lvm2
187. Setting device to 64848
188. Setting dev_size to 3749707776
189. Setting label_sector to 1
190. Setting ext_flags to 1
191. Setting ext_version to 2
192. Setting size to 1044480
193. Setting start to 4096
194. Setting ignore to 0
195. Setting id to EgaC5R-Q0An-X79Q-xGRL-5zDI-MN16-lclIBO
196. Setting format to lvm2
197. Setting device to 64864
198. Setting dev_size to 3749707776
199. Setting label_sector to 1
200. Setting ext_flags to 1
201. Setting ext_version to 2
202. Setting size to 1044480
203. Setting start to 4096
204. Setting ignore to 0
205. Setting id to NnvDT4-eUM4-V2dP-Fqv1-O28z-OVoH-z939Bh
206. Setting format to lvm2
207. Setting device to 64880
208. Setting dev_size to 3749707776
209. Setting label_sector to 1
210. Setting ext_flags to 1
211. Setting ext_version to 2
212. Setting size to 1044480
213. Setting start to 4096
214. Setting ignore to 0
215. Setting id to XZsZfn-y2aH-MiNA-mo95-jpdQ-Jufp-eIgBga
216. Setting format to lvm2
217. Setting device to 64896
218. Setting dev_size to 3749707776
219. Setting label_sector to 1
220. Setting ext_flags to 1
221. Setting ext_version to 2
222. Setting size to 1044480
223. Setting start to 4096
224. Setting ignore to 0
225. Setting response to OK
226. Setting response to OK
227. /dev/vdb: size is 3749707776 sectors
228. /dev/vdc: size is 3749707776 sectors
229. /dev/vdd: size is 3749707776 sectors
230. /dev/vde: size is 3749707776 sectors
231. /dev/vdf: size is 3749707776 sectors
232. /dev/vdg: size is 3749707776 sectors
233. /dev/vdh: size is 3749707776 sectors
234. /dev/vdi: size is 3749707776 sectors
235. Adding vgdata01/lv01 to the list of LVs to be processed.
236. Adding vgdata01/lv02 to the list of LVs to be processed.
237. Adding vgdata01/lv03 to the list of LVs to be processed.
238. Adding vgdata01/lv04 to the list of LVs to be processed.
239. Processing LV lv01 in VG vgdata01.
240. --- Logical volume ---
241. global/lvdisplay_shows_full_device_path not found in config: defaulting to 0
242. LV Path /dev/vgdata01/lv01
243. LV Name lv01
244. VG Name vgdata01
245. LV UUID GtVTn9-mWcL-sTJA-QyRq-VocV-eu1s-374mkU
246. LV Write Access read/write
247. LV Creation host, time pg11-test, 2018-08-24 20:44:30 +0800
248. LV Status available
249. # open 1
250. LV Size 4.00 TiB
251. Current LE 32768
252. Segments 1
253. Allocation inherit
254. Read ahead sectors auto
255. - currently set to 8192
256. Block device 252:0
258. Processing LV lv02 in VG vgdata01.
259. --- Logical volume ---
260. global/lvdisplay_shows_full_device_path not found in config: defaulting to 0
261. LV Path /dev/vgdata01/lv02
262. LV Name lv02
263. VG Name vgdata01
264. LV UUID 17VdCH-KVNZ-FF3a-g7ic-IY4y-qav3-jdX3CJ
265. LV Write Access read/write
266. LV Creation host, time pg11-test, 2018-08-24 20:44:37 +0800
267. LV Status available
268. # open 1
269. LV Size 4.00 TiB
270. Current LE 32768
271. Segments 1
272. Allocation inherit
273. Read ahead sectors auto
274. - currently set to 8192
275. Block device 252:1
277. Processing LV lv03 in VG vgdata01.
278. --- Logical volume ---
279. global/lvdisplay_shows_full_device_path not found in config: defaulting to 0
280. LV Path /dev/vgdata01/lv03
281. LV Name lv03
282. VG Name vgdata01
283. LV UUID XY3M0w-EJdu-rx4z-Jn9n-QigT-mAVi-zps4te
284. LV Write Access read/write
285. LV Creation host, time pg11-test, 2018-08-24 20:44:57 +0800
286. LV Status available
287. # open 1
288. LV Size 4.00 TiB
289. Current LE 32768
290. Segments 1
291. Allocation inherit
292. Read ahead sectors auto
293. - currently set to 8192
294. Block device 252:2
296. Processing LV lv04 in VG vgdata01.
297. --- Logical volume ---
298. global/lvdisplay_shows_full_device_path not found in config: defaulting to 0
299. LV Path /dev/vgdata01/lv04
300. LV Name lv04
301. VG Name vgdata01
302. LV UUID vWtHPq-ycHf-n8AO-3E0V-R5F6-WTXc-LocpJ8
303. LV Write Access read/write
304. LV Creation host, time pg11-test, 2018-09-28 10:08:27 +0800
305. LV Status available
306. # open 1
307. LV Size <1.97 TiB
308. Current LE 16120
309. Segments 1
310. Allocation inherit
311. Read ahead sectors auto
312. - currently set to 8192
313. Block device 252:3
315. Unlocking /run/lock/lvm/V_vgdata01
316. Setting global/notify_dbus to 1
318. ./tpcc.lua --pgsql-host=/tmp --pgsql-port=4801 --pgsql-user=postgres --pgsql-db=postgres --threads=96 --tables=1 --scale=1000 --trx_level=RC --db-ps-mode=auto --db-driver=pgsql --time=60 --report-interval=5 --enable_purge=yes run
320. SQL statistics:
321. queries performed:
322. read: 12443189
323. write: 12830786
324. other: 1992972
325. total: 27266947
326. transactions: 994038 (16549.36 per sec.)
327. queries: 27266947 (453957.01 per sec.)
328. ignored errors: 4229 (70.41 per sec.)
329. reconnects: 0 (0.00 per sec.)
331. General statistics:
332. total time: 60.0634s
333. total number of events: 994038
335. Latency (ms):
336. min: 0.36
337. avg: 5.79
338. max: 138.96
339. 95th percentile: 16.41
340. sum: 5757585.45
342. Threads fairness:
343. events (avg/stddev): 10354.5625/127.26
344. execution time (avg/stddev): 59.9748/0.01
346. 数据装载速度 ： 89.5 MB/s
348. tpmc total ： 99.3万
350. tpcm neworder ： 41.4万
1. 1000仓库结果
3. tpmc total: 36万
5. tpmc neworder : 15万
7. 瓶颈：io
性能小结
1、pg 12 (单机自建) ecs 16c128g + 1.8T local ssd*2
1000仓库，64并发，tpmc total: 26万
10000仓库，64并发，tpmc total: 13万
2、pg 12 (单机自建) ecs 64c512g + 1.8T local ssd*8
1000仓库，64并发，tpmc total: 99万
10000仓库，64并发，tpmc total: 41万
3、pg 12 (单机自建)(104c 768g，essd 32TB，hugepage，sharedbuffer=600GB)
unlogged table：
1000仓库，208并发，tpmc total: 184万
logged table:
1000仓库，104并发，tpmc total: 168万
参考
《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》
https://github.com/digoal/sysbench-tpcc/blob/master/README.md
