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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL : 慎用 db_link  

2011-10-11 10:41:19|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

           今天由于业务需要,需要在生产库上创建一个 db_link, 每天取源端库一张表一天数据,表大小为2G左右,
考虑到这个数据每天只取一次,而且只取一天数据,于是准备在库上创建 db_link, 但在测试过程中发现性能问题。
在创建完 db_link 后,在库上执行 " select * from view_xxx  limit 1" 时,发现十分钟都没有执行完, 而且
数据库负载很高,达到 20, 同时 IO 等侍也很高,达到 20, 下面在测试环境下模拟下这种情形。


--1 测试环境
主机: 8核 8G

源库     mpc_db_statistic
源库表   mpc_log_register(表大小: 193 M)

测试库   skytf
视图     view_mpc_log_register

   准备在数据库 skytf 创建视图 view_mpc_log_register 取源库 mpc_db_statistic 的数据 mpc_log_register。
  
mpc_log_register 表结构
                                       Table "mpc_db_statistic.mpc_log_register"
    Column     |              Type              |                              Modifiers                              
---------------+--------------------------------+----------------------------------------------------------------------
 record_id     | integer                        | not null default nextval('mpc_log_register_record_id_seq'::regclass)
 client_ip     | character varying(64)          | not null
 client_type   | integer                        | default 0
 reg_time      | timestamp(0) without time zone |
 reg_account   | character varying(32)          |
 reg_userid    | integer                        | default 0
 update_time   | timestamp(0) without time zone | default now()
 imsi          | character varying              |
 imei          | character varying              |
 manufacturers | character varying              |
 model         | character varying              |
 appid         | integer                        |
 scr_width     | integer                        |
 scr_height    | integer                        |
 sms_center    | character varying              |
 mem_size      | integer                        |
 access_ip     | character varying(32)          |
Indexes:
    "mpc_log_register_pkey" PRIMARY KEY, btree (record_id)
    "log_register_client_id_index" btree (client_ip)  


--2 create view
CREATE OR REPLACE VIEW view_mpc_log_register AS
SELECT *
  FROM dblink('dbname=mpc_db_statistic host=localhost port=1921 user=mpc_db_statistic password=mpc_db_statistic' ,
              'select * from mpc_log_register')
          as t1 (
                  record_id     integer                        ,
                  client_ip     character varying(64)          ,
                  client_type   integer                        ,
                  reg_time      timestamp(0) without time zone ,
                  reg_account   character varying(32)          ,
                  reg_userid    integer                        ,
                  update_time   timestamp(0) without time zone ,
                  imsi          character varying              ,
                  imei          character varying              ,
                  manufacturers character varying              ,
                  model         character varying              ,
                  appid         integer                        ,
                  scr_width     integer                        ,
                  scr_height    integer                        ,
                  sms_center    character varying              ,
                  mem_size      integer                        ,
                  access_ip     character varying(32)         
                );

--3 配置 pg_hba.conf
host  mpc_db_statistic mpc_db_statistic 127.0.0.1/32  md5

 备注:这行需要加到 pg_hba.conf "# IPv4 local connections:"部分第一行,如果不加这行,那么在查询视图时
       可能会报以下ERROR:
                   ERROR:  password is required
                   DETAIL:  Non-superuser cannot connect if the server does not request a password.
                   HINT:  Target servers authentication method must be changed.      


--4 查询视图测试             
skytf=> select record_id,client_ip from view_mpc_log_register limit 1;
NOTICE:  identifier "dbname=mpc_db_statistic host=localhost port=1921 user=mpc_db_statistic password=mpc_db_statistic" will be truncated to "dbname=mpc_db_statistic host=localhost port=1921 user=mpc_db_st"
 record_id |      client_ip     
-----------+---------------------
    235215 | 117.136.25.45:30119
(1 row)

Time: 16697.482 ms                                                                                                                                                                                            


--5 查询当前进程
postgres=# select datname,procpid,current_query from pg_stat_activity where current_query !='<IDLE>';
     datname      | procpid |                                       current_query                                       
------------------+---------+--------------------------------------------------------------------------------------------
 skytf            |   29265 | select record_id,client_ip from view_mpc_log_register where record_id=1 limit 1;
 postgres         |   29853 | select datname,procpid,current_query from pg_stat_activity where current_query !='<IDLE>';
 mpc_db_statistic |   29946 | select * from mpc_log_register        
 
 备注:一开始查询,可以看到有两个进程,一个是 skytf 库查询视图进程,另一个是 mpc_db_statistic库
            的全表扫描进程, 奇怪了,进程 29265 明明是根据索引字段 record_id 来查询的,为什么到了目标
            库就是全表扫描" select * from mpc_log_register"?
 
 
