阿里云数据库ApsaraDB 关注
手机版
  1. 云栖社区>
  2. 阿里云数据库ApsaraDB>
  3. 博客>
  4. 正文

为PostgreSQL讨说法 - 浅析《UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL》

德哥 2016-07-28 18:43:12 浏览27373 评论14 发表于: 阿里云数据库ApsaraDB

PostgreSQL

摘要: 背景 最近有一篇文档,在国外闹得沸沸扬扬,是关于UBER使用mysql替换postgres原因的文章。 文章涉及到PG数据库的部分,论点过度的浮于表面,没有深入的理解和分析。 很容易导致用户对PostgreSQL的误解。

背景

最近有一篇文档,在国外闹得沸沸扬扬,是关于UBER使用mysql替换postgres原因的文章。

英文原文
https://eng.uber.com/mysql-migration/

来自高可用架构的 中文翻译

文章涉及到 PG数据库的部分,背后的原理并没有深入的剖析,导致读者对PostgreSQL的误解

uber在文章阐述的遇到的PG问题

We encountered many Postgres limitations:

  • Inefficient architecture for writes
  • Inefficient data replication
  • Issues with table corruption
  • Poor replica MVCC support
  • Difficulty upgrading to newer releases

本文接下来会依依介绍其背后的原理

1. Inefficient architecture for writes

uber文章的观点

PG的MVCC机制,更新数据为新增版本,会带来两个问题

  • SSD的写放大

  • 索引的写放大

本文观点

事实并不是PG的MVCC的问题,所有的数据库只要支持并发读写,就需要MVCC,只是版本管理的手段可能不一样。
有通过回滚段管理的,也有通过多版本进行管理的。

原理剖析

基于回滚段实现MVCC的数据库
当更新一条记录时,有些数据库需要将整个数据块拷贝到回滚段区域(有些是基于逻辑行的拷贝,则拷贝到回滚段的是记录)。
注意写回滚段也是会产生REDO写操作的。
带来一个问题,包含更新、删除操作的事务会变慢,相当于双倍的时间。

  • 更新可能在当前的row进行。
    这种情况,只要索引字段不变化,索引就不需要变。
    如果索引字段值发生变化,索引也要变化。
    screenshot

  • 如果更新后的记录超过原来行的长度,可能在本页找一块空闲区域(如果能装下),也可能要到其他页找一块区域进行更新,有擦除旧记录,写入新纪录的写操作。
    不管怎样,索引都要变化。
    screenshot

基于回滚段实现MVCC的数据库,除了前面说的更新、删除操作的响应变慢,同时还有另一个影响如果要回滚事务,开销会很大(特别是当事务修改的数据量很大时),因为要从回滚段将整个块拷贝到数据文件(基于逻辑行拷贝的回滚则是类似重新来一遍UNDO事务的SQL操作,同时还需要擦除之前更改的行)。
代价非常高
通常出现在执行大事务,同时回滚段写满的时候,报snapshot too old,导致事务不得不回滚,回滚又会是一个非常漫长的操作。

基于多版本实现MVCC的数据库
当更新一条记录时,产生一个新的版本。

  • PostgreSQL 会优先使用在当前页更新(HOT),即在当前页进行更新,不管行长度是否发生变化。
    这种情况,只要索引字段不变化,索引就不需要变。
    如果索引字段值发生变化,索引也要变化。
    (hot时,索引不变,通过HEAP页内旧item指向新item来做到定位到新的记录)
    screenshot

  • 如果未在当前页更新,则索引才需要变化
    (通过配置表的fillfactor,可以大大减少这种情况的发送,尽量走HOT)
    如果读者还是担心这个问题,我们可以做一个压测试验,看看到底会不会更新索引,会不会对更新造成性能影响如何?
    有几个参数需要注意,很多用户可能不关注这个,导致了膨胀

    autovacuum_work_mem = 4GB               # min 1MB, or -1 to use maintenance_work_mem
    autovacuum = on                 # Enable autovacuum subprocess?  'on'
    autovacuum_max_workers = 8              # max number of autovacuum subprocesses
    autovacuum_naptime = 1s         # time between autovacuum runs
    autovacuum_vacuum_threshold = 50        # min number of row updates before
    autovacuum_analyze_threshold = 50       # min number of row updates before
    autovacuum_vacuum_scale_factor = 0.002  # fraction of table size before vacuum
    autovacuum_analyze_scale_factor = 0.001 # fraction of table size before analyze
    autovacuum_vacuum_cost_delay = 0        # default vacuum cost delay for
    

    测试1000万数据,9个字段,8个索引,更新其中的mod_time字段。

    postgres=# create table tbl(id int, mod_time timestamp(0), c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int) with (fillfactor=80);
    CREATE TABLE
    Time: 1.906 ms
    postgres=# insert into tbl select i,clock_timestamp(),i+1,i+2,i+3,i+4,i+5,i+6,i+6 from generate_series(1,10000000) t(i);
    INSERT 0 10000000
    Time: 14522.098 ms
    postgres=# create index idx1 on tbl(c1) with (fillfactor=80);
    CREATE INDEX
    Time: 3005.753 ms
    postgres=# create index idx2 on tbl(c2) with (fillfactor=80);
    CREATE INDEX
    Time: 2793.361 ms
    postgres=# create index idx3 on tbl(c3) with (fillfactor=80);
    CREATE INDEX
    Time: 2804.031 ms
    postgres=# create index idx4 on tbl(c4) with (fillfactor=80);
    CREATE INDEX
    Time: 2856.954 ms
    postgres=# create index idx5 on tbl(c5) with (fillfactor=80);
    CREATE INDEX
    Time: 2895.643 ms
    postgres=# create index idx6 on tbl(c6) with (fillfactor=80);
    CREATE INDEX
    Time: 2932.394 ms
    postgres=# create index idx7 on tbl(c7) with (fillfactor=80);
    CREATE INDEX
    Time: 2939.927 ms
    postgres=# alter table tbl add constraint pk_tbl primary key(id) with (fillfactor=80);
    ALTER TABLE
    Time: 3292.544 ms
    

    记录下当前表的大小和8个索引的大小,用于压测后对比大小变化

    postgres=# \dt+ tbl
                    List of relations
    Schema | Name | Type  |  Owner   |  Size  | Description 
    --------+------+-------+----------+--------+-------------
    public | tbl  | table | postgres | 919 MB | 
    (1 row)
    postgres=# \di+ 
                                      List of relations
    Schema |         Name          | Type  |  Owner   |      Table       |  Size  | Description 
    --------+-----------------------+-------+----------+------------------+--------+-------------
    public | idx1                  | index | postgres | tbl              | 241 MB | 
    public | idx2                  | index | postgres | tbl              | 241 MB | 
    public | idx3                  | index | postgres | tbl              | 241 MB | 
    public | idx4                  | index | postgres | tbl              | 241 MB | 
    public | idx5                  | index | postgres | tbl              | 241 MB | 
    public | idx6                  | index | postgres | tbl              | 241 MB | 
    public | idx7                  | index | postgres | tbl              | 241 MB | 
    public | pk_tbl                | index | postgres | tbl              | 241 MB | 
    

    全力压测30分钟,更新mod_time字段

    $ vi test.sql
    \setrandom id 1 10000000
    update tbl set mod_time=now() where id=:id;
    压测开始
    pgbench -M prepared -n -r -P 5 -f ./test.sql -c 48 -j 48 -T 1800
    

    压测结果,更新速度维持在 13万/s 以上。 这个压力应该可以覆盖很多的用户吧。

    progress: 5.0 s, 133373.6 tps, lat 0.357 ms stddev 0.269
    progress: 10.0 s, 133148.2 tps, lat 0.359 ms stddev 0.310
    progress: 15.0 s, 134249.0 tps, lat 0.356 ms stddev 0.299
    progress: 20.0 s, 131037.9 tps, lat 0.364 ms stddev 0.341
    progress: 25.0 s, 135326.3 tps, lat 0.353 ms stddev 0.292
    progress: 30.0 s, 135023.9 tps, lat 0.354 ms stddev 0.289
    ......
    progress: 1385.0 s, 135997.9 tps, lat 0.351 ms stddev 0.261
    progress: 1390.0 s, 133152.5 tps, lat 0.359 ms stddev 0.302
    progress: 1395.0 s, 133540.7 tps, lat 0.357 ms stddev 0.287
    progress: 1400.0 s, 132034.8 tps, lat 0.362 ms stddev 0.314
    progress: 1405.0 s, 135366.6 tps, lat 0.353 ms stddev 0.266
    progress: 1410.0 s, 134606.6 tps, lat 0.355 ms stddev 0.280
    .....
    progress: 1855.0 s, 134013.7 tps, lat 0.356 ms stddev 0.298
    progress: 1860.0 s, 132374.8 tps, lat 0.361 ms stddev 0.306
    progress: 1865.0 s, 133868.3 tps, lat 0.357 ms stddev 0.282
    progress: 1870.0 s, 133457.1 tps, lat 0.358 ms stddev 0.303
    progress: 1875.0 s, 133598.3 tps, lat 0.357 ms stddev 0.297
    progress: 1880.0 s, 133234.5 tps, lat 0.358 ms stddev 0.297
    progress: 1885.0 s, 131778.9 tps, lat 0.362 ms stddev 0.319
    progress: 1890.0 s, 134932.2 tps, lat 0.354 ms stddev 0.274
    ......
    progress: 2235.0 s, 135724.6 tps, lat 0.352 ms stddev 0.284
    progress: 2240.0 s, 136845.0 tps, lat 0.349 ms stddev 0.256
    progress: 2245.0 s, 136240.6 tps, lat 0.350 ms stddev 0.264
    progress: 2250.0 s, 136983.2 tps, lat 0.348 ms stddev 0.248
    progress: 2255.0 s, 137494.5 tps, lat 0.347 ms stddev 0.251
    ......
    

    压测结束后,查看表和索引的大小,如果按UBER文中指出的,会更新索引,但实际上,结果说话,表和索引根本没有膨胀。
    UBER 文章对用户的误导不攻自破。

    表的大小未变化
    postgres=# \dt+
                          List of relations
    Schema |       Name       | Type  |  Owner   |  Size   | Description 
    --------+------------------+-------+----------+---------+-------------
    public | tbl              | table | postgres | 919 MB  | 
    索引的大小也未变化
    postgres=# \di+
                                      List of relations
    Schema |         Name          | Type  |  Owner   |      Table       |  Size  | Description 
    --------+-----------------------+-------+----------+------------------+--------+-------------
    public | idx1                  | index | postgres | tbl              | 241 MB | 
    public | idx2                  | index | postgres | tbl              | 241 MB | 
    public | idx3                  | index | postgres | tbl              | 241 MB | 
    public | idx4                  | index | postgres | tbl              | 241 MB | 
    public | idx5                  | index | postgres | tbl              | 241 MB | 
    public | idx6                  | index | postgres | tbl              | 241 MB | 
    public | idx7                  | index | postgres | tbl              | 241 MB | 
    public | pk_tbl                | index | postgres | tbl              | 241 MB | 
    

