圈子推荐
查看所有吧>>
活跃用户
    PostgreSQL分区方案测试(二)

    2.2.2 hash分区

    建立表:


    drop table if exists shiptrack cascade;

    create table shiptrack(

        id int not null, 

        pos_time timestamp without time zone not null, 

        lon numeric not null,  

        lat numeric not null 

    )  partition by hash(id);

    create index shiptrack_id_idx on shiptrack using btree(id);

    create index shiptrack_time_idx on shiptrack using btree(pos_time);

    创建分区表,分区数量4:


    do language plpgsql $$  

     DECLARE

        i int;

        sql text;

            tableCount int;

     BEGIN

            tableCount :=4;

        for i in 1..tableCount loop

            sql:=format('create table if not exists shiptrack_%s PARTITION OF shiptrack for VALUES WITH (MODULUS %s, REMAINDER %s)',i,tableCount,i-1);

            execute sql;

            raise notice '%',sql;

        end loop;

    end;  

    $$;

    测试脚本 test.sql:


    set _id random(1,700000)

    set x random(-180,180)

    set y random(-90,90)

    INSERT INTO shiptrack(id,pos_time,lon ,lat) values(:_id,clock_timestamp(),:x, :y);

    hash分区数量4,tps:32442

    同理:

    hash分区数量40,tps:20611。

    hash分区数量400,tps: 3167。


    2.2 pg_pathman测试(仅hash分区测试)

    建立表:


    drop table if exists shiptrack cascade;

    create table shiptrack(

        id int not null, 

        pos_time timestamp without time zone not null, 

        lon numeric not null,  

        lat numeric not null 

    ) ;

    create index shiptrack_id_idx on shiptrack using btree(id);

    create index shiptrack_time_idx on shiptrack using btree(pos_time);

    创建分区表,分区数量4:


    select create_hash_partitions('shiptrack'::regclass,'id',4,false); 

    测试脚本 test.sql:


    set _id random(1,700000)

    set x random(-180,180)

    set y random(-90,90)

    INSERT INTO shiptrack(id,pos_time,lon ,lat) values(:_id,clock_timestamp(),:x, :y);

    pg_pathman分区数量4,tps:29948

    同理:

    pg_pathman分区数量40,tps:28267。

    pg_pathman分区数量400,tps: 23283。

    pg_pathman分区数量1000,tps: 17922。


    2.3 citus分布式表分区

    2.3.1 citus--逐条插入

    注意:citus分布式,各个环境与单机环境配置有差异,不太好同上单独比较。

    建立表:


    drop table if exists shiptrack cascade;

    create table shiptrack(

        id int not null, 

        pos_time timestamp without time zone not null, 

        lon numeric not null,  

        lat numeric not null 

    ) ;

    create index shiptrack_id_idx on shiptrack using btree(id);

    create index shiptrack_time_idx on shiptrack using btree(pos_time);

    创建分区表,分区数量4:


    --设置分区数量

    set citus.shard_count=4;

    --设置副本数量

    set citus.shard_replication_factor=2;

    --对表进行分区

    select create_distributed_table('shiptrack','id','hash');

    测试脚本


    set _id random(1,700000)

    set x random(-180,180)

    set y random(-90,90)

    INSERT INTO shiptrack(id,pos_time,lon ,lat) values(:_id,clock_timestamp(),:x, :y);

    citus分区数量4,tps:25342

    同理:

    citus分区数量40,tps:26112。

    citus分区数量400,tps: 26817。

    citus分区数量1000,tps: 26524。

    逐条sql插入性能比较稳定。


    2.3.2 citus--批量插入

    建表和分区同上,只需更新下test.sql脚本为批量操作,单批次数量1000:


    set _id random(1,700000)

    set x random(-180,180)

    set y random(-90,90)

    INSERT INTO shiptrack(id,pos_time,lon ,lat) select 

    :_id,clock_timestamp(),:x, :y from generate_series(1,1000);

    创建表:


    drop table if exists shiptrack cascade;

    create table shiptrack(

        id int not null, 

        pos_time timestamp without time zone not null, 

        lon numeric not null, 

        lat numeric not null 

    ) ;

    create index shiptrack_id_idx on shiptrack using btree(id);

    create index shiptrack_time_idx on shiptrack using btree(pos_time);


    --设置分区数量

    set citus.shard_count=4;

    --执行分区

    select create_distributed_table('shiptrack','id','hash');

    执行压测语句:


    [postgres@~]$ pgbench -M prepared -n -r -P 1 -f test.sql -c 80 -j 80 -T 30 -U postgres mytest

    通过设置set citus.shard_count为4,40,400,450,550,700,1000,重试上述步骤,得到tps依次是:

    citus分区数量4,tps:1563

    同理:

    citus分区数量40,tps:1549。

    citus分区数量400,tps: 1583。

    citus分区数量450,tps: 1115。

    citus分区数量550,tps: 1063。

    citus分区数量700,tps: 895。

    citus分区数量1000,tps: 706。


    三 测试现象与总结

    单表不分区:tps:36662

    数据插入与分区表数量关系测试汇总如下表:


    测试方式 分区数 4 分区数 40 分区数 400 分区数 1000

    pg自带分区 list 34492 21473 3220 未测试

    pg自带分区 hash 32442 20611 3167 未测试

    pg_pathman hash 29948 28267 23283 17922

    citus hash(单条插入) 25342 26112 26817 26524

    citus hash(批量插入) 1563 1549 1583 706

    现象:


    随着分区表数量增加,除了citus都有性能衰减现象。

    pg_pathman衰减比较平滑,pg自带的分区表指数级别衰减。

    pg自带的hash分区,性能稍微弱于自带的list分区。

    citus单条插入性能与分区数量递增关系几乎没有影响。

    citus以1000数据量为一批次,在分区数为400之前稳定,从400之后开始平滑衰减。

    个人总结:


    pg11自带的分区,适用于分区数量不大的业务场景。

    在单机pg分区中,如果分区数量比较大,建议采用pg_pathman,可以获取更好的性能。

    在具备分布式数据节点的前提下,使用citus的分区在插入和查询都能有较好的表现。


    • 分享到:
    排序方式:回复时间 共有0条评论