Opened 8 years ago

Closed 3 years ago

#2923 closed enhancement (wontfix)

Improve materialized views in the metrics database

Reported by: karsten Owned by:
Priority: High Milestone:
Component: Metrics/Website Version:
Severity: Normal Keywords:
Cc: Actual Points:
Parent ID: Points:
Reviewer: Sponsor:

Description (last modified by iwakeh)

The metrics database schema uses periodically updated tables similar to materialized views for aggregating statistics. When inserting data into the database, we write the dates that have changed to a separate updates table. Every three hours, we delete the aggregates for these days and recompute them, which takes a few minutes.

The recompute step that takes most of the time is refresh_user_stats(), which is no surprise given the complexity of that function. We should try to simplify this function, possibly by pre-computing partial results that can be reused for other statistics. Ideally, recomputing aggregates should run in under one minute, given that we want to add more materialized views for more aggregate statistics in the future. In particular, I'd like to know which particular SQL parts slow us down in order to avoid them in the future.

Child Tickets

Change History (4)

comment:1 Changed 8 years ago by karsten

Component: MetricsMetrics Website

comment:2 Changed 8 years ago by karsten

Owner: karsten deleted
Priority: normalmajor
Status: newassigned

I'm not working on this, though it would be good if someone did. We're only updating stats once per day, because it takes roughly 1 hour to do so. Changing owner to None.

comment:3 Changed 3 years ago by iwakeh

Description: modified (diff)
Severity: Normal

If this is still a problem I'd suggest using postgres analysis options in order to find out if adding an index here and there would help.

(Corrected the SQL file link in the description.)

comment:4 Changed 3 years ago by karsten

Resolution: wontfix
Status: assignedclosed

I'd say this ticket lost its relevance 2 years ago with this commit. Closing. Thanks for the suggestion though!

Note: See TracTickets for help on using tickets.