PostgreSQL 11 1万亿 tpcb 性能测试 on 阿里云ECS + ESSD + zfs/lvm2条带 + block_size=32K

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 标签PostgreSQL , pgbench , tpcb , tpcc , tpch , lvm2 , zfs , 条带背景最近的几个PostgreSQL OLTP与OLAP的测试:《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbe...

标签

PostgreSQL , pgbench , tpcb , tpcc , tpch , lvm2 , zfs , 条带


背景

最近的几个PostgreSQL OLTP与OLAP的测试:

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

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

《PostgreSQL 100亿 tpcb 性能 on ECS》

《[未完待续] PostgreSQL on 阿里云ECS+ESSD - 1000亿 tpcb、1000W tpcc 测试》

覆盖面:

1、SF=10, SF=200 TPCH

2、1000W TPCC

3、100亿 TPCB

4、1000亿 TPCB

5、1万亿 TPCB(约125TB 单表。本文要测试的)

本文使用的是16块ESSD云盘,测试时,使用了两套文件系统,ZFS与EXT4,都使用到了条带。

环境

1、ecs,CentOS 7.4 x64

2、CPU

lscpu  
  
Architecture:          x86_64  
CPU op-mode(s):        32-bit, 64-bit  
Byte Order:            Little Endian  
CPU(s):                64  
On-line CPU(s) list:   0-63  
Thread(s) per core:    2  
Core(s) per socket:    32  
Socket(s):             1  
NUMA node(s):          1  
Vendor ID:             GenuineIntel  
CPU family:            6  
Model:                 85  
Model name:            Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz  
Stepping:              4  
CPU MHz:               2499.996  
BogoMIPS:              4999.99  
Hypervisor vendor:     KVM  
Virtualization type:   full  
L1d cache:             32K  
L1i cache:             32K  
L2 cache:              1024K  
L3 cache:              33792K  
NUMA node0 CPU(s):     0-63  
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 fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1  

3、内核

uname -a  
Linux pg11-320tb-zfs 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux  

4、内存

free -g  
              total        used        free      shared  buff/cache   available  
Mem:            503           2         500           0           0         498  
Swap:             0           0           0  

5、时钟

echo tsc > /sys/devices/system/clocksource/clocksource0/current_clocksource  

6、块设备

lsblk  
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT  
vda    253:0    0  200G  0 disk   
└─vda1 253:1    0  200G  0 part /  
vdb    253:16   0   20T  0 disk   
vdc    253:32   0   20T  0 disk   
vdd    253:48   0   20T  0 disk   
vde    253:64   0   20T  0 disk   
vdf    253:80   0   20T  0 disk   
vdg    253:96   0   20T  0 disk   
vdh    253:112  0   20T  0 disk   
vdi    253:128  0   20T  0 disk   
vdj    253:144  0   20T  0 disk   
vdk    253:160  0   20T  0 disk   
vdl    253:176  0   20T  0 disk   
vdm    253:192  0   20T  0 disk   
vdn    253:208  0   20T  0 disk   
vdo    253:224  0   20T  0 disk   
vdp    253:240  0   20T  0 disk   
vdq    253:256  0   20T  0 disk   

配置ECS虚拟机OS参数

1、内核参数

vi /etc/sysctl.conf    
    
# add by digoal.zhou        
fs.aio-max-nr = 1048576        
fs.file-max = 76724600        
        
# 可选:kernel.core_pattern = /data01/corefiles/core_%e_%u_%t_%s.%p                 
# /data01/corefiles 事先建好,权限777,如果是软链接,对应的目录修改为777        
        
kernel.sem = 4096 2147483647 2147483646 512000            
# 信号量, ipcs -l 或 -u 查看,每16个进程一组,每组信号量需要17个信号量。        
        
kernel.shmall = 107374182              
# 所有共享内存段相加大小限制 (建议内存的80%),单位为页。        
kernel.shmmax = 274877906944           
# 最大单个共享内存段大小 (建议为内存一半), >9.2的版本已大幅降低共享内存的使用,单位为字节。        
kernel.shmmni = 819200                 
# 一共能生成多少共享内存段,每个PG数据库集群至少2个共享内存段        
        
net.core.netdev_max_backlog = 10000        
net.core.rmem_default = 262144               
# The default setting of the socket receive buffer in bytes.        
net.core.rmem_max = 4194304                  
# The maximum receive socket buffer size in bytes        
net.core.wmem_default = 262144               
# The default setting (in bytes) of the socket send buffer.        
net.core.wmem_max = 4194304                  
# The maximum send socket buffer size in bytes.        
net.core.somaxconn = 4096        
net.ipv4.tcp_max_syn_backlog = 4096        
net.ipv4.tcp_keepalive_intvl = 20        
net.ipv4.tcp_keepalive_probes = 3        
net.ipv4.tcp_keepalive_time = 60        
net.ipv4.tcp_mem = 8388608 12582912 16777216        
net.ipv4.tcp_fin_timeout = 5        
net.ipv4.tcp_synack_retries = 2        
net.ipv4.tcp_syncookies = 1            
# 开启SYN Cookies。当出现SYN等待队列溢出时,启用cookie来处理,可防范少量的SYN攻击        
net.ipv4.tcp_timestamps = 1            
# 减少time_wait        
net.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 = 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.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 = 65536        
vm.overcommit_memory = 0             
#  在分配内存时,允许少量over malloc, 如果设置为 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 = 40000 65535            
# 本地自动分配的TCP, UDP端口号范围        
fs.nr_open=20480000        
# 单个进程允许打开的文件句柄上限        
        
# 以下参数请注意        
# vm.extra_free_kbytes = 4096000        
# vm.min_free_kbytes = 2097152  # vm.min_free_kbytes 建议每32G内存分配1G vm.min_free_kbytes       
# 如果是小内存机器,以上两个值不建议设置        
# vm.nr_hugepages = 66536            
#  建议shared buffer设置超过64GB时 使用大页,页大小 /proc/meminfo Hugepagesize        
# vm.lowmem_reserve_ratio = 1 1 1        
# 对于内存大于64G时,建议设置,否则建议默认值 256 256 32    

2、资源限制

vi /etc/security/limits.conf    
    
# nofile超过1048576的话,一定要先将sysctl的fs.nr_open设置为更大的值,并生效后才能继续设置nofile.        
        
* soft    nofile  1024000        
* hard    nofile  1024000        
* soft    nproc   unlimited        
* hard    nproc   unlimited        
* soft    core    unlimited        
* hard    core    unlimited        
* soft    memlock unlimited        
* hard    memlock unlimited       

3、关闭透明大页,使用精准时钟(可选)

vi /etc/rc.local    
    
touch /var/lock/subsys/local    
         
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then        
   echo never > /sys/kernel/mm/transparent_hugepage/enabled        
fi      
  
# 时钟  
echo tsc > /sys/devices/system/clocksource/clocksource0/current_clocksource  
  
su - postgres -c "pg_ctl start"      

部署 PostgreSQL 11

rpm -ivh https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm    
    
rpm -ivh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm     
    
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 lvm2 perf      
    
yum install -y postgresql11*    

块设备部署策略1 - zfs

zfsonlinux

《[未完待续] PostgreSQL on ECS 高效率持续备份设计 - By ZFS on Linux》

《PostgreSQL OLTP on ZFS 性能优化》

