Changes between Initial Version and Version 3 of Ticket #2923


Ignore:
Timestamp:
Jul 21, 2016, 4:18:53 PM (4 years ago)
Author:
iwakeh
Comment:

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.)

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #2923

    • Property Status changed from new to assigned
    • Property Component changed from Metrics to Metrics Website
    • Property Priority changed from normal to major
    • Property Owner karsten deleted
    • Property Severity changed from to Normal
  • Ticket #2923 – Description

    initial v3  
    1 The [https://gitweb.torproject.org/metrics-web.git/blob/HEAD:/db/tordir.sql 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.
     1The [https://gitweb.torproject.org/metrics-web.git/tree/modules/legacy/db/tordir.sql 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.
    22
    33The 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.