AI 助理
备案控制台
开发者社区 数据库 文章 正文

PgSQL · 性能优化 · 如何潇洒的处理每天上百TB的数据增量

2016-04-21 4490
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
简介：

背景

本文主要介绍PostgreSQL 在中高端x86服务器上的数据插入速度（目标表包含一个时间字段的索引），帮助企业用户了解PostgreSQL在这类场景下的性能表现。

这类场景常见于运营商网关数据和金融行业数据，产生量大，并且要求快速插入大数据库中持久化保存。另外, 用户如果需要流式实时处理，可以参考基于PostgreSQL的流式处理方案，一天处理1万亿的实时流式处理是如何实现的？PostgreSQL “物联网”应用 - 1 实时流式数据处理案例(万亿每天)

TEST CASE

  1. 平均每条记录长度360字节（比较常见的长度）；
  2. 时间字段创建索引；
  3. 每轮测试插入12TB数据，插入完12T后清除数据继续插入。循环；
  4. 测试满24小时停止测试；
  5. 统计24小时插入的记录数；

TEST 结果

24小时一共完成12轮测试，平均每轮测试耗时7071秒。
506万行/s，1.78 GB/s，全天插入4372亿，154TB数据。

测试的硬件环境

1. X86服务器
2. 3?核。
3. 5??G 内存
4. 几块SSD，15TB容量

软件环境

1. CENTOS 6.x x64
2. xfs
3. PostgreSQL 9.5

系统配置参考

pgsql_perf_tuning

数据库配置

./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的方法

参数

listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
fsync=on
port = 1921                             # (change requires restart)
max_connections = 600                   # (change requires restart)
superuser_reserved_connections = 13     # (change requires restart)
unix_socket_directories = '.'   # comma-separated list of directories
unix_socket_permissions = 0700          # begin with 0 to use octal notation
tcp_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 128kB
huge_pages = on                 # on, off, or try
work_mem = 512MB                                # min 64kB
maintenance_work_mem = 1GB              # min 1MB
autovacuum_work_mem = 1GB               # min 1MB, or -1 to use maintenance_work_mem
dynamic_shared_memory_type = posix      # the default is the first option
bgwriter_delay = 10ms                   # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000            # 0-1000 max buffers written/round
bgwriter_lru_multiplier = 2.0
synchronous_commit = off                # synchronization level;
full_page_writes = on                  # recover from partial page writes
wal_buffers = 2047MB                    # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms         # 1-10000 milliseconds
checkpoint_timeout = 55min              # range 30s-1h
max_wal_size = 512GB
checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 40GB
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_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 the
log_checkpoints = off
log_connections = off
log_disconnections = off
log_error_verbosity = verbose           # terse, default, or verbose messages
log_timezone = 'PRC'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
default_text_search_config = 'pg_catalog.english'
autovacuum=off

创建测试表 :
每32K的block存储89条记录, 每条记录360字节。

postgres=# select string_agg(i,'') from (select md5(random()::text) i from generate_series(1,10) t(i)) t(i);
                               string_agg
----------------------------------------------------------------------
 53d3ec7adbeacc912a45bdd8557b435be848e4b1050dc0f5e46b75703d4745833541b5dabc177db460b6b1493961fc72c478daaaac74bcc89aec4f946a496028d9cff1cc4144f738e01ea36436455c216aa697d87fe1f87ceb49134a687dc69cba34c9951d0c9ce9ca82bba229d56874af40498dca5f
d8dfb9c877546db76c35a3362d6bdba6472d3919289b6eaeeab58feb4f6e79592fc1dd8253fd4c588a29
(1 row)

postgres=# create unlogged table test(crt_time timestamp, info text default '53d3ec7adbeacc912a45bdd8557b435be848e4b1050dc0f5e46b75703d4745833541b5dabc177db460b6b1493961fc72c478daaaac74bcc89aec4f946a496028d9cff1cc4144f738e01ea36436455c216aa697d87fe1f87ceb49134a687dc69cba34c9951d0c9ce9ca82bba229d56874af40498dca5f
d8dfb9c877546db76c35a3362d6bdba6472d3919289b6eaeeab58feb4f6e79592fc1dd8253fd4c588a29');

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个物理块设备上创建3个表空间目录，同时在数据库中创建表空间。
tbs1, tbs2, tbs3.