1、zfs yum配置

yum install -y http://download.zfsonlinux.org/epel/zfs-release.el7_4.noarch.rpm  

2、当前内核对应的kernel-devel

http://vault.centos.org

uname -a  
Linux pg11-320tb-zfs 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux  
rpm -ivh http://vault.centos.org/7.4.1708/updates/x86_64/Packages/kernel-devel-3.10.0-693.2.2.el7.x86_64.rpm  

3、安装zfs

yum install -y zfs   

查看日志,是否有报错,正常情况下没有报错

测试是否可用

modprobe zfs  
  
zfs get -o all  

系统启动将自动加载zfs

创建zpool

essd底层三副本,无需再使用zfs的RAID功能。

parted -a optimal -s /dev/vdb mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdc mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdd mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vde mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdf mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdg mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdh mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdi mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdj mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdk mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdl mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdm mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdn mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdo mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdp mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdq mklabel gpt mkpart primary 1MiB 100%FREE  
zpool create zp1 -f -o ashift=13 vdb1 vdc1 vdd1 vde1 vdf1 vdg1 vdh1 vdi1 vdj1 vdk1 vdl1 vdm1 vdn1 vdo1 vdp1 vdq1  
zpool get all zp1  
NAME  PROPERTY                       VALUE                          SOURCE  
zp1   size                           318T                           -  
zp1   capacity                       0%                             -  
zp1   altroot                        -                              default  
zp1   health                         ONLINE                         -  
zp1   guid                           12407519490197584982           -  
zp1   version                        -                              default  
zp1   bootfs                         -                              default  
zp1   delegation                     on                             default  
zp1   autoreplace                    off                            default  
zp1   cachefile                      -                              default  
zp1   failmode                       wait                           default  
zp1   listsnapshots                  off                            default  
zp1   autoexpand                     off                            default  
zp1   dedupditto                     0                              default  
zp1   dedupratio                     1.00x                          -  
zp1   free                           318T                           -  
zp1   allocated                      960K                           -  
zp1   readonly                       off                            -  
zp1   ashift                         13                             local  
zp1   comment                        -                              default  
zp1   expandsize                     -                              -  
zp1   freeing                        0                              -  
zp1   fragmentation                  0%                             -  
zp1   leaked                         0                              -  
zp1   multihost                      off                            default  
zp1   feature@async_destroy          enabled                        local  
zp1   feature@empty_bpobj            enabled                        local  
zp1   feature@lz4_compress           active                         local  
zp1   feature@multi_vdev_crash_dump  enabled                        local  
zp1   feature@spacemap_histogram     active                         local  
zp1   feature@enabled_txg            active                         local  
zp1   feature@hole_birth             active                         local  
zp1   feature@extensible_dataset     active                         local  
zp1   feature@embedded_data          active                         local  
zp1   feature@bookmarks              enabled                        local  
zp1   feature@filesystem_limits      enabled                        local  
zp1   feature@large_blocks           enabled                        local  
zp1   feature@large_dnode            enabled                        local  
zp1   feature@sha512                 enabled                        local  
zp1   feature@skein                  enabled                        local  
zp1   feature@edonr                  enabled                        local  
zp1   feature@userobj_accounting     active                         local  

创建zfs

zfs create -o mountpoint=/data01 -o recordsize=8K -o atime=off -o primarycache=metadata -o logbias=throughput -o secondarycache=none zp1/data01  
  
  
zfs set canmount=off zp1  

优化两个参数

cd /sys/module/zfs/parameters/  
echo 1 > zfs_prefetch_disable  
echo 15 > zfs_arc_shrink_shift   

查看当前参数

zfs get all zp1/data01  
NAME        PROPERTY              VALUE                  SOURCE  
zp1/data01  type                  filesystem             -  
zp1/data01  creation              Wed Sep 19 10:26 2018  -  
zp1/data01  used                  192K                   -  
zp1/data01  available             308T                   -  
zp1/data01  referenced            192K                   -  
zp1/data01  compressratio         1.00x                  -  
zp1/data01  mounted               yes                    -  
zp1/data01  quota                 none                   default  
zp1/data01  reservation           none                   default  
zp1/data01  recordsize            8K                     local  
zp1/data01  mountpoint            /data01                local  
zp1/data01  sharenfs              off                    default  
zp1/data01  checksum              on                     default  
zp1/data01  compression           off                    default  
zp1/data01  atime                 off                    local  
zp1/data01  devices               on                     default  
zp1/data01  exec                  on                     default  
zp1/data01  setuid                on                     default  
zp1/data01  readonly              off                    default  
zp1/data01  zoned                 off                    default  
zp1/data01  snapdir               hidden                 default  
zp1/data01  aclinherit            restricted             default  
zp1/data01  createtxg             81                     -  
zp1/data01  canmount              on                     default  
zp1/data01  xattr                 on                     default  
zp1/data01  copies                1                      default  
zp1/data01  version               5                      -  
zp1/data01  utf8only              off                    -  
zp1/data01  normalization         none                   -  
zp1/data01  casesensitivity       sensitive              -  
zp1/data01  vscan                 off                    default  
zp1/data01  nbmand                off                    default  
zp1/data01  sharesmb              off                    default  
zp1/data01  refquota              none                   default  
zp1/data01  refreservation        none                   default  
zp1/data01  guid                  3373300831209850945    -  
zp1/data01  primarycache          metadata               local  
zp1/data01  secondarycache        none                   default  
zp1/data01  usedbysnapshots       0B                     -  
zp1/data01  usedbydataset         192K                   -  
zp1/data01  usedbychildren        0B                     -  
zp1/data01  usedbyrefreservation  0B                     -  
zp1/data01  logbias               throughput             local  
zp1/data01  dedup                 off                    default  
zp1/data01  mlslabel              none                   default  
zp1/data01  sync                  standard               default  
zp1/data01  dnodesize             legacy                 default  
zp1/data01  refcompressratio      1.00x                  -  
zp1/data01  written               192K                   -  
zp1/data01  logicalused           76K                    -  
zp1/data01  logicalreferenced     76K                    -  
zp1/data01  volmode               default                default  
zp1/data01  filesystem_limit      none                   default  
zp1/data01  snapshot_limit        none                   default  
zp1/data01  filesystem_count      none                   default  
zp1/data01  snapshot_count        none                   default  
zp1/data01  snapdev               hidden                 default  
zp1/data01  acltype               off                    default  
zp1/data01  context               none                   default  
zp1/data01  fscontext             none                   default  
zp1/data01  defcontext            none                   default  
zp1/data01  rootcontext           none                   default  
zp1/data01  relatime              off                    default  
zp1/data01  redundant_metadata    all                    default  
zp1/data01  overlay               off                    default  

初始化数据库

1、目录

mkdir /data01/pg11  
  
chown postgres:postgres /data01/pg11  

2、环境变量

su - postgres  
  
vi .bash_profile  
  
export PS1="$USER@`/bin/hostname -s`-> "        
export PGPORT=1921        
export PGDATA=/data01/pg11/pg_root$PGPORT        
export LANG=en_US.utf8        
export PGHOME=/usr/pgsql-11      
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH        
export DATE=`date +"%Y%m%d%H%M"`      
export PATH=$PGHOME/bin:$PATH:.        
export MANPATH=$PGHOME/share/man:$MANPATH        
export PGHOST=$PGDATA        
export PGUSER=postgres        
export PGDATABASE=postgres        
alias rm='rm -i'        
alias ll='ls -lh'        
unalias vi   