另外再给大家分析一个信息,PostgreSQL nbtree 索引方法针对更新做了优化,可以大幅降低锁的产生,所以并发的更新性能是非常棒的。
我们来看一个测试,更新c1与mod_time两个字段,其中c1是索引字段。
压测脚本变更如下

vi test.sql
\setrandom id 1 10000000
update tbl set mod_time=now(),c1=c1+1 where id=:id; 

压测数据截取,可以看出性能是很好的,和单纯更新非索引列差不多

...
progress: 1025.0 s, 138077.5 tps, lat 0.346 ms stddev 0.264
progress: 1030.0 s, 138746.9 tps, lat 0.344 ms stddev 0.270
progress: 1035.0 s, 137590.2 tps, lat 0.347 ms stddev 0.273
progress: 1040.0 s, 139072.3 tps, lat 0.343 ms stddev 0.258
progress: 1045.0 s, 140480.3 tps, lat 0.340 ms stddev 0.255
...

欲了解PostgreSQL nbtree的原理,可以参考Lehman & Yao Algorithm
src/backend/access/nbtree/README

小结

  • 基于回滚段实现MVCC的数据库,在更新数据时SSD写放大 > 100%(因为回滚段是一定要写的,并行写回滚段的操作也需要写REDO);而基于多版本实现MVCC的数据库,SSD写放大的概率低于100%(因为可能发生HOT,发生在当前页),而且旧记录只改行的xmax标记,产生的REDO极少。

  • 基于回滚段实现MVCC的数据库,在删除数据时SSD写放大是100%(因为回滚段是一定要写的,并行写回滚段的操作也需要写REDO);而基于多版本实现MVCC的数据库,SSD写放大的概率为0 (因为只需要改一下行头部的xmax的标记)。

  • 基于回滚段或MVCC的数据库,索引的写放大,都与是否发生行迁移有关,概率差不多。

  • 基于回滚段实现MVCC的数据库,如果要回滚事务,开销会很大(特别是当事务修改的数据量很大时),因为要从回滚段将整个块拷贝到数据文件(基于逻辑行拷贝的回滚则是类似重新来一遍UNDO事务的SQL操作,同时还需要擦除之前更改的行)。

  • 基于多版本实现MVCC的数据库,事务回滚非常快,因为不需要拷贝行或者数据块,也不需要修改已更新的记录,只是记录clog时将当前事务标记为ABORT即可,也就是说只需要改2个比特位。

  • 早在2007年,PostgreSQL就已经使用HOT技术完美的解决了索引更新的问题,根本不存在UPDATE数据时一定需要更新索引的问题。
    我在很多场合分享过HOT的原理,也有相应的文章介绍。
    要了解细节的话,可以看一下PostgreSQL源码中的 src/backend/access/heap/README.HOT
    另外PostgreSQL还支持hash, gin, gist, sp-gist, brin索引,用户如果想了解这些索引,可以参考
    https://www.pgcon.org/2016/schedule/attachments/434_Index-internals-PGCon2016.pdf

  • UBER文章指出的基于B+tree即secondary index指向PK,仅仅是一种避免UPDATE数据需要索引的方法。
    但是这种方法引入了一些问题如下 :
    1. 插入数据会变慢,因为数据存储是B+Tree结构的。
    2. 如果插入的是随机的PK值,则会频繁的带来页分裂,会造成IO写放大。
    3. 为了解决索引分裂的问题,导致了写堵塞读。 原因是引入了ChangeBuffer,当读的数据还在ChangeBuffer中时,需要先将其merge到b+tree,merge过程是堵塞读的。
    4. 查询secondary时,要再走一遍primary index,带来了额外的离散扫描开销,如果secondary是范围扫描或者多点扫描,这个放大倍数是很可观的。 例如用户要从secondary index扫描10条记录,primary index的深度是4,那么除了secondary index的数据块扫描,还有额外多扫描40个primary的块。
    screenshot
    PostgreSQL是记录的(block_number, offset),所以1条记录只需要扫描1个数据块。
    screenshot
    5. 因为b+tree会将行存储在索引页中,所以一页能存下的记录数会大大减少,从而导致b+tree的层级比单纯的b-tree深一些。 特别是行宽较宽的表。
    例如行宽为几百字节,16K的页可能就只能存储十几条记录,一千万记录的表,索引深度达到7级,加上metapage,命中一条记录需要扫描8个数据块。
    而使用PostgreSQL堆表+PK的方式,索引页通常能存几百条记录(以16K为例,约存储800条记录),索引深度为3时能支撑5亿记录,所以命中一条记录实际上只需要扫描5个块(meta+2 branch+leaf+heap)。

