1. 云图网首页
  2. 技术专区
  3. 数据库

探索：PostgreSQL 的 MVCC 机制对性能的影响

• 数据库

今天思考了下 PostgreSQL 的 MVCC原理，PG在 delete 记录时， 不会立刻在物理上删除记录，而将原始记录保留在原来 page, 只是改变下状态位；而 update 记录时，会先复制一份新的记录，并在复制的这份上修改；想到：这种机制在大量数据更新后，是否会影响查询性能？ 下面实验模拟下，模拟两个场景：

  • 场景一：大量数据更新后，全表扫描的性能是否有变化？
  • 场景二：大量数据更新后，走索引的查询SQL性能是否有变化？

为了准确的测试，在测试前将 PostgreSQL 的 autovacuum 参数设置成 off 。

场景一：测试全表扫描

创建测试表并插入数据

1
2
3
4
5
6
 
skytf=> create table test_59 ( id integer primary key ,name varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_59_pkey" for table "test_59"  
CREATE TABLE

skytf=> insert into test_59 select generate_series(1,10000000),'francs';  
INSERT 0 10000000

表分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
skytf=> /dt+ test_59  
 List of relations  
Schema | Name | Type | Owner | Size | Description  
--------+---------+-------+-------+--------+-------------  
skytf | test_59 | table | skytf | 422 MB |  
(1 row)

skytf=> analyze test_59;  
ANALYZE

skytf=> select relname,relpages,reltuples from pg_class where relname='test_59';  
relname | relpages | reltuples  
---------+----------+-----------  
test_59 | 54055 | 1e+07  
(1 row)

备注：原表大小为 422M, 共有 54055 PAGE。

执行计划

1
2
3
4
5
6
7
8
 
skytf=> explain ( analyze on, buffers on ) select count(*) from test_59;  
 QUERY PLAN  
-----------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=179055.00..179055.01 rows=1 width=0) (actual time=11958.853..11958.854 rows=1 loops=1)  
 Buffers: shared hit=54055  
 -> Seq Scan on test_59 (cost=0.00..154055.00 rows=10000000 width=0) (actual time=0.008..5984.120 rows=10000000 loops=1)  
 Buffers: shared hit=54055  
Total runtime: 11958.889 ms(5 rows)

备注：”shared hit” 值为 54055, 扫描了 54055 PAGE。耗时为 11.9秒左右；

全表更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
skytf=> update test_59 set name='aaa';  
UPDATE 10000000

skytf=> /dt+ test_59  
 List of relations  
Schema | Name | Type | Owner | Size | Description  
--------+---------+-------+-------+--------+-------------  
skytf | test_59 | table | skytf | 768 MB |  
(1 row)

skytf=> analyze test_59;  
ANALYZE

skytf=> select relname,relpages,reltuples from pg_class where relname='test_59';  
relname | relpages | reltuples  
---------+----------+-----------  
test_59 | 98302 | 1e+07  
(1 row)

备注：表表更新后，表大小增加了,为 768M, PAGE也增加了，为 98302。

再次 EXPLAIN

1
2
3
4
5
6
7
8
 
skytf=> explain ( analyze on, buffers on ) select count(*) from test_59;  
 QUERY PLAN  
--------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=223302.00..223302.01 rows=1 width=0) (actual time=14442.960..14442.961 rows=1 loops=1)  
 Buffers: shared hit=71327 read=26975 written=27  
 -> Seq Scan on test_59 (cost=0.00..198302.00 rows=10000000 width=0) (actual time=2452.108..8477.221 rows=10000000 loops=1)  
 Buffers: shared hit=71327 read=26975 written=27  
Total runtime: 14443.009 ms(5 rows)

备注：再次观察PLAN，发现扫描的 Buffers 总数为 “71327+26975=98302” , 耗时也增加了，为 14.4 秒左右, 可见大批量数据修改后，全表扫描的SQL扫描的块更多，耗时也增加了。

vacuum

1
2
3
4
5
6
7
8
9
10
 
skytf=> vacuum test_59;  
VACUUM

