From 617bf40924bc8d627734eb2fb25a9f3980ede5d5 Mon Sep 17 00:00:00 2001 From: Neil Johnson Date: Wed, 25 Apr 2018 17:37:29 +0100 Subject: Generate user daily stats --- synapse/app/homeserver.py | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'synapse/app') diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py index a0e465d644..808567365a 100755 --- a/synapse/app/homeserver.py +++ b/synapse/app/homeserver.py @@ -473,6 +473,9 @@ def run(hs): " changes across releases." ) + def generate_user_daily_visit_stats(): + hs.get_datastore().generate_user_daily_visits() + if hs.config.report_stats: logger.info("Scheduling stats reporting for 3 hour intervals") clock.looping_call(phone_stats_home, 3 * 60 * 60 * 1000) @@ -485,6 +488,9 @@ def run(hs): # be quite busy the first few minutes clock.call_later(5 * 60, phone_stats_home) + clock.looping_call(generate_user_daily_visit_stats, 60 * 1000) + clock.call_later(5 * 60, generate_user_daily_visit_stats) + if hs.config.daemonize and hs.config.print_pidfile: print (hs.config.pid_file) -- cgit 1.5.1 From 5917562b6040448cd26b67fac52d61b41ba08f6d Mon Sep 17 00:00:00 2001 From: Neil Johnson Date: Tue, 1 May 2018 12:12:22 +0100 Subject: 10 mins seems more reasonable that every minute --- synapse/app/homeserver.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'synapse/app') diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py index 808567365a..328ab8032c 100755 --- a/synapse/app/homeserver.py +++ b/synapse/app/homeserver.py @@ -488,7 +488,7 @@ def run(hs): # be quite busy the first few minutes clock.call_later(5 * 60, phone_stats_home) - clock.looping_call(generate_user_daily_visit_stats, 60 * 1000) + clock.looping_call(generate_user_daily_visit_stats, 10 * 60 * 1000) clock.call_later(5 * 60, generate_user_daily_visit_stats) if hs.config.daemonize and hs.config.print_pidfile: -- cgit 1.5.1 From f077e97914c9b5c82c94786130d98af52516cde0 Mon Sep 17 00:00:00 2001 From: Neil Johnson Date: Mon, 14 May 2018 13:50:58 +0100 Subject: instead of inserting user daily visit data at the end of the day, instead insert incrementally through the day --- synapse/app/homeserver.py | 19 +++++++++++++--- synapse/storage/__init__.py | 54 ++++++++++++--------------------------------- 2 files changed, 30 insertions(+), 43 deletions(-) (limited to 'synapse/app') diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py index f785a7a22b..bfc79a5e81 100755 --- a/synapse/app/homeserver.py +++ b/synapse/app/homeserver.py @@ -17,6 +17,7 @@ import gc import logging import os import sys +import datetime import synapse import synapse.config.logger @@ -475,9 +476,24 @@ def run(hs): " changes across releases." ) + # def recurring_user_daily_visit_stats(): + def generate_user_daily_visit_stats(): hs.get_datastore().generate_user_daily_visits() + # Since user daily stats are bucketed at midnight UTC, + # and user_ips.last_seen can be updated at any time, it is important to call + # generate_user_daily_visit_stats immediately prior to the day end. Assuming + # an hourly cadence, the simplist way is to allign all calls to the hour + # end + end_of_hour = datetime.datetime.now().replace(microsecond=0, second=0, minute=0) \ + + datetime.timedelta(hours=1) \ + - datetime.timedelta(seconds=10) # Ensure method fires before day transistion + + time_to_next_hour = end_of_hour - datetime.datetime.now() + clock.call_later(time_to_next_hour.seconds, + clock.looping_call(generate_user_daily_visit_stats, 60 * 60 * 1000)) + if hs.config.report_stats: logger.info("Scheduling stats reporting for 3 hour intervals") clock.looping_call(phone_stats_home, 3 * 60 * 60 * 1000) @@ -490,9 +506,6 @@ def run(hs): # be quite busy the first few minutes clock.call_later(5 * 60, phone_stats_home) - clock.looping_call(generate_user_daily_visit_stats, 10 * 60 * 1000) - clock.call_later(5 * 60, generate_user_daily_visit_stats) - if hs.config.daemonize and hs.config.print_pidfile: print (hs.config.pid_file) diff --git a/synapse/storage/__init__.py b/synapse/storage/__init__.py index b51cf70336..6949876c13 100644 --- a/synapse/storage/__init__.py +++ b/synapse/storage/__init__.py @@ -353,48 +353,22 @@ class DataStore(RoomMemberStore, RoomStore, Generates daily visit data for use in cohort/ retention analysis """ def _generate_user_daily_visits(txn): - logger.info("Calling _generate_user_daily_visits") - # determine timestamp of previous days - yesterday = datetime.datetime.utcnow() - datetime.timedelta(days=1) - yesterday_start = datetime.datetime(yesterday.year, yesterday.month, - yesterday.day, tzinfo=tz.tzutc()) - yesterday_start_time = int(time.mktime(yesterday_start.timetuple())) * 1000 - - # Check that this job has not already been completed - sql = """ - SELECT timestamp - FROM user_daily_visits - ORDER by timestamp desc limit 1 - """ - txn.execute(sql) - row = txn.fetchone() - - # Bail if the most recent time is yesterday - if row and row[0] == yesterday_start_time: - return - - # Not specificying an upper bound means that if the update is run at - # 10 mins past midnight and the user is active during a 30 min session - # that the user is still included in the previous days stats - # This does mean that if the update is run hours late, then it is possible - # to overstate the cohort, but this seems a reasonable trade off - # The alternative is to insert on every request - but prefer to avoid - # for performance reasons - sql = """ - SELECT user_id, device_id - FROM user_ips - WHERE last_seen > ? - """ - txn.execute(sql, (yesterday_start_time,)) - user_visits = txn.fetchall() + # determine timestamp of the day start + now = datetime.datetime.utcnow() + today_start = datetime.datetime(now.year, now.month, + now.day, tzinfo=tz.tzutc()) + today_start_time = int(time.mktime(today_start.timetuple())) * 1000 + logger.info(today_start_time) sql = """ - INSERT INTO user_daily_visits (user_id, device_id, timestamp) - VALUES (?, ?, ?) - """ - - for visit in user_visits: - txn.execute(sql, (visit + (yesterday_start_time,))) + INSERT INTO user_daily_visits (user_id, device_id, timestamp) + SELECT user_id, device_id, ? + FROM user_ips AS u + LEFT JOIN user_daily_visits USING (user_id, device_id) + WHERE last_seen > ? AND timestamp IS NULL + GROUP BY user_id, device_id; + """ + txn.execute(sql, (today_start_time, today_start_time)) return self.runInteraction("generate_user_daily_visits", _generate_user_daily_visits) -- cgit 1.5.1 From 05ac15ae824cc538b869e3cc8db7af2ac22e6754 Mon Sep 17 00:00:00 2001 From: Neil Johnson Date: Tue, 15 May 2018 17:01:33 +0100 Subject: Limit query load of generate_user_daily_visits The aim is to keep track of when it was last called and only query from that point in time --- synapse/app/homeserver.py | 21 ++++++---------- synapse/storage/__init__.py | 60 +++++++++++++++++++++++++++++++++++---------- 2 files changed, 54 insertions(+), 27 deletions(-) (limited to 'synapse/app') diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py index bfc79a5e81..f25eaf9ffc 100755 --- a/synapse/app/homeserver.py +++ b/synapse/app/homeserver.py @@ -476,23 +476,16 @@ def run(hs): " changes across releases." ) - # def recurring_user_daily_visit_stats(): - def generate_user_daily_visit_stats(): hs.get_datastore().generate_user_daily_visits() - # Since user daily stats are bucketed at midnight UTC, - # and user_ips.last_seen can be updated at any time, it is important to call - # generate_user_daily_visit_stats immediately prior to the day end. Assuming - # an hourly cadence, the simplist way is to allign all calls to the hour - # end - end_of_hour = datetime.datetime.now().replace(microsecond=0, second=0, minute=0) \ - + datetime.timedelta(hours=1) \ - - datetime.timedelta(seconds=10) # Ensure method fires before day transistion - - time_to_next_hour = end_of_hour - datetime.datetime.now() - clock.call_later(time_to_next_hour.seconds, - clock.looping_call(generate_user_daily_visit_stats, 60 * 60 * 1000)) + def recurring_user_daily_visit_stats(): + clock.looping_call(generate_user_daily_visit_stats, 60 * 60 * 1000) + + # Rather than update on per session basis, batch up the requests. + # If you increase the loop period, the accuracy of user_daily_visits + # table will decrease + clock.looping_call(generate_user_daily_visit_stats, 5 * 60 * 1000) if hs.config.report_stats: logger.info("Scheduling stats reporting for 3 hour intervals") diff --git a/synapse/storage/__init__.py b/synapse/storage/__init__.py index 6949876c13..52f176a03c 100644 --- a/synapse/storage/__init__.py +++ b/synapse/storage/__init__.py @@ -214,6 +214,9 @@ class DataStore(RoomMemberStore, RoomStore, self._stream_order_on_start = self.get_room_max_stream_ordering() self._min_stream_order_on_start = self.get_room_min_stream_ordering() + # Used in _generate_user_daily_visits to keep track of progress + self._last_user_visit_update = self._get_start_of_day() + super(DataStore, self).__init__(db_conn, hs) def take_presence_startup_info(self): @@ -348,27 +351,58 @@ class DataStore(RoomMemberStore, RoomStore, return self.runInteraction("count_r30_users", _count_r30_users) + def _get_start_of_day(self): + """ + Returns millisecond unixtime for start of UTC day. + """ + now = datetime.datetime.utcnow() + today_start = datetime.datetime(now.year, now.month, + now.day, tzinfo=tz.tzutc()) + return int(time.mktime(today_start.timetuple())) * 1000 + def generate_user_daily_visits(self): """ Generates daily visit data for use in cohort/ retention analysis """ def _generate_user_daily_visits(txn): + logger.info("Calling _generate_user_daily_visits") + today_start = self._get_start_of_day() + a_day_in_milliseconds = 24 * 60 * 60 * 1000 - # determine timestamp of the day start - now = datetime.datetime.utcnow() - today_start = datetime.datetime(now.year, now.month, - now.day, tzinfo=tz.tzutc()) - today_start_time = int(time.mktime(today_start.timetuple())) * 1000 - logger.info(today_start_time) sql = """ INSERT INTO user_daily_visits (user_id, device_id, timestamp) - SELECT user_id, device_id, ? - FROM user_ips AS u - LEFT JOIN user_daily_visits USING (user_id, device_id) - WHERE last_seen > ? AND timestamp IS NULL - GROUP BY user_id, device_id; - """ - txn.execute(sql, (today_start_time, today_start_time)) + SELECT u.user_id, u.device_id, ? + FROM user_ips AS u + LEFT JOIN ( + SELECT user_id, device_id, timestamp FROM user_daily_visits + WHERE timestamp IS ? + ) udv + ON u.user_id = udv.user_id AND u.device_id=udv.device_id + WHERE last_seen > ? AND last_seen <= ? AND udv.timestamp IS NULL + """ + + # This means that the day has rolled over but there could still + # be entries from the previous day. There is an edge case + # where if the user logs in at 23:59 and overwrites their + # last_seen at 00:01 then they will not be counted in the + # previous day's stats - it is important that the query is run + # to minimise this case. + if today_start > self._last_user_visit_update: + yesterday_start = today_start - a_day_in_milliseconds + txn.execute(sql, (yesterday_start, yesterday_start, + self._last_user_visit_update, today_start)) + self._last_user_visit_update = today_start + + txn.execute(sql, (today_start, today_start, + self._last_user_visit_update, + today_start + a_day_in_milliseconds)) + # Update _last_user_visit_update to now. The reason to do this + # rather just clamping to the beginning of the day is to limit + # the size of the join - meaning that the query can be run more + # frequently + + now = datetime.datetime.utcnow() + self._last_user_visit_update = int(time.mktime(now.timetuple())) * 1000 return self.runInteraction("generate_user_daily_visits", _generate_user_daily_visits) -- cgit 1.5.1 From c92a8aa5780a71b5ab0227f204606004b16ff7fb Mon Sep 17 00:00:00 2001 From: Neil Johnson Date: Tue, 15 May 2018 17:31:11 +0100 Subject: pep8 --- synapse/app/homeserver.py | 1 - 1 file changed, 1 deletion(-) (limited to 'synapse/app') diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py index f25eaf9ffc..41ee8b0a69 100755 --- a/synapse/app/homeserver.py +++ b/synapse/app/homeserver.py @@ -17,7 +17,6 @@ import gc import logging import os import sys -import datetime import synapse import synapse.config.logger -- cgit 1.5.1 From a2204cc9cc3f9edb9ae6c1f564b8f315e62d1978 Mon Sep 17 00:00:00 2001 From: Neil Johnson Date: Wed, 16 May 2018 09:47:20 +0100 Subject: remove unused method recurring_user_daily_visit_stats --- synapse/app/homeserver.py | 3 --- 1 file changed, 3 deletions(-) (limited to 'synapse/app') diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py index 41ee8b0a69..a4648b84d5 100755 --- a/synapse/app/homeserver.py +++ b/synapse/app/homeserver.py @@ -478,9 +478,6 @@ def run(hs): def generate_user_daily_visit_stats(): hs.get_datastore().generate_user_daily_visits() - def recurring_user_daily_visit_stats(): - clock.looping_call(generate_user_daily_visit_stats, 60 * 60 * 1000) - # Rather than update on per session basis, batch up the requests. # If you increase the loop period, the accuracy of user_daily_visits # table will decrease -- cgit 1.5.1