PostgreSQL 異步消息(LISTEN/NOTIFY)緩存有多大？

标簽

PostgreSQL , 異步消息 , notify , listen , queue

https://github.com/digoal/blog/blob/master/201807/20180716_01.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL異步消息功能的一些應用：

《PostgreSQL 流式處理應用實踐 - 二手商品實時歸類》 《PostgreSQL 事件觸發器應用 - DDL審計記錄 + 異步通知(notify)》 《從電波表到資料庫小程式之 - 資料庫異步廣播(notify/listen)》 《use PostgreSQL async Notification as a chat group》 《PostgreSQL Notify/Listen Like ESB》

那麼一條異步消息支援多大的容量，當用戶端消費堵塞時，資料庫端最多可以HOLD多少條異步消息(或者多少容量)呢？

https://github.com/digoal/blog/blob/master/201807/20180716_01.md#%E5%8D%95%E6%9D%A1%E5%BC%82%E6%AD%A5%E6%B6%88%E6%81%AF%E7%9A%84%E4%B8%8A%E9%99%90 單條異步消息的上限

/*  
 * 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;  
           

https://github.com/digoal/blog/blob/master/201807/20180716_01.md#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AB%AF%E6%9C%80%E5%A4%9A%E5%8F%AF%E4%BB%A5hold%E5%A4%9A%E5%B0%91%E5%BC%82%E6%AD%A5%E6%B6%88%E6%81%AF 資料庫端最多可以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  
           

https://github.com/digoal/blog/blob/master/201807/20180716_01.md#%E5%8F%82%E8%80%83 參考

src/include/access/slru.h

src/backend/commands/async.c