PERF: reduce window of consistency on user actions

PERF: reduce window of consistency on user actions

Databases can have a lot of user actions, self joining and running an aggregate on millions of rows can be very costly

This optimisation will reduce the regular window of consistency down to 13 hours, this ensures the job runs much faster

diff --git a/app/jobs/scheduled/ensure_db_consistency.rb b/app/jobs/scheduled/ensure_db_consistency.rb
index dd85fa0..4d60218 100644
--- a/app/jobs/scheduled/ensure_db_consistency.rb
+++ b/app/jobs/scheduled/ensure_db_consistency.rb
@@ -6,22 +6,60 @@ module Jobs
     every 12.hours
 
     def execute(args)
-      UserVisit.ensure_consistency!
-      Group.ensure_consistency!
-      Notification.ensure_consistency!
-      UserAction.ensure_consistency!
-      TopicFeaturedUsers.ensure_consistency!
-      PostRevision.ensure_consistency!
+      start_measure
+
+      [
+        UserVisit,
+        Group,
+        Notification,
+        TopicFeaturedUsers,
+        PostRevision,
+        Topic,
+        Badge,
+        CategoryUser,
+        UserOption,
+        Tag,
+        CategoryTagStat,
+        User,
+        UserAvatar,
+        Category
+      ].each do |klass|
+        klass.ensure_consistency!
+        measure(klass)
+      end
+
+      UserAction.ensure_consistency!(13.hours.ago)
+      measure(UserAction)
+
       UserStat.ensure_consistency!(13.hours.ago)
-      Topic.ensure_consistency!
-      Badge.ensure_consistency!
-      CategoryUser.ensure_consistency!
-      UserOption.ensure_consistency!
-      Tag.ensure_consistency!
-      CategoryTagStat.ensure_consistency!
-      User.ensure_consistency!
-      UserAvatar.ensure_consistency!
-      Category.ensure_consistency!
+      measure(UserStat)
+
+      Rails.logger.debug(format_measure)
+      nil
+    end
+
+    private
+
+    def format_measure
+      result = +"EnsureDbConsitency Times\n"
+      result << @measure_times.map do |name, duration|
+        "  #{name}: #{duration}"
+      end.join("\n")
+      result
+    end
+
+    def start_measure
+      @measure_times = []
+      @measure_start = Process.clock_gettime(Process::CLOCK_MONOTONIC)
     end
+
+    def measure(step = nil)
+      @measure_now = Process.clock_gettime(Process::CLOCK_MONOTONIC)
+      if @measure_start
+        @measure_times << [step, @measure_now - @measure_start]
+      end
+      @measure_start = @measure_now
+    end
+
   end
 end
diff --git a/app/models/user_action.rb b/app/models/user_action.rb
index d660fa5..df79c89 100644
--- a/app/models/user_action.rb
+++ b/app/models/user_action.rb
@@ -302,7 +302,7 @@ class UserAction < ActiveRecord::Base
     end
   end
 
-  def self.synchronize_target_topic_ids(post_ids = nil)
+  def self.synchronize_target_topic_ids(post_ids = nil, limit: nil)
 
     # nuke all dupes, using magic
     builder = DB.build <<~SQL
@@ -319,6 +319,16 @@ class UserAction < ActiveRecord::Base
       user_actions.id > ua2.id
     SQL
 
+    if limit
+      builder.where(<<~SQL, limit: limit)
+        user_actions.target_post_id IN (
+          SELECT target_post_id
+          FROM user_actions
+          WHERE created_at > :limit
+        )
+      SQL
+    end
+
     if post_ids
       builder.where("user_actions.target_post_id in (:post_ids)", post_ids: post_ids)
     end
@@ -336,11 +346,21 @@ class UserAction < ActiveRecord::Base
       builder.where("target_post_id in (:post_ids)", post_ids: post_ids)
     end
 
+    if limit
+      builder.where(<<~SQL, limit: limit)
+        target_post_id IN (
+          SELECT target_post_id
+          FROM user_actions
+          WHERE created_at > :limit
+        )
+      SQL
+    end
+
     builder.exec
   end
 
-  def self.ensure_consistency!
-    self.synchronize_target_topic_ids
+  def self.ensure_consistency!(limit = nil)
+    self.synchronize_target_topic_ids(nil, limit: limit)
   end
 
   def self.update_like_count(user_id, action_type, delta)

GitHub sha: 4fce6484

2 Likes