生產環境修改PostgreSQL表索引對應的資料表空間

標籤：

通過iostat命令發現某塊磁碟的io使用率經常保持在100%，通過blkid命令擷取linux raid儲存盤符和掛載點的關係後，最後發現是掛載點上的一個資料庫資料表空間在佔用大io。

現象

[email protected]:~$ iostat -xm 3 |grep -v dmavg-cpu:  %user   %nice %system %iowait  %steal   %idle          11.68    0.00    3.82    8.63    0.00   75.87Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %utilsda               0.00     0.69    0.29    1.54     0.00     0.01    18.01     0.00    1.45    4.34    0.91   0.57   0.10sdb               0.00     0.77    3.51    2.63     0.42     0.57   329.19     0.03    4.23    0.61    9.07   0.52   0.32sdc               0.00    12.98   31.28  283.84     1.17     5.46    43.07     0.10    2.88   21.27    0.85   0.57  18.00sdd               0.00     0.08    0.01    0.95     0.00     0.42   889.72     0.34  358.73   65.53  361.07   4.14   0.40sde               0.42    13.04   58.26  766.30     1.60     6.63    20.45     0.71    0.86    4.56    0.58   0.89  73.57sdf               0.11     8.62   56.90  217.50     3.02     2.50    41.15     0.63    2.28   10.76    0.07   0.89  24.46

解決辦法

現在知道個別磁碟io使用率很高，接下來就是需要修改個別表索引的資料表空間到空閑磁碟中。

通過alter index直接移動索引會鎖住其它更新操作，大索引的移動需要很長時間，在生產環境中不可取。可以通過以下方式解決：

1。通過create index concurrently在新的資料表空間重建和原資料表空間定義一樣的索引（名字不同）。

2。刪除原資料表空間的索引。

create index concurrently的介紹可以參考這篇文章：http://my.oschina.net/Kenyon/blog/93465

實際操作

下面是原來一個表的索引詳情，需要把除了主鍵外在indextbs上的索引移動到預設資料表空間。

Indexes:
"article_111_pkey" PRIMARY KEY, btree (aid), tablespace "indextbs"
"article_111_url_hash" UNIQUE CONSTRAINT, btree (url_hash), tablespace "indextbs"
"article_111_bid_titlehash_idx" btree (bid, title_hash), tablespace "indextbs"
......

1、移動article_111_bid_titlehash_idx索引

CREATE INDEX CONCURRENTLY article_111_bid_title_hash_idx ON article_111 USING btree (bid, title_hash COLLATE pg_catalog."default") TABLESPACE pg_default ;drop index article_111_bid_titlehash_idx ;

2、移動article_111_url_hash索引

這個索引有一個唯一性限制式，和前面方法有些區別。

CREATE UNIQUE INDEX CONCURRENTLY article_111_urlhash_idx ON article_111 USING btree (url_hash) ;alter table article_111 drop constraint article_111_url_hash,add unique using index article_111_urlhash_idx ;

 

參考網址：

http://www.sijitao.net/1823.html

http://www.postgresql.org/docs/9.1/static/sql-altertable.html

生產環境修改PostgreSQL表索引對應的資料表空間