摘要： 本文主要介紹并測試一下PostgreSQL 在中高端x86服務(wù)器上的數(shù)據(jù)插入速度，幫助企業(yè)用戶了解PostgreSQL在這種純插入場景的性能。（例如運營商網(wǎng)關(guān)數(shù)據(jù)，金融行業(yè)數(shù)據(jù)，產(chǎn)生量大，并且要求快速插入大數(shù)據(jù)庫中持久化保存。） 測試結(jié)果寫在前面：每32K的block存儲89條記錄, 每條記錄約3

本文主要介紹并測試PostgreSQL 在中高端x86服務(wù)器上的數(shù)據(jù)插入速度（目標(biāo)表包含一個時間字段的索引），幫助企業(yè)用戶了解PostgreSQL在這類場景下的性能表現(xiàn)。這類場景常見于 : 運營商網(wǎng)關(guān)數(shù)據(jù)，金融行業(yè)數(shù)據(jù)，產(chǎn)生量大，并且要求快速插入大數(shù)據(jù)庫中持久化保存。另外, 用戶如果需要流式實時處理，可以參考基于PostgreSQL的流式處理方案，一天處理1萬億的實時流式處理是如何實現(xiàn)的？https://yq.aliyun.com/articles/166

TEST CASE

.1. 平均每條記錄長度360字節(jié)， (比較常見的長度) .2. 時間字段創(chuàng)建索引。.3. 每輪測試插入12TB數(shù)據(jù)，插入完12T后清除數(shù)據(jù)繼續(xù)插入。循環(huán)。.4. 測試滿24小時停止測試。.5. 統(tǒng)計24小時插入的記錄數(shù)。

TEST 結(jié)果

24小時一共完成12輪測試，平均每輪測試耗時7071秒。506萬行/s，1.78 GB/s，全天插入4372億，154TB數(shù)據(jù)。

測試的硬件環(huán)境