彩蛋

  • PostgreSQL TOAST机制
    PostgreSQL的TOAST机制,可以将变长类型的值,自动压缩存储到另一片区域,通过内部的POINT指向,而不影响行的其他值。 例如存储文档,或者图片的表,如果这个表上有一些字段要更新,有一些字段不要更新,那么在更新时,PostgreSQL数据库会有非常大的优势,因为行很小。
    screenshot
    基于回滚段实现MVCC的数据库,需要拷贝旧的记录或数据块到回滚段,记录或块越大,这个开销越大。
    screenshot
    存储文档、图像、非结构化数据,使用PostgreSQL很有优势。

  • MySQL innodb是基于B+树的存储,当PK数据随机数据写入时存在巨大写放大,因为经常要分裂,不仅影响插入速度和查询速度,同时数据存放也会变得非常无序。
    即使按PK顺序扫描时,也可能出现大量的离散IO。
    screenshot
    基于B+树结构的存储,为了提高插入速度,如果使用index cache的话,则影响并发的查询,因为查询时要先合并索引。
    screenshot
    另一方面,B+树的存储,必须要求表需要一个PK(即使表没有PK的需求,也要硬塞一个PK列进来),secondary index则指向这个PK。
    如果PK发生更新,则所有的secondary index都要更新,也就是说,为了保证secondary不更新,务必确保PK不更新。
    如果要对secondary index进行范围扫描,物理的扫描上是离散的。
    screenshot
    所以uber本文提出的,secondary index 不需要变更的好处,其实背后是有以上代价存在的(例如一定要加PK,插入速度更慢,插入时PK不能随机否则分裂带来的IO巨大,使用secondary index范围扫描时会造成离散的IO等弊端),把原理,代价都交代清楚,才能看得更明白。

    PostgreSQL 有几种方法来消除这种离散IO。
    1. bitmap scan,获取heap tuple前,先根据ctid的blockid排序然后再从heap获取记录,以获得物理上顺序的扫描。
    screenshot
    2. cluster by index,将表的物理存储顺序按照索引的顺序来存放,从而使用该索引进行范围扫描时,则是顺序的扫描。
    但是请注意cluster的行为是一次性的,表依旧是堆表,只是物理存储的顺序与索引的顺序相关性一致,从而达到了查询时消除离散扫描的功效,它更适合静态的历史数据。
    例如微博类的应用,可以将历史数据按用户ID和时间索引进行cluster化,那么在根据时间或用户ID查询这个用户的历史记录时,就不会产生离散的IO。
    screenshot
    3. BRIN索引,这个是针对流式记录的一种索引,只记录块或者相邻块的元数据,如取值范围。 从而实现快速检索的目的。 详见
    https://yq.aliyun.com/articles/27860

  • PostgreSQL的表是基于HEAP存储的,不存在以上B+树存储的问题,随便怎么插入,速度都很快。

  • SSD的原子写,通常SSD写入时是以最小单位为4K的写入,即使修改很小的数据。
    那么以directio或buffer io为主的数据库,哪个对SSD的伤害更大呢?
    对于directio的数据库,因为每次都是真实的伤害,而buffer io的数据库,OS层还会合并IO,可以大幅降低SSD的真实写(os 层调整vm.dirty_background_ratio可以调整写频率,从而影响合并粒度)。
    PostgreSQL的shared buffer管理是基于buffer io的管理,对SSD来说是一种很好的保护,有兴趣的童鞋可以测试验证一下。
    screenshot

2. Inefficient data replication

uber文章的观点

PG的复制低效,有写放大。

本文观点

PostgreSQL的流复制非常高效,延迟几乎为0,同时还支持流的压缩和加密传输,很多企业用流复制来实现异地容灾,HA,读写分离的应用场景。
同时PostgreSQL也支持逻辑复制(>=9.4支持流式逻辑复制, <9.4的版本则支持基于触发器或者基于异步消息的逻辑复制)。

原理剖析

  • 问题反驳 1 (复制低效)
    我第一次听说PG的复制低效,要知道PG的复制是基于流式的物理变更,业界有名的高效,延迟极低(复制延迟与事务大小无关),几乎是接近0的延迟。
    甚至用来做主备同步复制,对主库事务提交的RT影响也是可控的,主库依旧可以保持几十万的tps。

    PostgreSQL流复制原理
    即时唤醒,流式复制,所以延迟极低。
    screenshot

  • 问题反驳 2 (REDO写放大)
    基于回滚段实现MVCC的数据库,在更新时,拷贝到回滚段的旧版本,是要写REDO的。

    而基于多版本实现MVCC的数据库,旧版本仅仅需要写修改行头bit位的REDO,所以基于多版本实现MVCC的数据库,更新时写入的REDO应该是基于回滚段实现MVCC的数据库的一半甚至更少(比如基于物理的回滚段要拷贝整个块,产生的REDO也很大)。
    screenshot

    同时,由于基于回滚段实现MVCC的数据库回滚时,要将回滚段的数据拷贝回数据文件,是会产生REDO的,这一点,基于多版本实现MVCC的数据库不存在这种写放大的问题。
    screenshot

  • 问题反驳 3(复制流量放大)
    基于REDO的物理复制,意思就是要把REDO复制一份到备库。
    所以REDO写了多少,就要复制多少到备库,网络的流量也是这样的。

    另一种是基于REDO的逻辑复制,需要复制的数据不仅仅包括新的数据,还要包括旧的版本数据(PK或者full row)。
    可能一条记录更新前和更新后的数据都要复制。

    对更新操作来说,物理复制,不需要复制旧的记录(因为产生REDO的仅仅是XMAX的变化)过去,而逻辑复制则需要复制旧的记录过去。
    另外需要注意的是,目前PG的垃圾回收也是以物理恢复的形式复制的,在实现上还有改进空间,比如通过逻辑的方式复制垃圾回收(只复制block id),可以大大减少网络传输的流量。
    screenshot

    而 uber 文章并没有指出,事实上 MySQL 目前只支持逻辑复制,并且如果要开启逻辑复制,不仅仅要写redo,同时还要写 binlog,等于写了双份日志,这个写放大也是很大的。

    MySQL redo 用于恢复数据库,binlog用于复制。
    screenshot

    自PostgreSQL 9.4开始,PG内核层就同时支持物理复制和逻辑复制,而且仅仅写一份日志就能同时支持物理以及逻辑复制。
    在9.4版本之前,则可以通过其他软件进行逻辑复制(例如Londiste3, slone-I)
    screenshot

    逻辑复制需要注意1,被复制的表一定要有PK。 物理复制不存在这个问题

    逻辑复制需要注意2,大事务导致主备的延迟非常大,因为备库一定要等主库事务结束,备库才能开始回放该事务。 物理复制不存在这个问题

小结

  • PG的复制是业界有名的高效,延迟极低(关键是复制延迟与事务大小无关),网络好的话,几乎是接近0的延迟。
  • 基于多版本实现MVCC的数据库,就版本仅仅需要写修改行头bit位的REDO,所以基于多版本实现MVCC的数据库,更新时写入的REDO应该是基于回滚段实现MVCC的数据库的一半甚至更少(比如物理回滚段要拷贝整个块,产生的REDO也很大)。
  • 对更新操作来说,基于REDO的物理复制,不需要复制旧的记录过去,而逻辑复制则需要复制旧的记录过去,物理复制产生的网络流量更小。
  • 逻辑复制有一个弊端,一定要PK。 物理复制不存在这个问题
  • 逻辑复制另一个弊端,大事务导致主备的延迟非常大,因为备库一定要等主库事务结束,备库才能开始回放该事务。 物理复制不存在这个问题,不需要等待主库事务结束后再回放redo

彩蛋

  • PostgreSQL可以开启协议层压缩,同时可以选择是否加密传输,压缩传输REDO。更高效,更安全。
  • PG的用户如果有主备环境,可以关闭FULL_PAGE_WRITE,产生的REDO更少(第一次更新的PAGE不需要写FULL PAGE)。
    但是需要注意,如果关闭了FPW并且主库因主机问题或在OS问题挂了,需要从备份环境恢复。
  • PG用户,可以将checkpoint拉长,减少FULL PAGE的产生,从而减少REDO的产生。
  • PG的用户,如果需要从PG或者MYSQL复制到阿里云的rds PG,可以使用阿里dbsync插件,目前支持全量复制,增量的逻辑复制正在开发中。
    https://help.aliyun.com/document_detail/35458.html
    https://help.aliyun.com/document_detail/35459.html
    http://yunpan.taobao.com/s/11YcWhIiBxV (提取码:Ed23Sh)

