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

PostgreSQL 中文网

 
 
 

日志

 
 

Using CONCURRENTLY without taking any locks when creating index  

2011-02-25 15:20:56|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

         PostgreSQL 创建索引时通常会堵住 DML语句,如果给生产系统的大表加索引,可能加索引的
过程会很长,那么在索引创建过程中,Application 的 insert,delete,update 语句会被 block,
这对应用来说是非常沉痛的。

        幸运的是 PostgreSQL 在创建索引时,提供一个"CONCURRENTLY"选项,创建索引时, 使用这个
选项则可以在线创建索引,同时又不会阻塞应用的 DML 语句,真是太棒了,下面这两段是介绍这个参
数的,来自官网。

--About CONCURRENTLY
      Creating an index can interfere with regular operation of a database. Normally PostgreSQL
locks the table to be indexed against writes and performs the entire index build with a
single scan of the table. Other transactions can still read the table, but if they try to
insert, update, or delete rows in the table they will block until the index build is finished.
This could have a severe effect if the system is a live production database. Very large tables
can take many hours to be indexed, and even for smaller tables, an index build can lock out
writers for periods that are unacceptably long for a production system.

      PostgreSQL supports building indexes without locking out writes. This method is invoked by
specifying the CONCURRENTLY option of CREATE INDEX. When this option is used, PostgreSQL
must perform two scans of the table, and in addition it must wait for all existing transactions
that could potentially use the index to terminate. Thus this method requires more total work
than a standard index build and takes significantly longer to complete. However, since it allows
normal operations to continue while the index is built, this method is useful for adding new
indexes in a production environment. Of course, the extra CPU and I/O load imposed by the
index creation might slow other operations.


--创建索引语法
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

    下面来做下测试,分别测试不带 "CONCURRENTLY" 参数和加上这个参数时的场景,具体步骤如下。
   
   
1场景一:创建索引时不带"CONCURRENTLY"参数  
--这里有张测试表,相关信息如下
skytf=> select pg_size_pretty(pg_relation_size('my_table_201102'));
 pg_size_pretty
----------------
 1436 MB
(1 row)

skytf=> \d my_table_201102
           Table "skytf.my_table_201102"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 create_time | timestamp without time zone | not null
 log_time    | timestamp without time zone |
 user_id     | character varying(32)       |
 action      | character varying(128)      |
 app_id      | character varying(32)       |
 result      | numeric(10,0)               |
 server_id   | character varying(32)       |
 arg1        | character varying(128)      |
 arg2        | character varying(128)      |
 arg3        | character varying(128)      |
 arg4        | character varying(128)      |
 username    | character varying(64)       |
 clientip    | character varying(32)       |
Indexes:
    "idx_my_table_201102_create_time" btree (create_time)
Inherits: my_table

--session一: 执行创建索引语句
skytf=> create index idx_my_table_201102_action on my_table_201102 using btree (action);

  备注:这张表有1G多, 这个SESION正在执行,还没跑会。

--session二:向表中插入记录
skytf=> insert into my_table_201102 (create_time,action) values (now(),'test');
  
   此时发现 session二正处于等侍状态,说明 insert 操作已经被 session一阻塞了。
  
1场景二:创建索引时 加上"CONCURRENTLY"参数 ,看下会是什么情况  
--先删除之前创建的索引
skytf=> drop index idx_my_table_201102_action;
DROP INDEX 

--session一: 执行创建索引操作
skytf=> create index  CONCURRENTLY idx_my_table_201102_action on my_table_201102 using btree (action);

   备注:表有1G多, 这个SESION正在执行,还没跑会
  
--session二: 向这个表中插入记录
skytf=> insert into my_table_201102 (create_time,action) values (now(),'test_b');
INSERT 0 1   

  备注,当session一还在创建索引的过程中,立即新开一个 session二向些表中插入记录,发现
        插入动作可以立即执行下去,说明创建索引时带参数 "CONCURRENTLY"不会阻塞 DML语句。
        (DELETE 操作就没测试了,有兴趣的朋友可以测试下。) 
 
--比较两种方法所花费的时间

--不带 "CONCURRENTLY" 参数
skytf=> create index idx_my_table_201102_action on my_table_201102 using btree (action);
CREATE INDEX
Time: 46094.907 ms
       
--带 "CONCURRENTLY" 参数
skytf=> create index  CONCURRENTLY idx_my_table_201102_action on my_table_201102 using btree (action);
CREATE INDEX
Time: 55521.221 ms            

   由于带"CONCURRENTLY" 创建索引时需要做更多的维护工作,所以耗时稍微长些。
  
--Reindex 用法
skytf=> \h reindex
Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

     备注:但 Reindex 命令却不提供 "CONCURRENTLY" 参数。
  
  
总结  1 生产系统给表加索引时,建议使用"CONCURRENTLY"属性,对生产产生最小影响。
         2 在使用"CONCURRENTLY"属性时,由于 PG需要做更多的内部操作,所以耗时稍微长些。
         3 在使用"CONCURRENTLY"属性时,如果创建索引途中因为 " uniqueness violation "
            或者会话中断等原因创建失败,则这个索引需要删除,再重新创建。
        4 Reindex 命令不提供 "CONCURRENTLY" 参数,生产系统在执行 Reindex 时还需谨慎。

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

历史上的今天

评论

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

页脚

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