亚洲中字慕日产2020,大陆极品少妇内射AAAAAA,无码av大香线蕉伊人久久,久久精品国产亚洲av麻豆网站

資訊專欄INFORMATION COLUMN

PG不能自動創(chuàng)建分區(qū)，那是因為裝備不夠

IT那活兒 / 3654人閱讀
PG不能自動創(chuàng)建分區(qū)，那是因為裝備不夠
[
前言
]


PostgreSQL傳統(tǒng)的分區(qū)方法，使用約束來區(qū)分不同的分區(qū)存儲數(shù)據(jù)（配置constraint_exclusion = partition），執(zhí)行選擇/刪除/更新時執(zhí)行計劃根據(jù)約束和查詢條件排除不需要的查詢的分區(qū)表。調(diào)用COPY或插入數(shù)據(jù)時使用插入或規(guī)則，將數(shù)據(jù)插入對應(yīng)的分區(qū)表。


傳統(tǒng)的做法，無論是查詢還是插入，對性能的影響都較長。pg_pathman與傳統(tǒng)的繼承分區(qū)表做法有一個不同的地方，分區(qū)的定義放置在一張元數(shù)據(jù)表中，表的信息會緩存在內(nèi)存中，同時使用HOOK來實現(xiàn)關(guān)系的替換，所以效率非常高。目前支持兩種分區(qū)模式，范圍和哈希，其中范圍使用二進(jìn)制搜索查找對應(yīng)的分區(qū)，哈希使用哈希搜索查找對應(yīng)的分區(qū)。


[
pg_pathman裝備介紹
]


一、pg_pathman用到的hook如下

1. pg_pathman使用ProcessUtility_hook鉤子來處理分區(qū)表的COPY查詢。

2. RuntimeAppend（重寫Append計劃節(jié)點）

3. RuntimeMergeAppend（重寫MergeAppend計劃節(jié)點）

4. PartitionFilter（INSERT觸發(fā)器的直接替代）


二、pg_pathman特性

1.目前支持range，hash分區(qū)。

2.支持自動分區(qū)管理（通過函數(shù)接口創(chuàng)建分區(qū)，自動將主表數(shù)據(jù)遷移到分區(qū)表），或手工分區(qū)管理（通過函數(shù)實現(xiàn)，將現(xiàn)有的表綁定到分區(qū)表，或者從分區(qū)表分割） 。

3.支持的分區(qū)分區(qū)類型包括int，float，date，以及其他常用類型，包括自定義的域。

4.通過CUSTOM SCAN實現(xiàn)了有效的分區(qū)表JOIN，子查詢過濾分區(qū)。

5.使用RuntimeAppend和RuntimeMergeAppend自定義計劃節(jié)點實現(xiàn)了動態(tài)分區(qū)選擇。

6. PartitionFilter HOOK，實現(xiàn)就地插入，代替?zhèn)鹘y(tǒng)的插入觸發(fā)器或插入規(guī)則。

7.支持自動添加分區(qū)。目前僅支持范圍分區(qū)表。

8.支持從/到直接讀取或?qū)懭敕謪^(qū)表的復(fù)制，提高效率。

9.支持分區(qū)分區(qū)的更新，需要添加替換，如果不需要更新分區(qū)分區(qū)，則不建議添加此轉(zhuǎn)換器，會產(chǎn)生一定的性能影響。

10.允許用戶自定義定義函數(shù)，在創(chuàng)建分區(qū)時會自動觸發(fā)。

11.非插入式創(chuàng)建分區(qū)表，以及后臺自動將主表數(shù)據(jù)遷移到分區(qū)表，非插入式。

12.支持FDW，通過配置參數(shù)pg_pathman.insert_into_fdw =（禁用| postgres | any_fdw）支持postgres_fdw或任意fdw(外部分區(qū))

13.支持GUC參數(shù)配置，注意通過使用了HOOK，如果其他插件也使用了相同的HOOK，需要將pg_pathman放在后面注冊，如pg_stat_statements。

shared_preload_libraries =pg_stat_statements，pg_pathman


三、pg_pathman為什么高效

插入優(yōu)化，使用PartitionFilter替換關(guān)系，替換初始化的方式。效率提高非常明顯。

查詢優(yōu)化，分區(qū)定義加載在內(nèi)存中，使用二進(jìn)制搜索和哈希搜索對應(yīng)范圍與哈希分區(qū)表，使用RuntimeAppend和RuntimeMerge附加自定義計劃節(jié)點以在運行時選擇分區(qū)；

同時運行時過濾，支持子查詢。傳統(tǒng)的約束法不支持子查詢過濾。