3. Issues with table corruption

uber文章的观点

用户在使用PG 9.2 时,因为极端情况下的主备切换,导致了一些数据corruption问题。

本文观点

从社区了解到,这个问题已经在9.2的版本修复,后面的版本也没有这个问题。

PG一直以来就是一个以稳定性和功能强大著称的数据库,在企业市场有非常好的口碑。

国内的银行,运营商,保险,互联网公司都有在核心环境使用

  • 平安科技、阿里巴巴、高德、去哪儿、腾讯、用友、阳光、中移动、探探、智联、典典、华为、斯凯、通策医疗、同花顺、核电、国家电网、邮储银行、友盟、莲子。。。。。。

海外的汽车生产巨头,政府部门,医疗,物流等各个行业也都有非常多的用户

  • 生物制药 {Affymetrix(基因芯片), 美国化学协会, gene(结构生物学应用案例), …}
  • 电子商务 { CD BABY, etsy(与淘宝类似), whitepages, flightstats, Endpoint Corporation …}
  • 学校 {加州大学伯克利分校, 哈佛大学互联网与社会中心, .LRN, 莫斯科国立大学, 悉尼大学, …}
  • 金融 {Journyx, LLC, trusecommerce(类似支付宝), 日本证券交易交所, 邮储银行, 同花顺…}
  • 游戏 {MobyGames, …}
  • 政府 {美国国家气象局, 印度国家物理实验室, 联合国儿童基金, 美国疾病控制和预防中心, 美国国务院, 俄罗斯杜马…}
  • 医疗 {calorieking, 开源电子病历项目, shannon医学中心, …}
  • 制造业 {Exoteric Networks, 丰田, 捷豹路虎}
  • 媒体 {IMDB.com, 美国华盛顿邮报国会投票数据库, MacWorld, 绿色和平组织, …}
  • 零售 {ADP, CTC, Safeway, Tsutaya, Rockport, …}
  • 科技 {Sony, MySpace, Yahoo, Afilias, APPLE, 富士通, Omniti, Red Hat, Sirius IT, SUN, 国际空间站, Instagram, Disqus, …}
  • 通信 {Cisco, Juniper, NTT(日本电信), 德国电信, Optus, Skype, Tlestra(澳洲电讯), 中国移动…}
  • 物流 {SF}

小结

基于逻辑复制的数据库,主库压力大或者有长事务时,备库追不上主库时有发生。
又或者因为某些原因导致主备不一致,即使发现了,可能并没有很好的修复手段,因为你不知道该以哪个数据为准。
逻辑复制导致主备不一致的原因较多,例如 主库执行失败,备库执行成功,或者备库执行成功,主库执行失败。
又或者 主库和备库的环境不一致,例如字符集,或者其他的,都非常容易导致主和备的不一致。

对于要求主备严格一致的场景,强烈建议使用物理复制。

4. Poor replica MVCC support

uber文章的观点

PG备库的MVCC支持较差,查询会与恢复堵塞

本文观点

首先,PG的备库分两种,一种是物理备库,一种是逻辑备库。

对于逻辑备库来说,与MYSQL的恢复机制是一样的,既然是一样就不需要讨论了。

UBER文章说的 查询会与恢复堵塞,说的是物理备库,但必须纠正一个观点,查询是否堵塞恢复,要论场景,况且堵塞的情况极为少见。

还有一点要注意,逻辑复制也会有堵塞备库的QUERY。

原理剖析

物理复制,什么情况下查询会堵塞、或与恢复冲突?
当以下操作产生的REDO被复制到备库,并且备库准备拿这些REDO来恢复时。

  • Access Exclusive locks taken on the primary server, including both explicit LOCK commands and various DDL actions, conflict with table accesses in standby queries.
    主库的访问排它锁,与备库对应的锁产生冲突。
    例如主库truncate a表, 备库查询a表。
    这种情况的冲突面很窄。

  • Dropping a tablespace on the primary conflicts with standby queries using that tablespace for temporary work files.
    主库删除表空间,备库使用这个表空间产生临时文件。 例如主库删除TBS,备库的一个大的查询需要写临时文件,并且这个临时文件是写到这个表空间的。
    这种情况非常少见,也很容易规避,新建一个临时表空间不要删除即可。

  • Dropping a database on the primary conflicts with sessions connected to that database on the standby.
    主库删除数据库,备库刚好连在这个数据库上。
    这种情况也非常的少见。

  • Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still "see" any of the rows to be removed.
    主库回收dead tuple的REDO,同事备库当前的query snapshot需要看到这些记录。
    这种情况可以通过参数控制,恢复优先,或查询优先。 可以配置时间窗口。
    而且这种冲突出现的概率也非常的小,除非用户在备库使用repeatable read,同时是非常大的事务。
    而通常用户用的都是read committed.

  • Application of a vacuum cleanup record from WAL conflicts with queries accessing the target page on the standby, whether or not the data to be removed is visible.
    同上,但是当query访问的页就是要清理垃圾的页时,也是有冲突的。
    这是物理复制与逻辑复制唯一有差别的地方,但是对现实场景来说,这种情况出现的概率也不大。

PostgreSQL提供了3种解决备库上查询与恢复冲突的办法

  • 在主库配置vacuum_defer_cleanup_age来解决以上最后两种冲突。

    vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is delayed
    
  • 在备库配置recovery延迟来解决以上所有冲突,给备库的QUERY设置一个执行窗口

    max_standby_archive_delay = 30s        # max delay before canceling queries
    # when reading WAL from archive;
    # -1 allows indefinite delay
    # 
    max_standby_streaming_delay = 30s      # max delay before canceling queries
    # when reading streaming WAL;
    # -1 allows indefinite delay
    
  • 在备库配置hot_standby_feedback,备库会反馈给主库QUERY情况(可能是快照,而不是QUERY本身)(如果是级联环境,则会反馈给最上层的主库)
    从而主库知道备库在干什么,在cleanup dead tuple时,会考虑备库的情况,防止冲突。

    hot_standby_feedback = off             # send info from standby to prevent query conflicts
    # 
    wal_retrieve_retry_interval = 1s 
    

很显然UBER没有意识到PostgreSQL防止冲突的手段。

在备库查询与恢复冲突只会导致apply延迟,并不会堵塞备库接收主库产生的REDO。
对于PG来说,主备冲突导致的备库apply延迟,理论上不会有逻辑复制在碰到大事务时那么可怕,逻辑复制遇到大事务,导致的延迟是很严重。
在现实应用场景中,很少有用户担心PG的备库延迟,即使有短暂的冲突,因为是基于块的恢复,恢复速度是很快的,马上就能追平(只要备库的IO能力够好,通常追平是瞬间完成的)。

逻辑复制,什么情况下查询会堵塞、与恢复冲突?

  • 备库发起一个repeatable read的事务,由于备库不断的恢复,备库的该查询事务有可能因为snapshot too old失败。
  • 主库发起的DDL语句,回放时会与备库的查询冲突,DDL的回放会被完全堵塞。
  • 主库删除一个数据库,回放时如果备库正好连在这个数据库上,产生冲突。

小结

基于物理复制或逻辑复制,只要备库拿来使用,都有可能出现查询与恢复冲突的情况。
PG对于冲突的处理非常的人性化,你可以选择恢复优先 or 查询优先,设置时间窗口即可。
同时PG还支持备库的QUERY反馈机制,主库可以根据备库的QUERY,控制垃圾回收的延迟窗口,避免QUERY和垃圾回收的冲突。

5. Difficulty upgrading to newer releases

uber文章的观点

PG的跨版本升级较难,跨版本不支持复制

本文观点

PG的大版本升级的途径非常多,也很方便。

