[未完待续] PostgreSQL 应用场景 与 TEST CASE(压测) 精华
背景
PostgreSQL 架构
进程模型和介绍
共享内存
物理文件
IO操作
异步IO
同步IO
表扫描
索引扫描
并行扫描
HA
网络、CPU、内存、磁盘 标准测试
iperf
pi
memtest+
fio的测试规格。
pgbench 压测工具的讲解
测试模型 - 长短连接
测试模型 - 连接数
测试模型 - 软硬解析
测试模型 - 随机数分布算法
测试模型 - 规格
大
小
中
小、中混合
## 规格搭配算法：
大规格：分配给单个实例 80% 的资源（根据主机出厂指标分配：CPU\IOPS\MEMORY）。
小规格：多个小规格填满 80% 的资源（根据主机出厂指标分配：CPU\IOPS\MEMORY）。 单个实例规格 ( 1核，1G，600 IOPS )
中、小规格混合：中规格，( 16核，128G，36000 IOPS ) ，小规格按实际剩余资源（以80%为水位）填补。
压测的数据量scale：
大规格(50亿数据量，pgbench -i -s 10000)，中规格(10亿数据量，pgbench -i -s 2000)，小规格(500万数据量，pgbench -i -s 50)。
压测连接数：
大规格(规格核数8)，中规格(规格核数8)，小规格(规格核数*8)。
需求：
提供脚本，数据库postgresql.auto.conf模板
应用场景1 -
业务背景
test case 1
1、测试背景
2、硬件需求特性
3、结构设计
4、索引
5、UDF
6、初始化数据（映射，规格：数据量）
7、测试脚本（映射，规格：参数范围）
8、测试参数（映射，规格：线程、连接数、长短连接、绑定变量与否）
9、pgbench 输出指标
10、稳定性基线（tps, 响应延迟, 方差。 (最大，最小，平均)）
11、性能基线（tps, 响应延迟, 方差。 (最大，最小，平均)）
test case 2
……………….
应用场景2 -
业务背景
test case 1
test case 2
其他场景
测试规格
2 C, 5000 IOPS, 16 G
4 C, 10000 IOPS, 32 G
8 C, 20000 IOPS, 64 G
16 C, 50000 IOPS, 128 G
32 C, 100000 IOPS, 256 G
64 C, 200000 IOPS, 480 G
每个规格，覆盖测试并发：
1, 1*Cores, 2*Cores, 3*Cores, 4*Cores, 8*Cores, 16*Cores 并发。
PostgreSQL数据库参数
# 规格相关(8C, 64G为例)
shared_buffers = 16GB # 1/4 主机内存 = 16G
work_mem = 64MB # 1/4 主机内存 / 256 = 16G/256 = 64MB (假设256个并发同时使用work_mem)
wal_buffers = 512MB # min( 2047MB, shared_buffers/32 ) = 512MB
maintenance_work_mem = 2GB # min( 2G, (1/4 主机内存)/autovacuum_max_workers ) = min(2G, 16/5) = 2GB
max_wal_size = 32GB # shared_buffers*2 = 16*2 = 32GB
min_wal_size = 8GB # max_wal_size/4 = 32/4 = 8GB
effective_cache_size = 40GB # 5/8 主机内存 = 40GB
max_parallel_workers_per_gather = 6 # 主机cores-2 = 8-2 = 6
# 优化器相关
# https://github.com/digoal/blog/blob/master/201311/20131126_03.md
seq_page_cost = 1.0
random_page_cost = 1.2
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
# 其他
listen_addresses = '0.0.0.0'
port = 1921
max_connections = 2000
superuser_reserved_connections = 3
unix_socket_directories = '.'
unix_socket_permissions = 0700
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 5.0
effective_io_concurrency = 0
max_worker_processes = 128
max_parallel_workers = 128
wal_level = replica
fsync = on
synchronous_commit = off
full_page_writes = on # 支持原子写超过BLOCK_SIZE的块设备，在对齐后可以关闭。或者支持cow的文件系统可以关闭。
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 35min
archive_mode = off
max_wal_senders = 10
max_replication_slots = 10
wal_receiver_status_interval = 1s
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 5s
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_line_prefix = '%m [%p] '
log_lock_waits = on
log_statement = 'all'
log_timezone = 'PRC'
log_autovacuum_min_duration = 0
autovacuum_max_workers = 5
autovacuum_naptime = 60s
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 1000000000
autovacuum_multixact_freeze_max_age = 1200000000
autovacuum_vacuum_cost_delay = 0
statement_timeout = 0
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 800000000
vacuum_multixact_freeze_min_age = 50000000
vacuum_multixact_freeze_table_age = 800000000
datestyle = 'iso, ymd'
timezone = 'PRC'
lc_messages = 'en_US.UTF8'
lc_monetary = 'en_US.UTF8'
lc_numeric = 'en_US.UTF8'
lc_time = 'en_US.UTF8'
default_text_search_config = 'pg_catalog.simple'
场景
5、WITH查询 (OLTP)
7、CTE递归查询with recursive
8、树形类型ltree查询 (OLTP)
1、背景
层级结构的数据的存储与高效率检索
2、设计
3层，每一个节点的下一层100个元素，共100万记录。(通常一家公司100万人已经很多了。)
求任意一个节点的所有上层节点，所有下层节点。
3、准备测试表
create extension ltree;
create table t_ltree(
id int primary key,
path ltree
);
4、准备测试数据
insert into t_ltree(0, '0');
do language plpgsql $$
declare
begin
for x in 1..3
loop
for y in 100^(i-1)+1..100^i
loop
--
end loop;
end loop;
end;
$$;
blob, clog, large obj 性能
ssl 性能
列存储
24、包含子查询 (OLTP)
25、包含嵌套查询 (OLTP)
26、包含聚合查询 (OLAP)
partial index.
sharding, 读写分离
28、包含? op ANY|ALL (ARRAY)关键字 (OLTP)
29、带多个判断条件的查询 (OLTP)
30、intersect 查询 (OLAP)
31、except 查询 (OLAP)
32、union all 查询 (OLAP)
32、union 查询 (OLAP)
47、JSONB 点查询 (OLTP)
48、JSONB 全文检索 (OLTP)
49、DB端业务逻辑应用 - 存储过程编程应用(plpgsql) (OLTP)
50、FDW 下推(select clause, where , sort , join , agg) (OLTP+OLAP)
51、阿里云 OSS外部表读写，支持压缩格式。 (OLAP)
54、hll 估值统计 (OLAP)
55、SQL流计算、实时聚合
56、图业务，图式搜索
57、导航业务，最短路径
PostgreSQL比Oracle或其他数据库有优势的场景
先罗列一下场景，后期补充实际的SQL。在PARSER到对应SQL时可以给出提示和转换SQL。
1、ltree树形类型，可以简化递归 SQL，或connect by表设计。
create extension ltree;
CREATE TABLE test (path ltree);
INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);
Top
/ | \
Science Hobbies Collections
/ | \
Astronomy Amateurs_Astronomy Pictures
/ \ |
Astrophysics Cosmology Astronomy
/ | \
Galaxies Stars Astronauts
ltreetest=> SELECT path FROM test WHERE path <@ 'Top.Science';
path
------------------------------------
Top.Science
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(4 rows)
ltreetest=> SELECT path FROM test WHERE path ~ '*.Astronomy.*';
path
-----------------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
Top.Collections.Pictures.Astronomy
Top.Collections.Pictures.Astronomy.Stars
Top.Collections.Pictures.Astronomy.Galaxies
Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)
ltreetest=> SELECT path FROM test WHERE path ~ '*.!pictures@.*.Astronomy.*';
path
------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)
ltreetest=> SELECT path FROM test WHERE path @ 'Astro*% & !pictures@';
path
------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
Top.Hobbies.Amateurs_Astronomy
(4 rows)
ltreetest=> SELECT path FROM test WHERE path @ 'Astro* & !pictures@';
path
------------------------------------
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)
2、文本搜索（支持全文检索、模糊查询、正则查询、相似查询）
支持全文索引，模糊查询索引、正则查询索引、相似查询索引。
亿级文本，毫秒级响应。
3、空间数据处理（点、线、面、3D、4D对象；地理信息库支持平面坐标操作、球坐标操作）
支持空间索引，空间对象计算。
4、多值类型(jsonb, array, tsvector, hstore)
支持多值列倒排索引。
5、多字段任意组合条件过滤查询
三种内置技术，解决任意组合条件过滤查询效率问题：
bloom索引，gin多列索引，单列多索引bitmap scan。
5、地理数据支持路径规划
支持多种图式搜索算法
6、秒杀
支持advisory lock。36万TPS 秒杀更新同一记录。
7、物联网，区间搜索（BRIN）
支持BRIN索引，BITMAP扫描。效率高，索引小（几百KB支持几百GB的表）。
8、FDW下推能力
下推(select clause, where , sort , join , agg)
9、机器学习(MADLib)
支持机器学习SQL库MADlib，支持plr, plpython。
阿里云HybridDB for PostgreSQL支持import python library。
10、支持多种索引接口
11、插件丰富，垂直领域扎根业务能力强
12、扩展能力强(类型、操作符、索引、UDF、采样接口、自定义外部数据访问接口、存储过程语言接口、扫描接口)
13、支持阿里云OSS对象存储外部表
14、支持存储过程
15、支持NOSQL特性
16、支持多核 并行计算
17、支持sharding（基于FDW）
18、支持异构数据访问（通过外部表读写Oracle, mysql, hadoop, 等。）
https://wiki.postgresql.org/wiki/Fdw
参考
《PostgreSQL、Greenplum 应用案例宝典《如来神掌》 - 目录》
《PostgreSQL 使用 pgbench 测试 sysbench 相关case》
https://www.postgresql.org/docs/10/static/pgbench.html