震精 - PostgreSQL 单机3.9 万亿/天(计数器、序列、自增)

本文涉及的产品
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 Tair(兼容Redis),内存型 2GB
简介:

标签

PostgreSQL , 计数器 , 序列 , 自增值


背景

数据库中,自增序列是常见的需求,例如计数,主键,唯一值,或者自动生成的流水号等等。

因此序列这个功能就应运而生,序列的功能在很多商业数据库中都支持需求,PostgreSQL当然也支持,而且更好用。

在PostgreSQL中可以创建多个序列,设置序列的起始值,步长,缓存大小,是否轮回等。

postgres=# \h create sequence
Command:     CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

序列的应用场景

1. 作为字段默认值,default nextval('序列名');

postgres=# create table seq_test(id serial, info text);
CREATE TABLE
postgres=# \d+ seq_test
                                             Table "public.seq_test"
 Column |  Type   |                       Modifiers                       | Storage  | Stats target | Description 
--------+---------+-------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('seq_test_id_seq'::regclass) | plain    |              | 
 info   | text    |                                                       | extended |              | 

2. 作为继承表的分布键

例如某个表有64个继承表,为了区分每个继承表的数据,可以将继承表的主键都按64取模,得到的结果不同来区分,使用步长很好的解决这个问题。

create sequence seq0 minvalue 0 increment by 64 start with 0 ;
create sequence seq1 minvalue 0 increment by 64 start with 1 ;
...
create table tbl0(id int default nextval('seq0'), .....);
create table tbl1(id int default nextval('seq1'), .....);
...

3. 作为计数器

select nextval('序列名');

4. 其他

那么PostgreSQL序列的性能怎样呢?

http://thebuild.com/blog/2015/10/30/dont-assume-postgresql-is-slow/

这个是某位网友的测试,我接下来会在某个32核的机器上测试一下,序列值的生成性能将达到每秒4.5千万。

Don’t Assume PostgreSQL is Slow

You can’t build a real-life system without caching.

That being said, it’s often the case that parts of the system you think are going to be slow aren’t. 

I’ve noticed a tendency to build out a huge stack of components (”we’ll have PostgreSQL, and Redis, and Celery, and Varnish, and…”) without actually measuring where the bottlenecks are.

Example: A counter.

  Suppose you need a global counter for something. 

  It needs to be super-fast, and available across all of the web front ends. 

  It’s not transactional (you never “uncount” based on a rollback), but you do want it to be persistent.

Option 1: Drop Redis into the stack, use INCR to keep the counter, and have some other process that reads the counter and spills it into PostgreSQL, 

  then have some other process that picks up the count when Redis starts and initializes it 

  (or be smart enough to read from both places and add them when yo need it), and accept that there are windows in which you might use counts.

Option 2: Use SERIAL in PostgreSQL.

But option 2 is really really really slow compared to super-ultra-fast Redis, right?

Not really (test on an Amazon i2-2xlarge instance, client over local sockets, Python client):

  Ten million INCRs in Redis: 824 seconds.

  Ten million SELECT nextval('') in PostgreSQL: 892 seconds.

So: Slower. 6.8 microseconds per increment slower. And no elaborate Redis tooling.

So, build for operation, apply load, then decide what to cache. Human intuition about what might be slow is almost certainly wrong.

32核序列生成性能测试数据

使用unix socket连接,厕所12种场景,每次取多条时,统一为每次取10万条。

1. 单个序列(nocache),1个客户端,每个客户端,每次取一条

create sequence seq;

vi test.sql
select nextval('seq');

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1 -T 100

tps = 44112.339267 (including connections establishing)
tps = 44114.552052 (excluding connections establishing)
4.4万/s

瓶颈

perf record -ag -p   PID

perf report --stdio
     3.58%  postgres  [kernel.kallsyms]      [k] _spin_lock_irqsave
     3.15%  postgres  postgres               [.] AllocSetAlloc.lto_priv.1064
     2.17%  postgres  postgres               [.] PostgresMain

2. 单个序列(nocache),1个客户端,每个客户端,每次取多条

vi test.sql
select nextval('seq') from generate_series(1,100000);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1 -T 100

tps = 12.785754 (including connections establishing)
tps = 12.786493 (excluding connections establishing)
127.8万/s

瓶颈

     5.77%  postgres  postgres            [.] int8out
     4.28%  postgres  postgres            [.] SearchCatCache
     3.84%  postgres  libc-2.12.so        [.] memcpy

3. 单个序列(cache),1个客户端,每个客户端,每次取一条

postgres=# alter sequence seq cache 100000;
ALTER SEQUENCE