.1. X86服務(wù)器     .2. 3?核。   .3. 5??G 內(nèi)存   .4. 幾塊SSD，15TB容量   
軟件環(huán)境
.1. CENTOS 6.x x64   .2 .xfs   .3. PostgreSQL 9.5   
系統(tǒng)配置參考
https://github.com/digoal/pgsql_admin_script/blob/master/pgsql_perf_tuning.md
數(shù)據(jù)庫配置
./configure --PRefix=/home/digoal/pgsql9.5.1 --with-blocksize=32 --with-segsize=128 --with-wal-blocksize=32 --with-wal-segsize=64  make && make install  
PostgreSQL支持hugepage的方法請參考：https://yq.aliyun.com/articles/8482參數(shù)
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;fsync=onport = 1921                             # (change requires restart)max_connections = 600                   # (change requires restart)superuser_reserved_connections = 13     # (change requires restart)unix_socket_directories = '.'   # comma-separated list of directoriesunix_socket_permissions = 0700          # begin with 0 to use octal notationtcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;tcp_keepalives_count = 10               # TCP_KEEPCNT;shared_buffers = 256GB                   # min 128kBhuge_pages = on                 # on, off, or trywork_mem = 512MB                                # min 64kBmaintenance_work_mem = 1GB              # min 1MBautovacuum_work_mem = 1GB               # min 1MB, or -1 to use maintenance_work_memdynamic_shared_memory_type = posix      # the default is the first optionbgwriter_delay = 10ms                   # 10-10000ms between roundsbgwriter_lru_maxpages = 1000            # 0-1000 max buffers written/roundbgwriter_lru_multiplier = 2.0  synchronous_commit = off                # synchronization level;full_page_writes = on                  # recover from partial page writeswal_buffers = 2047MB                    # min 32kB, -1 sets based on shared_bufferswal_writer_delay = 10ms         # 1-10000 millisecondscheckpoint_timeout = 55min              # range 30s-1hmax_wal_size = 512GBcheckpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0effective_cache_size = 40GB   log_destination = 'csvlog'              # Valid values are combinations oflogging_collector = on          # Enable capturing of stderr and csvloglog_directory = 'pg_log'                # directory where log files are written,log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,log_file_mode = 0600                    # creation mode for log files,log_truncate_on_rotation = on           # If on, an existing log file with thelog_checkpoints = offlog_connections = offlog_disconnections = offlog_error_verbosity = verbose           # terse, default, or verbose messageslog_timezone = 'PRC'log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions anddatestyle = 'iso, mdy'timezone = 'PRC'lc_messages = 'C'                       # locale for system error messagelc_monetary = 'C'                       # locale for monetary formattinglc_numeric = 'C'                        # locale for number formattinglc_time = 'C'                           # locale for time formattingdefault_text_search_config = 'pg_catalog.english'autovacuum=off
創(chuàng)建測試表 :每32K的block存儲89條記錄, 每條記錄360字節(jié)。
postgres=# select string_agg(i,'') from (select md5(random()::text) i from generate_series(1,10) t(i)) t(i);                               string_agg                                                                       ---------------------------------------------------------------------- 53d3ec7adbeacc912a45bdd8557b435be848e4b1050dc0f5e46b75703d4745833541b5dabc177db460b6b1493961fc72c478daaaac74bcc89aec4f946a496028d9cff1cc4144f738e01ea36436455c216aa697d87fe1f87ceb49134a687dc69cba34c9951d0c9ce9ca82bba229d56874af40498dca5fd8dfb9c877546db76c35a3362d6bdba6472d3919289b6eaeeab58feb4f6e79592fc1dd8253fd4c588a29(1 row)postgres=# create unlogged table test(crt_time timestamp, info text default '53d3ec7adbeacc912a45bdd8557b435be848e4b1050dc0f5e46b75703d4745833541b5dabc177db460b6b1493961fc72c478daaaac74bcc89aec4f946a496028d9cff1cc4144f738e01ea36436455c216aa697d87fe1f87ceb49134a687dc69cba34c9951d0c9ce9ca82bba229d56874af40498dca5fd8dfb9c877546db76c35a3362d6bdba6472d3919289b6eaeeab58feb4f6e79592fc1dd8253fd4c588a29');postgres=# alter table test alter column info set storage plain;postgres=# insert into test select now() from generate_series(1,1000);postgres=# select ctid from test limit 1000;
分別在3個物理塊設(shè)備上創(chuàng)建3個表空間目錄，同時在數(shù)據(jù)庫中創(chuàng)建表空間。 tbs1, tbs2, tbs3.
創(chuàng)建多個分表，用于減少 block extend 沖突。
do language plpgsql $$declarei int;sql text;begin  for i in 1..42 loop    sql := 'create unlogged table test'||i||' (like test including all) tablespace tbs1';    execute sql;    sql := 'create index idx_test'||i||' on test'||i||' using brin (crt_time) with (pages_per_range=512) tablespace tbs1';    execute sql;  end loop;  for i in 43..84 loop    sql := 'create unlogged table test'||i||' (like test including all) tablespace tbs2';    execute sql;    sql := 'create index idx_test'||i||' on test'||i||' using brin (crt_time) with (pages_per_range=512) tablespace tbs2';    execute sql;  end loop;  for i in 85..128 loop    sql := 'create unlogged table test'||i||' (like test including all) tablespace tbs3';    execute sql;    sql := 'create index idx_test'||i||' on test'||i||' using brin (crt_time) with (pages_per_range=512) tablespace tbs3';    execute sql;  end loop;end; $$;
又見黑科技 BRIN 索引方法
這里使用的是brin范圍索引，PostgreSQL 針對物聯(lián)網(wǎng)流式數(shù)據(jù)的黑科技。
postgres=# /di                 List of relations Schema |    Name     | Type  |  Owner   |  Table  --------+-------------+-------+----------+--------- public | idx_test1   | index | postgres | test1 public | idx_test10  | index | postgres | test10 public | idx_test100 | index | postgres | test100 public | idx_test101 | index | postgres | test101 public | idx_test102 | index | postgres | test102 public | idx_test103 | index | postgres | test103 public | idx_test104 | index | postgres | test104 public | idx_test105 | index | postgres | test105 public | idx_test106 | index | postgres | test106............ public | idx_test90  | index | postgres | test90 public | idx_test91  | index | postgres | test91 public | idx_test92  | index | postgres | test92 public | idx_test93  | index | postgres | test93 public | idx_test94  | index | postgres | test94 public | idx_test95  | index | postgres | test95 public | idx_test96  | index | postgres | test96 public | idx_test97  | index | postgres | test97 public | idx_test98  | index | postgres | test98 public | idx_test99  | index | postgres | test99(128 rows)
生成測試腳本, 一個連接一次插入178條記錄，占用2個32KB的block :
vi test.sql insert into test(crt_time) values (now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()); for ((i=1;i<=128;i++)) do sed "s/test/test$i/" test.sql > ./test$i.sql; done
開始測試前清除數(shù)據(jù)：
do language plpgsql $$  declarei int;sql text;begin  for i in 1..128 loop    sql := 'truncate test'||i;    execute sql;  end loop;end; $$;
測試方法：每輪測試插入12TB數(shù)據(jù)。通過以下方式控制：.1. 使用128個并行連接，每個連接執(zhí)行1572864個事務(wù)。.2. 一共執(zhí)行201326592個事務(wù)（每個事務(wù)插入178條記錄）。.3. 一共插入35836133376條記錄（358.36 億記錄）（共計12TB 數(shù)據(jù)，索引空間另算）。進(jìn)行下一輪測試前，輸出日志，并TRUNCATE所有的數(shù)據(jù)，然后重復(fù)以上測試。直到測試滿24小時，輸出統(tǒng)計數(shù)據(jù)。測試腳本如下 :
vi test.sh#!/bin/bashif [ $# -ne 5 ]; then  echo "please use: $0 ip port dbname user pwd"  exit 1fiIP=$1PORT=$2DBNAME=$3USER=$4PASSWord=$5export PGPASSWORD=$PASSWORDDEP_CMD="psql"which $DEP_CMD if [ $? -ne 0 ]; then  echo -e "dep commands: $DEP_CMD not exist."  exit 1fitruncate() {psql -h $IP -p $PORT -U $USER $DBNAME <<EOFdo language plpgsql /$/$  declarei int;sql text;begin  for i in 1..128 loop    sql := 'truncate test'||i;    execute sql;  end loop;end; /$/$;checkpoint;/qEOF}# truncate data firsttruncateSTART=`date +%s`echo "`date +%F%T` $START"for ((x=1;x>0;x++)) do # ------------------------------------------------------echo "Round $x test start: `date +%F%T` `date +%s`"for ((i=1;i<=128;i++)) do   pgbench -M prepared -n -r -f ./test$i.sql -h $IP -p $PORT -U $USER $DBNAME -c 1 -j 1 -t 1572864 >>./$i.log 2>&1 & done waitecho "Round $x test end: `date +%F%T` `date +%s`"# ------------------------------------------------------if [ $((`date +%s`-$START)) -gt 86400 ]; then  echo "end `date +%F%T` `date +%s`"  echo "duration second: $((`date +%s`-$START))"  exit 0fiecho "Round $x test end, start truncate `date +%F%T` `date +%s`"truncateecho "Round $x test end, end truncate `date +%F%T` `date +%s`"done
測試
nohup ./test.sh xxx.xxx.xxx.xxx 1921 postgres postgres postgres >./test.log 2>&1 &
測試結(jié)果
24小時完成12輪測試，平均每輪測試耗時7071秒。 506萬行/s（每行360字節(jié)），1.78GB/s，全天插入4372億，154TB數(shù)據(jù)。
查詢性能
postgres=# select min(crt_time),max(crt_time) from test1;            min             |            max             ----------------------------+---------------------------- 2016-04-08 00:32:26.842728 | 2016-04-08 02:29:41.583367(1 row)postgres=# explain select count(*) from test1 where crt_time between '2016-04-08 00:32:00' and '2016-04-08 00:33:00';                                                                            QUERY PLAN                                                                             ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate  (cost=1183919.81..1183919.82 rows=1 width=0)   ->  Bitmap Heap Scan on test1  (cost=14351.45..1180420.19 rows=1399849 width=0)         Recheck Cond: ((crt_time >= '2016-04-08 00:32:00'::timestamp without time zone) AND (crt_time <= '2016-04-08 00:33:00'::timestamp without time zone))         ->  Bitmap Index Scan on idx_test1  (cost=0.00..14001.49 rows=1399849 width=0)               Index Cond: ((crt_time >= '2016-04-08 00:32:00'::timestamp without time zone) AND (crt_time <= '2016-04-08 00:33:00'::timestamp without time zone))(5 rows)Time: 0.382 mspostgres=# select count(*) from test1 where crt_time between '2016-04-08 00:32:00' and '2016-04-08 00:33:00';  count  --------- 2857968(1 row)Time: 554.474 ms
小結(jié)
.1. 這個CASE主要的應(yīng)用場景是實時的大數(shù)據(jù)入庫，例如 物聯(lián)網(wǎng) 的應(yīng)用場景，大量的 傳感器 會產(chǎn)生龐大的數(shù)據(jù)。又比如傳統(tǒng)的 運營商網(wǎng)關(guān) ，也會有非常龐大的流量數(shù)據(jù)或業(yè)務(wù)數(shù)據(jù)需要實時的入庫。索引方面，用到了PostgreSQL黑科技BRIN。.2. 除了實時入庫，用戶如果需要流式實時處理，可以參考基于PostgreSQL的流式處理方案，
一天處理1萬億的實時流式處理是如何實現(xiàn)的？
https://yq.aliyun.com/articles/166
.3. 瓶頸, 還是在IO上面 , 有幾個表現(xiàn)，TOP大量進(jìn)程處于D(front io)狀態(tài) 。
       w: S  --  Process Status          The status of the task which can be one of:             ’D’ = uninterruptible sleep             ’R’ = running             ’S’ = sleeping             ’T’ = traced or stopped             ’Z’ = zombie