我这里给出两个方法
1. 方法1 , 通过迁移元数据的方式升级,这种升级方式,取决于元数据的大小(即数据结构,函数,视图等元信息)所以不管数据库多大,都能很快的完成升级。
例如以10万张表,1万个函数,1000个视图为例,这样的元数据大小可能在几十MB的水平。 自动化程度高的话,导出再导入应该可以控制在分钟级别完成。
关键是它能支持原地升级,也就是说,你不需要再准备一套环境,特别是数据库非常庞大的情况下,再准备一套环境是很恐怖的开销。
当然,如果企业有环境的话,为了保险,通常的做法是,复制一个备库出来,在备库实现原地升级,然后激活备库转换为主库的角色。
备库升级结束后,再升级老的主库,由于只动到元数据,所以主备的差异很小,rsync一小部分数据给老的主库,就能让老的主库实现升级,同时将老的主库切换成备库即可。
简单的几步就完成了主备的大版本升级。
screenshot

基于pg_upgrade的大版本升级可以参考我以前写的文章
http://blog.163.com/digoal@126/blog/static/1638770402014111991023862/
http://blog.163.com/digoal@126/blog/static/163877040201341981648918/

2. 方法2 , 通过逻辑复制增量平滑升级,与MySQL的升级方法一样,也很便利,但是要求一定要准备一个备库环境,如果数据库已经很庞大的话,总的升级时间会比较漫长。
对于 >= 9.4的版本可以使用PG内置的逻辑复制。
小于9.4的版本则可以使用londiste3或者slony-I。

PG跨版本支持复制,而且支持的很好。
对于>=9.4的版本,可以用基于流的逻辑复制。
对于<9.4的版本,可以使用londiste3, slony-I。

扩展阅读,用心感受PostgreSQL


内核扩展

《找对业务G点, 体验酸爽 - PostgreSQL内核扩展指南》
https://yq.aliyun.com/articles/55981

《当物流调度遇见PostgreSQL - GIS, 路由, 机器学习 (狮子,女巫,魔衣橱)》
https://yq.aliyun.com/articles/57857

《弱水三千,只取一瓢,当图像搜索遇见PostgreSQL (Haar wavelet)》
https://yq.aliyun.com/articles/58246

《用PostgreSQL支持含有更新,删除,插入的实时流式计算》
https://yq.aliyun.com/articles/30985

《PostgreSQL 内核扩展之 - 管理十亿级3D扫描数据》
https://yq.aliyun.com/articles/57095

《PostgreSQL 内核扩展之 - ElasticSearch同步插件》
https://yq.aliyun.com/articles/56824

《为了部落 - 如何通过PostgreSQL基因配对,产生优良下一代》
https://yq.aliyun.com/articles/55869

《PostgreSQL 结巴分词》
https://yq.aliyun.com/articles/58007

《PostgreSQL 如何高效解决 按任意字段分词检索的问题 - case 1》
https://yq.aliyun.com/articles/58006

《mongoDB BI 分析利器 - PostgreSQL FDW (MongoDB Connector for BI)》
https://yq.aliyun.com/articles/57987

《关键时刻HINT出彩 - PG优化器的参数如何优化、执行计划如何固化》
https://yq.aliyun.com/articles/57945

《PostgreSQL Oracle兼容性之 - 锁定执行计划 (Outline system)》
https://yq.aliyun.com/articles/57999

《使用PostgreSQL 流复制decode 对接kafka,实现数据跨应用融合》
http://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/


场景与优化

《PostgreSQL 如何潇洒的处理每天上百TB的数据增量》
https://yq.aliyun.com/articles/8528

《PostgreSQL 秒杀场景优化》
https://yq.aliyun.com/articles/3010

《PostgreSQL独孤九式搞定物联网》
https://yq.aliyun.com/articles/52405

《PostgreSQL 用CPU "硬解码" 提升1倍 数值运算能力 助力金融大数据量计算》
https://yq.aliyun.com/articles/7482

《PostgreSQL 百亿数据 秒级响应 正则及模糊查询》
https://yq.aliyun.com/articles/7444

《PostgreSQL 1000亿数据量 正则匹配 速度与激情》
https://yq.aliyun.com/articles/7549

《PostgreSQL 百亿地理位置数据 近邻查询性能优化》
https://yq.aliyun.com/articles/2999


大数据实践

《Greenplum 数据分布黄金法则 - 论分布列与分区的选择》
https://yq.aliyun.com/articles/57822

《Greenplum行存与列存的选择以及转换方法》
https://yq.aliyun.com/articles/59132

《阿里云ApsaraDB RDS用户 - OLAP最佳实践》
https://yq.aliyun.com/articles/57778

《Greenplum 资源隔离的原理与源码分析》
https://yq.aliyun.com/articles/57763

《PostgreSQL 多维分析 CASE》
https://yq.aliyun.com/articles/53750

《一致性哈希在分布式数据库中的应用探索》
https://yq.aliyun.com/articles/57954

《PostgreSQL 9.5新特性 width_bucket 位置插值,展示柱状图》
https://yq.aliyun.com/articles/2642

《PostgreSQL 9.5 新特性 高斯(正态)分布和指数分布 数据生成器》
https://yq.aliyun.com/articles/2639

《一个简单算法可以帮助物联网,金融 用户 节约98%的数据存储成本》
https://yq.aliyun.com/articles/18042

《开源数据库 PostgreSQL 攻克并行计算难题》
https://yq.aliyun.com/articles/44655

《PostgreSQL 并行计算 - 助力实时精准营销应用》
https://yq.aliyun.com/articles/44649

《PostgreSQL 9.6 并行计算 优化器算法浅析》
https://yq.aliyun.com/articles/59180

《PostgreSQL 计算 任意类型 字段之间的线性相关性》
https://yq.aliyun.com/articles/18038

《HLL 估值算法在PostgreSQL大数据 估值计算中的应用》
http://blog.163.com/digoal@126/blog/static/16387704020131264480325/
http://blog.163.com/digoal@126/blog/static/1638770402013127917876/
http://blog.163.com/digoal@126/blog/static/16387704020131288553810/

《PostgreSQL 流式计算数据库pipelineDB》
http://www.pipelinedb.com/

《旋转门数据压缩算法在PostgreSQL中的实现》
https://yq.aliyun.com/articles/59101

《PostgreSQL 三角函数的用法举例 - 已知3点求夹角(旋转门续)》
https://yq.aliyun.com/articles/59175

《PostgreSQL 文本数据分析实践之 - 相似度分析》
https://yq.aliyun.com/articles/59212


最佳实践

《固若金汤 - PostgreSQL pgcrypto加密插件》
https://yq.aliyun.com/articles/58377

《PostgreSQL 物联网黑科技 - 瘦身500倍的索引(范围索引 BRIN)》
https://yq.aliyun.com/articles/27860

《PostgreSQL 物联网黑科技 - 阅后即焚》
https://yq.aliyun.com/articles/27722

《如何用PostgreSQL解决一个人工智能 语义去重 的小问题》
https://yq.aliyun.com/articles/25899

《PostgreSQL 老湿机图解平安科技遇到的垃圾回收"坑",及解法》
https://yq.aliyun.com/articles/57710

《PostgreSQL雕虫小技,分组TOP性能提升44倍》
https://yq.aliyun.com/articles/57315

《PostgreSQL 9.6 黑科技 bloom 算法索引,一个索引支撑任意列组合查询》
https://yq.aliyun.com/articles/51131

《PostgreSQL 9.6 攻克金融级多副本可靠性问题》
https://yq.aliyun.com/articles/45518

《distinct xx和count(distinct xx)的 变态优化方法》
https://yq.aliyun.com/articles/39689

《PostgreSQL 百亿级数据范围查询, 分组排序窗口取值 变态优化 case》
https://yq.aliyun.com/articles/39680

《中文模糊查询性能优化 by PostgreSQL trgm》
https://yq.aliyun.com/articles/39033

《PostgreSQL Oracle兼容性之 - connect by》
https://yq.aliyun.com/articles/54657

《论云数据库编程能力的重要性》
https://yq.aliyun.com/articles/38377

《使用sysbench测试阿里云RDS PostgreSQL性能》
https://yq.aliyun.com/articles/35517

《PostgreSQL merge json的正确姿势》
https://yq.aliyun.com/articles/54646

