Re: FW: huge SubtransSLRU and SubtransBuffer wait_event

Hi Alvaro ,
looks like Xact slru buffer use a different way to control size, do we
need to increase Xact and how to increase that ? we plan to increase to
20 times size of existing buffers, any side impact to 20 times increase
these subtrans ?
----------------+-------------+-------------+-----------+--------------+-------------+---------+-----------+-------------------------------
CommitTs | 1284048 | 387594150 | 54530 | 1305858 |
0 | 0 | 5 | 2024-01-19 05:01:38.900698+00
MultiXactMember | 30252 | 23852620477 | 48555852 | 26106 |
0 | 127 | 0 | 2024-01-19 05:01:38.900698+00
MultiXactOffset | 10638 | 23865848376 | 18434993 | 9375 |
127 | 127 | 5 | 2024-01-19 05:01:38.900698+00
Notify | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 2024-01-19 05:01:38.900698+00
Serial | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 2024-01-19 05:01:38.900698+00
Subtrans | 513486 | 12127027243 | 153119082 | 431238 |
0 | 0 | 0 | 2024-01-19 05:01:38.900698+00
Xact | 32107 | 22450403108 | 72043892 | 18064 |
0 | 0 | 3 | 2024-01-19 05:01:38.900698+00
other | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 2024-01-19 05:01:38.900698+00
(8 rows)

Thanks,

James

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 於 2024年3月1日週五 下午3:35寫道：

> On 2024-Mar-01, James Pang wrote:
>
> > one question:
> > we need to increase all SLRU buffers together , MULTIXACT, XACT,
> > Subtrans, COMMIT TS , for example, got all of them doubled based on
> > existing size ?
>
> No need.
>
> > or only increase Subtrans , or Subtrans and multixact ?
>
> Just increase the sizes for the ones that are causing you pain. You can
> have a look at pg_stat_slru for some metrics that might be useful in
> determining which are those.
>
> --
> Álvaro Herrera PostgreSQL Developer —
> https://www.EnterpriseDB.com/
>