PostgreSQL on xfs 性能优化 - 2

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:
承接上一篇XFS性能优化。
本文是对比XFS优化前后的PostgreSQL TPC-B性能。

配置PostgreSQL
数据库参数
listen_addresses='0.0.0.0'
port=1921
max_connections=1000
unix_socket_directories='.'
shared_buffers=32GB
maintenance_work_mem=512MB
autovacuum_work_mem=512MB
dynamic_shared_memory_type=posix
bgwriter_delay=10ms
wal_level=hot_standby
synchronous_commit=off
full_page_writes=off
wal_buffers=16MB
wal_writer_delay=10ms
max_wal_size=32GB
max_wal_senders=10
max_replication_slots=10
hot_standby=on
wal_receiver_status_interval=1s
hot_standby_feedback=on
random_page_cost=1.0
effective_cache_size=256GB
log_destination='csvlog'
logging_collector=on
log_checkpoints=on
log_connections=on
log_disconnections=on
log_error_verbosity=verbose
log_timezone='PRC'
datestyle='iso,
timezone='PRC'
lc_messages='C'
lc_monetary='C'
lc_numeric='C'
lc_time='C'
default_text_search_config='pg_catalog.english'

$PGDATA和pg_xlog都放在XFS文件系统中。

生成测试数据
生成5亿测试数据
pgbench -i -s 5000

初始化性能对比
XFS未优化时的统计信息如下

pgbench初始化测试数据时的磁盘iostat
IO落在一块盘上
平均IO响应0.36毫秒,平均IO等待0.87毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.60    0.00    1.35    0.19    0.00   94.87

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     1.00    0.00    4.00     0.00    40.00    10.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfc               0.00     0.00    0.00  250.00     0.00 206752.00   827.01     0.22    0.86   0.36   9.10
dm-0              0.00     0.00    0.00  250.00     0.00 206752.00   827.01     0.22    0.87   0.36   9.10

vacuum 时的iostat
IO落在一块盘上
平均IO响应0.28毫秒,平均IO等待26.56毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.88    0.00    4.77    0.00    0.00   93.35

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfb               0.00     0.00    0.00 3211.00     0.00 3280888.00  1021.77    86.20   26.53   0.28  90.20
dfc               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.00 3210.00     0.00 3279864.00  1021.76    86.21   26.56   0.28  90.20



XFS未优化后的统计信息如下

pgbench初始化测试数据时的磁盘iostat
IO分摊到多快盘上
平均IO响应0.00毫秒,平均IO等待0.01毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.57    0.00    2.50    0.03    0.00   93.90

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00 9262.00     0.00 148096.00    15.99     0.11    0.01   0.01   8.60
dfb               0.00     0.00    0.00 9243.00     0.00 147888.00    16.00     0.11    0.01   0.01   8.80
dfc               0.00     0.00    0.00 9256.00     0.00 148096.00    16.00     0.17    0.02   0.01   9.00
dm-0              0.00     0.00    0.00 27761.00     0.00 444080.00    16.00     0.42    0.01   0.00   9.30

vacuum 时的iostat
IO分摊到多快盘上
平均IO响应0.01毫秒,平均IO等待0.02毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.09    0.00   13.57    0.10    0.00   84.25

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00 52311.00     0.00 836976.00    16.00     0.70    0.01   0.01  63.50
dfb               0.00     0.00    0.00 52313.00     0.00 837008.00    16.00     0.74    0.01   0.01  66.90
dfc               0.00     0.00    0.00 52312.00     0.00 836984.00    16.00     1.11    0.02   0.02  94.30
dm-0              0.00     0.00    0.00 156936.00     0.00 2510952.00    16.00     2.90    0.02   0.01  97.40



压测性能对比
压测命令
nohup pgbench -M prepared -n -r -P 1 -c 96 -j 96 -T 3600 >./bench.log 2>&1 &

XFS优化前性能指标
XFS优化前的IOSTAT
未发生检查点时
IO落到一个块设备
平均IO响应0.04毫秒,平均IO等待0.1毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          73.16    0.00   22.17    0.06    0.00    4.61

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfb               0.00     0.00    0.00  922.00     0.00 12456.00    13.51     0.00    0.00   0.00   0.30
dfc               0.00     0.00    0.00 3955.00     0.00 131776.00    33.32     0.44    0.11   0.05  18.20
dm-0              0.00     0.00    0.00 4877.00     0.00 144232.00    29.57     0.47    0.10   0.04  21.00

