Re: MultiXact\SLRU buffers configuration

On 11/10/20 7:16 AM, Andrey Borodin wrote:
>
>
>> 10 нояб. 2020 г., в 05:13, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> написал(а):
>> After the issue reported in [1] got fixed, I've restarted the multi-xact
>> stress test, hoping to reproduce the issue. But so far no luck :-(
>
>
> Tomas, many thanks for looking into this. I figured out that to make multixact sets bigger transactions must hang for a while and lock large set of tuples. But not continuous range to avoid locking on buffer_content.
> I did not manage to implement this via pgbench, that's why I was trying to hack on separate go program. But, essentially, no luck either.
> I was observing something resemblant though
>
> пятница, 8 мая 2020 г. 15:08:37 (every 1s)
>
> pid | wait_event | wait_event_type | state | query
> -------+----------------------------+-----------------+--------+----------------------------------------------------
> 41344 | ClientRead | Client | idle | insert into t1 select generate_series(1,1000000,1)
> 41375 | MultiXactOffsetControlLock | LWLock | active | select * from t1 where i = ANY ($1) for share
> 41377 | MultiXactOffsetControlLock | LWLock | active | select * from t1 where i = ANY ($1) for share
> 41378 | | | active | select * from t1 where i = ANY ($1) for share
> 41379 | MultiXactOffsetControlLock | LWLock | active | select * from t1 where i = ANY ($1) for share
> 41381 | | | active | select * from t1 where i = ANY ($1) for share
> 41383 | MultiXactOffsetControlLock | LWLock | active | select * from t1 where i = ANY ($1) for share
> 41385 | MultiXactOffsetControlLock | LWLock | active | select * from t1 where i = ANY ($1) for share
> (8 rows)
>
> but this picture was not stable.
>

Seems we haven't made much progress in reproducing the issue :-( I guess
we'll need to know more about the machine where this happens. Is there
anything special about the hardware/config? Are you monitoring size of
the pg_multixact directory?

> How do you collect wait events for aggregation? just insert into some table with cron?
>

No, I have a simple shell script (attached) sampling data from
pg_stat_activity regularly. Then I load it into a table and aggregate to
get a summary.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company