[
pg_pathman裝備使用
]

1、安裝部署

--下載安裝包

https://github.com/postgrespro/pg_pathman

--pg_pathman安裝

unzip pg_pathman-master.zip

cd pg_pathman-master

make USE_PGXS=1

make USE_PGXS=1 install

--修改參數(shù)

alter system set shared_preload_libraries=pg_stat_statements,pg_pathman,telepg_monitor;

注意pg_pathman需寫在pg_stat_statements之后

--重啟實例

$pg_ctl restart -m fast

--創(chuàng)建pg_pathman擴展

c ksl

postgres=# create extension pg_pathman;

CREATE EXTENSION


ksl=# dx

                                                    List of installed extensions

    Name     | Version |   Schema   |                                          Description                                          

-------------+---------+------------+-------------------------------------------------------

 pageinspect | 1.7 | public | inspect the contents of database pages at a low level

 pg_pathman  | 1.5 | public | Partitioning tool for PostgreSQL

   plpgsql | 1.0  | pg_catalog | PL/pgSQL procedural language

(3 rows)


二、RANGE分區(qū)實戰(zhàn)舉例

創(chuàng)建需要分區(qū)的主表

postgres=# create table part_test(id int, info text, crt_time timestamp not null);  -- 分區(qū)列必須有not null約束  

CREATE TABLE



插入一批測試數(shù)據(jù)，模擬已經(jīng)有數(shù)據(jù)了的主表

postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id|| hour)::interval from generate_series(1,10000) t(id); 

INSERT 0 10000

postgres=# select * from part_test limit 10;                    

 id |               info               |          crt_time          

----+----------------------------------+----------------------------

  1 | 36fe1adedaa5b848caec4941f87d443a | 2016-10-25 10:27:13.206713

  2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893

  3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904

  4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691

  5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916

  6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921

  7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693

  8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936

  9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942

 10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947

(10 rows)



注意:  

1. 分區(qū)列必須有not null約束  

2. 分區(qū)個數(shù)必須能覆蓋已有的所有記錄  


創(chuàng)建分區(qū)，每個分區(qū)包含1個月的跨度數(shù)據(jù)

postgres=#select create_range_partitions(part_test::regclass, -- 主表OID

 crt_time, -- 分區(qū)列名

 2020-11-05 00:00:00::timestamp,  -- 開始值

 interval 1 month,    -- 間隔；interval 類型，用于時間分區(qū)表

 24,            -- 分多少個區(qū)

 false) ;             -- 不遷移數(shù)據(jù)

NOTICE:  sequence "part_test_seq" does not exist, skipping

 create_range_partitions 

-------------------------

     24

(1 row)


由于不遷移數(shù)據(jù)，所以數(shù)據(jù)還在主表

postgres=# select count(*) from only part_test;

 count 

-------

 10000

(1 row)



使用非堵塞式的遷移接口

postgres=# select partition_table_concurrently(part_test::regclass,

                             10000,

                             1.0);

NOTICE:  worker started, you can stop it with the following command: select stop_concurrent_part_task(part_test);

 partition_table_concurrently 

------------------------------


(1 row)

  

--查看后臺的數(shù)據(jù)遷移任務(wù)

select * from pathman_concurrent_part_tasks;


遷移結(jié)束后，主表數(shù)據(jù)已經(jīng)沒有了，全部在分區(qū)中

postgres=# select count(*) from only part_test;

 count 

-------

     0

(1 row)


數(shù)據(jù)遷移完成后，建議禁用主表，這樣執(zhí)行計劃就不會出現(xiàn)主表了

postgres=# select set_enable_parent(part_test::regclass, false);

 set_enable_parent 

-------------------

(1 row)

postgres=# explain select * from part_test where crt_time = 2020-11-05 00:00:00::timestamp;

              QUERY PLAN                                  

-------------------------------------------------------------------------------

 Append  (cost=0.00..16.18 rows=1 width=45)

   ->  Seq Scan on part_test_1  (cost=0.00..16.18 rows=1 width=45)

         Filter: (crt_time = 2020-11-05 00:00:00::timestamp without time zone)

(3 rows)


建議

1. 分區(qū)列必須有not null約束

2. 分區(qū)個數(shù)必須能覆蓋已有的所有記錄

3. 建議使用非堵塞式遷移接口

4. 建議數(shù)據(jù)遷移完成后，禁用主表


1700w數(shù)據(jù)大概遷移了一個多小時，如果表有索引可以先刪除索引，數(shù)據(jù)遷移完成后再建索引，因為在創(chuàng)建分區(qū)的時候，所有的分區(qū)表都會多帶帶創(chuàng)建索引，這也是不能保證全局唯一的原因。