创建多个分表，用于减少 block extend 冲突。

do language plpgsql $$
declare
i 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;
$$;

PostgreSQL 黑科技 BRIN 索引方法

这里使用的是brin范围索引，PostgreSQL 针对物联网流式数据的黑科技。

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

开始测试前清除数据：

do language plpgsql $$
declare
i int;
sql text;
begin
  for i in 1..128 loop
    sql := 'truncate test'||i;
    execute sql;
  end loop;
end;
$$;

测试方法：
每轮测试插入12TB数据。通过以下方式控制：

  1. 使用128个并行连接，每个连接执行1572864个事务；
  2. 一共执行201326592个事务（每个事务插入178条记录）；
  3. 一共插入35836133376条记录（358.36 亿记录）（共计12TB 数据，索引空间另算）。

进行下一轮测试前，输出日志，并TRUNCATE所有的数据，然后重复以上测试。直到测试满24小时，输出统计数据。

测试脚本如下 :

vi test.sh
#!/bin/bash

if [ $# -ne 5 ]; then
  echo "please use: $0 ip port dbname user pwd"
  exit 1
fi

IP=$1
PORT=$2
DBNAME=$3
USER=$4
PASSWORD=$5

export PGPASSWORD=$PASSWORD

DEP_CMD="psql"
which $DEP_CMD
if [ $? -ne 0 ]; then
  echo -e "dep commands: $DEP_CMD not exist."
  exit 1
fi

truncate() {
psql -h $IP -p $PORT -U $USER $DBNAME <<EOF
do language plpgsql \$\$
declare
i int;
sql text;
begin
  for i in 1..128 loop
    sql := 'truncate test'||i;
    execute sql;
  end loop;
end;
\$\$;
checkpoint;
\q
EOF
}

# truncate data first
truncate

START=`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

wait
echo "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 0
fi

echo "Round $x test end, start truncate `date +%F%T` `date +%s`"
truncate
echo "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 &

查询性能(索引能力)

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 ms

postgres=# 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

小结

  1. 这个CASE主要的应用场景是实时的大数据入库，例如物联网的应用场景，大量的传感器会产生庞大的数据。
    又比如传统的运营商网关，也会有非常庞大的流量数据或业务数据需要实时的入库。索引方面，用到了PostgreSQL黑科技BRIN。

  2. 除了实时入库，用户如果需要流式实时处理，可以参考基于PostgreSQL的流式处理方案，一天处理1万亿的实时流式处理是如何实现的？PostgreSQL “物联网”应用 - 1 实时流式数据处理案例(万亿每天)

  3. 瓶颈, 还是在IO上面 , 有几个表现，TOP大量进程处于D(front io)状态 。

     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

    所有块设备的使用率均达100% 。
    清理数据时 ：

     Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
 dfa               0.00     0.00 5807.39 167576.65 1464080.93 1340613.23    16.18   535.69    3.02   0.01 116.77
 dfb               0.00     0.00 5975.10 185132.68 1506714.40 1481061.48    15.63   459.46    2.32   0.01 110.62
 dfc               0.00     0.00 5715.56 182584.05 1440771.98 1460672.37    15.41   568.02    2.93   0.01 112.37

    插入数据时：

     Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
 dfa               0.00     0.00    0.00 235936.00     0.00 1887488.00     8.00  2676.34   11.17   0.00  99.10
 dfb               0.00     0.00    0.00 237621.00     0.00 1900968.00     8.00    66.02    0.10   0.00  99.10
 dfc               0.00     0.00    0.00 239830.00     0.00 1918632.00     8.00    10.66    0.04   0.00 101.30

    IO层面的性能问题，可以通过优化代码（例如 PostgreSQL bgwriter 在写出数据时，尽量顺序写出），便于OS层进行IO合并，来缓解IO压力，从这个信息来看，单次写IO的大小还可以再大点。

有几个工具你可能用得上，perf、systemtap和goprof。如果要较全面的分析，建议把 PostgreSQL –enable-profiling 打开用于诊断。

文章标签：
物联网平台
云原生数据库 PolarDB
关系型数据库
SQL
PostgreSQL
索引
物联网
相关实践学习
钉钉群中如何接收IoT温控器数据告警通知
本实验主要介绍如何将温控器设备以MQTT协议接入IoT物联网平台，通过云产品流转到函数计算FC，调用钉钉群机器人API，实时推送温湿度消息到钉钉群。
阿里云AIoT物联网开发实战
本课程将由物联网专家带你熟悉阿里云AIoT物联网领域全套云产品，7天轻松搭建基于Arduino的端到端物联网场景应用。 开始学习前，请先开通下方两个云产品，让学习更流畅： IoT物联网平台：https://iot.console.aliyun.com/ LinkWAN物联网络管理平台：https://linkwan.console.aliyun.com/service-open
db匠
目录
相关文章
何雨晨
|
23天前
|
存储 Oracle 关系型数据库
【实操】单表数据量 200 GB，PostgreSQL 怎么应对？？
【实操】单表数据量 200 GB，PostgreSQL 怎么应对？？
何雨晨
60 1
阿里云瑶池数据库SelectDB
|
6月前
|
存储 监控 Apache
查询提速11倍、资源节省70%，阿里云数据库内核版 Apache Doris 在网易日志和时序场景的实践
网易的灵犀办公和云信利用 Apache Doris 改进了大规模日志和时序数据处理，取代了 Elasticsearch 和 InfluxDB。Doris 实现了更低的服务器资源消耗和更高的查询性能，相比 Elasticsearch，查询速度提升至少 11 倍，存储资源节省达 70%。Doris 的列式存储、高压缩比和倒排索引等功能，优化了日志和时序数据的存储与分析，降低了存储成本并提高了查询效率。在灵犀办公和云信的实际应用中，Doris 显示出显著的性能优势，成功应对了数据增长带来的挑战。
阿里云瑶池数据库SelectDB
53180 2
查询提速11倍、资源节省70%，阿里云数据库内核版 Apache Doris 在网易日志和时序场景的实践
db匠
|
并行计算 关系型数据库 测试技术
PgSQL · 特性分析 · PostgreSQL 9.6 让多核并行起来
背景 经过多年的酝酿（从支持work process到支持动态fork共享内存，再到内核层面支持并行计算），PostgreSQL 的多核并行计算功能终于在2016年发布的9.6版本中正式上线，为PG的scale up能力再次拔高一个台阶，标志着开源数据库已经攻克了并行计算的难题。 相信有很多小伙伴已经开始测试了。 在32物理核的机器上进行了测试，重计算的场景，性能程线性提升。 目前并行计算支
db匠
5629 0
技术小能手
|
JavaScript 关系型数据库 MySQL
MySQL · 捉虫动态 · UK 包含 NULL 值备库延迟分析
技术小能手
2296 1
【沉淀】
|
存储 缓存 关系型数据库
【沉淀】实例迁移、Insert和写入性能——数倍，甚至数十倍提升，HybridDB for MySQL负责人王骞谈自己经历和收获
在外人看来，这位性格颇为内向，看起来低调的技术人，却做了一件很高调的事情——成功推动RDS分布式数据库HybridDB for MySQL上线公测，并在2017年成功商业化。这个可以同时支持OLTP和OLAP，提供了10T到1P解决方案的数据库，完善了阿里云RDS团队的产品族，具备了向专业评测机构Gartner定义的数据库魔力象限冲刺的能力。
【沉淀】
9079 0
db匠
|
SQL 存储 NoSQL
MSSQL · 特性分析 · 列存储技术做实时分析
摘要 数据分析指导商业行为的价值越来越高，使得用户对数据实时分析的要求变得越来越高。使用传统RDBMS数据分析架构，遇到了前所未有的挑战，高延迟、数据处理流程复杂和成本过高。这篇文章讨论如何利用SQL Server 2016列存储技术做实时数据分析，解决传统分析方法的痛点。 传统RDBMS数据分析 在过去很长一段时间，企业均选择传统的关系型数据库做OLAP和Data Warehouse工作。这一
db匠
3616 0
db匠
|
关系型数据库 Java 测试技术
PgSQL · 性能优化 · PostgreSQL TPC-C极限优化玩法
简介 本文以工业界测试模型TPC-C为测试模型，介绍PostgreSQL数据库从系统层面的优化到数据库层面的优化方法。 TPmC从 256195.32 提升到 606466.31 是如何做到的。 测试环境介绍 16核开HT共32线程 256G 1600MHz 内存 万兆网卡 3 块 6
db匠
3956 0
技术小能手
|
SQL 关系型数据库 数据库
MySQL · 性能优化· CloudDBA SQL优化建议之统计信息获取
技术小能手
2803 0
技术小能手
|
存储 测试技术 OLAP
PgSQL · 特性介绍 · 列存元数据扫描介绍
摘要 本文通过对于阿里云分析型数据库HybridDB for postgresql 数据库的列存扫描的优化特征的解析，让大家了解列存元数据扫描是如何达到提升查询扫描的速度的效果。从而使的分析型查询执行时间进一步缩短。
技术小能手
1999 0
db匠
|
存储 测试技术 OLAP
PgSQL · 特性介绍 · 列存元数据扫描介绍
摘要 本文通过对于阿里云分析型数据库HybridDB for postgresql 数据库的列存扫描的优化特征的解析，让大家了解列存元数据扫描是如何达到提升查询扫描的速度的效果。从而使的分析型查询执行时间进一步缩短。最终能够更好的为阿里云的用户提供更高性价比的服务。 关键字 Meta data scan，HybridDB for postgresql， GreenPlum，column stor
db匠
3590 0

热门文章

最新文章

  • 1
    阿里Java代码规约插件即将全球首发，邀您来发布仪式现场
  • 2
    淘宝 NPM 镜像
  • 3
    MySQL数据表生成ER图
  • 4
    Object reference not set to an instance of an object.
  • 5
    锐捷网络&视易K米，携手助力2016腾讯全球合作伙伴万人大会
  • 6
    WF4.0实战(九)：猜数字游戏，测下你的智力
  • 7
    数据结构复习笔记(6)
  • 8
    OutOfMemoryError与StackOverFlowError
  • 9
    Percona XtraDB Cluster 集群环境建立与验证指南
  • 10
    在没有ISA管控的时候
  • 1
    小程序数据绑定机制的优点
    22
  • 2
    小程序的 API 做了什么处理，能够做到全局变量的隐藏
    21
  • 3
    在 Babel 插件中使用 TypeScript 类型
    18
  • 4
    Kotlin - 函数与Lambda表达式
    17
  • 5
    Kotlin - 区间与数组
    15
  • 6
    Kotlin - 运算符与中缀表达式
    16
  • 7
    babel 原理，怎么写 babel 插件
    16
  • 8
    Kotlin - 面向对象之抽象类与接口
    13
  • 9
    Kotlin - 参数与异常
    13
  • 10
    Kotlin - 分支与循环
    15

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • Sqoop 企业级大数据迁移方案实战
  • 云数据库优化十大典型案例
  • RDS MySQL 高效设计及性能调优
  • 云数据库优化经典案例
  • 如何在 PolarDB-X 中优化慢 SQL

    • 相关电子书

    更多
  • HTAP能力持续增强 HybridDB for MySQL分析性能提升
  • 数据在PG中的升华
  • HareQL：快速HBase查询工具的发展过程

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • AnalyticDB MySQL游戏行业数据分析实践
  • AnalyticDB MySQL海量数据秒级分析体验
  • 快速体验PolarDB开源数据库
  • PolarDB MySQL HTAP：实时数据分析加速
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月