skytf=> /dt+ test_59  
 List of relations  
Schema | Name | Type | Owner | Size | Description  
--------+---------+-------+-------+--------+-------------  
skytf | test_59 | table | skytf | 769 MB |  
  
Time: 24472.248 ms

查看执行计划，如下：

1
2
3
4
5
6
7
8
9
 
skytf=> explain ( analyze on, buffers on ) select count(*) from test_59;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=223410.00..223410.01 rows=1 width=0) (actual time=11867.036..11867.037 rows=1 loops=1)  
 Buffers: shared hit=85158 read=13252  
 -> Seq Scan on test_59 (cost=0.00..198410.00 rows=10000000 width=0) (actual time=61.639..5903.459 rows=10000000 loops=1)  
 Buffers: shared hit=85158 read=13252  
Total runtime: 11867.089 ms  
(5 rows)

备注：vaccum后，表大小没变，全表扫描扫描的PAGE数也没变；

场景二：测试走索引的查询

索引查询

1
2
3
4
5
6
7
 
skytf=> explain ( analyze on, buffers on ) select * from test_59 where id=1;  
 QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------  
Index Scan using test_59_pkey on test_59 (cost=0.00..5.91 rows=1 width=8) (actual time=0.017..0.020 rows=1 loops=1)  
 Index Cond: (id = 1)  
 Buffers: shared hit=6  
Total runtime: 0.048 ms(4 rows)

备注：索引索引只扫描了 6个 page。

批量更新 function

1
2
3
4
5
6
7
8
9
10
 
CREATE or replace FUNCTION fun_update_test_59() RETURNS INTEGER AS $$  
 DECLARE  
 i INTEGER ;  
 BEGIN  
 for i in 1..10000 loop  
 update test_59 set name='test' where id=1;  
 end loop;  
 return 1;  
 END;  
$$ LANGUAGE 'plpgsql';

备注：计划只更新 id=1 的这条记录，这里用了一个 function 反复更新同一记录；

执行批量更新函数，如下。

1
2
3
4
5
6
 
skytf=> select fun_update_test_59() ;  
fun_update_test_59  
--------------------  
 1  
(1 row)
Time: 2191.491 ms

再次查看PLAN

1
2
3
4
5
6
7
8
 
skytf=> explain ( analyze on, buffers on ) select * from test_59 where id=1;  
 QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------  
Index Scan using test_59_pkey on test_59 (cost=0.00..5.91 rows=1 width=8) (actual time=0.036..1.555 rows=1 loops=1)  
 Index Cond: (id = 1)  
 Buffers: shared hit=59  
Total runtime: 1.579 ms  
(4 rows)

备注：id=1 的这条记录被更新 10000 次以后，再次索引扫描却扫描了 59 个 page， 说明数据更新后，对走索引的扫描语句的性能也有一定影响；

再次执行 explain analyze，如下：

1
2
3
4
5
6
7
8
 
skytf=> explain ( analyze on, buffers on ) select * from test_59 where id=1;  
 QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------  
Index Scan using test_59_pkey on test_59 (cost=0.00..5.91 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1)  
 Index Cond: (id = 1)  
 Buffers: shared hit=4  
Total runtime: 0.049 ms  
(4 rows)

备注：再次”explain analyze” 后，发现扫描的块又回到了 4, 这是因为再次查询后，需要的数据都在内存里。

总结

  1. PG大批量 UPDATE 数据后，全表扫描SQL，索引扫描SQL将扫描更多的块，性能会降低。
  2. 可以推测PG大批量 DELETE 数据后 , 全表扫描SQL，索引扫描SQL扫描块不变，性能不
    会有太大影响。
  3. 对于ORACLE，根据以前的经验，UPDATE 大量数据后，由于更新的数据不在原表 PAGE保存，而保存
    在UNDO 表空间，性能不会有太大影响。
  4. 对于ORACLE，根据以前的经验，DELETE大量数据后, 由于表的 HWM 不会降低，性能不会有太大影响。

原创文章，作者：745907710，如若转载，请注明出处：https://blog.ytso.com/236435.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论