diff options
author | Yorick Peterse <yorickpeterse@gmail.com> | 2016-10-07 16:24:09 +0300 |
---|---|---|
committer | Yorick Peterse <yorickpeterse@gmail.com> | 2016-10-10 13:27:08 +0300 |
commit | 237c8f66e6608420629503280aaea555ee980022 (patch) | |
tree | fa50e780b93cd9d812383625d987efd45a63f98b /app/models | |
parent | 434d98b22a6381447a9c59cec16fc324ade198df (diff) |
Precalculate trending projects
This commit introduces a Sidekiq worker that precalculates the list of
trending projects on a daily basis. The resulting set is stored in a
database table that is then queried by Project.trending.
This setup means that Unicorn workers no longer _may_ have to calculate
the list of trending projects. Furthermore it supports filtering without
any complex caching mechanisms.
The data in the "trending_projects" table is inserted in the same order
as the project ranking. This means that getting the projects in the
correct order is simply a matter of:
SELECT projects.*
FROM projects
INNER JOIN trending_projects ON trending_projects.project_id = projects.id
ORDER BY trending_projects.id ASC;
Such a query will only take a few milliseconds at most (as measured on
GitLab.com), opposed to a few seconds for the query used for calculating
the project ranks.
The migration in this commit does not require downtime and takes care of
populating an initial list of trending projects.
Diffstat (limited to 'app/models')
-rw-r--r-- | app/models/project.rb | 16 | ||||
-rw-r--r-- | app/models/trending_project.rb | 35 |
2 files changed, 38 insertions, 13 deletions
diff --git a/app/models/project.rb b/app/models/project.rb index 88e4bd14860..74d54e69648 100644 --- a/app/models/project.rb +++ b/app/models/project.rb @@ -375,19 +375,9 @@ class Project < ActiveRecord::Base %r{(?<project>#{name_pattern}/#{name_pattern})} end - def trending(since = 1.month.ago) - # By counting in the JOIN we don't expose the GROUP BY to the outer query. - # This means that calls such as "any?" and "count" just return a number of - # the total count, instead of the counts grouped per project as a Hash. - join_body = "INNER JOIN ( - SELECT project_id, COUNT(*) AS amount - FROM notes - WHERE created_at >= #{sanitize(since)} - AND system IS FALSE - GROUP BY project_id - ) join_note_counts ON projects.id = join_note_counts.project_id" - - joins(join_body).reorder('join_note_counts.amount DESC') + def trending + joins('INNER JOIN trending_projects ON projects.id = trending_projects.project_id'). + reorder('trending_projects.id ASC') end def cached_count diff --git a/app/models/trending_project.rb b/app/models/trending_project.rb new file mode 100644 index 00000000000..27e3732da17 --- /dev/null +++ b/app/models/trending_project.rb @@ -0,0 +1,35 @@ +class TrendingProject < ActiveRecord::Base + belongs_to :project + + # The number of months to include in the trending calculation. + MONTHS_TO_INCLUDE = 1 + + # The maximum number of projects to include in the trending set. + PROJECTS_LIMIT = 100 + + # Populates the trending projects table with the current list of trending + # projects. + def self.refresh! + # The calculation **must** run in a transaction. If the removal of data and + # insertion of new data were to run separately a user might end up with an + # empty list of trending projects for a short period of time. + transaction do + delete_all + + timestamp = connection.quote(MONTHS_TO_INCLUDE.months.ago) + + connection.execute <<-EOF.strip_heredoc + INSERT INTO #{table_name} (project_id) + SELECT project_id + FROM notes + INNER JOIN projects ON projects.id = notes.project_id + WHERE notes.created_at >= #{timestamp} + AND notes.system IS FALSE + AND projects.visibility_level = #{Gitlab::VisibilityLevel::PUBLIC} + GROUP BY project_id + ORDER BY count(*) DESC + LIMIT #{PROJECTS_LIMIT}; + EOF + end + end +end |