3、初始化

initdb -D $PGDATA -U postgres -E SQL_ASCII --lc-collate=C --lc-ctype=en_US.utf8 --wal-segsize=1024   

huge page配置

zfs 可以绕过文件系统cache,所以建议一配置较大postgresql shared buffer,并使用huge page

《PostgreSQL Huge Page 使用建议 - 大内存主机、实例注意》

300GB/2MB=153600  
sysctl -w vm.nr_hugepages=159600     
  
echo "vm.nr_hugepages=159600" >> /etc/sysctl.conf  

postgresql.auto.conf

listen_addresses = '0.0.0.0'    
port = 1921    
max_connections = 2000    
superuser_reserved_connections = 3    
unix_socket_directories = '., /var/run/postgresql, /tmp'    
tcp_keepalives_idle = 60    
tcp_keepalives_interval = 10    
tcp_keepalives_count = 10    
huge_pages = on  # 使用huge page  
shared_buffers = 300GB    
max_prepared_transactions = 2000    
work_mem = 32MB    
maintenance_work_mem = 2GB    
dynamic_shared_memory_type = posix    
vacuum_cost_delay = 0    
bgwriter_delay = 10ms    
bgwriter_lru_maxpages = 1000    
bgwriter_lru_multiplier = 10.0    
effective_io_concurrency = 0    
max_worker_processes = 128    
max_parallel_workers = 64    
max_parallel_maintenance_workers = 64   
max_parallel_workers_per_gather = 0    
parallel_leader_participation = on    
min_parallel_table_scan_size=0  
min_parallel_index_scan_size=0  
parallel_setup_cost=0  
parallel_tuple_cost=0  
wal_level = minimal      
synchronous_commit = off    
full_page_writes=off       #  zfs内置了checksum,cow. 关闭 fpw . 如果BLOCKDEV能保证8K原子写时,也可以关闭   
wal_writer_delay = 10ms    
checkpoint_timeout = 30min    
max_wal_size = 600GB    
min_wal_size = 150GB    
checkpoint_completion_target = 0.1    
max_wal_senders = 0    
effective_cache_size = 200GB    
log_destination = 'csvlog'    
logging_collector = on    
log_directory = 'log'    
log_filename = 'postgresql-%a.log'    
log_truncate_on_rotation = on    
log_rotation_age = 1d    
log_rotation_size = 0    
log_checkpoints = on     
log_connections = on    
log_disconnections = on    
log_error_verbosity = verbose     
log_line_prefix = '%m [%p] '    
log_timezone = 'PRC'    
log_autovacuum_min_duration = 0    
autovacuum_max_workers = 16    
autovacuum_freeze_max_age = 1200000000    
autovacuum_multixact_freeze_max_age = 1400000000    
autovacuum_vacuum_cost_delay = 0ms    
vacuum_freeze_table_age = 1150000000    
vacuum_multixact_freeze_table_age = 1150000000    
datestyle = 'iso, mdy'    
timezone = 'PRC'    
lc_messages = 'C'    
lc_monetary = 'C'    
lc_numeric = 'C'    
lc_time = 'C'    
default_text_search_config = 'pg_catalog.english'    
jit = off    
cpu_tuple_cost=0.00018884145574257426      
cpu_index_tuple_cost = 0.00433497085216479990      
cpu_operator_cost = 0.00216748542608239995      
seq_page_cost=0.014329      
random_page_cost = 0.016   
  

restart 数据库

pg_ctl restart -m fast  

zfs on linux 性能问题

本例测试时,(vacuum 时很慢,可以看出问题 pgbench -i -s 1000 可复现)

vacuum 进程D状态,stack如下

[<ffffffffc0174132>] cv_wait_common+0xb2/0x150 [spl]
[<ffffffffc0174208>] __cv_wait_io+0x18/0x20 [spl]
[<ffffffffc073c42b>] zio_wait+0x10b/0x1b0 [zfs]
[<ffffffffc0687124>] dmu_buf_hold_array_by_dnode+0x154/0x4a0 [zfs]
[<ffffffffc06885f2>] dmu_read_uio_dnode+0x52/0x100 [zfs]
[<ffffffffc06886ec>] dmu_read_uio_dbuf+0x4c/0x70 [zfs]
[<ffffffffc07242d5>] zfs_read+0x135/0x3f0 [zfs]
[<ffffffffc0743990>] zpl_read_common_iovec.constprop.9+0x80/0xd0 [zfs]
[<ffffffffc0743aa6>] zpl_aio_read+0xc6/0xf0 [zfs]
[<ffffffff812001ad>] do_sync_read+0x8d/0xd0
[<ffffffff81200bac>] vfs_read+0x9c/0x170
[<ffffffff81201a6f>] SyS_read+0x7f/0xe0
[<ffffffff816b5009>] system_call_fastpath+0x16/0x1b
[<ffffffffffffffff>] 0xffffffffffffffff

块设备部署策略1 - lvm2 , ext4

1、停库,消除zfs

pg_ctl stop -m immediate  
  
zfs destroy zp1/data01  
zpool destroy zp1  

2、清理块设备头信息

wipefs -f -a /dev/vd[b-q]  
  
  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdb  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdc  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdd  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vde  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdf  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdg  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdh  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdi  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdj  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdk  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdl  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdm  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdn  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdo  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdp  
dd bs=1024 count=1000 if=/dev/zero of=/dev/vdq  
  
  
parted -a optimal -s /dev/vdb mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdc mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdd mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vde mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdf mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdg mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdh mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdi mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdj mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdk mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdl mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdm mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdn mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdo mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdp mklabel gpt mkpart primary 1MiB 100%FREE  
parted -a optimal -s /dev/vdq mklabel gpt mkpart primary 1MiB 100%FREE  
  
  
wipefs -f -a /dev/vd[b-q]1  

3、创建PV

pvcreate /dev/vd[b-q]1  

4、创建vg

vgcreate -A y -s 128M vgdata01 /dev/vd[b-q]1    

5、创建逻辑卷,配置条带

lvcreate -A y -i 16 -I 8 -L 4TiB -n lv03 vgdata01    
lvcreate -A y -i 16 -I 8 -L 220TiB -n lv01 vgdata01    
lvcreate -A y -i 16 -I 8 -l 100%FREE -n lv02 vgdata01    

6、查看

[root@pg11-320tb-zfs ~]# pvs  
  PV         VG       Fmt  Attr PSize   PFree  
  /dev/vdb   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdc   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdd   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vde   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdf   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdg   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdh   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdi   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdj   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdk   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdl   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdm   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdn   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdo   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdp   vgdata01 lvm2 a--  <20.00t    0   
  /dev/vdq   vgdata01 lvm2 a--  <20.00t    0   
[root@pg11-320tb-zfs ~]# vgs  
  VG       #PV #LV #SN Attr   VSize    VFree  
  vgdata01  16   3   0 wz--n- <320.00t    0   
[root@pg11-320tb-zfs ~]# lvs  
  LV   VG       Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert  
  lv01 vgdata01 -wi-a----- 220.00t                                                      
  lv02 vgdata01 -wi-a----- <96.00t                                                      
  lv03 vgdata01 -wi-a-----   4.00t   