--禁止自動擴展分區(qū)

select set_auto(part_test::regclass, false);

insert into ksl.part_test values (1,test,2023-01-01::timestamp);


--向后添加分區(qū)

select append_range_partition(part_test::regclass);


--向前添加分區(qū)

select prepend_range_partition(part_test::regclass);


--停止遷移任務(wù)

select top_concurrent_part_task(part_test::regclass);


-- 查看后臺的數(shù)據(jù)遷移任務(wù)

select * from pathman_concurrent_part_tasks;


-- 分裂范圍分區(qū),數(shù)據(jù)會自動遷移到另一個分區(qū)

select split_range_partition(

part_test_1::regclass,     -- 分區(qū)oid

      2020-11-25 00:00:00::timestamp,     -- 分裂值

      part_test_1_2);        -- 分區(qū)表名


--合并范圍分區(qū),目前僅支持范圍分區(qū)

--指定兩個需要合并分區(qū)，必須為相鄰分區(qū)

select merge_range_partitions(part_test_1::regclass, part_test_1_2::regclass) ;


--不相鄰的分區(qū)合并會報錯

select merge_range_partitions(part_test_2::regclass, part_test_12::regclass) ;

ERROR:  partitions "part_test_2" and "part_test_12" are not adjacent


--合并后，會刪掉其中一個分區(qū)表

--刪除單個范圍分區(qū)

drop_range_partition(

partition TEXT,   -- 分區(qū)名稱

        delete_data BOOLEAN DEFAULT TRUE)  -- 是否刪除分區(qū)數(shù)據(jù)，如果false，表示分區(qū)數(shù)據(jù)遷移到主表。

  

--刪除分區(qū)， 數(shù)據(jù)遷移到主表

select drop_range_partition(part_test_1,false);

  

--刪除分區(qū)，分區(qū)數(shù)據(jù)也刪除，不遷移到主表

select drop_range_partition(part_test_3,true);

  

--刪除所有分區(qū)，并且指定是否要將數(shù)據(jù)遷移到主表

drop_partitions(parent REGCLASS,delete_data BOOLEAN DEFAULT FALSE)


-- 刪除所有分區(qū)表，并將數(shù)據(jù)遷移到主表

select drop_partitions(part_test::regclass, false);


--綁定分區(qū)(已有的表加入分區(qū)表)

--將已有的表，綁定到已有的某個分區(qū)主表。

--已有的表與主表要保持一致的結(jié)構(gòu)，包括dropped columns。 (查看pg_attribute的一致性)

--如果設(shè)置了回調(diào)函數(shù)，會觸發(fā)。

--綁定分區(qū)時，自動創(chuàng)建繼承關(guān)系，自動創(chuàng)建約束

attach_range_partition(

relation REGCLASS,    -- 主表OID

          partition REGCLASS,    -- 分區(qū)表OID

          start_value ANYELEMENT,  -- 起始值

          end_value   ANYELEMENT)  -- 結(jié)束值

create table part_test_1 (like part_test including all);

select attach_range_partition(part_test::regclass, part_test_1::regclass, 2020-11-05 00:00:00::timestamp, 2020-12-05 00:00:00::timestamp);


--解綁分區(qū)(將分區(qū)變成普通表)

--將分區(qū)從主表的繼承關(guān)系中刪除, 不刪數(shù)據(jù)，刪除繼承關(guān)系，刪除約束

detach_range_partition(partition REGCLASS)  -- 指定分區(qū)名，轉(zhuǎn)換為普通表  

select detach_range_partition(part_test_2);


-- 更新觸發(fā)器

--如果分區(qū)字段要被更新，需要創(chuàng)建更新觸發(fā)器，否則不需要。

create_range_update_trigger(parent REGCLASS)

ksl=> select * from part_test_3 limit 10;

  id  |               info               |          crt_time          

------+----------------------------------+----------------------------

 1450 | d16ae9fa14aabb821df6692beef610e6 | 2021-01-05 00:33:46.657077

 1451 | b88247d2cb9acb9e98ba472f575f180c | 2021-01-05 01:33:46.657081

 1452 | 344c48262f105e8622099b24d9ed7d8a | 2021-01-05 02:33:46.657086

 1453 | bd6e36744447ab70a1624134de9dbde0 | 2021-01-05 03:33:46.65709

 1454 | 3d8c3470df5dcbb1e5ad68974fabf11a | 2021-01-05 04:33:46.657094

 1455 | 71664d8dcdad66ef2ccd0464cc61279b | 2021-01-05 05:33:46.657098

 1456 | 7f0da1bec230ad34741081a5da79b995 | 2021-01-05 06:33:46.657102

 1457 | 34045bcda2117d5643a54c29febd51b6 | 2021-01-05 07:33:46.657107

 1458 | 103a593f0be11898153cf58d5ca576be | 2021-01-05 08:33:46.657111

 1459 | 16e4d2340014ddfeb195c141c0395474 | 2021-01-05 09:33:46.657117

