Skip to content

Resolve "Unable to view project audit events: statement timeouts"

Patrick Bair requested to merge 39474-unable-to-view-project-audit-events-statement-timeouts into master

What does this MR do?

Add a new index on the audit_events table, with columns (entity_id, entity_type, id desc).

Issue: #39474 (closed)

Benefit:

The previous query plan for the problematic query seen in the linked issue:

 Limit  (cost=20103.72..20103.77 rows=20 width=211) (actual time=48723.352..48723.371 rows=20 loops=1)
   Buffers: shared hit=3264 read=45543 dirtied=860 written=12393
   I/O Timings: read=44655.212 write=2762.186
   ->  Sort  (cost=20103.72..20130.32 rows=10642 width=211) (actual time=48723.318..48723.322 rows=20 loops=1)
         Sort Key: audit_events.id DESC
         Sort Method: top-N heapsort  Memory: 35kB
         Buffers: shared hit=3264 read=45543 dirtied=860 written=12393
         I/O Timings: read=44655.212 write=2762.186
         ->  Index Scan using index_audit_events_on_entity_id_and_entity_type on public.audit_events  (cost=0.57..19820.54 rows=10642 width=211) (actual time=11.616..48647.458 rows=49227 loops=1)
               Index Cond: ((audit_events.entity_id = 278964) AND ((audit_events.entity_type)::text = 'Project'::text))
               Buffers: shared hit=3261 read=45543 dirtied=860 written=12393
               I/O Timings: read=44655.212 write=2762.186

And the new query plan with the index in place:

 Limit  (cost=0.57..37.76 rows=20 width=211) (actual time=0.585..0.688 rows=20 loops=1)
   Buffers: shared hit=20 read=4
   I/O Timings: read=0.451
   ->  Index Scan using index_testing_id_desc on public.audit_events  (cost=0.57..18081.72 rows=9724 width=211) (actual time=0.550..0.650 rows=20 loops=1)
         Index Cond: ((audit_events.entity_id = 278964) AND ((audit_events.entity_type)::text = 'Project'::text))
         Buffers: shared hit=20 read=4
         I/O Timings: read=0.451

Considerations:

  • In the linked issue @abrandl suggested using partial indexes for each of the entity_type values, which currently is only User, Project and Group. I didn't test that specifically, but it seems like a good suggestion. The main reason I didn't attempt that approach was the concern that a new entity_type value could be added in the future, which specific partial indexes wouldn't cover. Open to thoughts of whether that is a realistic concern.
  • The existing index on (entity_id, entity_type) will be redundant after this change.
  • Still see gradual degradation in performance as we paginate back through the entries.

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by 🤖 GitLab Bot 🤖

Merge request reports