PostgreSQL 批量导入性能 (采用dblink 异步调用)-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

PostgreSQL 批量导入性能 (采用dblink 异步调用)

简介:

标签

PostgreSQL , unlogged table , 批量 , dblink


背景

批量导入数据,怎样能挖掘出系统的比较极限的潜能?

瓶颈通常在哪里?

1、WAL lock

2、INDEX lock

3、EXTEND LOCK

4、autovacuum 干扰

因此最好的方法就是排除以上问题,例如

1、使用多表,解决单表EXTEND LOCK问题

2、使用unlogged table(异常时会丢失数据,切记仅限场景使用)多表,解决WAL LOCK问题

3、不使用索引,解决INDEX LOCK问题

4、导入时不使用autovacuum,解决autovacuum干扰问题

基本上可以挖掘出机器的最大潜力。

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

单表测试

1、创建测试表

postgres=# create unlogged table ut(c1 int8) with (autovacuum_enabled=off, toast.autovacuum_enabled=off);  
CREATE TABLE  
Time: 12.723 ms  

2、生成1亿数据

postgres=# insert into ut select generate_series(1,100000000);  
INSERT 0 100000000  
Time: 43378.465 ms (00:43.378)  
  
postgres=# copy ut to '/data01/pg/ut.csv';  
COPY 100000000  
Time: 20292.684 ms (00:20.293)  
# ll -ht /data01/pg/ut.csv   
-rw-r--r-- 1 digoal digoal 848M Apr 27 22:02 /data01/pg/ut.csv  

3、创建插件

create extension dblink;  

4、创建重复建立连接不报错的函数

create or replace function dis_conn(name) returns void as $$      
declare      
begin      
  perform dblink_disconnect($1);      
  return;      
exception when others then      
  return;      
end;      
$$ language plpgsql strict;      
  
create or replace function conn(          
  name,   -- dblink名字          
  text    -- 连接串,URL          
) returns void as $$            
declare            
begin            
  perform dis_conn($1);  
  perform dblink_connect($1, $2);           
  return;            
exception when others then            
  return;            
end;            
$$ language plpgsql strict;     

5、创建测试函数,调用dblink,56个并发同时写单个表

create or replace function get_res() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');           
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('copy ut from %L', '/data01/pg/ut.csv'));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;    

6、测试

\timing  
  
select * from get_res() as t(id int);  

7、观测锁等待,都在单表的扩展BLOCK上。

postgres=# select wait_event_type, wait_event, count(*) from pg_stat_activity where wait_event is not null group by 1,2;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 Activity        | WalWriterMain       |     1  
 Lock            | extend              |    55  
 Activity        | LogicalLauncherMain |     1  
 Activity        | BgWriterMain        |     1  
 Activity        | CheckpointerMain    |     1  
 Activity        | AutoVacuumMain      |     1  
 IO              | DataFileWrite       |     1  
(7 rows)  

src/include/storage/lock.h

/*  
 * LOCKTAG is the key information needed to look up a LOCK item in the  
 * lock hashtable.  A LOCKTAG value uniquely identifies a lockable object.  
 *  
 * The LockTagType enum defines the different kinds of objects we can lock.  
 * We can handle up to 256 different LockTagTypes.  
 */  
