Bug #18490

Permission table bloat

Added by Peter Amstutz about 2 months ago. Updated about 2 months ago.

Status:
Resolved
Priority:
Normal
Assigned To:
Category:
API
Target version:
Start date:
12/01/2021
Due date:
% Done:

100%

Estimated time:
(Total: 0.00 h)
Story points:
-
Release relationship:
Auto

Description

Postgres seems to be creating a lot of fragmentation in the materialized_permissions table in a context with a lot of users (~1000) and large groups. Reported the table size went from 200MB to 2GB to 20 GB in the span of a few days (? or even faster).

The group sync script is very slow in this context while there are other updates ongoing.

Normal usage (e.g. navigating a large project with lots of subprojects in a read-only arv-mount) is also very slow when the table is fragmented that way.

Vacuum full helps but can often not complete because normal usage patterns prevent it from completing.

This has been observed on Postgres 9.5 and 13.


Subtasks

Task #18510: ReviewResolvedPeter Amstutz

Associated revisions

Revision b97c5edf (diff)
Added by Peter Amstutz about 2 months ago

Merge branch '18490-redundant-updates' refs #18490

Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <>

History

#1 Updated by Ward Vandewege about 2 months ago

  • Description updated (diff)

#2 Updated by Ward Vandewege about 2 months ago

  • Description updated (diff)

#3 Updated by Ward Vandewege about 2 months ago

  • Description updated (diff)

#4 Updated by Peter Amstutz about 2 months ago

18490-redundant-updates @ 26aa25c76d3ea4285e724fe874c76aa9da03b4c9

Add a where clause to "insert on conflict update" to suppress redundant updates of rows that haven't changed.

https://ci.arvados.org/view/Developer/job/developer-run-tests/2820/

#5 Updated by Peter Amstutz about 2 months ago

  • Assigned To set to Peter Amstutz
  • Status changed from New to In Progress
  • Category set to API
  • Subject changed from Research postgres tuning for tables with lots of updates to Permission table bloat

Feedback from customer after emailing them and they applied a hotfix from #note-4:

"This seems to have solved the issue. We had over 100 new users added to a large group, and the table is currently sitting at 273MB. I'll continue to monitor it to check for any changes but it definitely looks promising."

#6 Updated by Peter Amstutz about 2 months ago

  • Description updated (diff)

#7 Updated by Lucas Di Pentima about 2 months ago

My only observation is that it would be convenient to add a comment explaining the workaround, just for future reference. Apart from that, LGTM.

#8 Updated by Peter Amstutz about 2 months ago

Lucas Di Pentima wrote:

My only observation is that it would be convenient to add a comment explaining the workaround, just for future reference. Apart from that, LGTM.

Yea, that's a good idea. Will do.

#9 Updated by Peter Amstutz about 2 months ago

  • Status changed from In Progress to Resolved

#10 Updated by Peter Amstutz about 2 months ago

  • Release set to 48

Also available in: Atom PDF