发生检查点时
IO落到一个块设备
平均IO响应0.03毫秒,平均IO等待0.6毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          72.67    0.00   25.48    0.00    0.00    1.85

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfb               0.00     0.00    0.00 32539.00     0.00 3184272.00    97.86    20.65    0.63   0.03 100.20
dfc               0.00     0.00    0.00 2566.00     0.00 84464.00    32.92     0.35    0.14   0.09  22.90
dm-0              0.00     0.00    0.00 35104.00     0.00 3268672.00    93.11    21.06    0.60   0.03 100.10

tps
transaction type: TPC-B (sort of)
scaling factor: 5000
query mode: prepared
number of clients: 96
number of threads: 96
duration: 3600 s
number of transactions actually processed: 34796111
latency average: 9.929 ms
latency stddev: 14.829 ms
tps = 9664.902474 (including connections establishing)
tps = 9665.028796 (excluding connections establishing)
statement latencies in milliseconds:
        0.006275        \set nbranches 1 * :scale
        0.001541        \set ntellers 10 * :scale
        0.001213        \set naccounts 100000 * :scale
        0.002230        \setrandom aid 1 :naccounts
        0.001708        \setrandom bid 1 :nbranches
        0.001497        \setrandom tid 1 :ntellers
        0.001460        \setrandom delta -5000 5000
        1.284175        BEGIN;
        1.139272        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        1.012388        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        1.682350        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        2.705569        UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        1.058834        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        1.015118        END;


XFS优化后性能指标

未发生检查点时
IO分摊到多快盘上
平均IO响应0.02毫秒,平均IO等待0.02毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          65.34    0.00   26.94    0.00    0.00    7.72

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00 1428.00     0.00 22832.00    15.99     0.02    0.02   0.02   2.40
dfb               0.00     0.00    0.00 1417.00     0.00 22672.00    16.00     0.03    0.02   0.02   2.50
dfc               0.00     0.00    0.00 1417.00     0.00 22672.00    16.00     0.04    0.02   0.02   3.50
dm-0              0.00     0.00    0.00 4262.00     0.00 68176.00    16.00     0.10    0.02   0.02   9.50

检查点发生时
IO分摊到多快盘上
平均IO响应0.01毫秒,平均IO等待0.02毫秒
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          58.82    0.00   35.25    0.00    0.00    5.93

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dfa               0.00     0.00    0.00 52709.00     0.00 843272.00    16.00     1.12    0.02   0.01  72.30
dfb               0.00     0.00    0.00 52632.00     0.00 842048.00    16.00     0.95    0.02   0.01  74.10
dfc               0.00     0.00    0.00 52805.00     0.00 844832.00    16.00     1.32    0.02   0.02  89.90
dm-0              0.00     0.00    0.00 158143.00     0.00 2530120.00    16.00     3.72    0.02   0.01  99.50

tps
transaction type: TPC-B (sort of)
scaling factor: 5000
query mode: prepared
number of clients: 96
number of threads: 96
duration: 3600 s
number of transactions actually processed: 144901132
latency average: 2.381 ms
latency stddev: 1.571 ms
tps = 40248.632845 (including connections establishing)
tps = 40249.214064 (excluding connections establishing)
statement latencies in milliseconds:
        0.006825        \set nbranches 1 * :scale
        0.002457        \set ntellers 10 * :scale
        0.001856        \set naccounts 100000 * :scale
        0.003474        \setrandom aid 1 :naccounts
        0.002448        \setrandom bid 1 :nbranches
        0.002864        \setrandom tid 1 :ntellers
        0.003144        \setrandom delta -5000 5000
        0.220207        BEGIN;
        0.372330        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        0.247961        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        0.301182        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        0.305694        UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        0.247623        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        0.645872        END;

tps曲线
PostgreSQL on xfs 性能优化 - 2 - 德哥@Digoal - PostgreSQL research