typedef enum LockTagType  
{  
        LOCKTAG_RELATION,                       /* whole relation */  
        /* ID info for a relation is DB OID + REL OID; DB OID = 0 if shared */  
        LOCKTAG_RELATION_EXTEND,        /* the right to extend a relation */  
        /* same ID info as RELATION */  
        LOCKTAG_PAGE,                           /* one page of a relation */  
        /* ID info for a page is RELATION info + BlockNumber */  
        LOCKTAG_TUPLE,                          /* one physical tuple */  
        /* ID info for a tuple is PAGE info + OffsetNumber */  
        LOCKTAG_TRANSACTION,            /* transaction (for waiting for xact done) */  
        /* ID info for a transaction is its TransactionId */  
        LOCKTAG_VIRTUALTRANSACTION, /* virtual transaction (ditto) */  
        /* ID info for a virtual transaction is its VirtualTransactionId */  
        LOCKTAG_SPECULATIVE_TOKEN,      /* speculative insertion Xid and token */  
        /* ID info for a transaction is its TransactionId */  
        LOCKTAG_OBJECT,                         /* non-relation database object */  
        /* ID info for an object is DB OID + CLASS OID + OBJECT OID + SUBID */  
  
        /*  
         * Note: object ID has same representation as in pg_depend and  
         * pg_description, but notice that we are constraining SUBID to 16 bits.  
         * Also, we use DB OID = 0 for shared objects such as tablespaces.  
         */  
        LOCKTAG_USERLOCK,                       /* reserved for old contrib/userlock code */  
        LOCKTAG_ADVISORY                        /* advisory user locks */  
} LockTagType;  

8、观察 iotop,写入速度625MB/s

Total DISK READ :      15.46 K/s | Total DISK WRITE :     625.28 M/s  
Actual DISK READ:      15.46 K/s | Actual DISK WRITE:     620.88 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
12574 be/4 digoal      7.73 K/s   30.95 M/s  0.00 % 14.23 % postgres: postgres postgres 127.0.0.1(58722) COPY          
16198 be/4 postgres    0.00 B/s   61.84 K/s  0.00 %  4.55 % postgres: stats collector process  
12524 be/4 root        0.00 B/s    0.00 B/s  0.00 %  4.36 % [kworker/u112:2]  
12590 be/4 digoal      3.87 K/s   36.13 M/s  0.00 %  4.05 % postgres: postgres postgres 127.0.0.1(58754) COPY          
12579 be/4 digoal      3.87 K/s   52.75 M/s  0.00 %  3.24 % postgres: postgres postgres 127.0.0.1(58732) COPY          
27166 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.02 % [kworker/39:2]  
12589 be/4 digoal      0.00 B/s   30.92 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58752) COPY waiting  
12569 be/4 digoal      0.00 B/s   56.44 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58712) COPY waiting  
12607 be/4 digoal      0.00 B/s   51.55 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58788) COPY waiting  
12556 be/4 digoal      0.00 B/s   20.59 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58686) COPY waiting  
12610 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58794) COPY waiting  
12586 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58746) COPY waiting  
12576 be/4 digoal      0.00 B/s   26.20 M/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58726) COPY waiting  
12601 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58776) COPY waiting  
12591 be/4 digoal      0.00 B/s   61.84 K/s  0.00 %  0.01 % postgres: postgres postgres 127.0.0.1(58756) COPY waiting  
12593 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58760) COPY waiting  
12603 be/4 digoal      0.00 B/s   30.92 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58780) COPY waiting  
12604 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58782) COPY waiting  
12605 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58784) COPY waiting  
12600 be/4 digoal      0.00 B/s   27.90 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58774) COPY waiting  
12562 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58698) COPY waiting  
12561 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58696) COPY waiting  
12572 be/4 digoal      0.00 B/s   61.99 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58718) COPY          
12557 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58688) COPY waiting  
12588 be/4 digoal      0.00 B/s   30.95 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58750) COPY waiting  
12585 be/4 digoal      0.00 B/s   26.69 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58744) COPY waiting  
12587 be/4 digoal      0.00 B/s 1298.69 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58748) COPY waiting  
12575 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58724) COPY waiting  
12555 be/4 digoal      0.00 B/s   16.97 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58684) COPY waiting  
12584 be/4 digoal      0.00 B/s   30.98 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58742) COPY waiting  
12578 be/4 digoal      0.00 B/s    9.72 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58730) COPY waiting  
12595 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58764) COPY waiting  
12609 be/4 digoal      0.00 B/s   14.62 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58792) COPY waiting  
12565 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58704) COPY waiting  
12597 be/4 digoal      0.00 B/s    0.00 B/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58768) COPY waiting  
12596 be/4 digoal      0.00 B/s    8.52 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58766) COPY waiting  
12567 be/4 digoal      0.00 B/s   30.95 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58708) COPY waiting  
12568 be/4 digoal      0.00 B/s   76.46 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58710) COPY waiting  
12577 be/4 digoal      0.00 B/s    6.10 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58728) COPY waiting  
12581 be/4 digoal      0.00 B/s   61.84 K/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58736) COPY waiting  
12583 be/4 digoal      0.00 B/s    7.31 M/s  0.00 %  0.00 % postgres: postgres postgres 127.0.0.1(58740) COPY waiting  