7、创建ext4文件系统,配置条带

mkfs.ext4 /dev/mapper/vgdata01-lv01 -m 0 -O extent,uninit_bg -E lazy_itable_init=1,stride=2,stripe_width=32 -b 4096 -T largefile -L lv01    
mkfs.ext4 /dev/mapper/vgdata01-lv02 -m 0 -O extent,uninit_bg -E lazy_itable_init=1,stride=2,stripe_width=32 -b 4096 -T largefile -L lv02    
mkfs.ext4 /dev/mapper/vgdata01-lv03 -m 0 -O extent,uninit_bg -E lazy_itable_init=1,stride=2,stripe_width=32 -b 4096 -T largefile -L lv03    

8、配置挂载

vi /etc/fstab     
LABEL=lv01 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0  
LABEL=lv02 /data02 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0  
LABEL=lv03 /data03 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0  
    
    
mkdir /data01    
mkdir /data02    
mkdir /data03    
    
    
mount -a    
[root@pg11-320tb-zfs ~]# df -h  
Filesystem                 Size  Used Avail Use% Mounted on  
/dev/vda1                  197G  2.1G  185G   2% /  
devtmpfs                   252G     0  252G   0% /dev  
tmpfs                      252G     0  252G   0% /dev/shm  
tmpfs                      252G  596K  252G   1% /run  
tmpfs                      252G     0  252G   0% /sys/fs/cgroup  
tmpfs                       51G     0   51G   0% /run/user/0  
/dev/mapper/vgdata01-lv01  220T   20K  220T   1% /data01  
/dev/mapper/vgdata01-lv02   96T   20K   96T   1% /data02  
/dev/mapper/vgdata01-lv03  4.0T   89M  4.0T   1% /data03  

9、创建数据库数据、表空间、WAL日志目录

[root@pg11-320tb-zfs ~]# mkdir /data01/pg11  
[root@pg11-320tb-zfs ~]# mkdir /data02/pg11  
[root@pg11-320tb-zfs ~]# mkdir /data03/pg11  
[root@pg11-320tb-zfs ~]# chown postgres:postgres /data0*/pg11  

10、配置环境变量

su - postgres  
  
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=1921  
export PGDATA=/data01/pg11/pg_root$PGPORT  
export LANG=en_US.utf8  
export PGHOME=/usr/pgsql-11  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGUSER=postgres  
export PGDATABASE=postgres  
alias rm='rm -i'  
alias ll='ls -lh'  
unalias vi  

11、初始化数据库

initdb -D $PGDATA -X /data03/pg11/pg_wal1921 -U postgres -E SQL_ASCII --lc-collate=C --lc-ctype=en_US.utf8 --wal-segsize=1024   

12、创建表空间

mkdir /data01/pg11/tbs1  
mkdir /data02/pg11/tbs2  
  
  
create tablespace tbs1 location '/data01/pg11/tbs1';  
create tablespace tbs2 location '/data02/pg11/tbs2';  

1万亿 tpcb test

初始化数据

nohup pgbench -i -s 10000000 -I dtg -n --tablespace=tbs1 >./init.log 2>&1 &  

耗时810688秒,约123.3万行/s

999999100000 of 1000000000000 tuples (99%) done (elapsed 810688.06 s, remaining 0.73 s)
999999200000 of 1000000000000 tuples (99%) done (elapsed 810688.14 s, remaining 0.65 s)
999999300000 of 1000000000000 tuples (99%) done (elapsed 810688.22 s, remaining 0.57 s)
999999400000 of 1000000000000 tuples (99%) done (elapsed 810688.30 s, remaining 0.49 s)
999999500000 of 1000000000000 tuples (99%) done (elapsed 810688.38 s, remaining 0.41 s)
999999600000 of 1000000000000 tuples (99%) done (elapsed 810688.46 s, remaining 0.32 s)
999999700000 of 1000000000000 tuples (99%) done (elapsed 810688.54 s, remaining 0.24 s)
999999800000 of 1000000000000 tuples (99%) done (elapsed 810688.61 s, remaining 0.16 s)
999999900000 of 1000000000000 tuples (99%) done (elapsed 810688.69 s, remaining 0.08 s)
1000000000000 of 1000000000000 tuples (100%) done (elapsed 810688.77 s, remaining 0.00 s)
done.

数据占用空间120TB

postgres=# \l+
                                                                 List of databases
   Name    |  Owner   | Encoding  | Collate |   Ctype    |   Access privileges   |  Size  | Tablespace |                Description                 
-----------+----------+-----------+---------+------------+-----------------------+--------+------------+--------------------------------------------
 postgres  | postgres | SQL_ASCII | C       | en_US.utf8 |                       | 120 TB | pg_default | default administrative connection database
 template0 | postgres | SQL_ASCII | C       | en_US.utf8 | =c/postgres          +| 15 MB  | pg_default | unmodifiable empty database
           |          |           |         |            | postgres=CTc/postgres |        |            | 
 template1 | postgres | SQL_ASCII | C       | en_US.utf8 | =c/postgres          +| 15 MB  | pg_default | default template for new databases
           |          |           |         |            | postgres=CTc/postgres |        |            | 
(3 rows)



postgres=# \dt+
                          List of relations
 Schema |       Name       | Type  |  Owner   |  Size   | Description 
--------+------------------+-------+----------+---------+-------------
 public | pgbench_accounts | table | postgres | 120 TB  | 
 public | pgbench_branches | table | postgres | 344 MB  | 
 public | pgbench_history  | table | postgres | 0 bytes | 
 public | pgbench_tellers  | table | postgres | 4201 MB | 
 public | t                | table | postgres | 804 MB  | 
(5 rows)


postgres=# \db+
                                        List of tablespaces
    Name    |  Owner   |      Location       | Access privileges | Options |  Size   | Description 
------------+----------+---------------------+-------------------+---------+---------+-------------
 pg_default | postgres |                     |                   |         | 850 MB  | 
 pg_global  | postgres |                     |                   |         | 2206 kB | 
 tbs1       | postgres | /data01/digoal/tbs1 |                   |         | 120 TB  | 
 tbs2       | postgres | /data02/digoal/tbs2 |                   |         | 213 MB  | 
(4 rows)

新增、删除字段测试,秒级

新增、删除字段都只需要改元数据,秒级完成。

PostgreSQL新增带default值的字段,也是秒级完成。

《PostgreSQL 11 preview - 添加非空默认值不需要 rewrite table - online add column with default value》

postgres=# \timing
Timing is on.
postgres=# select * from pgbench_accounts limit 10;
 aid | bid | abalance |                                        filler                                        
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |                                                                                     
   2 |   1 |        0 |                                                                                     
   3 |   1 |        0 |                                                                                     
   4 |   1 |        0 |                                                                                     
   5 |   1 |        0 |                                                                                     
   6 |   1 |        0 |                                                                                     
   7 |   1 |        0 |                                                                                     
   8 |   1 |        0 |                                                                                     
   9 |   1 |        0 |                                                                                     
  10 |   1 |        0 |                                                                                     
(10 rows)

Time: 498.051 ms

-- 秒级添加字段

postgres=# alter table pgbench_accounts add column col1 text;
ALTER TABLE
Time: 1254.611 ms (00:01.255)
  
