Page MenuHomePhabricator

[Spike] Estimate how many writes echo_push_subscription would be having
Closed, ResolvedPublicSpike

Description

Background information

Follow-up from DBA review T246716#6324971, we should estimate how many writes echo_push_subscription table would be having and what is the impact on performance.

Hypothesis

  • From the volume estimate T247438 we can estimate how many writes echo_push_subscription will be having if we combine with the metrics of how many login/logout apps have.

Questions we want to answer

  1. How many writes echo_push_subscription would be having?
  2. Does it affect performance of current queries?

Results

...

Event Timeline

Restricted Application changed the subtype of this task from "Task" to "Spike". · View Herald TranscriptJul 29 2020, 4:28 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

From [1] and [2]

  • For web:
    • ~300,000 new accounts per month
      • For opt-in rate of 10%: 30,000 new subscription rows per month
    • 128,140 current active users
      • For opt-in rate of 10%: 12,814 subscription rows for current active users

[1] https://phabricator.wikimedia.org/T247438
[2] https://en.wikipedia.org/wiki/Special:Statistics

@JoeWalsh any ideas about the numbers we are expecting in apps? Unfortunately T247438 mostly focuses on the number of notifications we are expecting to send.
Given that our estimate is ~40% opt-in rate for iOS and ~50% opt-in rate it would be useful to find out:

  • Current active users (iOS/Android)
  • New users per month (iOS/Android)

Current active users (iOS/Android)
New users per month (iOS/Android)

CC @Charlotte @JMinor @SNowick_WMF to help get these numbers

Per Apple analytics we get ~3000 new users on an average day, on a base of ~250K daily users. These numbers are rising but slowly. And these are opt in numbers, with 30% opt in rate, so we can assume an upper bound of 10K new users a day, and 750K daily uniques.

With about a 30% log in rate and then a 40% subscription rate, yields about 1,200 new subs per day. Obvs there will be a bit of a spike at release, but a few thousand subs a day is likely the range we're talking about.

Per Apple analytics we get ~3000 new users on an average day, on a base of ~250K daily users. These numbers are rising but slowly. And these are opt in numbers, with 30% opt in rate, so we can assume an upper bound of 10K new users a day, and 750K daily uniques.

With about a 30% log in rate and then a 40% subscription rate, yields about 1,200 new subs per day. Obvs there will be a bit of a spike at release, but a few thousand subs a day is likely the range we're talking about.

Android gets ±11,000 new users per day according to the Play Store, with about 6.8m DAU. Assuming a 30% log in rate and 50% subscription rate, this means about 1650 new subs per day.

Thanks @JMinor @Charlotte. That means in total:

  • Web + apps: 115k monthly subscription entries

@Jgiannelos OK, it took me a minute to understand where 115k new subs/month was coming from, but I see that it's 30k (web) + (1,650 * 30) (Android) + (1,200 * 30) (iOS) = 115,500. (I note that the web estimate includes only the Wikipedias, but the numbers for non-Wikipedia projects are likely small enough that we can safely disregard them here.)

So that makes 115,500 / (30 * 24 * 60) ~ 2.67 writes per minute, on average.

I think this is in good shape for v1, closing as resolved.