多表测试

1、创建多表

do language plpgsql $$  
declare  
begin  
  for i in 0..55 loop  
    execute format('drop table if exists ut%s', i);  
    execute format('create unlogged table ut%s (like ut) with (autovacuum_enabled=off, toast.autovacuum_enabled=off)', i);  
  end loop;  
end;  
$$;  

2、修改写入函数,每个异步调用写入一张独立表

create or replace function get_res() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');             
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('copy ut%s from %L', i, '/data01/pg/ut.csv'));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;    

3、测试

\timing  
  
select * from get_res() as t(id int);  
  
Time: 457688.440 ms (07:37.688)  

4、观察锁,变成了数据文件扩展锁,而不是多会话争抢的扩展BLOCK锁。

postgres=# select wait_event_type, wait_event, count(*) from pg_stat_activity where wait_event is not null group by 1,2;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 Activity        | WalWriterMain       |     1  
 Activity        | LogicalLauncherMain |     1  
 IO              | DataFileExtend      |    55  
 Activity        | BgWriterMain        |     1  
 Activity        | CheckpointerMain    |     1  
 Activity        | AutoVacuumMain      |     1  
(6 rows)  

5、观察iotop ,写入速度达到了1098MB/s

Total DISK READ :       3.85 K/s | Total DISK WRITE :    1098.74 M/s  
Actual DISK READ:       3.85 K/s | Actual DISK WRITE:    1096.81 M/s  
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND                                                                                                                                                                         
13584 be/4 digoal      0.00 B/s   18.69 M/s  0.00 % 84.78 % postgres: postgres postgres 127.0.0.1(58926) COPY  
13629 be/4 digoal      3.85 K/s   18.20 M/s  0.00 % 84.68 % postgres: postgres postgres 127.0.0.1(59016) COPY  
13612 be/4 digoal      0.00 B/s   18.26 M/s  0.00 % 84.64 % postgres: postgres postgres 127.0.0.1(58982) COPY  
13622 be/4 digoal      0.00 B/s   18.38 M/s  0.00 % 84.54 % postgres: postgres postgres 127.0.0.1(59002) COPY  
13582 be/4 digoal      0.00 B/s   18.28 M/s  0.00 % 84.51 % postgres: postgres postgres 127.0.0.1(58922) COPY  
13585 be/4 digoal      0.00 B/s   18.08 M/s  0.00 % 84.46 % postgres: postgres postgres 127.0.0.1(58928) COPY  
13597 be/4 digoal      0.00 B/s   18.60 M/s  0.00 % 84.38 % postgres: postgres postgres 127.0.0.1(58952) COPY  
13602 be/4 digoal      0.00 B/s   19.00 M/s  0.00 % 84.33 % postgres: postgres postgres 127.0.0.1(58962) COPY  
13605 be/4 digoal      0.00 B/s   18.88 M/s  0.00 % 84.28 % postgres: postgres postgres 127.0.0.1(58968) COPY  
13607 be/4 digoal      0.00 B/s   18.73 M/s  0.00 % 84.28 % postgres: postgres postgres 127.0.0.1(58972) COPY  
13631 be/4 digoal      0.00 B/s   18.90 M/s  0.00 % 84.27 % postgres: postgres postgres 127.0.0.1(59020) COPY  
13581 be/4 digoal      0.00 B/s   18.65 M/s  0.00 % 84.25 % postgres: postgres postgres 127.0.0.1(58920) COPY  
13633 be/4 digoal      0.00 B/s   18.93 M/s  0.00 % 84.22 % postgres: postgres postgres 127.0.0.1(59024) COPY  
13589 be/4 digoal      0.00 B/s   18.87 M/s  0.00 % 84.15 % postgres: postgres postgres 127.0.0.1(58936) COPY  
13601 be/4 digoal      0.00 B/s   19.03 M/s  0.00 % 84.06 % postgres: postgres postgres 127.0.0.1(58960) COPY  
13610 be/4 digoal      0.00 B/s   19.83 M/s  0.00 % 84.04 % postgres: postgres postgres 127.0.0.1(58978) COPY  
13609 be/4 digoal      0.00 B/s   19.44 M/s  0.00 % 84.03 % postgres: postgres postgres 127.0.0.1(58976) COPY  
13588 be/4 digoal      0.00 B/s   19.09 M/s  0.00 % 83.94 % postgres: postgres postgres 127.0.0.1(58934) COPY  
13619 be/4 digoal      0.00 B/s   19.70 M/s  0.00 % 83.90 % postgres: postgres postgres 127.0.0.1(58996) COPY  
13580 be/4 digoal      0.00 B/s   19.56 M/s  0.00 % 83.89 % postgres: postgres postgres 127.0.0.1(58918) COPY  
13630 be/4 digoal      0.00 B/s   19.03 M/s  0.00 % 83.87 % postgres: postgres postgres 127.0.0.1(59018) COPY  
13625 be/4 digoal      0.00 B/s   19.03 M/s  0.00 % 83.82 % postgres: postgres postgres 127.0.0.1(59008) COPY  
13624 be/4 digoal      0.00 B/s   18.96 M/s  0.00 % 83.80 % postgres: postgres postgres 127.0.0.1(59006) COPY  
13579 be/4 digoal      0.00 B/s   19.29 M/s  0.00 % 83.77 % postgres: postgres postgres 127.0.0.1(58916) COPY  
13613 be/4 digoal      0.00 B/s   19.45 M/s  0.00 % 83.76 % postgres: postgres postgres 127.0.0.1(58984) COPY  
13608 be/4 digoal      0.00 B/s   18.76 M/s  0.00 % 83.75 % postgres: postgres postgres 127.0.0.1(58974) COPY  
13617 be/4 digoal      0.00 B/s   19.07 M/s  0.00 % 83.74 % postgres: postgres postgres 127.0.0.1(58992) COPY  
13611 be/4 digoal      0.00 B/s   19.53 M/s  0.00 % 83.69 % postgres: postgres postgres 127.0.0.1(58980) COPY  
13590 be/4 digoal      0.00 B/s   19.45 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58938) COPY  
13593 be/4 digoal      0.00 B/s   19.30 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58944) COPY  
13592 be/4 digoal      0.00 B/s   18.99 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58942) COPY  
13594 be/4 digoal      0.00 B/s   19.33 M/s  0.00 % 83.63 % postgres: postgres postgres 127.0.0.1(58946) COPY  
13620 be/4 digoal      0.00 B/s   19.13 M/s  0.00 % 83.62 % postgres: postgres postgres 127.0.0.1(58998) COPY  
13578 be/4 digoal      0.00 B/s   19.89 M/s  0.00 % 83.62 % postgres: postgres postgres 127.0.0.1(58914) COPY  
13598 be/4 digoal      0.00 B/s   19.66 M/s  0.00 % 83.60 % postgres: postgres postgres 127.0.0.1(58954) COPY  
13596 be/4 digoal      0.00 B/s   19.58 M/s  0.00 % 83.50 % postgres: postgres postgres 127.0.0.1(58950) COPY  
13623 be/4 digoal      0.00 B/s   19.63 M/s  0.00 % 83.49 % postgres: postgres postgres 127.0.0.1(59004) COPY  
13618 be/4 digoal      0.00 B/s   19.89 M/s  0.00 % 83.43 % postgres: postgres postgres 127.0.0.1(58994) COPY  
13591 be/4 digoal      0.00 B/s   19.94 M/s  0.00 % 83.43 % postgres: postgres postgres 127.0.0.1(58940) COPY  
13595 be/4 digoal      0.00 B/s   19.81 M/s  0.00 % 83.39 % postgres: postgres postgres 127.0.0.1(58948) COPY  
13621 be/4 digoal      0.00 B/s   19.94 M/s  0.00 % 83.36 % postgres: postgres postgres 127.0.0.1(59000) COPY  
13615 be/4 digoal      0.00 B/s   19.34 M/s  0.00 % 83.28 % postgres: postgres postgres 127.0.0.1(58988) COPY  
13614 be/4 digoal      0.00 B/s   19.59 M/s  0.00 % 83.27 % postgres: postgres postgres 127.0.0.1(58986) COPY  
13599 be/4 digoal      0.00 B/s   18.60 M/s  0.00 % 83.26 % postgres: postgres postgres 127.0.0.1(58956) COPY  
13626 be/4 digoal      0.00 B/s   21.10 M/s  0.00 % 83.16 % postgres: postgres postgres 127.0.0.1(59010) COPY  
13604 be/4 digoal      0.00 B/s   19.62 M/s  0.00 % 83.05 % postgres: postgres postgres 127.0.0.1(58966) COPY  
13627 be/4 digoal      0.00 B/s   20.44 M/s  0.00 % 82.94 % postgres: postgres postgres 127.0.0.1(59012) COPY  
13603 be/4 digoal      0.00 B/s   20.69 M/s  0.00 % 82.91 % postgres: postgres postgres 127.0.0.1(58964) COPY  
13587 be/4 digoal      0.00 B/s   20.30 M/s  0.00 % 82.91 % postgres: postgres postgres 127.0.0.1(58932) COPY  
13628 be/4 digoal      0.00 B/s   20.24 M/s  0.00 % 82.65 % postgres: postgres postgres 127.0.0.1(59014) COPY  
13616 be/4 digoal      0.00 B/s   19.89 M/s  0.00 % 82.62 % postgres: postgres postgres 127.0.0.1(58990) COPY  
13606 be/4 digoal      0.00 B/s   20.26 M/s  0.00 % 82.55 % postgres: postgres postgres 127.0.0.1(58970) COPY  

6、导入完成后,创建索引,开启autovacuum.

create or replace function set_tbl() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');             
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('alter table ut%s set (autovacuum_enabled=on, toast.autovacuum_enabled=on)', i));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;   
create or replace function crt_idx_tbl() returns setof record as $$      
declare      
begin      
  for i in 0..55 loop             
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres');             
    perform 1 from dblink_get_result('link'||i) as t(res text);            
    perform dblink_send_query('link'||i, format('create index idx_ut%s_1 on ut%s (c1)', i, i));            
  end loop;         
  for i in 0..55 loop      
    return query select * from dblink_get_result('link'||i) as t(res text);      
  end loop;      
end;      
$$ language plpgsql strict;   
\timing  
  
select * from set_tbl() as t(res text);  
  
select * from crt_idx_tbl() as t(res text);  

小结

本文介绍了如何非常快速的导入数据到数据库中,使得硬件的能力得以发挥。在阿里云ECS上,采用PostgreSQL实现了每分钟约导入8亿记录的效果。

有兴趣的同学可以拿阿里云RDS PG,RDS PPAS 10试一试。

参考

《阿里云 RDS PostgreSQL 隐藏开关 - hash to btree, unlogged to logged》

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接