-- 秒级添加非空默认值字段
postgres=# alter table pgbench_accounts add column col2 text default 'hello digoal';
ALTER TABLE
Time: 1253.689 ms (00:01.254)


postgres=# select * from pgbench_accounts limit 10;
 aid | bid | abalance |                                        filler                                        | col1 |     col2     
-----+-----+----------+--------------------------------------------------------------------------------------+------+--------------
   1 |   1 |        0 |                                                                                      |      | hello digoal
   2 |   1 |        0 |                                                                                      |      | hello digoal
   3 |   1 |        0 |                                                                                      |      | hello digoal
   4 |   1 |        0 |                                                                                      |      | hello digoal
   5 |   1 |        0 |                                                                                      |      | hello digoal
   6 |   1 |        0 |                                                                                      |      | hello digoal
   7 |   1 |        0 |                                                                                      |      | hello digoal
   8 |   1 |        0 |                                                                                      |      | hello digoal
   9 |   1 |        0 |                                                                                      |      | hello digoal
  10 |   1 |        0 |                                                                                      |      | hello digoal
(10 rows)

Time: 502.608 ms
postgres=# explain analyze select * from pgbench_accounts limit 10;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.00 rows=10 width=168) (actual time=0.022..0.026 rows=10 loops=1)
   ->  Seq Scan on pgbench_accounts  (cost=0.00..184941625.46 rows=673387096855 width=168) (actual time=0.020..0.022 rows=10 loops=1)
 Planning Time: 0.057 ms
 Execution Time: 0.041 ms
(4 rows)

Time: 0.453 ms

-- 分析表
postgres=# analyze pgbench_accounts ;
ANALYZE
Time: 67373.884 ms (01:07.374)

删除字段,毫秒级
postgres=# alter table pgbench_accounts drop column col1;
ALTER TABLE
Time: 7.610 ms
postgres=# alter table pgbench_accounts drop column col2;
ALTER TABLE
Time: 0.546 ms

创建索引

加载初始化数据结束后,创建索引

1、修改并行度

psql  
  
analyze;  
alter table pgbench_accounts set (parallel_workers=64);  
alter table pgbench_tellers set (parallel_workers=64);  
alter table pgbench_branches set (parallel_workers=64);  

2、创建索引

nohup pgbench -i -s 10000000 -I p -n --index-tablespace=tbs2 >./init_pkey.log 2>&1 &  

开启了64并行度,开始时的消耗资源情况

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.20    0.00   38.52   16.31    0.00   41.97

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
vdb            2783.00  6362.00 3972.00  169.00 49328.00 52360.00    49.11     5.35    1.29    0.93    9.91   0.24  98.90
vdc            2760.00  6361.00 4020.00  167.00 49296.00 51764.00    48.27     5.19    1.23    0.88    9.59   0.24  98.70
vdd            2747.00  6361.00 4010.00  169.00 49332.00 51860.00    48.43     5.18    1.24    0.88    9.64   0.24  98.80
vde            2757.00  6362.00 4032.00  169.00 49344.00 51864.00    48.18     5.13    1.22    0.87    9.54   0.23  98.50
vdf            2732.00  6360.00 4012.00  191.00 49336.00 52532.00    48.47     5.21    1.24    0.85    9.50   0.24  99.10
vdg            2716.00  6361.00 4039.00  191.00 49320.00 52036.00    47.92     5.28    1.25    0.86    9.35   0.23  99.20
vdh            2742.00  6361.00 4038.00  191.00 49340.00 52032.00    47.94     5.46    1.29    0.91    9.26   0.23  98.80
vdi            2749.00  6361.00 4041.00  193.00 49328.00 52544.00    48.12     5.35    1.26    0.88    9.40   0.23  99.20
vdj            2776.00  6385.00 3953.00  169.00 49344.00 52020.00    49.18     5.41    1.31    0.93   10.12   0.24  99.10
vdk            2767.00  6384.00 3999.00  171.00 49328.00 52028.00    48.61     5.52    1.32    0.96    9.76   0.24  99.10
vdl            2775.00  6386.00 3984.00  172.00 49328.00 52032.00    48.78     5.56    1.34    0.97    9.94   0.24  99.10
vdm            2759.00  6385.00 4039.00  172.00 49296.00 52416.00    48.31     5.58    1.32    0.95    9.87   0.23  98.90
vdn            2776.00  6369.00 3967.00  163.00 49352.00 51840.00    49.00     5.48    1.32    0.95   10.33   0.24  99.70
vdo            2776.00  6370.00 3978.00  163.00 49352.00 52220.00    49.06     5.42    1.31    0.93   10.56   0.24  99.30
vdp            2782.00  6370.00 4004.00  162.00 49356.00 51840.00    48.58     5.60    1.34    0.97   10.44   0.24  99.70
vdq            2759.00  6370.00 4033.00  161.00 49352.00 51828.00    48.25     5.61    1.34    0.97   10.48   0.24  99.50
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-1              0.00     0.00 108392.00 104717.00 790740.00 837136.00    15.28  1293.57    5.82    0.94   10.87   0.00 100.90
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00


top - 07:41:20 up 12 days, 22:02,  2 users,  load average: 61.15, 26.97, 10.80
Tasks: 607 total,  39 running, 568 sleeping,   0 stopped,   0 zombie
%Cpu(s):  3.4 us, 54.3 sy,  0.0 ni, 21.2 id, 21.1 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 52819500+total,  2192660 free, 33286761+used, 19313472+buff/cache
KiB Swap:        0 total,        0 free,        0 used. 19267680+avail Mem 
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                                 
65466 digoal    20   0  0.296t  25764   2044 R  68.4  0.0   1:11.05 postgres: parallel worker for PID 65299  
65450 digoal    20   0  0.296t  25756   2076 D  63.2  0.0   1:10.51 postgres: parallel worker for PID 65299  
65460 digoal    20   0  0.296t  25764   2048 R  63.2  0.0   1:10.37 postgres: parallel worker for PID 65299  
65469 digoal    20   0  0.296t  25752   2056 R  63.2  0.0   1:10.48 postgres: parallel worker for PID 65299  
65474 digoal    20   0  0.296t  25764   2052 R  63.2  0.0   1:10.36 postgres: parallel worker for PID 65299  
65478 digoal    20   0  0.296t  25764   2060 R  63.2  0.0   1:10.64 postgres: parallel worker for PID 65299  
65479 digoal    20   0  0.296t  25752   2056 R  63.2  0.0   1:10.47 postgres: parallel worker for PID 65299  
65484 digoal    20   0  0.296t  25760   2056 R  63.2  0.0   1:10.63 postgres: parallel worker for PID 65299  
65485 digoal    20   0  0.296t  25748   2068 R  63.2  0.0   1:11.10 postgres: parallel worker for PID 65299  