tps = 45007.157046 (including connections establishing)
tps = 45009.591614 (excluding connections establishing)
4.5万/s

瓶颈

     3.47%  postgres  [kernel.kallsyms]      [k] _spin_lock_irqsave
     3.05%  postgres  postgres               [.] AllocSetAlloc.lto_priv.1064
     2.44%  postgres  [kernel.kallsyms]      [k] _spin_lock

4. 单个序列(cache),1个客户端,每个客户端,每次取多条

tps = 16.761021 (including connections establishing)
tps = 16.761574 (excluding connections establishing)
167.6万/s

瓶颈

     7.62%  postgres  postgres           [.] int8ou
     5.45%  postgres  postgres           [.] SearchCatCache
     4.94%  postgres  postgres           [.] AllocSetAlloc.lto_priv.1064
     4.65%  postgres  libc-2.12.so       [.] memcpy
     4.32%  postgres  postgres           [.] hash_search_with_hash_value

5. 单个序列(nocache),64个客户端,每个客户端,每次取一条

postgres=# alter sequence seq cache 1;
ALTER SEQUENCE

vi test.sql
select nextval('seq');

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100

tps = 418597.316363 (including connections establishing)
tps = 418849.992275 (excluding connections establishing)
41.8万/s

瓶颈

     5.41%  postgres  postgres               [.] LWLockWaitListLock.lto_priv.1132
     3.33%  postgres  postgres               [.] GetSnapshotData
     3.21%  postgres  [kernel.kallsyms]      [k] _spin_lock

6. 多个序列(nocache),64个客户端,每个客户端,每次取一条

postgres=# do language plpgsql     
declare
begin
  for i in 0..63 loop
    execute 'create sequence seq_'||i||' increment by 64 MINVALUE 0 start '||i;
  end loop;
end;
    ;

vi test.sql
\set id random(0,63)
select nextval('seq_'||:id);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100

tps = 1078827.770563 (including connections establishing)
tps = 1079469.007184 (excluding connections establishing)
107万/s

7. 单个序列(nocache),64个客户端,每个客户端,每次取多条

vi test.sql
select nextval('seq') from generate_series(1,100000);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100

tps = 6.671587 (including connections establishing)
tps = 6.675305 (excluding connections establishing)
66.7万/s

瓶颈

    24.25%  postgres  postgres            [.] LWLockWaitListLock.lto_priv.1132
            |
            --- LWLockWaitListLock.lto_priv.1132

     8.07%  postgres  postgres            [.] LWLockAcquire.constprop.859
            |
            --- LWLockAcquire.constprop.859

     6.38%  postgres  postgres            [.] LWLockDequeueSelf.lto_priv.1133
            |
            --- LWLockDequeueSelf.lto_priv.1133

8. 多个序列(nocache),64个客户端,每个客户端,每次取多条

vi test.sql
\set id random(0,63)
select nextval('seq_'||:id) from generate_series(1,100000);

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100

tps = 15.196673 (including connections establishing)
tps = 15.206898 (excluding connections establishing)
152万/s

瓶颈

pgbench分配的随机值不够均匀,可能导致冲突依旧存在,因此性能并没有上去多少。

如果使用更多的序列,可以看到无冲突情况下,多并发取值的效果。

9. 单个序列(cache),64个客户端,每个客户端,每次取一条

postgres=# alter sequence seq cache 100000;
ALTER SEQUENCE

vi test.sql
select nextval('seq');

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 100

tps = 1323611.827976 (including connections establishing)
tps = 1324468.027166 (excluding connections establishing)
132万/s

瓶颈

    10.06%  postgres  postgres               [.] s_lock
     8.31%  postgres  postgres               [.] GetSnapshotData
     2.87%  postgres  postgres               [.] AllocSetAlloc.lto_priv.1064
     2.03%  postgres  postgres               [.] LWLockRelease

10. 多个序列(cache),64个客户端,每个客户端,每次取一条

postgres=# do language plpgsql     
declare
begin
  for i in 0..63 loop
    execute 'alter sequence seq_'||i||' cache 100000';
  end loop;
end;
    ;

tps = 1286465.725029 (including connections establishing)
tps = 1287261.097736 (excluding connections establishing)
128万/s

11. 单个序列(cache),64个客户端,每个客户端,每次取多条

tps = 455.050231 (including connections establishing)
tps = 455.313609 (excluding connections establishing)
4550万/s

瓶颈

     6.20%  postgres  postgres            [.] int8out
     5.44%  postgres  postgres            [.] SearchCatCache
     5.38%  postgres  libc-2.12.so        [.] memcpy
     4.04%  postgres  postgres            [.] AllocSetAlloc.lto_priv.1064
     3.95%  postgres  postgres            [.] printtup.lto_priv.1769

12. 多个序列(cache),64个客户端,每个客户端,每次取多条

tps = 260.329785 (including connections establishing)
tps = 260.488070 (excluding connections establishing)
2603万/s

瓶颈

nextval属于volatile函数,||拼接序列名,带来了操作符的开销
     7.31%  postgres  postgres              [.] SearchCatCache
     6.55%  postgres  postgres              [.] AllocSetAlloc.lto_priv.1064
     3.20%  postgres  libc-2.12.so          [.] __strlen_sse42
     3.13%  postgres  postgres              [.] ExecMakeFunctionResultNoSets
     3.06%  postgres  postgres              [.] nocachegetattr

小结

如果需要较为正确的perf诊断,PG的编译参数要改一下。

《PostgreSQL 源码性能诊断(perf profiling)指南》

1. 序列的cache是会话层级的,例如一次cache 100个序列值,那么一次会消耗100,如果会话退出,这100个没有被用完的序列值也会被消耗掉。

cache的好处是减少锁冲突。

如果你不能用cache,那么可以用多个序列来解决锁冲突的问题。

如果需要并轨,只需要将多个序列的步长设为一样,同时起始值错开即可并轨。

2. 单个序列值,并发批量取序列值,如何提升性能?

设置cache

3. 并发单步取序列值,不允许cache的情况下,如何提升性能?

使用多个序列,每个会话对应一个序列,多个序列要求步长一致,起始值不一致即可。

4. 从上面的测试情况来看,理论上多个序列的性能应该更好,但是测试使用了||来拼接出序列名,带来了额外的开销,所以性能并未体现有些。

以单序列,批量并行取序列值的最好成绩来估算,每秒生成4550万自增值,那么一天可以生成3.9万亿个自增值,你想让它成为瓶颈都难。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
10月前
|
缓存 关系型数据库 PostgreSQL
PostgreSQL自增ID的初始化语句
【5月更文挑战第10天】PostgreSQL自增ID的初始化语句
274 2
|
10月前
|
缓存 关系型数据库 MySQL
postgresql|数据库|序列Sequence的创建和管理
postgresql|数据库|序列Sequence的创建和管理
240 0
|
关系型数据库 Java 数据库
使用jpa在postgresql数据库中创建主键自增表
jpa依赖 org.springframework.boot spring-boot-starter-data-jpa org.
3435 0
|
Ubuntu 关系型数据库 PostgreSQL
Docker Compose 部署 PostgreSQL 单机版——基于Ubuntu (含有pgadmin)
Docker Compose 部署 PostgreSQL 单机版——基于Ubuntu (含有pgadmin)
688 0
|
关系型数据库 PostgreSQL Docker
Docker 安装 PostgreSQL 单机版——基于Ubuntu
Docker 安装 PostgreSQL 单机版——基于Ubuntu
464 0
|
关系型数据库 PostgreSQL
『PostgreSQL』PGSQL手动创建Sequence序列
📣读完这篇文章里你能收获到 - 在PostgreSQL中对Sequence的管理及使用
750 0
|
存储 关系型数据库 数据库
分布式 PostgreSQL 集群(Citus)官方示例 - 时间序列数据
分布式 PostgreSQL 集群(Citus)官方示例 - 时间序列数据
276 0
分布式 PostgreSQL 集群(Citus)官方示例 - 时间序列数据
|
关系型数据库 MySQL 数据库
PostgreSQL数据库实现表字段的自增
PostgreSQL数据库实现表字段的自增
2030 0
|
SQL 关系型数据库 测试技术
PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 2 - 单机分区表 (dblink 异步调用并行) (4亿图像)
标签 PostgreSQL , imgsmlr , GiST , 图像特征值 , 小波转换 背景 续 《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试 1 - 单机单表 (4亿图像)》 使用分区表+dblink异步接口并行调用。
1539 0
|
关系型数据库 测试技术 开发工具
PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 1 - 单机单表 (4亿图像)
标签 PostgreSQL , imgsmlr , GiST , 图像特征值 , 小波转换 背景 imgsmlr是PostgreSQL的一款支持以图搜图的插件, https://github.com/postgrespro/imgsmlr 这个插件新增了: 1、几种图像特征值数据类型, 2、图像特征值相似算子, 3、图像特征值相似排序索引支持, 4、图像相似排序的索引(通过扩展GiST索引接口实现)支持, 5、png,gif等图像格式特征值提取函数。
2406 0

相关产品

  • 云原生数据库 PolarDB