《PostgreSQL 在路上的特性 - 远离触发器, 拥抱内置分区》
https://yq.aliyun.com/articles/54456

《PostgreSQL 如何轻松搞定行驶、运动轨迹合并和切分》
https://yq.aliyun.com/articles/54445

《在PostgreSQL中如何生成kmean算法的测试数据》
https://yq.aliyun.com/articles/53992

《在PostgreSQL中如何生成线性相关的测试数据》
https://yq.aliyun.com/articles/53993


内核探索
《PostgreSQL plan cache 源码浅析 - 如何确保不会计划倾斜》
https://yq.aliyun.com/articles/55719

《为什么用 PostgreSQL 绑定变量 没有 Oracle pin S 等待问题》
https://yq.aliyun.com/articles/55698

《PostgreSQL 同步流复制原理和代码浅析》
https://yq.aliyun.com/articles/55676

《深入浅出PostgreSQL B-Tree索引结构》
https://yq.aliyun.com/articles/53701

《PostgreSQL 可靠性和一致性 代码分析》
https://yq.aliyun.com/articles/37395

《PostgreSQL HOT技术》
src/backend/access/heap/README.HOT

《PostgreSQL B-Tree GIN GIST SP-GIST BRIN HASH索引内部结构》
https://www.pgcon.org/2016/schedule/attachments/434_Index-internals-PGCon2016.pdf


更多内容请访问
云栖PostgreSQL圈子
https://yq.aliyun.com/groups/29

云栖Greenplum圈子
https://yq.aliyun.com/groups/13

ApsaraDB 数据库内核组月报(涵盖MySQL PostgreSQL Greenplum mongoDB 等数据库引擎)
http://mysql.taobao.org/monthly/

我的BLOG
http://blog.163.com/digoal@126

我的git
https://github.com/digoal

小结

每种数据库都要去深入了解,才能去解决业务上面对的问题。

每种数据库存在即有存在的理由,有它适合的场景,MySQL和PostgreSQL发展这么多年,都有各自的用户群体,相互都有学习和借鉴的地方。

**作为数据库内核工作者,要多学习,把数据库做好,把最终用户服务好才是王道 ** ,

UBER发表的该文章对PG的论点过于表面和片面,再加上 很多国内的用户对PostgreSQL的认识还停留在07年以前的水平 ,读者要多思考,否则容易被拿去当枪使 。

基于线程和进程的讨论非常多,优劣都很鲜明,PostgreSQL基于进程的模型,优势则是非常稳健,可以动态的fork worker进程,动态的分配共享内存段,新增插件很方便。

所以PG的扩展能力极强,看看PG那无数的插件就知道了,它是一个贴近用户,并且高度可定制化的数据库。

本文末尾的扩展阅读也包含了大量通过插件方式扩展PG功能的文章。

劣势就是建立连接时的开销较大,但通常应用都有连接池,没有的话可以通过加连接池解决这个问题。

PG社区对PostgreSQL的技术普及与推广任重道远,加油!!!

本文仅对uber发文的PG部分,从技术实现的角度作出剖析和解释,网友可以多多交流。

祝大家玩得开心,欢迎随时来 阿里云促膝长谈业务需求 ,恭候光临。

PostgreSQL作为一个开源数据库,还有很多值得改进和优化的地方,阿里云ApsaraDB for PG的小伙伴们一直在努力。

阿里云的小伙伴们加油,努力做好内核与服务,打造 最贴地气的云数据库

本文为云栖社区原创内容,未经允许不得转载,如需转载请发送邮件至yqeditor@list.alibaba-inc.com

用云栖社区APP,舒服~

【云栖快讯】快速解决数据库难题,云数据库经典案例及最佳实践直播专场!阿里云数据库专家团队成员倾囊相授!赶紧报名。  详情请点击

网友评论

1F
webdevelop

之前就一直有看德哥相关的PostgresSQL的文章,看到那篇新闻,马上就想到过来看德哥是怎么说的,看完恢复一些对PG的信心,不过从另外一个角度来讲,大多数普通PG用户水平很可能是不如 Evan Klitzke的,碰到类似的问题可能会更抓瞎,那篇文章中有没有值得反思或者引申出来的问题呢?

德哥 赞同
德哥

好问题,我对每一个问题补充一些反思

评论
2F
ayanamistyang

然而uber文中提到的几个问题依旧无解,一是更新一个列后,所有index需要重建;一个是业务上可能几乎不会有事务回滚操作,代价高低的评判就完全不一样;还有就是连接数的问题;互联网公司大多把逻辑放到应用上,pg高扩展性无从发挥

德哥

并不是更新一个列后,需要更新所有索引,我在文章中已经解释了。 麻烦看清楚再回复吧。

ayanamistyang

@digoal 你文中那个验证是有问题的。写放大不是说,写完以后空间会膨胀,而是一个4k的写请求,在SSD上需要操作超过4k的block才能完成,因为SSD的寿命取决于block的操作次数,所以看起来很少的写请求,就把SSD寿命给打没了,这才是让人介意的地方。你拿压测前后表大小来比较毫无意义。

德哥

@ayanamistyang 这个测试不是用来说明SSD擦写的问题呀。 关于没有SSD擦写放大,你如果有疑问的话,看一下我在文章中介绍的buffer io吧。

评论
3F
金克枟

复制那块,现在还有多少开发会设计没有主键的表?还有多少开发会在线上业务代码里写大事务?
PG难道都是为这些无关紧要,可以被避免的场景做优化么?
不要列客户,也不要列相关阅读,直面问题。
直面那些高频问题,而不是偶然问题。

金克枟

数据库客户,希望数据库来帮忙解决那些在应用层,或者部署上解决不了的问题,而不是防呆。。。

德哥

UBER中提到的每个问题都有解,只是他们没写出来,又或者不知道怎么解。 相信仔细阅读完本文,你会找到答案。

金克枟

@digoal Innodb有InsertBuffer来优化非唯一索引的更新,因此对MySQL来讲,不存在二级索引写放大。 Pg呢?最多跟没有InsertBuffer的MySQL一个level。不要提回滚,那是罕见事件。

德哥

@金克枟 你还没有理解b+tree组织表和heap表的区别,如果你说b+tree组织表解决了写放大的问题,但是你没说这是PK不能被更新的前提。 同时B+TREE组织表还引入了其他的问题 写速度变慢,读写冲突(must merge insert buffer first)。 再回过来看PG和ORACLE的HEAP存储表,PG早在2007年就有HOT技术解决了索引更新的问题,请翻一下本文的测试数据,完全没有索引更新的问题。

金克枟

@digoal UBER的问题是对索引列的更新带来的写放大,你的例子是对非索引列的更新。InsertBuffer推迟了对索引的立即更新,从而缓解了写放大。

德哥

@金克枟 你不知道insertbuffer是以读堵塞为代价的么?

德哥

@金克枟 而且只是缓解么,万一buffer挂了会怎样,要保证可靠性,又得写REDO吧,是不是有多了REDO写呢。 一个气球这里挤下去了,另外一个地方就突起了。

德哥

再回到索引,你可知道PG还支持Hash, GiST, GIN, SP-GiST, BRIN, Bloom索引算法么? 而且接口公开,可以解任何数据结构的索引组织问题。 再说到insert buffer,要实现都可以,tradeoff的问题。

金克枟

@digoal 这就是权衡啊,读堵塞,顺序多写,都是可以接受,可以优化的。

德哥

@金克枟 对啊,你的讨论前提框定了《 接受读堵塞,必须顺序写》。 oh my god。

金克枟

@digoal 我的意思是,所谓读阻塞,是Page读入内存后,合并InsertBuffer之后才可用,这是内存cpu操作,可以优化。磁盘的顺序写,多一点,也可以优化。比等待一次随机磁盘IO的开销要小多了啊。关键是PG要实现类似ChangeBuffer的功能,把IOPS优化到机制才行。

德哥

@金克枟 PG的GIN索引就包含了change buffer的功能,你可以去了解一下的。 关键它还不堵塞读。

评论
4F
xinxinjinjin