所有塊設(shè)備的使用率均達(dá)100% 。清理數(shù)據(jù)時 ：
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %utildfa               0.00     0.00 5807.39 167576.65 1464080.93 1340613.23    16.18   535.69    3.02   0.01 116.77dfb               0.00     0.00 5975.10 185132.68 1506714.40 1481061.48    15.63   459.46    2.32   0.01 110.62dfc               0.00     0.00 5715.56 182584.05 1440771.98 1460672.37    15.41   568.02    2.93   0.01 112.37
插入數(shù)據(jù)時 ：
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %utildfa               0.00     0.00    0.00 235936.00     0.00 1887488.00     8.00  2676.34   11.17   0.00  99.10dfb               0.00     0.00    0.00 237621.00     0.00 1900968.00     8.00    66.02    0.10   0.00  99.10dfc               0.00     0.00    0.00 239830.00     0.00 1918632.00     8.00    10.66    0.04   0.00 101.30
IO層面的性能問題，可以通過優(yōu)化代碼（例如 PostgreSQL bgwriter 在寫出數(shù)據(jù)時，盡量順序?qū)懗觯阌贠S層進(jìn)行IO合并，來緩解IO壓力，從這個信息來看，單次寫IO的大小還可以再大點。
有幾個工具你可能用得上，perf, systemtap, goprof.如果要較全面的分析，建議把PostgreSQL --enable-profiling打開用于診斷。