Production environment modifies table space for PostgreSQL table indexes

Using the Iostat command to find that the IO usage of a disk is often maintained at 100%, after acquiring the relationship between the Linux RAID storage drive letter and the mount point through the Blkid command, it is finally discovered that a database tablespace on the mount point occupies large io.

Phenomenon

[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

Solutions

Now that the individual disk IO usage is high, the next step is to modify the table space on the individual table indexes to the free disk.

Moving an index directly through ALTER index locks other update operations, and large index movements take a long time to be inaccessible in a production environment. can be resolved in the following ways:

1. Indexed by CREATE index concurrently in the new tablespace reconstruction and the original table space definition (different names).

2. Deletes the index of the original table space.

Create index concurrently can refer to this article: http://my.oschina.net/Kenyon/blog/93465

Actual operation

The following is the index details of the original table, which need to be moved to the default tablespace in addition to the index on the primary key external 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. Mobile Article_111_bid_titlehash_idx Index

CREATE INDEX concurrently article_111_bid_title_hash_idx on article_111 USING btree (bid, Title_hash COLLATE pg_catalog. " Default ") tablespace pg_default;d ROP index ARTICLE_111_BID_TITLEHASH_IDX;

2. Mobile Article_111_url_hash Index

This index has a uniqueness constraint, which is somewhat different from the previous method.

CREATE UNIQUE INDEX concurrently article_111_urlhash_idx on article_111 USING btree (url_hash); ALTER TABLE article_111 DR OP constraint article_111_url_hash,add unique using index ARTICLE_111_URLHASH_IDX;

Reference URL:

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

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

Production environment modifies table space for PostgreSQL table indexes