Re: Impact of multixact "members" limit exceeded

Hey Lawrence,

Thanks for the input, I will check if this is the case, I do not believe it
is but its worth checking out. To me it looks like normal inserts.
I think we were just insanely behind on the vacuum. I have another server,
same code for the App on top of the DB which does not have this issue.

For sure an upgrade to PG 9.5 or 9.6 will solve ALOT of issues.
Again thanks for the input, always appreciated!

Best regards

Daniel

On Thu, Jun 14, 2018 at 9:03 AM, Lawrence Jones <lawrjone(at)gmail(dot)com> wrote:

> Hey Daniel,
>
> This may be entirely unrelated to your query, but we’ve previously
> experienced issues with 9.4 and crazy multixact members growth.
>
> After digging into the issue, we found the culprit was code that would
> perform the following actions:
>
> begin;
> for query in many_queries:
> savepoint <x>;
> select * from table where id=‘myid' for update;
> update table increment(counter) where id=‘myid';
> release savepoint;
> commit;
>
> While I found it very difficult to find documentation on the purpose of
> multixacts, and I’m certain to have critical misunderstandings around their
> workings, it seemed that Postgres was inferring each new savepoint as a new
> potential transaction that would lock the given row, and each of those
> transactions needed to be added to the on-going multixact members list.
>
> The members list (I believe) is immutable, and adding a new member
> requires construction of a new members list, and so your multixact members
> offset will grow quadratically with the number of times you lock your row.
> Where we were locking repeatedly in a large loop, we were seeing huge
> multixact members growth that would trigger the override for multixact
> vacuums, regardless of disabling or configuring your autovacuum.
>
> If you’re continually seeing multixact members growth that outpaces your
> vacuums, then I suggest having a scan for the above usage pattern, or
> alternatively upgrading. We couldn’t replicate the growth in 9.5 and above,
> as presumably newer Postgres versions correctly no-op when a transaction
> tries locking a row when its parent is already present in the multixact.
>
> This comes with the disclaimer of a Postgres amateur, it simply seemed
> relevant to your situation.
>
> Lawrence
>
>
> On 14 Jun 2018, at 04:47, Daniel Lagerman <spjheruur(at)gmail(dot)com> wrote:
>
> Hello Álvaro,
>
> I'm running at 9.4.3, I know its an older version but upgrading it outside
> the scope right now for various reasons.
> Based on the settings I posted what would be your recommended settings
> right now to resolve this situation?
>
> Maintenance is limited to weekends as this is a database which needs to be
> online 24/5 and consumes a lot of data and responds to many queries.
>
> I very much appreciate the recommendation but I would also like to now the
> impact of this warning, data right now does not seem to be corrupted, get
> overwritten, for that matter not insert new data or cause issues when read.
> It just seems that is has stopped creating "members" and I can't read into
> what problem that would cause, table was around 1.6 TB, it was cleaned up
> and the re-packed using pg-repack to go down to around 400 GB in size,
> however a freeze vacuum or autovacuum did not complete.
>
> Then when it comes to vacuum, do you think its worth increasing work_mem
> to say 200 GB, for the maintenance period only, and let autovacuum go to
> town on the tables with the highest age and lowest minmixid? Or should one
> run for example flexible freeze instead with zero pauses as nothing is
> happening on the weekends, which means no inbound transactions. Also worth
> mentioning is thatI restart the server on the weekend after I have backed
> up the data.
>
> I have tried running autovacuum for 40 hours before with 5 GB work_mem, on
> the weekend, across 6 tables at the same time and it never completed on the
> bigger tables which are around 2 TB.
>
> Best regards
>
> Daniel
>
>
> On Wed, Jun 13, 2018 at 11:29 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com
> > wrote:
>
>> On 2018-Jun-13, Daniel Lagerman wrote:
>>
>> > Hello!
>> >
>> > I have a question where I'm trying to determine the seriousness of the
>> > problem.
>>
>> What's your server version?
>>
>> The way to reclaim members space is to move forwards the
>> pg_database.datminmxid value from the database where it is oldest; and
>> the way to move *that* forward is to advance the pg_class.relminmxid
>> value from the tables in which it is oldest. So you need to set a low
>> value vacuum_multixact_freeze_min_age and _table_age and vacuum those
>> tables.
>>
>> --
>> Álvaro Herrera https://www.2ndQuadrant.com/
>> <https://www.2ndquadrant.com/>
>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>
>
>
>