小结
经过XFS优化,单次IO响应延迟始终保持在0.01毫秒左右,性能表现平稳。
使用了9000个allocation group, fsync时,并行度更高,所以对TPS影响更低了。
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
4月前
|
关系型数据库 MySQL 分布式数据库
PolarDB auto_inc场景下的性能优化实践
PolarDB auto_inc场景下的性能优化实践 在数据库的使用场景中,并发插入数据或并发导入数据场景是最常见的。针对这一场景,PolarDB MySQL版进行了深度性能优化,以提高插入性能。本文将详细介绍PolarDB在auto_inc场景下的性能优化相关内容。
63 2
|
8月前
|
关系型数据库 测试技术 分布式数据库
PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等. 本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.
596 4
|
11月前
|
关系型数据库 PostgreSQL
PostgreSQL 性能优化: 等待事件
等待事件是 PostgreSQL 的重要优化工具。当您能查明会话为什么在等待资源以及会话在做什么时,您就能更好地减少瓶颈。您可以使用本节中的信息来查找可能的原因和纠正措施。
195 0
|
11月前
|
关系型数据库 PostgreSQL
PostgreSQL 性能优化: EXPLAIN 使用教程
PostgreSQL为每个收到的查询产生一个查询计划。选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的,因此系统包含了一个复杂的规划器来尝试选择好的计划。你可以使用EXPLAIN命令察看规划器为任何查询生成的查询计划。
161 0
|
11月前
|
关系型数据库 PostgreSQL 索引
PostgreSQL 性能优化: 执行计划
PostgreSQL为每个收到的查询产生一个查询计划。查询计划的结构是一个计划结点的树。最底层的结点是扫描结点:它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型:顺序扫描、索引扫描、位图索引扫描。也还有不是表的行来源,例如VALUES子句和FROM中返回集合的函数,它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作,那么就会在扫描结点之上有其它额外的结点来执行这些操作。并且,做这些操作通常都有多种方法,因此在这些位置也有可能出现不同的结点类型。
84 0
|
关系型数据库 分布式数据库 调度
PolarDB auto_inc 场景性能优化之路
在数据库的使用场景中, 最常见的场景是并发插入或者导入数据场景, 在该场景中并不指定自增id, 由数据库自动生成自增id, 然后插入到数据库中, 因此我们也叫auto_inc 场景的数据插入. 典型的业务场景如: 游戏行业开服过程中的大批的登录注册场景, 电商活动中给商家后台推单场景等等. 我们看看PolarDB 是如何优化针对这种并发插入场景进行优化的. 背景知识: 在这种并发
102 0
PolarDB auto_inc 场景性能优化之路
|
存储 固态存储 Cloud Native
【Paper Reading】PolarDB计算存储分离架构性能优化之路
本篇论文收录在 VLDB 2022,介绍了云原生数据库PolarDB在计算存储分离架构下遇到的性能挑战,分析了云存储相对于传统本地存储的工作特性差异及其根因,讨论了将各类存储引擎部署至云存储上时所会遇到的问题挑战,并提出了统一的优化框架 CloudJump。最后通过实验证明优化框架CloudJump适用于PolarDB,也适用于 RocksDB。
【Paper Reading】PolarDB计算存储分离架构性能优化之路
|
存储 关系型数据库 分布式数据库
Paper Reading 预告 | 揭秘 PolarDB 计算存储分离架构性能优化之路
12月29日 19:00 锁定「阿里云数据库视频号」揭秘PolarDB计算存储分离架构性能优化之路
Paper Reading 预告 | 揭秘 PolarDB 计算存储分离架构性能优化之路
|
SQL 存储 运维
PolarDB 大表场景性能优化技术揭秘
**背景** 很多时候业务架构设计里面最重要的一环就是数据库模型设计, 由于单机MySQL 的限制, 很多业务架构师不得不考虑对大表进行拆分, 通过中间件或者其他手段进行分库分表. 很多业务在快速发展阶段,开始考虑数据拆分的原因其实并不是计算能力遇到了瓶颈,而是海量数据的存储到达了单实例的上限,但是由于最初设计的时候没有考虑到海量数据的使用方式,或是在业务逻辑中,数据无法进行清理或归档。 运
242 0
|
缓存 运维 网络协议
PostgreSQL 性能优化和体系化运维(一)|学习笔记
快速学习 PostgreSQL 性能优化和体系化运维(一)
329 0