Subject: Re: MultiXact\SLRU buffers configuration
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Alexander Korotkov <aekorotkov@gmail.com>,
 Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
 Daniel Gustafsson <daniel@yesql.se>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <a7f1c4e1-1015-92a4-2bd4-6736bd13d03e@postgrespro.ru>
 <2087E87D-44CA-4443-8E6A-5087F07443F4@yandex-team.ru>
 <CAPpHfduekT=BrM3rLqvo4ajzKYmDk+6aCoYJrNHhK0Je+v2z=Q@mail.gmail.com>
 <8A94938B-054C-4439-9866-2C220B4D0DD7@yandex-team.ru>
 <CAPpHfduT0Kb+8fMt4t0EF5G_3hig+wYgeRadQnNq20F4VxP7LA@mail.gmail.com>
 <CAPpHfdsUtSGxRrKn65F_wrE66sNztNY4TpDGDHh2kvAZZDOMqA@mail.gmail.com>
 <20201028013651.de5cj2xadgmba5nf@development>
 <13D8FD63-559A-4737-B7FD-05288D1CEF8B@yandex-team.ru>
 <20201028233243.ygm6yqlynkqpzekr@development>
 <43F3DE92-F236-4EA5-B4D6-39BEF6BD849D@yandex-team.ru>
 <20201029134933.xd4mh2cofuf6tdfz@development>
 <65C1B4BA-D16F-4939-978B-AC8F370F5A5E@yandex-team.ru>
 <9b4d17df-b811-8323-16be-3cab913216d1@enterprisedb.com>
 <A64B924A-B08E-417D-BD3D-D396D722053B@yandex-team.ru>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
Message-ID: <35862787-8b4d-a290-789e-6e12dc6527e8@enterprisedb.com>
Date: Tue, 10 Nov 2020 19:07:07 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.4.0
MIME-Version: 1.0
In-Reply-To: <A64B924A-B08E-417D-BD3D-D396D722053B@yandex-team.ru>
Content-Type: multipart/mixed;
 boundary="------------A131C6BE314487D1368E8D38"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------A131C6BE314487D1368E8D38
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit


On 11/10/20 7:16 AM, Andrey Borodin wrote:
> 
> 
>> 10 нояб. 2020 г., в 05:13, Tomas Vondra <tomas.vondra@enterprisedb.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

--------------A131C6BE314487D1368E8D38
Content-Type: application/x-shellscript;
 name="collect-wait-events.sh"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="collect-wait-events.sh"

IyEvYmluL2Jhc2gKCndoaWxlIC9iaW4vdHJ1ZTsgZG8KCglwc3FsIC10IC1BIC1jICdzZWxl
Y3QgZXh0cmFjdChlcG9jaCBmcm9tIG5vdygpKSwgd2FpdF9ldmVudF90eXBlLCB3YWl0X2V2
ZW50LCBjb3VudCgqKSBmcm9tIHBnX3N0YXRfYWN0aXZpdHkgZ3JvdXAgYnkgMSwgMiwgMycg
dGVzdAoKCXBzcWwgLXQgLUEgLWMgJ3NlbGVjdCBwZ19zbGVlcCgwLjEpJyB0ZXN0ID4gL2Rl
di9udWxsIDI+JjEKCmRvbmUK
--------------A131C6BE314487D1368E8D38--