注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

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

2011-10-21 10:20:03|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

    

       今天思考了下 PostgreSQL 的 MVCC原理,PG在 delete 记录时, 不会立刻在物理上删除记录,而将
原始记录保留在原来 page, 只是改变下状态位;而 update 记录时,会先复制一份新的记录,并在复制的这
份上修改;想到:这种机制在大量数据更新后,是否会影响查询性能? 下面实验模拟下,模拟两个场景:
1 大量数据更新后,全表扫描的性能是否有变化?
2 大量数据更新后,走索引的查询SQL性能是否有变化?
为了准确的测试,在测试前将 PostgreSQL 的 autovacuum 参数设置成 off 。

 

场景一:测试全表扫描
--1 创建测试表并插入数据
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
 
--2 表分析
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。

--3 执行计划
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秒左右;
 
--4 全表更新
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。

--5 再次 EXPLAIN
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扫描的块更多,耗时也增加了。

--6 vacuum
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

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 索引查询
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。

--2 批量更新 function
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 反复更新同一记录;

--3 执行更新
skytf=> select fun_update_test_59() ;
 fun_update_test_59
--------------------
                  1
(1 row)

Time: 2191.491 ms  


--4 再次查看PLAN
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, 说明数据更新后,对走索引的扫描
              语句的性能也有一定影响;
     
--5 再次explain analyze         
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 不会降低,性能不会有太大影响;    

  评论这张
 
阅读(26919)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017