水一个 问题点还是在于pg单表索引过多的时候 update需要修改数据块的量和wal日志的生成量上 单表索引过多应该和uber的业务模型有关 技术实现还是要和业务挂钩的 任何技术都不能满足所有业务需求 另基于回滚段的数据库在发生行迁移的时候回导致查询的时候多读块 索引指针应该是指向rowid的不会变

德哥

你说的问题早在2007年就有HOT技术解决了,看文章中的测试数据吧。

评论
5F
夜读书

其实关于pg的mvcc,应该是一种取舍吧,优点是回滚特别快,因为新老数据都可以在表文件中,缺点是表膨胀,这可能是与PG的设计思想有关,但问题不大,世上本就没有完美的。
而关于复制的issue,应该是uber踩雷了,而那个bug吧,其实社区修复的还挺快的,也许uber没注意,或者大意啦,呵呵
对于是物理复制还是逻辑复制,我感觉没啥好讲的,看pg的趋势,基本是向主流商业数据库靠拢了,至少db2就是这么玩的,hadr,物理备份缺点就是不太灵活,但确实是可以做到同步的(对一些严苛的环境,必须数据同步),如果网络环境好。而逻辑复制呢,灵活,可以选部分表,部分数据,还可以对数据进行加工,但只能尽可能的减小延时,接近同步,是达不到真正同步的。pglogical再成熟一点就好了。物理还是逻辑可根据自己应用特点灵活选择。

很喜欢德哥的文章,以上观点,如有错误,麻烦指正 ,谢谢!!

德哥 赞同
德哥

膨胀和用户使用或配置有关系,你看到我文章中的测试吗,每秒13万的持续强力更新压测,索引和表并没有发生膨胀。
用户没有使用好则有可能出现膨胀,例如有未关闭的长事务(9.6通过snapshot too old解决了这个问题),又或者用户参数配置有问题(关闭了autovacuum,naptime太长,worker太少,autovacuum maintenance work mem太小,factor太大,sleep太长)。

物理复制和逻辑复制,PG都支持的。逻辑复制现在也很多成熟的插件支持啦,如果开发能力强的话完全可以自定义逻辑复制的,阿里云提供的dbsync软件就是逻辑复制的典型代表。 也在本文中有链接的。

PG还有很多可以打磨的地方,我们会努力做好内核与服务,打造最贴地气的云数据库 。

夜读书

@digoal 其实pg可以参考商业数据库的一些成熟方案,我看github上有一个pg复制的项目,逻辑复制结合了kafka,这个就非常像db2的Q replication。明年应该是版本10了吧,逻辑复制可能会更好,PG有些插件用着用着,就不更新了,感觉没有官方支持的踏实啊

德哥

@夜读书 对的,结合流,可以长出很多方案,Bottled Water就是其中一种,相当于把redo解析成了binlog存入kafka,可以提供很好的分发左右,方案很好的。 阿里云提供的dbsync也是其中一种。
以PostgreSQL社区一贯以来的作风,通常是开放大量的接口,让用户一起来玩,玩法可以有很多。 如果真的有很好的开源插件,并且开源协议允许,是有可能吸纳到内核来的。 社区那边也有很多在做这方面工作的,例如oleg带领带postgrespro。 包括2nd,EDB,ntt 都有开源的东西。

夜读书

@digoal PG确实开放,但用户选择的时候,可能会有些担心,同类的方案有好几个,哪个最好,哪个支持最长久,哪个有可能吸纳到内核中来呢? 商业数据库,倒不用担心了,不用选 :)

评论
6F
毛小普

内容还不是很懂,boogieboard不错!

(来自社区APP)
7F
xfdblover

本文似乎回避了一个问题,Inefficient architecture for writes,这个主要针对的是老版本数据的清理机制----autovacuum。
正是因为垃圾清理机制的低效,才会导致写操作的低效。

德哥

垃圾回收8.4就改进了,有visibilitimap 不需要扫描非脏块, 锁方面只和DDL锁冲突。 调度方面,用户可以自己设置阈值进行调度。 如果垃圾回收有问题的话,你觉得我在文中这种压力下不会膨胀么?

xfdblover

基于visibilitimap的垃圾回收是非常低效的处理方法,单个表每次发现有几十上百个修改,就会触发vacuum。
visibilitimap是标记整个页面的。
假如表文件达到1GB以上,有500个修改行分布在随机的500个页面上,试想一下,顺序去扫描这500个页面,几乎相当于扫描整个表,对于单个页面还得逐行检查。这个代价还是蛮高的。

xfdblover

另外像Insert这种操作,oracle和innodb在事务提交后都不需要进行垃圾清理,PG还得当作垃圾清理一下。

德哥

@xfdblover 你要知道经常读写的表,算热表,热表按LRU的算法,是不会挤出去的。 所以实际上你的担心是多余的。 你用极端的情况来说一件normal的事情,是不合理的。

德哥

@xfdblover 写UNDO不算写么? UNDO回收不算写么? 写UNDO 日志不算写么? 什么逻辑?

xfdblover

@digoal 如果缓存够大,确实没什么影响。一般来说缓存大小要小于访问操作的数据量大小。
这取决于当时最长事务的持续时间,如果事务足够长,那在这段时间内被淘汰的可能性越大。

xfdblover

UNDO确实需要写也需要回收,但是这个及时的,是稳定的。是分散到每个DML操作进行的。
而PG的回收是集中的,是可能引起系统性能急剧下降的(性能毛刺),这有点类似于全量检查点带来的影响。

德哥

@xfdblover PG的垃圾回收是有调度的,是稳定的,是分散到每个表的。 你还是要看看实际的测试数据,武断的下定论是会误导别人的,误人误己。

xfdblover

等待一个最长事务结束后,将这个事务期间产生的所有垃圾数据一次性做一次清理,这也叫稳定的吗?
或者像oracle在每个DML操作过程中清理数据,这才叫真正的稳定?

xfdblover

另外,如果长事务还没提交,那这段过程中去清理,vacuum进程是不是都在做无用功?

xfdblover

如果清理效率低和不及时,那会不会数据膨胀越来越厉害?

德哥

@xfdblover 对性能没有影响,就是稳定。我建议你还是拿数据说话。

德哥

@xfdblover 立即回收与延迟回收,你想想哪个高效? 即时回收,以牺牲事务RT为前提。 延迟回收,RT显然比即时回收低,但是带来的问题是可能膨胀,注意是可能,只要回收及时就不会有这个问题。 你也看到文章中的测试,在极端的数据更新压力测试下,并没有发生你所分析和担心的问题,所以关键还是使用者对其了解,配置好,就可以消除这个问题。 事实胜于雄辩啊。

fxdblover

有点不太认同。延迟回收也需要耗IO,而系统的IOPS有上限的,如果延迟回收太低效,就会占据更多的IO,使得正常的DML操作的IO请求得不到处理,发生延迟。

德哥

@fxdblover 写回滚段不用IO么?回收回滚段不用IO么? 如果在DML内回收RT岂不是更高。 而延迟回收,不仅仅能降低RT,还能更好的调度,错开冲突。

xfdblover

UNDO需要IO,但是,就像你说的,经常读写的块,算热块,UNDO块按LRU的算法,在回收前是不会挤出去的。
UNDO最大的好处是老数据集中,容易控制,不会出现磁盘空间占用无法控制的局面;
PG的垃圾数据比较分散,容易出现垃圾数据膨胀控制不住的局面。并且性能容易发生突变和不稳定的现象。

德哥

@xfdblover 你来一个长事务,修改大量的块,UNDO 的块一样挤出去, 回收时照样产生大量的物理IO。

xfdblover

UNDO只需要写和读,回收直接重用覆盖就行了。
写是写的REDO,刷盘是后台进程刷的,如果是增量检查点,每次不需要全部刷盘。
读是访问老版本数据的时候去读的。而老版本一般访问频率不高,单独分离出来可以防止表空间膨胀,表空间的垃圾清理也更加简单。

xfdblover

UNDO需要读,PG的老版本也需要读,只不过老版本数据所在位置不一样

德哥

