gistfile1.txt

TL;DR Based on the assumptions below, the current cron strategy will be viable for a very very long time. In a worst case scenario, until we have 125,000 users paying through iOS.

At our last sprint demo, Mike and Warren asked about the scaling limits of the re-verification cron.

Assumptions:

* Reverifying each receipt will have a mean transaction time of 2seconds. This is higher than our initial estimates to allow some additional buffer room.
* Our IOS subscription patterns will have a similar histogram as our Payflow signups.
* We are ok with cron running for 18hrs consecutively. I chose 18hrs as I can only assume it will take several months to schedule a team to fix the issue in the future.

Existing payflow subscriptions have the following shape to their signups. In the month of August 2013 we had 38075 payments through payflow. The existing distribution of payments per day looks like:


mysql> SELECT COUNT(*), COUNT(*) / 38075 AS ratio, DAY(date) AS day FROM payment_in WHERE MONTH(date) = 08 AND YEAR(date) = 2013 GROUP BY day ORDER BY day;
+----------+--------+------+
| COUNT(*) | ratio  | day  |
+----------+--------+------+
|     2172 | 0.0570 |    1 |
|     1518 | 0.0399 |    2 |
|     1385 | 0.0364 |    3 |
|     1252 | 0.0329 |    4 |
|     1337 | 0.0351 |    5 |
|     1278 | 0.0336 |    6 |
|     1275 | 0.0335 |    7 |
|     1281 | 0.0336 |    8 |
|     1162 | 0.0305 |    9 |
|     1257 | 0.0330 |   10 |
|     1216 | 0.0319 |   11 |
|     1229 | 0.0323 |   12 |
|     1215 | 0.0319 |   13 |
|     1300 | 0.0341 |   14 |
|     1362 | 0.0358 |   15 |
|     1187 | 0.0312 |   16 |
|     1125 | 0.0295 |   17 |
|     1173 | 0.0308 |   18 |
|     1258 | 0.0330 |   19 |
|     1225 | 0.0322 |   20 |
|     1169 | 0.0307 |   21 |
|     1149 | 0.0302 |   22 |
|     1129 | 0.0297 |   23 |
|     1102 | 0.0289 |   24 |
|     1053 | 0.0277 |   25 |
|     1223 | 0.0321 |   26 |
|     1231 | 0.0323 |   27 |
|     1302 | 0.0342 |   28 |
|     1115 | 0.0293 |   29 |
|     1105 | 0.0290 |   30 |
|      290 | 0.0076 |   31 |
+----------+--------+------+
31 rows in set (0.73 sec)

As with our customer's billing the 1st of the month is an exceptionally busy day, and should be the benchmark for capacity planning.

Given the previous assumptions about mean transaction time and max execution time, we can comfortable re-verify 21,000 ((60 * 60 * 18) / 3) systems per day. We can use this max value to estimate capacity for the rest of the month.

21000 / 0.057 = 368421.052

This means we'll need to reach ~368,000 customers paying through iOS before we will need to re-architect the cron based on the current assumptions. Given that our current iOS install base is ~84,000 people and they convert at lower than 6%, we would need 6.1 million customers signed up through iOS to reach capacity, at the current conversion rate.

368,000 / 0.6 = 6,133,333


## Caveats ##

If the mean time to re-verify an account doubles or triples we'll reach capacity much sooner. Instead of 21,000 per day, we'll only be able to do 10000 per day at 6 seconds, and 7200 at 9 seconds. These day rates equate to 175,000 and 126,000 paying customers.