Total DISK READ :     834.93 M/s | Total DISK WRITE :    1006.90 M/s
Actual DISK READ:     835.23 M/s | Actual DISK WRITE:     994.87 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                      
65461 be/4 digoal     13.90 M/s   12.74 M/s  0.00 % 51.43 % postgres: parallel worker for PID 65299
65457 be/4 digoal     13.86 M/s   12.95 M/s  0.00 % 51.25 % postgres: parallel worker for PID 65299
65505 be/4 digoal     14.82 M/s   13.05 M/s  0.00 % 51.09 % postgres: parallel worker for PID 65299
65466 be/4 digoal     12.57 M/s   13.48 M/s  0.00 % 50.72 % postgres: parallel worker for PID 65299
65462 be/4 digoal     13.16 M/s   13.23 M/s  0.00 % 50.70 % postgres: parallel worker for PID 65299
65491 be/4 digoal      8.85 M/s   12.99 M/s  0.00 % 50.59 % postgres: parallel worker for PID 65299
65451 be/4 digoal     12.33 M/s   13.48 M/s  0.00 % 50.57 % postgres: parallel worker for PID 65299
65477 be/4 digoal     12.37 M/s   13.20 M/s  0.00 % 50.38 % postgres: parallel worker for PID 65299
65459 be/4 digoal      8.45 M/s   19.33 M/s  0.00 % 50.27 % postgres: parallel worker for PID 65299
65488 be/4 digoal     12.34 M/s   12.74 M/s  0.00 % 50.21 % postgres: parallel worker for PID 65299
65495 be/4 digoal     13.83 M/s   13.26 M/s  0.00 % 50.19 % postgres: parallel worker for PID 65299
65450 be/4 digoal      9.20 M/s   19.45 M/s  0.00 % 50.14 % postgres: parallel worker for PID 65299
65503 be/4 digoal     14.02 M/s   19.66 M/s  0.00 % 50.13 % postgres: parallel worker for PID 65299

等待事件情况

postgres=# select wait_event,wait_event_type,count(*) from pg_stat_activity where wait_event is not null group by 1,2;
     wait_event      | wait_event_type | count 
---------------------+-----------------+-------
 BufFileRead         | IO              |    59
 BufFileWrite        | IO              |     1
 CheckpointerMain    | Activity        |     1
 BgWriterHibernate   | Activity        |     1
 AutoVacuumMain      | Activity        |     1
 LogicalLauncherMain | Activity        |     1
 WalWriterMain       | Activity        |     1
(7 rows)
  
-[ RECORD 7 ]----+---------------------------------------------------------------------------------
datid            | 13220
datname          | postgres
pid              | 65448
usesysid         | 10
usename          | postgres
application_name | pgbench
client_addr      | 
client_hostname  | 
client_port      | 
backend_start    | 2018-10-02 07:38:46.003833+08
xact_start       | 2018-10-02 07:38:46.003114+08
query_start      | 2018-10-02 07:38:46.003114+08
state_change     | 2018-10-02 07:38:46.00439+08
wait_event_type  | IO
wait_event       | BufFileRead
state            | active
backend_xid      | 
backend_xmin     | 598
query            | alter table pgbench_accounts add primary key (aid) using index tablespace "tbs2"
backend_type     | parallel worker

临时空间使用情况,大概19TB,worker工作结束后,开始合并索引。

digoal@pg11-320tb-zfs-> cd $PGDATA/base
digoal@pg11-320tb-zfs-> du -sh *
16M     1
16M     13219
16M     13220
19T     pgsql_tmp

每个并行的worker进程完成自己的任务后,开始合并索引,合并速度

Total DISK READ :     116.21 M/s | Total DISK WRITE :     169.91 M/s
Actual DISK READ:     116.21 M/s | Actual DISK WRITE:     197.28 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                       
65299 be/4 digoal    116.21 M/s  169.91 M/s  0.00 %  8.13 % postgres: postgres postgres [local] ALTER TABLE
65298 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % pgbench -i -s 10000000 -I p -n --index-tablespace=tbs2
51030 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres                 
51032 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: logger
51034 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: checkpointer
51035 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: background writer
51036 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: walwriter
51037 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: autovacuum launcher
51038 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: stats collector
51039 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: logical replication launcher

索引占用空间 20 TB

postgres=# \di+
                                      List of relations
 Schema |         Name          | Type  |  Owner   |      Table       |  Size   | Description 
--------+-----------------------+-------+----------+------------------+---------+-------------
 public | pgbench_accounts_pkey | index | postgres | pgbench_accounts | 20 TB   | 
 public | pgbench_branches_pkey | index | postgres | pgbench_branches | 213 MB  | 
 public | pgbench_tellers_pkey  | index | postgres | pgbench_tellers  | 2125 MB | 
(3 rows)

索引创建耗时

7130分钟

开始
2018-10-02 07:51:00

结束
2018-10-07 06:41:08

压测脚本

只读

vi ro.sql  
  

\set aid random_gaussian(1, :range, 10.0)  
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 360 -D range=100000000
pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 360 -D range=1000000000
pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 360 -D range=10000000000
pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 360 -D range=100000000000
pgbench -M prepared -n -r -P 1 -f ./ro.sql -c 64 -j 64 -T 360 -D range=1000000000000

读写

vi rw.sql  
  
\set aid random_gaussian(1, :range, 10.0)  
\set bid random(1, 1 * :scale)  
\set tid random(1, 10 * :scale)  
\set delta random(-5000, 5000)  
BEGIN;  
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;  
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;  
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;  
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;  
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);  
END;  
pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 360 -D scale=10000000 -D range=100000000
pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 360 -D scale=10000000 -D range=1000000000
pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 360 -D scale=10000000 -D range=10000000000
pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 360 -D scale=10000000 -D range=100000000000
pgbench -M prepared -n -r -P 1 -f ./rw.sql -c 64 -j 64 -T 360 -D scale=10000000 -D range=1000000000000

1、1万亿 tpcb 只读测试 - 1万亿数据活跃

TPS:

QPS:

transaction type: ./ro.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 7737610
latency average = 2.977 ms
latency stddev = 35.840 ms
tps = 21492.371917 (including connections establishing)
tps = 21495.359217 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random_gaussian(1, :range, 10.0)
         2.975  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

1、1万亿 tpcb 读写测试 - 1万亿数据活跃

TPS:

QPS:

transaction type: ./rw.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 5812634
latency average = 3.963 ms
latency stddev = 48.480 ms
tps = 16143.312370 (including connections establishing)
tps = 16145.557184 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random_gaussian(1, :range, 10.0)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.025  BEGIN;
         3.511  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.063  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.155  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.119  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.047  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.041  END;

2、1万亿 tpcb 只读测试 - 1000亿数据活跃

TPS:

QPS:

transaction type: ./ro.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 8317050
latency average = 2.770 ms
latency stddev = 34.609 ms
tps = 23101.921465 (including connections establishing)
tps = 23105.640572 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random_gaussian(1, :range, 10.0)
         2.766  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

2、1万亿 tpcb 读写测试 - 1000亿数据活跃

TPS:

QPS:

transaction type: ./rw.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 6526147
latency average = 3.529 ms
latency stddev = 50.638 ms
tps = 18126.367839 (including connections establishing)
tps = 18134.592726 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random_gaussian(1, :range, 10.0)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.025  BEGIN;
         3.102  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.061  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.159  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.091  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.046  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.041  END;

3、1万亿 tpcb 只读测试 - 100亿数据活跃

TPS:

QPS:

transaction type: ./ro.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 212661629
latency average = 0.108 ms
latency stddev = 12.493 ms
tps = 590692.703049 (including connections establishing)
tps = 590774.219034 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set aid random_gaussian(1, :range, 10.0)
         0.107  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

3、1万亿 tpcb 读写测试 - 100亿数据活跃

TPS:

QPS:

