PolarDB10行代码提速200倍, 续
P
digoal约 12 分钟
digoal约 12 分钟参考文档点击文末 阅读原文 打开; 推荐 《最好的PostgreSQL学习镜像 》 ;
PolarDB提速200倍, 续
上一篇信息《 PolarDB提速200倍，10行PL/Python实现DuckDB代理 》受制于篇幅, 没有测试csv reader和postgres_scanner的效率, 这不马上就来了.先来个本周五北京的数据库线下活动, 欢迎扫码报名:
2、postgres_scanner
postgres_scanner, 把DuckDB当成PG的计算引擎.
下面的测试环境m2模拟x86容器, TPCH SF=5.
如果使用postgres_scanner, 就可以把DuckDB完全当成一个计算引擎来使用, 只要把PG里的表映射一份结构到DuckDB即可. 在进行DEMO之前, 先回答几个问题:
-
会不会下推条件? 会, 有参数
pg_experimental_filter_pushdown控制, 见 https://duckdb.org/docs/extensions/postgres.html -
con.sql('SET pg_experimental_filter_pushdown=true') -
会不会下推字段? 会, 只查询需要的字段 -
会不会并行获取? 会, 按ctid分段并行获取, 通过参数
pg_pages_per_taskpg_use_ctid_scan控制, 见 《duckdb postgres_scan 插件 - 不落地数据, 加速PostgreSQL数据分析》 -
con.sql('SET pg_use_ctid_scan=true'),con.sql('SET pg_pages_per_task=1000') -
备注: duckdb cli观察到了并行, 但是在python duckdb API里没有看到并行. -
会不会内存溢出? 我想应该不会, 因为可以处理大于内存的数据, 没有验证. 见 《DuckDB高效内存管理: 流式执行 , 临时文件 , 缓冲区管理》
DEMO
1、首先配置一下PolarDB PostgreSQL的日志审计, 便于观察DuckDB发起了哪些请求到PG里面
postgres=# alter system set log_statement='all';
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
在pg_log的audit中可以观察日志
postgres@3847ee12e901:~/tmp_master_dir_polardb_pg_1100_bld/pg_log$ pwd
/home/postgres/tmp_master_dir_polardb_pg_1100_bld/pg_log
less postgresql-2024-11-27_111939_0_audit.log
2024-11-27 13:55:31.031 CST [45041] [45041] LOG: statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
2024-11-27 13:55:31.031 CST [45041] [45041] LOG: statement:
COPY (SELECT "l_shipdate" FROM "public"."lineitem" WHERE ("l_shipdate" <= '1998-08-23' AND "l_shipdate" IS NOT NULL)) TO STDOUT (FORMAT "binary");
2024-11-27 13:55:31.031 CST [45041] [45041] LOG: statement: COMMIT
2、安装DuckDB postgres_scanner插件
postgres@3847ee12e901:~$ python3.8
Python 3.8.10 (default, Jul 29 2024, 17:02:10)
[GCC 9.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import duckdb
con>>> con = duckdb.connect('/data/duckdb_proxy.db')
>>> con.install_extension('postgres_scanner')
100% ▕████████████████████████████████████████████████████████████▏
>>> con.load_extension('postgres_scanner')
>>>
如果在比赛评测机上无法联网, 可以把下好的插件拷贝到对应位置即可
postgres@3847ee12e901:~/.duckdb/extensions/v1.1.3/linux_amd64_gcc4$ pwd
/home/postgres/.duckdb/extensions/v1.1.3/linux_amd64_gcc4
postgres@3847ee12e901:~/.duckdb/extensions/v1.1.3/linux_amd64_gcc4$ ll
total 46904
drwxr-xr-x 2 postgres postgres 4096 Nov 27 11:30 ./
drwxr-xr-x 3 postgres postgres 4096 Nov 27 11:30 ../
-rw-r--r-- 1 postgres postgres 48013534 Nov 27 11:30 postgres_scanner.duckdb_extension
-rw-r--r-- 1 postgres postgres 175 Nov 27 11:30 postgres_scanner.duckdb_extension.info
3、创建视图, 映射到PolarDB PostgreSQL中
>>> con.sql("""ATTACH 'dbname=postgres user=postgres host=/home/postgres/tmp_master_dir_polardb_pg_1100_bld port=5432' AS polardb (TYPE POSTGRES, SCHEMA 'public')""")
>>> con.sql("""create or replace view customer as select * from polardb.customer""")
>>> con.sql("""create or replace view lineitem as select * from polardb.lineitem""")
>>> con.sql("""create or replace view nation as select * from polardb.nation""")
>>> con.sql("""create or replace view orders as select * from polardb.orders""")
>>> con.sql("""create or replace view part as select * from polardb.part""")
>>> con.sql("""create or replace view partsupp as select * from polardb.partsupp""")
>>> con.sql("""create or replace view region as select * from polardb.region""")
>>> con.sql("""create or replace view supplier as select * from polardb.supplier""")
>>> con.sql('select count(*) from lineitem')
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 600572 │
└──────────────┘
>>> con.checkpoint()
>>> con.close()
PolarDB PostgreSQL -> Compute by DuckDB -> fetch from PolarDB PostgreSQL
-- 创建或替换函数
CREATE OR REPLACE FUNCTION duckdb_proxy(
query_text TEXT,
IN idbname text default 'postgres',
IN iuser text default 'postgres',
IN ihost text default '/home/postgres/tmp_master_dir_polardb_pg_1100_bld',
IN iport text default '5432',
IN ischema text default 'public'
)
RETURNS SETOF RECORD AS $$
import duckdb
# 连接到 DuckDB 数据库
con = duckdb.connect(database='/data/duckdb_proxy.db')
# 连接 PolarDB PostgreSQL 数据库, 并设置postgres_scanner参数.
con.sql("ATTACH 'dbname=" + idbname + " user=" + iuser + " host=" + ihost + " port=" + iport + "' AS polardb (TYPE POSTGRES, SCHEMA '" + ischema + "')")
con.sql('SET pg_experimental_filter_pushdown=true')
con.sql('SET pg_use_ctid_scan=true')
con.sql('SET pg_pages_per_task=1000')
con.sql('SET threads = 4')
con.sql("SET memory_limit = '4GB'")
# 获取结果并返回
for row in tuple(con.execute(query_text).fetchall()):
yield row
# 关闭连接
con.close()
$$ LANGUAGE plpython3u;
查看DuckDB执行计划
postgres=# select p from duckdb_proxy($$
explain select count(*) from lineitem where
l_shipdate <= ( date '1998-12-01' - interval '100' day )
$$) as t (x text, p text);
p
-------------------------------
┌───────────────────────────┐+
│ UNGROUPED_AGGREGATE │+
│ ──────────────────── │+
│ Aggregates: │+
│ count_star() │+
└─────────────┬─────────────┘+
┌─────────────┴─────────────┐+
│ PROJECTION │+
│ ──────────────────── │+
│ 42 │+
│ │+
│ ~0 Rows │+
└─────────────┬─────────────┘+
┌─────────────┴─────────────┐+
│ POSTGRES_SCAN │+
│ ──────────────────── │+
│ lineitem │+
│ │+
│ Projections: │+
│ l_shipdate │+
│ │+
│ Filters: │+
│ l_shipdate<='1998-08-23': │+
│ :DATE AND l_shipdate IS │+
│ NOT NULL │+
│ │+
│ ~0 Rows │+
└───────────────────────────┘+
(1 row)
explain analyze 看到q17 total time 53.22秒
postgres=# select p from duckdb_proxy($$
explain analyze select count(*) from lineitem where
l_shipdate <= ( date '1998-12-01' - interval '100' day )
$$) as t (x text, p text);
p
--------------------------------------------------------------------------------------------------------------------
┌─────────────────────────────────────┐ +
│┌───────────────────────────────────┐│ +
││ Query Profiling Information ││ +
│└───────────────────────────────────┘│ +
└─────────────────────────────────────┘ +
explain analyze select count(*) from lineitem where l_shipdate <= ( date '1998-12-01' - interval '100' day ) +
┌────────────────────────────────────────────────┐ +
│┌──────────────────────────────────────────────┐│ +
││ Total Time: 53.22s ││ +
│└──────────────────────────────────────────────┘│ +
└────────────────────────────────────────────────┘ +
┌───────────────────────────┐ +
│ QUERY │ +
└─────────────┬─────────────┘ +
┌─────────────┴─────────────┐ +
│ EXPLAIN_ANALYZE │ +
│ ──────────────────── │ +
│ 0 Rows │ +
│ (0.00s) │ +
└─────────────┬─────────────┘ +
┌─────────────┴─────────────┐ +
│ UNGROUPED_AGGREGATE │ +
│ ──────────────────── │ +
│ Aggregates: │ +
│ count_star() │ +
│ │ +
│ 1 Rows │ +
│ (0.02s) │ +
└─────────────┬─────────────┘ +
┌─────────────┴─────────────┐ +
│ PROJECTION │ +
│ ──────────────────── │ +
│ 42 │ +
│ │ +
│ 29479362 Rows │ +
│ (0.04s) │ +
└─────────────┬─────────────┘ +
┌─────────────┴─────────────┐ +
│ TABLE_SCAN │ +
│ ──────────────────── │ +
│ lineitem │ +
│ │ +
│ Projections: │ +
│ l_shipdate │ +
│ │ +
│ Filters: │ +
│ l_shipdate<='1998-08-23': │ +
│ :DATE AND l_shipdate IS │ +
│ NOT NULL │ +
│ │ +
│ 29479362 Rows │ +
│ (53.08s) │ +
└───────────────────────────┘ +
(1 row)
q17本地执行耗时 24秒
postgres=# explain analyze select count(*) from lineitem where
postgres-# l_shipdate <= ( date '1998-12-01' - interval '100' day );
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=750569.14..750569.15 rows=1 width=8) (actual time=23877.755..23919.287 rows=1 loops=1)
-> Gather (cost=750568.93..750569.14 rows=2 width=8) (actual time=23872.062..23918.897 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=749568.93..749568.94 rows=1 width=8) (actual time=23816.608..23816.672 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..718911.09 rows=12263133 width=0) (actual time=2.255..20950.107 rows=9826454 loops=3)
Filter: (l_shipdate <= '1998-08-23 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 173478
Planning Time: 10.225 ms
Execution Time: 23921.462 ms
(10 rows)
Time: 23974.188 ms (00:23.974)
q20 使用duckdb_proxy 56秒, 比本地快很多很多(本地没执行出来)
select * from duckdb_proxy($$
select
s_name,
s_address
from
supplier,
nation
where
s_suppkey in (
select
ps_suppkey
from
partsupp
where
ps_partkey in (
select
p_partkey
from
part
where
p_name like 'forest%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date '1994-01-01'
and l_shipdate < date '1994-01-01' + interval '1' year
)
)
and s_nationkey = n_nationkey
and n_name = 'CANADA'
order by
s_name;
$$) as t (s_name text, s_address text);
备注: 目前Linux arm架构版本无法直接install postgres_scanner插件, 需要其他方法安装
python duckdb extension 安装 (例如 postgres_scanner):
https://github.com/santosh-d3vpl3x/duckdb_extensions
https://pypi.org/project/duckdb-extension-postgres-scanner/#files
# pip安装不支持linux arm架构版本, 可以通过编译安装, 如下:
# https://github.com/duckdb/postgres_scanner/
按说明 build postgres_scanner
# 打包到某路径
con = duckdb.connect(database = ':memory:', config = {"allow_unsigned_extensions": "true"})
con.install_extension('postgres_scanner_path')
con.load_extension('postgres_scanner_path')
3、csv reader
https://duckdb.org/docs/data/csv/overview
con.execute("""
CREATE or replace view customer as select * from
read_csv('/data/customer.tbl',
delim = '|',
header = false,
columns = {
'c_custkey': 'INTEGER',
'c_name': 'VARCHAR(25)',
'c_address': 'VARCHAR(40)',
'c_nationkey': 'INTEGER',
'c_phone': 'CHAR(15)',
'c_acctbal': 'DECIMAL(15,2)',
'c_mktsegment': 'CHAR(10)',
'c_comment': 'VARCHAR(117)'
}
);
CREATE or replace view lineitem as select * from
read_csv('/data/lineitem.tbl',
delim = '|',
header = false,
columns = {
'l_orderkey': 'INTEGER',
'l_partkey': 'INTEGER',
'l_suppkey': 'INTEGER',
'l_linenumber': 'INTEGER',
'l_quantity': 'DECIMAL(15,2)',
'l_extendedprice': 'DECIMAL(15,2)',
'l_discount': 'DECIMAL(15,2)',
'l_tax': 'DECIMAL(15,2)',
'l_returnflag': 'CHAR(1)',
'l_linestatus': 'CHAR(1)',
'l_shipdate': 'DATE',
'l_commitdate': 'DATE',
'l_receiptdate': 'DATE',
'l_shipinstruct': 'CHAR(25)',
'l_shipmode': 'CHAR(10)',
'l_comment': 'VARCHAR(44)'
}
);
CREATE or replace view nation as select * from
read_csv('/data/nation.tbl',
delim = '|',
header = false,
columns = {
'n_nationkey': 'INTEGER',
'n_name': 'CHAR(25)',
'n_regionkey': 'INTEGER',
'n_comment': 'VARCHAR(152)'
}
);
CREATE or replace view orders as select * from
read_csv('/data/orders.tbl',
delim = '|',
header = false,
columns = {
'o_orderkey': 'INTEGER',
'o_custkey': 'INTEGER',
'o_orderstatus': 'CHAR(1)',
'o_totalprice': 'DECIMAL(15,2)',
'o_orderdate': 'DATE',
'o_orderpriority': 'CHAR(15)',
'o_clerk': 'CHAR(15)',
'o_shippriority': 'INTEGER',
'o_comment': 'VARCHAR(79)'
}
);
CREATE or replace view part as select * from
read_csv('/data/part.tbl',
delim = '|',
header = false,
columns = {
'p_partkey': 'INTEGER',
'p_name': 'VARCHAR(55)',
'p_mfgr': 'CHAR(25)',
'p_brand': 'CHAR(10)',
'p_type': 'VARCHAR(25)',
'p_size': 'INTEGER',
'p_container': 'CHAR(10)',
'p_retailprice': 'DECIMAL(15,2)',
'p_comment': 'VARCHAR(23)'
}
);
CREATE or replace view partsupp as select * from
read_csv('/data/partsupp.tbl',
delim = '|',
header = false,
columns = {
'ps_partkey': 'INTEGER',
'ps_suppkey': 'INTEGER',
'ps_availqty': 'INTEGER',
'ps_supplycost': 'DECIMAL(15,2)',
'ps_comment': 'VARCHAR(199)'
}
);
CREATE or replace view region as select * from
read_csv('/data/region.tbl',
delim = '|',
header = false,
columns = {
'r_regionkey': 'INTEGER',
'r_name': 'CHAR(25)',
'r_comment': 'VARCHAR(152)'
}
);
CREATE or replace view supplier as select * from
read_csv('/data/supplier.tbl',
delim = '|',
header = false,
columns = {
's_suppkey': 'INTEGER',
's_name': 'CHAR(55)',
's_address': 'VARCHAR(40)',
's_nationkey': 'INTEGER',
's_phone': 'CHAR(15)',
's_acctbal': 'DECIMAL(15,2)',
's_comment': 'VARCHAR(101)'
}
);
""")
小结
使用postgres_scanner相比parquet慢、某些请求比本地慢, 原因主要是:-
对于要传输较多数据(虽然有pushdown), 并且python DuckDB API没有使用并行fetch -
既然如此, 你可以换其他API试试? 例如GO, C++, C? 反正PolarDB PostgreSQL支持各种PL存储过程语言. -
postgresql heap table是行存储, 访问少量列大量记录时比parquet 列存储慢
-
csv是行存, parquet是列存 -
csv没有索引, parquet有sort key, min/max statics, bloom filter, 字典等索引信息. csv的过滤性没有parquet好, csv要全扫描, parquet可以过滤.
-
主要决定于fetch哪个快, 如果解决了postgres_scanner python duckdb API并行问题, 肯定是postgres_scanner更快(因为这个在PG里还有索引可用). 所以在未来我是看好postgres_scanner的.
彩蛋: 国产数据库周边生态
当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.
1、管控软件
鸣嵩 (前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是 KubeBlocks . 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.-
https://github.com/apecloud/kubeblocks
-
https://www.csudata.com/
-
https://pigsty.cc/zh/
2、审计监控诊断优化
翟总 (曾经是我背后的男人)到海信聚好看后研发的 DBdoctor , 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.-
https://www.dbdoctor.cn/
-
https://bytebase.cc/docs/introduction/what-is-bytebase/
-
https://www.modb.pro/db/567140
3、国产数据库IDE
IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿 达刚 老师的 DeskUI :-
https://www.deskui.com
4、数据同步&迁移&备份恢复
NineData , 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.-
https://www.ninedata.cloud/home
-
https://www.dsgdata.com/
公开课
如果你对PolarDB学习感兴趣可以阅读这个公开课系列:
- 国庆拿下这个国产信创数据库
-
国产数据库PolarDB公开课-1 架构解读
-
国产数据库PolarDB公开课-2 快速体验
-
国产数据库PolarDB公开课-3 安装部署
- 国产数据库PolarDB公开课-4 日常运维
-
国产数据库PolarDB公开课-5 特性解读与体验
-
国产数据库PolarDB公开课-6 使用PostgreSQL开源工具/插件
-
国产数据库PolarDB公开课-7 应用场景与实践
-
国产数据库PolarDB公开课-7.1 TPCH测试
-
国产数据库PolarDB公开课-8 生态
-
国产数据库PolarDB公开课-9 参与开源社区
除了PolarDB还非常值得关注的几款PG栈国产数据库:
- HaloDB (基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、
- IvorySQL (基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、
- ProtonBase (云原生分布式数仓. https://protonbase.com/ )、
- 成都 文武 数据库（https://ww-it.cn）
参考文档点击 阅读原文 获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: