作者
digoal
日期
2018-07-16
标签
PostgreSQL , 异步消息 , notify , listen , queue
背景
PostgreSQL异步消息功能的一些应用：
《PostgreSQL 流式处理应用实践 - 二手商品实时归类》
《PostgreSQL 事件触发器应用 - DDL审计记录 + 异步通知(notify)》
《从电波表到数据库小程序之 - 数据库异步广播(notify/listen)》
《use PostgreSQL async Notification as a chat group》
《PostgreSQL Notify/Listen Like ESB》
那么一条异步消息支持多大的容量，当客户端消费堵塞时，数据库端最多可以HOLD多少条异步消息(或者多少容量)呢？
单条异步消息的上限
单条异步消息的上限
```
/
* Maximum size of a NOTIFY payload, including terminating NULL. This
* must be kept small enough so that a notification message fits on one
* SLRU page. The magic fudge factor here is noncritical as long as it's
* more than AsyncQueueEntryEmptySize --- we make it significantly bigger
* than that, so changes in that data structure won't affect user-visible
* restrictions.
/
define NOTIFY_PAYLOAD_MAX_LENGTH (BLCKSZ - NAMEDATALEN - 128)
```
异步消息结构
/*
* Struct representing an entry in the global notify queue
*
* This struct declaration has the maximal length, but in a real queue entry
* the data area is only big enough for the actual channel and payload strings
* (each null-terminated). AsyncQueueEntryEmptySize is the minimum possible
* entry size, if both channel and payload strings are empty (but note it
* doesn't include alignment padding).
*
* The "length" field should always be rounded up to the next QUEUEALIGN
* multiple so that all fields are properly aligned.
*/
typedef struct AsyncQueueEntry
{
int length; /* total allocated length of entry */
Oid dboid; /* sender's database OID */
TransactionId xid; /* sender's XID */
int32 srcPid; /* sender's PID */
char data[NAMEDATALEN + NOTIFY_PAYLOAD_MAX_LENGTH];
} AsyncQueueEntry;
数据库端最多可以HOLD多少异步消息
```
/
* Define SLRU segment size. A page is the same BLCKSZ as is used everywhere
* else in Postgres. The segment size can be chosen somewhat arbitrarily;
* we make it 32 pages by default, or 256Kb, i.e. 1M transactions for CLOG
* or 64K transactions for SUBTRANS.
* Note: because TransactionIds are 32 bits and wrap around at 0xFFFFFFFF,
* page numbering also wraps around at 0xFFFFFFFF/xxxx_XACTS_PER_PAGE (where
* xxxx is CLOG or SUBTRANS, respectively), and segment numbering at
* 0xFFFFFFFF/xxxx_XACTS_PER_PAGE/SLRU_PAGES_PER_SEGMENT. We need
* take no explicit notice of that fact in slru.c, except when comparing
* segment and page numbers in SimpleLruTruncate (see PagePrecedes()).
*/
define SLRU_PAGES_PER_SEGMENT 32
```
* The amount of shared memory used for notify management (NUM_ASYNC_BUFFERS)
* can be varied without affecting anything but performance. The maximum
* amount of notification data that can be queued at one time is determined
* by slru.c's wraparound limit; see QUEUE_MAX_PAGE below.
```
/
* slru.c currently assumes that all filenames are four characters of hex
* digits. That means that we can use segments 0000 through FFFF.
* Each segment contains SLRU_PAGES_PER_SEGMENT pages which gives us
* the pages from 0 to SLRU_PAGES_PER_SEGMENT * 0x10000 - 1.
* It's of course possible to enhance slru.c, but this gives us so much
* space already that it doesn't seem worth the trouble.
* The most data we can have in the queue at a time is QUEUE_MAX_PAGE/2
* pages, because more than that would confuse slru.c into thinking there
* was a wraparound condition. With the default BLCKSZ this means there
* can be up to 8GB of queued-and-not-read data.
* Note: it's possible to redefine QUEUE_MAX_PAGE with a smaller multiple of
* SLRU_PAGES_PER_SEGMENT, for easier testing of queue-full behaviour.
*/
define QUEUE_MAX_PAGE (SLRU_PAGES_PER_SEGMENT * 0x10000 - 1)
```
当blocksize, pagesize=8KB时，最大可以HOLD约16GB。
8k * 65535 = 16GB
被HOLD的异步消息存在哪里？
```
digoal@iZbp13nu0s9j3x3op4zpd4Z-> ll
total 300K
-rw------- 1 digoal digoal 193 Nov 8 2017 backup_label.old
drwx------ 9 digoal digoal 4.0K Dec 15 2017 base
-rw------- 1 digoal digoal 30 Jun 16 11:37 current_logfiles
drwx------ 2 digoal digoal 4.0K Jun 16 11:37 global
drwx------ 2 digoal digoal 4.0K Nov 13 2017 log
drwx------ 2 digoal digoal 4.0K Nov 7 2017 pg_commit_ts
drwx------ 2 digoal digoal 4.0K Nov 7 2017 pg_dynshmem
-rw------- 1 digoal digoal 4.5K Nov 7 2017 pg_hba.conf
-rw------- 1 digoal digoal 1.6K Nov 7 2017 pg_ident.conf
drwx------ 4 digoal digoal 4.0K Jun 16 11:37 pg_logical
drwx------ 4 digoal digoal 4.0K Nov 7 2017 pg_multixact
drwx------ 2 digoal digoal 36K Jun 16 11:37 pg_notify
drwx------ 4 digoal digoal 4.0K Dec 27 2017 pg_replslot
drwx------ 2 digoal digoal 4.0K Nov 7 2017 pg_serial
drwx------ 2 digoal digoal 4.0K Nov 7 2017 pg_snapshots
drwx------ 2 digoal digoal 4.0K Jun 16 11:37 pg_stat
drwx------ 2 digoal digoal 4.0K Jun 16 11:37 pg_stat_tmp
drwx------ 2 digoal digoal 132K Dec 27 2017 pg_subtrans
drwx------ 2 digoal digoal 4.0K Nov 9 2017 pg_tblspc
drwx------ 2 digoal digoal 4.0K Nov 7 2017 pg_twophase
-rw------- 1 digoal digoal 3 Nov 7 2017 PG_VERSION
lrwxrwxrwx 1 digoal digoal 22 Nov 7 2017 pg_wal -> /data02/pg/pg_wal_1999
drwx------ 2 digoal digoal 20K Dec 27 2017 pg_xact
-rw------- 1 digoal digoal 2.5K Jan 11 2018 postgresql.auto.conf
-rw------- 1 digoal digoal 23K Jan 11 2018 postgresql.conf
-rw------- 1 digoal digoal 34 Jun 16 11:37 postmaster.opts
digoal@iZbp13nu0s9j3x3op4zpd4Z-> cd pg_notify/
digoal@iZbp13nu0s9j3x3op4zpd4Z-> ll
total 8.0K
-rw------- 1 digoal digoal 8.0K Jun 16 11:37 0000
```
参考
src/include/access/slru.h
src/backend/commands/async.c
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