transaction type: ./rw.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 27002477
latency average = 0.853 ms
latency stddev = 39.222 ms
tps = 75002.036277 (including connections establishing)
tps = 75012.139249 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random_gaussian(1, :range, 10.0)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.044  BEGIN;
         0.211  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.076  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.198  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.115  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.072  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.134  END;

4、1万亿 tpcb 只读测试 - 10亿数据活跃

TPS:

QPS:

transaction type: ./ro.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 374399291
latency average = 0.061 ms
latency stddev = 5.647 ms
tps = 1039962.270864 (including connections establishing)
tps = 1040949.958600 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random_gaussian(1, :range, 10.0)
         0.061  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

4、1万亿 tpcb 读写测试 - 10亿数据活跃

TPS:

QPS:

transaction type: ./rw.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 29574604
latency average = 0.779 ms
latency stddev = 16.197 ms
tps = 82148.432097 (including connections establishing)
tps = 82160.286498 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random_gaussian(1, :range, 10.0)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.043  BEGIN;
         0.144  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.074  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.207  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.106  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.070  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.131  END;

5、1万亿 tpcb 只读测试 - 1亿数据活跃

TPS: 1068052

QPS: 1068052

transaction type: ./ro.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 384510720
latency average = 0.060 ms
latency stddev = 4.332 ms
tps = 1068052.373377 (including connections establishing)
tps = 1068206.696327 (excluding connections establishing)
statement latencies in milliseconds:
         0.002  \set aid random_gaussian(1, :range, 10.0)
         0.059  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

5、1万亿 tpcb 读写测试 - 1亿数据活跃

TPS:

QPS:

transaction type: ./rw.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
duration: 360 s
number of transactions actually processed: 28314309
latency average = 0.814 ms
latency stddev = 16.406 ms
tps = 78647.191352 (including connections establishing)
tps = 78658.751759 (excluding connections establishing)
statement latencies in milliseconds:
         0.003  \set aid random_gaussian(1, :range, 10.0)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.001  \set delta random(-5000, 5000)
         0.043  BEGIN;
         0.184  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.076  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.217  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.096  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.069  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.125  END;

性能小结

环境:阿里云 ECS + 320T ESSD

表SIZE: 120 TB 写入耗时 810688秒,约123.3万行/s

索引SIZE: 20 TB 创建耗时 427800秒

索引深度: 4级(BLOCK_SIZE=32K, 每一页可以存储的item比8k多,所以1万亿的索引层级比1000亿低(8K))

单表数据量 TEST CASE QPS TPS
1万亿 tpcb 活跃数据1亿 只读 1068052 1068052
1万亿 tpcb 活跃数据10亿 只读 1039962 1039962
1万亿 tpcb 活跃数据100亿 只读 590692 590692
1万亿 tpcb 活跃数据1000亿 只读 23101 23101
1万亿 tpcb 活跃数据10000亿 只读 21492 21492
1万亿 tpcb 活跃数据1亿 读写 393235 78647
1万亿 tpcb 活跃数据10亿 读写 410740 82148
1万亿 tpcb 活跃数据100亿 读写 375010 75002
1万亿 tpcb 活跃数据1000亿 读写 90630 18126
1万亿 tpcb 活跃数据10000亿 读写 80715 16143

添加字段(含default值)耗时:1.25 秒。

删除字段耗时:1 毫秒。

附录 - pgbench_accounts 分区, 并行加载测试数据, 动态查询

1万亿单表,会带来什么问题?

1、单表125TB,创建索引耗时增加。PG 11 引入并行创建索引,解决。

2、单表125TB,垃圾回收时间拉长。PG 12 使用zheap引擎彻底杜绝。

3、单表125TB,FREEZE耗时拉长,甚至可能无法在20亿个事务内完成。PG未来版本,使用超过32位的XID,彻底解决。

4、单表125TB,必须放在单个目录下,可能导致文件系统上限(INODE,容量等上限)。

5、单表125TB,要做一些数据清理时不方便,如果有时间维度老化概念,用分区表,可以更好的管理冷热数据,例如pg_pathman。

pgbench转换为分区表。

1、建议使用pg_pathman,性能损失低。内置分区功能,目前还有性能问题。

《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》

《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

《分区表锁粒度差异 - pg_pathman VS native partition table》

《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》

使用内部分区,建议使用动态SQL,避免BIND问题。

分区demo

《PostgreSQL pgbench tpcb 数据生成与SQL部分源码解读》

《PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化》

装载数据

1、表

pgbench -i -I dt --tablespace=tbs1 -s 10000000  

2、分区

create table p (like pgbench_accounts) partition by RANGE ( aid ) tablespace tbs1;  
  
do language plpgsql $$                                                           
declare  
  i_rows_perpartition int8 := 244140625;  
begin  
  for i in 0..4096 loop  
    execute format ('create table pgbench_accounts%s partition of p for values from (%s) to (%s) tablespace tbs1', i, i*i_rows_perpartition, (i+1)*i_rows_perpartition);  
  end loop;  
end;  
$$;  
  
drop table pgbench_accounts;  
  
alter table p rename to pgbench_accounts;  
  
-- alter table pgbench_accounts add constraint pk_pgbench_accounts_aid primary key (aid) using index tablespace tbs2;  

3、加载任务

drop table task;  
create table task(id int primary key);  
insert into task select i from generate_series(0,4095) t(i);  

4、初始化记录

create table init_accounts(aid int8);  
insert into init_accounts select generate_series(0,244140624);  

5、并行状态UDF

create or replace function tpcb_init_accounts() returns void as $$  
declare  
  v_id int;  
begin  
  with tmp as (select * from task limit 1 for update skip locked),  
    tmp1 as (delete from task using tmp where task.id=tmp.id)  
    select id into v_id from tmp;  
    
  if found then  
    execute format ('insert into pgbench_accounts%s select aid+%s*244140625::int8, ((aid+%s*244140625::int8)-1)/100000 + 1, 0 from init_accounts on conflict do nothing', v_id, v_id, v_id);  
  end if;  
end;  
$$ language plpgsql strict;  

6、并行装载数据

vi test.sql  
select tpcb_init_accounts();  
  
nohup pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -t 100 >./init.log 2>&1 &  

初始化索引

1、任务表

drop table task;  
create table task(id int primary key);  
insert into task select i from generate_series(0,4095) t(i);  

2、并行创建索引UDF

create or replace function tpcb_init_accounts_pkey() returns void as $$  
declare  
  v_id int;  
begin  
  with tmp as (select * from task limit 1 for update skip locked),  
    tmp1 as (delete from task using tmp where task.id=tmp.id)  
    select id into v_id from tmp;  
    
  if found then  
    execute format ('analyze pgbench_accounts%s', v_id);  
    execute format ('alter table pgbench_accounts%s add constraint pk_pgbench_accounts%s_aid primary key (aid) using index tablespace tbs2', v_id, v_id);  
  end if;  
end;  
$$ language plpgsql strict;  

3、并行创建索引

vi test.sql  
select tpcb_init_accounts_pkey();  
  
nohup pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -t 100 >./init.log 2>&1 &  

小结

1、8K的block size,单表最大32TB,(由于ctid的block num是32BIT的寻址,所以8K block算出来的最大容量就是32TB,本文测试的单表1万亿,已经超过了32TB,所以需要选择更大的BLOCK SIZE才行,32K即可到达256TB单表)。