@xfdblover 回收绝对没有重用覆盖这么简单,申请内存还需要清零呢。如果直接重用,恐怕数据不一致吧。 重度UPDATE和SELECT的场景,UNDO很容易成为热点。 而且我以前也对比过ORA和PG,长时间的select和update大压力压测,基于UNDO的ORA没有任何优势。

评论
8F
xfdblover

每次说到MVCC机制的时候,社区似乎一直在用回滚快来回避这个问题。

德哥

这是两码事呢

xfdblover

回滚一般是程序出现异常,才会触发回滚,而应用系统一般不会经常出异常然后回滚。

德哥

@xfdblover 你说的没错,但是我说的是 “每次说到MVCC机制的时候,社区似乎一直在用回滚快来回避这个问题。” 这句话不对。 谢谢

评论
9F
xfdblover

而我认为这恰恰就是PG最为不合理的地方。
当然UBER提的其他问题确实要么是不会用要么是PG的bug。

10F
xfdblover

用户没有使用好则有可能出现膨胀,例如有未关闭的长事务(9.6通过snapshot too old解决了这个问题),又或者用户参数配置有问题(关闭了autovacuum,naptime太长,worker太少,autovacuum maintenance work mem太小,factor太大,sleep太长)。
---------长事务是没法避免的,一直用短事务也会有性能问题,snapshot too old只是避免最恶劣的情况发生。查询性能出现恶化是经常发生的事,原因也很多。

德哥

关于垃圾回收,还有改进的空间,snapshot too old还是很简单粗暴。 最好的方法是能根据版本的实际可见性进行回收,彻底解决膨胀的问题。 这个阿里云ApsaraDB for PostgreSQL在做的。

评论
11F
xfdblover
  1. Write the new row tuple to the tablespace
  2. Update the primary key index to add a record for the new tuple
  3. Update the (first, last) index to add a record for the new tuple
  4. Update the birth_year index to add a record for the new tuple

fillfactor参数默认值为100,正常情况下很容易触发这个问题。(为什么不能像oracle一样设成90?)

虽然将参数调到80~90,可以触发HOT,但是垃圾空间回收前再次更新,还是会触发上面4个步骤。

相比而言,回滚段需要写UNDO和REDO,但是这两个都是顺序写的,代价较小。

代价最高的就是触发索引的更新。因为磁盘页面随机访问数更多。

德哥

你说的都是用户没有正确的使用的情况,你看看我文章中的测试,13万每秒的更新,不能代表大多数应用么,没有发生膨胀,性能稳定。 了解数据库的这些机理,用好它不是更好么,人是活的,明知不对,为什么不能改变呢?

fxdblover

这种性能测试意义有限,其实单纯对比这些简单的场景,oracle, pg, mysq差别不是很大,简单做做优化也能有提升。
不过在真实业务场景,这些优化手段效果很有限。
真实场景不仅有update,还有insert和select,长事务,把fillfactor调小可能又会影响查询性能了(页面更多了),比如聚集查询啊,索引查询啊。因为一般来说读写比例为3:1。
为了让测试case更真实一些,可能需要多运行几个时间长一些的事务,或者时间长点的查询。

德哥

@fxdblover 基于回滚段的数据库,long query带来的后果是UNDO膨胀,或者遇到snapshot too old错误回滚,事务越长损失越大。 重跑也跑不出来,特别是报表系统,出问题的话,估计会有几十位领导盯着你,到时候哭都来不及。 为了保证长事务能跑下去,不得不设置非常大的undo表空间。 一个insert into .. select,如果数据量大一点,就可能snapshot too old,而PG完全不会这样。

xfdblover

不一定非得跑个一天的查询,但是几十秒或者几分钟的事务在实际环境中是会存在的

德哥

@xfdblover 讨论到后面我发现UNDO的问题更大,snapshot too old,膨胀后难以收缩,读写频繁容易成为热点,影响SQL RT,写放大,回滚开销巨大,一系列的问题,一样都不缺。 只是为什么没人抓着不放?

而PG的MVCC之所以被人抓着不放,可能还是深入了解PG原理的用户少,被一些人误导而已,当然也有不会用的成本在里面。 真正用好了,根本就不存在问题。 (当然,不是说没有优化的空间了,代码层是还有很大的优化空间的,我以前也写过很多这方面的文章,PG会越来越好,而且肯定会比UNDO好。 )

评论
12F
xfdblover

基于回滚段或MVCC的数据库,索引的写放大,都与是否发生行迁移有关,概率差不多。
--------避免行迁移一般存在两种机制:in-place update和HOT,而PG只支持HOT,所以概率不一样。
在页面无free space的情况下,in-place update可以继续防止行迁移,而HOT不行。

德哥

你所说的都是有前提的,凡是有前提的都是可以在设计时避免。
就比如使用回滚段的数据库,我没事就搞个长事务,一样报snapshot too old错误,如果这个事务是耗费大量系统资源,历经漫长时间在跑的报表事务,如果你无法快速发现,并按规定时间重跑完成,我估计你可能会看不到明天的太阳。
现在很多企业都有这样的问题吧,比如银行。

xfdblover

snapshot too old可以避免垃圾数据空间无限制的膨胀,要不然PG也不会去弄这个了。
回滚段是可以自动管理的,如果要像PG一样不受限制也是可以的,只不过这样不太好,否则磁盘空间占满,连数据库都启动不了,那就玩完了。

德哥

@xfdblover 所以你并不知道要使用多少回滚段,来几个不可控的查询,就有可能把回滚段用爆,用爆或膨胀后的回滚段要收缩也是非常要命的事情。

评论
13F
xfdblover

基于回滚段实现MVCC的数据库,在更新数据时SSD写放大 > 100%(因为回滚段是一定要写的,并行写回滚段的操作也需要写REDO);而基于多版本实现MVCC的数据库,SSD写放大的概率低于100%(因为可能发生HOT,发生在当前页),而且旧记录只改行的xmax标记,产生的REDO极少。
------如果老数据为1GB,新数据为9GB,那么这1GB的老数据不管是放在回滚段,还是放在数据页面,总共大小还是一样的。
而基于回滚段的MVCC,垃圾清理几乎不需要耗费多余的IO,但是PG在缓存不够用或者LRU缓存淘汰算法发挥作用的情况下,VACUUM就会触发多余的IO,所以会发生写放大。
对于delete操作,PG产生的REDO极少,但是因为数据膨胀带来的表空间扩大和索引空间扩大的影响也不能低估。
另外REDO日志极少并不会产生多大的效果,在PG的Full Page Write面前,其他操作节省的REDO日志都足以被抵消掉。

德哥

参数设置好,老的数据不可能有那么大。 所以你的假设不成立。
FULL PAGE WRITE是为了保证可靠性的,当操作系统crash时,可以做到数据库重启恢复后完全可靠,所以FPW并不是PG独有,只是其他数据库可能不允许你关闭它,或者没有告诉你有这个东西。
而PG允许你在文件系统支持checksum的情况下关闭它减少REDO,所以是非常人性化的设计。

评论
14F
pgfans

德哥,我现在有一个表大概5万行数据,每行平均大小400btye,有一个非索引列需要经常更新,每天更新的频率是50万次。如何设置一个合适的fillfactor来减少更新代码的表膨胀? 或者说fillfactor参数是怎么计算出来。

还有一个问题是当前我们RDS的参数如下,如果运行我上面描述的操作,需要调整吗?
autovacuum_vacuum_scale_factor 0.1
autovacuum_vacuum_cost_delay 0
autovacuum_vacuum_threshold 50
autovacuum_naptime 1min
autovacuum_max_workers 5
autovacuum_work_mem -1
maintenance_work_mem 128MB

德哥

这份参数应该是以前的,现在有几个值已经调整了 autovacuum_naptime, autovacuum_vacuum_scale_factor都调低了。

pgfans

如何设置一个合适的fillfactor来减少更新代码的表膨胀? 或者说fillfactor参数是怎么计算出来。

德哥

根据vacuum速度决定,如果能在产生10%dead tuple内全部清理掉,那就留10%。 看设置的autovacuum naptime, 以及worker , scale factor

评论