(10 rows)



--創(chuàng)建更新觸發(fā)器前，如果更新分區(qū)字段后的值跨分區(qū)了，會報約束錯誤。

ksl=> update part_test set crt_time=2021-11-05 00:33:46.657077 where id=1450;

ERROR:  new row for relation "part_test_3" violates check constraint "pathman_part_test_3_check"

DETAIL:  Failing row contains (1450, d16ae9fa14aabb821df6692beef610e6, 2021-11-05 00:33:46.657077).


--創(chuàng)建更新觸發(fā)器后，正常

--永久禁止分區(qū)表pg_pathman插件

--可以針對單個分區(qū)主表禁用pg_pathma

select disable_pathman_for(part_test);


禁用pg_pathman后，繼承關(guān)系和約束不會變化，只是pg_pathman不介入custom scan 執(zhí)行計劃。

禁用pg_pathman后的執(zhí)行計劃

postgres=# explain select * from part_test where crt_time=2020-11-25 00:00:00::timestamp;

                QUERY PLAN                                    

-------------------------------------------------------------------------------

 Append  (cost=0.00..16.00 rows=2 width=45)

   ->  Seq Scan on part_test  (cost=0.00..0.00 rows=1 width=45)

         Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

   ->  Seq Scan on part_test_1  (cost=0.00..16.00 rows=1 width=45)

         Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

(5 rows)


disable_pathman_for沒有可逆操作，請慎用。


--全局禁止pg_pathman

與禁用單個分區(qū)主表不同，全局禁止只需要調(diào)整參數(shù)即可，不需要修改pg_pathman的元數(shù)據(jù)，同時它是可逆操作。pg_pathman.enable

$ vi $PGDATA/postgresql.conf

pg_pathman.enable  = off


$ pg_ctl reload


[
總結(jié)
]


1.如果在建初始分區(qū)時，需要設(shè)置分區(qū)表的表空間，可以設(shè)置會話或事務(wù)的參數(shù)

setlocal default_tablespace=tablespacename;

2.disable_pathman_for函數(shù)沒有可逆操作，請慎用。

3.不建議關(guān)閉pg_pathman.enable

4.不建議開啟自動擴展范圍分區(qū)，一個錯誤的分區(qū)值可能導(dǎo)致創(chuàng)建很多分區(qū)。

5.推薦使用set_enable_parent禁用主表。

6.由于pg_pathman使用了customscan接口，所以只支持9.5以及以上版本。

7.傳統(tǒng)哈希分區(qū)需要輸入分區(qū)鍵值的約束條件，才能正確選擇分區(qū)。pg_pathman只要輸入鍵值即可。

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://www.ezyhdfw.cn/yun/130058.html

相關(guān)文章

  • postgresql分區(qū)表

    摘要：創(chuàng)建自動分區(qū)采用兩種方式采用視圖分區(qū)方式采用直接分區(qū)方式創(chuàng)建表創(chuàng)建索引采用視圖分區(qū)方式建立視圖定義分表保證分區(qū)后的可以自增按照時間進(jìn)行分區(qū)分表觸發(fā)器定義更新更新觸發(fā)器直接分區(qū)方式分表觸發(fā)器兩種方式比較視圖分區(qū)所有操作都是對視圖的操 創(chuàng)建自動分區(qū)采用兩種方式 采用視圖分區(qū)方式 采用直接分區(qū)方式 創(chuàng)建表 CREATE TABLE IF NOT EXISTS public.sales...

    EdwardUp 評論0 收藏0

  • PostgreSQL 自動分區(qū)分表維護(hù)管理插件 pathman 基礎(chǔ)使用

    摘要：使用數(shù)據(jù)庫會自動的根據(jù)從某幾個片中讀取數(shù)據(jù)。更加詳細(xì)的請參考德哥文章 官方地址：https://github.com/postgrespr...關(guān)于pathman的原理和優(yōu)化問題，請移步至https://yq.aliyun.com/article... 檢查環(huán)境變量如果直接執(zhí)行psql命令提示command not found則執(zhí)行下面的命令設(shè)置環(huán)境變量 root@host# PA...

    MASAILA 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<