编译时加上--with-blocksize=

./configure --with-blocksize=32

2、这么大的数据库怎么高效的备份,时间点恢复?

全量备份:1、ZFS快照,将快照发送到备份机(万兆网可以把网卡带宽跑满)。2、使用pg_basebackup备份全量。3、使用pg_rman备份全量。4、使用云盘快照备份全量。

增量备份:1、ZFS快照,将快照增量发送到备份机。2、pg_basebackup只能备份全量。3、使用pg_rman备份增量(通过BLOCK LSN号区分上一次备份以来修改过的数据块)。4、使用云盘快照备份增量。

归档备份:备份wal文件归档。

时间点恢复: 1、zfs快照克隆+归档恢复到时间点。 2、全量恢复+归档恢复到时间点。4、全量+增量+归档恢复到时间点。

3、此次测试tpcb,并发64时,前十几秒bind耗费的时间较多。

4、建议使用pg_pathman对大表进行分区,多大的表需要进行分区?

《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

参考

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

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

《PostgreSQL 100亿 tpcb 性能 on ECS》

《[未完待续] PostgreSQL on 阿里云ECS+ESSD - 1000亿 tpcb、1000W tpcc 测试》

相关实践学习
ECS云服务器新手上路
本实验会自动创建一台ECS实例。首先,远程登陆ECS实例,并部署应用。然后,登陆管理控制台,并对这台ECS实例进行管理操作。
7天玩转云服务器
云服务器ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,可降低 IT 成本,提升运维效率。本课程手把手带你了解ECS、掌握基本操作、动手实操快照管理、镜像管理等。了解产品详情:&nbsp;https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 弹性计算 关系型数据库
阿里云服务器ESSD云盘性能等级PL0、PL1、PL2、PL3区别,云盘性能级别PL知识点参考
在我们选择阿里云服务器系统盘和数据盘时,如果是选择ESSD云盘,还需要选择云盘的云盘性能级别PL,PL性能级别有PL3、PL2、PL1、PL0四个性能级别的云盘规格,如果是通过阿里云的活动来购买云服务器的话,由于系统盘默认一般为20G或40G容量,可选的PL性能级别通常只有PL0(单盘IOPS性能上限1万)和PL1(单盘IOPS性能上限5万)可选择,有的用户肯能并不清楚ESSD云盘的性能等级之间有何区别,单盘IOPS性能指的是什么,本文为大家介绍一下ESSD云盘的云盘性能级别PL3、PL2、PL1、PL0相关知识点。
阿里云服务器ESSD云盘性能等级PL0、PL1、PL2、PL3区别,云盘性能级别PL知识点参考
|
2月前
|
NoSQL 关系型数据库 MySQL
涉及rocketMQ,jemeter等性能测试服务器的安装记录
涉及rocketMQ,jemeter等性能测试服务器的安装记录
44 1
|
2天前
|
Oracle NoSQL 固态存储
阿里云服务器ESSD Entry云盘与ESSD云盘选择指南:性能与场景解析
在我们选择阿里云服务器的时候,有部分云服务器同时支持ESSD Entry云盘和ESSD云盘,选择不同的云盘,价格也有所差异,有的用户还不清楚他们之间的区别,因此不知道选择哪种更好更能满足自己场景的需求,本文为大家介绍一下阿里云服务器ESSD Entry云盘和ESSD云盘的区别及选择参考。
阿里云服务器ESSD Entry云盘与ESSD云盘选择指南:性能与场景解析
|
2月前
|
关系型数据库 MySQL 数据库
测试部署PolarDB-X 分布式与集中式
在本文中,作者详述了在CentOS 7.9上部署测试PolarDB-X分布式与集中式数据库的过程。PolarDB-X作为阿里云优化的分布式数据库,提供高稳定性和与MySQL的兼容性,是应对单体数据库扩展性和性能瓶颈的解决方案,同时也符合国产化需求。文章介绍了部署环境准备,包括关闭防火墙和SELinux,设置系统参数,安装Python3和Docker,以及配置MySQL客户端。接着,通过PXD工具部署了PolarDB-X的集中式和分布式版,遇到的问题包括阿里云镜像源异常导致的部署失败以及指定版本安装的困扰。最后,作者进行了初步的压力测试,并对文档完善、生态工具建设以及提供更多使用案例提出了建议。
47778 10
测试部署PolarDB-X 分布式与集中式
|
1月前
|
关系型数据库 MySQL 分布式数据库
PolarDB测试
在CentOS 7.9环境下,作者探索实践了PolarDB-X的分布式与集中式部署,强调了PolarDB-X的稳定性和与MySQL的高兼容性。测试涵盖Anolis OS和openEuler系统,涉及PXD工具和Kubernetes部署。部署步骤包括环境调整、Python 3、Docker和MySQL客户端的安装。在集群部署中,遇到镜像源和版本匹配问题,通过PXD解决。总结中建议官方改进文档、丰富生态工具并提供更多案例,以促进其发展。这次体验展现了PolarDB-X的技术潜力和国产数据库的重要性。
509 4
|
13天前
|
关系型数据库 MySQL 测试技术
《阿里云产品四月刊》—瑶池数据库微课堂|RDS MySQL 经济版 vs 自建 MySQL 性能压测与性价比分析
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代
|
16天前
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用问题之如何解决测试连接时出现2003-Can't connect的问题
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
2月前
|
存储 弹性计算 固态存储
阿里云服务器系统盘与数据盘ESSD AutoPL、ESSD云盘、SSD云盘区别及选择参考
阿里云系统盘与数据盘如何选择?目前阿里云服务器有ESSD AutoPL、高效云盘、ESSD云盘、SSD云盘可供选择,很多新手用户并不清楚他们之间的区别,也就不知道应该如何选择,因为不同的云盘在最大IOPS、最大吞吐量等性能上是有区别的,下面我们一起来他们之间的区别,这样就有助于我们选择适合自己需求的系统盘与数据盘了。
阿里云服务器系统盘与数据盘ESSD AutoPL、ESSD云盘、SSD云盘区别及选择参考
|
2月前
|
存储 弹性计算 固态存储
*PolarDB-PG开源版本 基于ECS+ESSD云盘共享存储的部署测评**
PolarDB-PG在阿里云ECS与ESSD云盘的组合下展现优秀性能,简化部署流程,提供高并发写入时低延迟稳定性。ESSD的高性能IOPS和读取速度优化了数据库表现,只读节点实现近乎实时数据访问。分布式部署保证高可用性,即使面对故障也能快速切换。尽管ESSD初期成本较高,但长期看能降低总体拥有成本,尤其适合高并发、大数据量场景。此解决方案平衡了性能、可用性和成本,是企业级应用的理想选择。
|
2月前
|
存储 弹性计算 网络协议
【阿里云弹性计算】ECS实例性能测试报告:阿里云实例性能横向评测
【5月更文挑战第27天】阿里云ECS性能横向评测对比了经济型e系列、计算型c7a系列实例的CPU、内存、网络和存储性能。使用SPEC CPU 2017、Stream、iperf和fio工具进行测试。结果显示,计算型c7a系列在CPU和网络性能上突出,经济型e系列性价比高。所有实例内存性能良好,ESSD云盘提供出色存储性能。用户应根据业务需求选择合适实例。
78 0

相关产品

  • 云原生数据库 PolarDB