选购机型
32核 128GB 1Mbps
系统盘：增强型SSD云
[root@node01 run]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Address sizes: 52 bits physical, 48 bits virtual
Byte Order: Little Endian
CPU(s): 32
On-line CPU(s) list: 0-31
Vendor ID: AuthenticAMD
BIOS Vendor ID: Red Hat
Model name: AMD EPYC 9K65 192-Core Processor
BIOS Model name: 3.0
CPU family: 26
Model: 0
Thread(s) per core: 2
Core(s) per socket: 16
Socket(s): 1
Stepping: 0
BogoMIPS: 4500.12
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 h
t syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc cpu
id extd_apicid amd_dcm tsc_known_freq pni pclmulqdq monitor ssse3 fma cx16 pcid sse4_1 sse4_2 x2api
c movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_lm cmp_legacy cr8_legacy abm sse4a misalig
nsse 3dnowprefetch osvw topoext perfctr_core ssbd ibrs ibpb stibp vmmcall fsgsbase bmi1 avx2 smep b
mi2 erms invpcid avx512f avx512dq rdseed adx smap avx512ifma clflushopt clwb avx512cd sha_ni avx512
bw avx512vl xsaveopt xsavec xgetbv1 xsaves avx512_bf16 clzero xsaveerptr wbnoinvd arat avx512vbmi u
mip avx512_vbmi2 gfni vaes vpclmulqdq avx512_vnni avx512_bitalg avx512_vpopcntdq rdpid movdiri movd
ir64b fsrm
Virtualization features:
Hypervisor vendor: KVM
Virtualization type: full
Caches (sum of all):
L1d: 768 KiB (16 instances)
L1i: 512 KiB (16 instances)
L2: 16 MiB (16 instances)
L3: 128 MiB (4 instances)
NUMA:
NUMA node(s): 1
NUMA node0 CPU(s): 0-31
Vulnerabilities:
Gather data sampling: Not affected
Itlb multihit: Not affected
L1tf: Not affected
Mds: Not affected
Meltdown: Not affected
Mmio stale data: Not affected
Reg file data sampling: Not affected
Retbleed: Not affected
Spec rstack overflow: Not affected
Spec store bypass: Mitigation; Speculative Store Bypass disabled via prctl
Spectre v1: Mitigation; usercopy/swapgs barriers and __user pointer sanitization
Spectre v2: Mitigation; Retpolines; IBPB conditional; IBRS_FW; STIBP always-on; RSB filling; PBRSB-eIBRS Not af
fected; BHI Not affected
Srbds: Not affected
Tsx async abort: Not affected
系统准备
机器名
hostnamectl set-hostname node01
修改hosts
cat >> /etc/hosts << here
10.206.16.5 node01
here
关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
关闭selinux
sed -i 's/enforcing/disabled/' /etc/selinux/config # 永久
setenforce 0 # 临时
时区设置
timedatectl set-timezone Asia/Shanghai
时间同步
yum install -y chrony
cat > /etc/chrony.conf <<here
driftfile /var/lib/chrony/drift
makestep 1.0 3
rtcsync
leapsectz right/UTC
logdir /var/log/chrony
log rawmeasurements
# 打印 tracking 日志，用来记录系统时钟的整体状态
log tracking
# 配置 NTP 服务器
server time1.tencentyun.com iburst
server time2.tencentyun.com iburst
server time3.tencentyun.com iburst
server time4.tencentyun.com iburst
server time5.tencentyun.com iburst
here
systemctl restart chronyd # 重启 chronyd 服务
systemctl enable chronyd # 开机自动启动 chronyd
禁用THP
echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
echo "never" > /sys/kernel/mm/transparent_hugepage/defrag
cat >> /etc/rc.local <<here
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
here
添加用户
postgres
getent group | grep 1001
groupadd -g 1001 postgres
useradd -g 1001 -u 1001 postgres
echo "adfs@tg#res" | passwd --stdin postgres
limits设置
cat >>/etc/security/limits.conf <<EOF
#postgres
postgres soft nproc 65535
postgres hard nproc 65535
postgres soft nofile 65535
postgres hard nofile 65535
postgres soft stack unlimited
EOF
系统参数
cat > /etc/sysctl.conf << "EOF"
############################for postgresql###########
kernel.shmall =4294967296
kernel.shmmax=135497418752
kernel.shmmni =4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max =7672460
fs.aio-max-nr =1048576
net.ipv4.ip_local_port_range= 9000 65000
net.core.rmem_default= 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 4194304
net.ipv4.tcp_max_syn_backlog= 4096
net.core.netdev_max_backlog= 10000
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
net.nf_conntrack_max = 1200000
net.netfilter.nf_conntrack_max = 1200000
vm.zone_reclaim_mode=0
vm.dirty_ratio = 95
vm.dirty_background_ratio = 5
vm.swappiness=1
vm.overcommit_memory= 0
vm.overcommit_ratio= 90
vm.extfrag_threshold=1000
EOFvm.extfrag_threshold=1000
EOF
sysctl -p
cpu是否支持
cat /proc/cpuinfo |grep avx
目录规划
test
fdisk -l
或使用LVM进行管理
yum install -y lvm2
pvcreate /dev/vdb
pvcreate /dev/vdc
pvcreate /dev/vdd
pvcreate /dev/vde
pvcreate /dev/vdf
pvcreate /dev/vdg
pvcreate /dev/vdh
pvcreate /dev/vdi
vgcreate vg_data /dev/vdb /dev/vdc /dev/vdd \
/dev/vde /dev/vdf /dev/vdg
vgcreate vg_test /dev/vdh /dev/vdi
lvcreate -n lv_pg -L 300G -i 6 -I 64k vg_data
lvcreate -n lv_test -L 100G -i 2 -I 64k vg_test
mkfs.ext4 /dev/vg_data/lv_pg
mount /dev/vg_data/lv_pg /data
mkdir -p /data_test
mkfs.ext4 /dev/vg_test/lv_test
mount /dev/vg_test/lv_test /data_test
注：条带化之后，负载均摊在vdb,vdc
软件安装
PG源码安装
在所有节点执行(node01,node02,node03)
下载源码
wget https://ftp.postgresql.org/pub/source/v16.8/postgresql-16.8.tar.gz &
安装依赖包
yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel \
libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ \
openssl-devel cmake
yum install -y readline readline-devel flex bison openssl openssl-devel git
yum install -y gcc gcc-c++ epel-release
yum install -y clang
yum install -y libicu-devel perl-ExtUtils-Embed zlib-devel openssl openssl-devel
yum install -y pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel
yum install -y tcl-devel python3-devel lz4 lz4-devel uuid libuuid-devel
yum install -y llvm5.0
yum install -y llvm5.0-devel*
yum install -y llvm
yum install -y llvm*-devel*
yum install -y perl-ExtUtils-Embed perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
yum install -y perl-Opcode
编译
tar zxf postgresql-16.8.tar.gz
cd postgresql-16.8
./configure --prefix=/usr/local/pgsql-16.8 \
--with-blocksize=8 --with-wal-blocksize=8 -with-segsize=1 \
--with-pgport=5432 --with-openssl \
--with-lz4 --with-perl --with-libxml --with-libxslt \
--with-pam --with-ldap --with-python --with-systemd
安装（完整安装，建议执行）
或把PostgreSQL相关的文档，HTML，以及其它的一些模块都会一起编译进去
make -j4 world && make install-world
make distclean
PG目录及服务规划
mkdir -p /data/pgdata
chown postgres.postgres -R /data/pgdata
env
cat >> /etc/profile <<here
export PATH=/usr/local/pgsql-16.8/bin:$PATH
export PGPORT=5432
export PGDATA=/data/pgdata
here
service file
vi /usr/lib/systemd/system/postgresql-16.service
[Unit]
Description=PostgreSQL 16 database server
Documentation=https://www.postgresql.org/docs/16/static/
After=syslog.target
After=network-online.target
[Service]
Type=notify
User=postgres
Group=postgres
Environment=PGDATA=/data/pgdata
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStart=/usr/local/pgsql-16.8/bin/postgres -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
TimeoutStartSec=0
TimeoutStopSec=1h
[Install]
WantedBy=multi-user.target
数据库搭建
mkdir -p /data/pg_archive
chown postgres.postgres -R /data/pg_archive/
su - postgres
cd /usr/local/pgsql-16.8/bin/
initdb -D /data/pgdata -U postgres -E UTF8
vi /data/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 3000
wal_level = replica
#archive_mode = on
#archive_command = 'cp %p /data/pg_archive/%f'
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 10MB
shared_buffers = 64GB
work_mem = 8MB
maintenance_work_mem = 2GB
maintenance_work_mem = 256MB
temp_buffers = 256MB
transaction_isolation = 'read committed'
default_transaction_isolation = 'read committed'
synchronous_commit = on
fsync = on
maintenance_work_mem = 2GB
vacuum_cost_limit = 2000
autovacuum = on
autovacuum_vacuum_cost_delay =10
max_worker_processes = 64
max_parallel_maintenance_workers = 6
max_parallel_workers_per_gather = 0
parallel_leader_participation = on
max_parallel_workers = 32
enable_mergejoin = off
enable_nestloop = off
enable_hashjoin = off
enable_bitmapscan = on
enable_material = off
wal_log_hints = off
log_duration = off
checkpoint_timeout = 15min
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.02
#setting for replication
hot_standby = on
max_wal_senders = 10
max_replication_slots = 10
wal_log_hints = 'on'
#restore_command=
#recovery_target_timelinee
pg_hba.conf
cat > $PGDATA/pg_hba.conf <<EOF
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 0.0.0.0/0 md5
host replication replicator 10.206.16.5/24 md5
EOF
pg_ctl -D $PGDATA start
psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'postgres';"
用户
CREATE DATABASE tpcc;
CREATE USER tpcc WITH PASSWORD 'tpcc@123';
ALTER DATABASE tpcc OWNER TO tpcc;
GRANT ALL PRIVILEGES ON DATABASE tpcc TO tpcc;
压测
sysbench
yum -y install automake
yum -y install automake libtool
yum install -y postgresql-devel
下载解压
wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.tar.gz
tar -xzf 1.0.20.tar.gz
cd sysbench-1.0.20/
源码安装
./autogen.sh # 生成必要的脚本文件
./configure --prefix=/usr/local/ --without-mysql \
--with-pgsql \
--with-pgsql-includes=/usr/local/pgsql-16.8/include \
--with-pgsql-libs=/usr/local/pgsql-16.8/lib
make install
数据准备
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
--time=60 --pgsql-host=10.206.16.5 --pgsql-port=5432 \
--report-interval=1 \
--pgsql-user=tpcc \
--pgsql-password=tpcc@123 \
--pgsql-db=tpcc \
--rand-seed=0 \
--tables=16 \
--table-size=1000000 \
--threads=256 prepare
开始测试
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
--time=60 --pgsql-host=10.206.16.5 --pgsql-port=5432 \
--report-interval=1 \
--pgsql-user=tpcc \
--pgsql-password=tpcc@123 \
--pgsql-db=tpcc \
--rand-seed=0 \
--tables=16 \
--table-size=1000000 \
--threads=256 run
清理
sysbench /usr/local/share/sysbench/oltp_read_write.lua \
--time=60 --pgsql-host=10.206.16.5 --pgsql-port=5432 \
--report-interval=1 \
--pgsql-user=tpcc \
--pgsql-password=tpcc@123 \
--pgsql-db=tpcc \
--rand-seed=0 \
--tables=16 \
--table-size=1000000 \
--threads=256 cleanup
测试结果：
128 并发 tps 16622.32
256 并发 tps 17979.55
128 threads:
[ 55s ] thds: 128 tps: 14390.22 qps: 287496.65 (r/w/o: 201292.22/57406.01/28798.42) lat (ms,95%): 17.32 err/s: 5.99 reconn/s: 0.00
[ 56s ] thds: 128 tps: 14463.30 qps: 289273.99 (r/w/o: 202482.18/57844.19/28947.62) lat (ms,95%): 17.32 err/s: 5.00 reconn/s: 0.00
[ 57s ] thds: 128 tps: 15249.52 qps: 305853.46 (r/w/o: 214016.32/61315.11/30522.04) lat (ms,95%): 16.71 err/s: 4.00 reconn/s: 0.00
[ 58s ] thds: 128 tps: 16107.56 qps: 322277.09 (r/w/o: 225603.77/64434.22/32239.11) lat (ms,95%): 12.75 err/s: 7.00 reconn/s: 0.00
[ 59s ] thds: 128 tps: 15446.05 qps: 308542.95 (r/w/o: 216094.66/61535.19/30913.10) lat (ms,95%): 14.21 err/s: 8.00 reconn/s: 0.00
[ 60s ] thds: 128 tps: 15695.65 qps: 314185.63 (r/w/o: 219918.86/62852.51/31414.27) lat (ms,95%): 13.46 err/s: 3.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 14039004
write: 4009882
other: 2006196
total: 20055082
transactions: 1002467 (16622.32 per sec.)
queries: 20055082 (332541.56 per sec.)
ignored errors: 319 (5.29 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.3077s
total number of events: 1002467
Latency (ms):
min: 1.49
avg: 7.66
max: 243.03
95th percentile: 12.98
sum: 7681207.51
Threads fairness:
events (avg/stddev): 7831.7734/62.87
execution time (avg/stddev): 60.0094/0.06
256 threads:
[ 50s ] thds: 256 tps: 17757.24 qps: 357063.22 (r/w/o: 249767.62/71735.10/35560.50) lat (ms,95%): 27.17 err/s: 9.00 reconn/s: 0.00
[ 51s ] thds: 256 tps: 18712.59 qps: 373150.85 (r/w/o: 261427.27/74271.42/37452.17) lat (ms,95%): 25.28 err/s: 7.00 reconn/s: 0.00
[ 52s ] thds: 256 tps: 17406.83 qps: 348845.38 (r/w/o: 244116.49/69894.24/34834.65) lat (ms,95%): 28.67 err/s: 8.00 reconn/s: 0.00
[ 53s ] thds: 256 tps: 18828.88 qps: 376352.55 (r/w/o: 263404.27/75267.51/37680.77) lat (ms,95%): 25.74 err/s: 8.00 reconn/s: 0.00
[ 54s ] thds: 256 tps: 17492.69 qps: 349652.71 (r/w/o: 244908.59/69706.75/35037.37) lat (ms,95%): 26.68 err/s: 12.00 reconn/s: 0.00
[ 55s ] thds: 256 tps: 17915.31 qps: 359092.19 (r/w/o: 251266.33/71958.25/35867.62) lat (ms,95%): 26.68 err/s: 9.00 reconn/s: 0.00
[ 56s ] thds: 256 tps: 18944.64 qps: 378683.81 (r/w/o: 265209.94/75561.60/37912.27) lat (ms,95%): 24.83 err/s: 4.00 reconn/s: 0.00
[ 57s ] thds: 256 tps: 17351.51 qps: 346899.18 (r/w/o: 242812.13/69351.04/34736.02) lat (ms,95%): 28.67 err/s: 12.00 reconn/s: 0.00
[ 58s ] thds: 256 tps: 17994.63 qps: 361102.78 (r/w/o: 252552.90/72539.62/36010.26) lat (ms,95%): 25.74 err/s: 8.00 reconn/s: 0.00
[ 59s ] thds: 256 tps: 18669.62 qps: 372699.42 (r/w/o: 261147.69/74172.49/37379.24) lat (ms,95%): 25.28 err/s: 9.00 reconn/s: 0.00
[ 60s ] thds: 256 tps: 17515.68 qps: 350795.52 (r/w/o: 245522.47/70208.69/35064.36) lat (ms,95%): 27.17 err/s: 7.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 15261414
write: 4358582
other: 2181078
total: 21801074
transactions: 1089628 (17979.55 per sec.)
queries: 21801074 (359731.58 per sec.)
ignored errors: 473 (7.80 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.6029s
total number of events: 1089628
Latency (ms):
min: 1.82
avg: 14.13
max: 580.88
95th percentile: 26.20
sum: 15398953.94
Threads fairness:
events (avg/stddev): 4256.3594/93.93
execution time (avg/stddev): 60.1522/0.19
benchmarkSQL
openJDK
yum search java-1.8
yum -y install java-1.8.0-openjdk-devel.x86_64
下载
wget https://github.com/pingcap/benchmarksql/archive/refs/heads/5.0-mysql-support-opt-2.1.zip
解压
unzip 5.0-mysql-support-opt-2.1.zip
cd /root/benchmarksql-5.0-mysql-support-opt-2.1
java -version
替换
cd /root/benchmarksql-5.0-mysql-support-opt-2.1/lib/postgres/
rm -f postgresql-9.3-1102.jdbc41.jar
wget https://jdbc.postgresql.org/download/postgresql-42.7.5.jar
编译
yum install ant* -y
cd /root/benchmarksql-5.0-mysql-support-opt-2.1
ant
准备
cd /root/benchmarksql-5.0-mysql-support-opt-2.1/run
修改对应配置
vim props.pg
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://10.206.16.5:5432/tpcc?prepareThreshold=1&batchMode=on&fetchsize=10&loggerLevel=off
user=tpcc
password=tpcc@123
warehouses=200
terminals=256
runMins=1
runTxnsPerTerminal=0
loadWorkers=100
limitTxnsPerMin=0
terminalWarehouseFixed=false
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
创建数据库表
cd /root/benchmarksql-5.0-mysql-support-opt-2.1/run
vim sql.common/tableCreates.sql
WITH (FILLFACTOR=60);
注：大表添加fillfactor
构建仓库
sh runDatabaseBuild.sh props.pg
运行压测
./runBenchmark.sh props.pg
清理
./runDatabaseDestroy.sh props.pg
系统负载
测试数据
tpmc 346524.84
03:14:07,637 [Thread-317] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 346524.84
03:14:07,637 [Thread-317] INFO jTPCC : Term-00, Measured tpmTOTAL = 769798.71
03:14:07,652 [Thread-317] INFO jTPCC : Term-00, Session Start = 2025-05-25 03:13:06
03:14:07,652 [Thread-317] INFO jTPCC : Term-00, Session End = 2025-05-25 03:14:07
03:14:07,653 [Thread-317] INFO jTPCC : Term-00, Transaction Count = 784642
03:14:07,653 [Thread-317] INFO jTPCC : executeTime[Payment]=16118049
03:14:07,653 [Thread-317] INFO jTPCC : executeTime[Order-Status]=125247
03:14:07,653 [Thread-317] INFO jTPCC : executeTime[Delivery]=1188902
03:14:07,653 [Thread-317] INFO jTPCC : executeTime[Stock-Level]=228772
03:14:07,653 [Thread-317] INFO jTPCC : executeTime[New-Order]=12456615
最后修改时间：2025-05-25 14:23:20
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
