[未完待续] PostgreSQL 应用场景与 TEST CASE(压测) 精华

digoal 2017-10-11

139

作者

digoal

日期

2017-10-11

背景

PostgreSQL 架构

进程模型和介绍

共享内存

物理文件

IO操作

异步IO

同步IO

表扫描

索引扫描

并行扫描

网络、CPU、内存、磁盘标准测试

iperf

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_buffers2 = 162 = 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、支持多种索引接口

《PostgreSQL 9种索引的原理和应用场景》

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》

《数据库界的华山论剑 tpc.org》

https://www.postgresql.org/docs/10/static/pgbench.html

文章转载自digoal，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[未完待续] PostgreSQL 应用场景 与 TEST CASE(压测) 精华

作者

日期

标签

背景

PostgreSQL 架构

网络、CPU、内存、磁盘 标准测试

pgbench 压测工具的讲解

测试模型 - 长短连接

测试模型 - 连接数

测试模型 - 软硬解析

测试模型 - 随机数分布算法

测试模型 - 规格

应用场景1 -

业务背景

test case 1

test case 2

应用场景2 -

业务背景

test case 1

test case 2

其他场景

测试规格

PostgreSQL数据库参数

规格相关(8C, 64G为例)

优化器相关

https://github.com/digoal/blog/blob/master/201311/20131126_03.md

其他

场景

5、WITH查询 (OLTP)

7、CTE递归查询with recursive

8、树形类型ltree查询 (OLTP)

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或其他数据库有优势的场景

参考

PostgreSQL 许愿链接

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

评论

相关阅读

[未完待续] PostgreSQL 应用场景与 TEST CASE(压测) 精华

网络、CPU、内存、磁盘标准测试

28、包含`? op ANY|ALL (ARRAY)`关键字 (OLTP)