--6 继续查询当前进程
postgres=# select datname,procpid,current_query from pg_stat_activity where current_query !='<IDLE>';
 datname  | procpid |                                       current_query                                       
----------+---------+--------------------------------------------------------------------------------------------
 skytf    |   29265 | select record_id,client_ip from view_mpc_log_register where record_id=1 limit 1;
 postgres |   29853 | select datname,procpid,current_query from pg_stat_activity where current_query !='<IDLE>';
(2 rows)                                                                                                             

 备注:过一会儿,发现目标库进程  29946 执行完毕,此时还有进程 29265 在跑。


--7 SQL执行过程中内存使用情况

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053        656      15396          0          8        501
-/+ buffers/cache:        146      15906
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053        792      15260          0          8        501
-/+ buffers/cache:        282      15771
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053        849      15203          0          8        501
-/+ buffers/cache:        339      15714
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1012      15041          0          8        501
-/+ buffers/cache:        501      15551
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1126      14926          0          8        501
-/+ buffers/cache:        616      15436
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1166      14886          0          8        501
-/+ buffers/cache:        656      15396
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1209      14843          0          8        501
-/+ buffers/cache:        699      15354
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1318      14734          0          8        514
-/+ buffers/cache:        795      15257
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1495      14558          0          8        546
-/+ buffers/cache:        940      15113
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1585      14467          0          8        563
-/+ buffers/cache:       1014      15039
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1672      14380          0          8        578
-/+ buffers/cache:       1085      14968
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1762      14290          0          8        595
-/+ buffers/cache:       1158      14895
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1858      14194          0          8        614
-/+ buffers/cache:       1235      14817
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       1952      14100          0          8        632
-/+ buffers/cache:       1311      14741
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       2044      14008          0          8        650
-/+ buffers/cache:       1385      14668
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053       2140      13912          0          8        669
-/+ buffers/cache:       1462      14590
Swap:        16386         31      16355

[root@ ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         16053        658      15394          0          8        501
-/+ buffers/cache:        148      15904
Swap:        16386         31      16355  

 备注:发现内存 used 值开始由 656M 一直增加到 2140 M, 当 SQL执行完后,又再次回到 658 M,
             推测SQL通过 db_link 查询远端数据时,会将远端表先取到本地库内存里,这个步骤非常耗时,
             耗IO,同时本地库负载也比异常高。


--8 结果出来了
skytf=> select record_id,client_ip from view_mpc_log_register limit 1;
NOTICE:  identifier "dbname=mpc_db_statistic host=localhost port=1921 user=mpc_db_statistic password=mpc_db_statistic" will be truncated to "dbname=mpc_db_statistic host=localhost port=1921 user=mpc_db_st"
 record_id |      client_ip     
-----------+---------------------
    235215 | 117.136.25.45:30119
(1 row)

Time: 16697.482 ms        

  备注:这张表不太大,只有193M,都花了 16 秒左右。

                           
                                       
--9 执行计划
skytf=> explain select record_id,client_ip from view_mpc_log_register where record_id=1 limit 1;
                              QUERY PLAN                              
-----------------------------------------------------------------------
 Limit  (cost=0.00..2.50 rows=1 width=150)
   ->  Function Scan on dblink t1  (cost=0.00..12.50 rows=5 width=150)
         Filter: (record_id = 1)
(3 rows)                                       
   备注: 走的是函数索引。
  

--10当目标表为2G大时
       由于前面源表大小才 193M,效果不明显,后来拿了张2G大小的表做实验,通过视图取一条数据,后来数据库
负载直接升到40左右,连数据库主机都 SSH 不上了。汗, PostgreSQL 的 db_link 得慎用。                      
                                                                      
                                                                      
--11 总结
     1 PostgreSQL 的 db_link 要慎用,没有特殊的需求不要使用,如果业务需要可以通过其它方式代替;
     2 PostgreSQL 的 db_link 在使用过程中会先把对端表数据全表扫描到本地库,尽管查询视图时用到了
        索引字段,但依然不会走索引扫描,而是走 "Function Scan" ,从而带来严重的性能问题
      (负载高,IO高)。               
                                           

  评论这张
 
阅读(31249)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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