Page MenuHomePhabricator

DBA review for Echo push notification subscription tables
Closed, ResolvedPublic

Description

In fiscal Q4 2019-2020 the Product Infrastructure team plans to build the basic infrastructure for push notifications for the Wikimedia product platforms (iOS, Android, and web). The initial focus of the project will be on providing push notifications for the apps.

The scope of work will tentatively consist of the following:

  1. A new Echo notifier type to direct Echo events to be handled via push notification.
  2. Additions to the Echo extension to manage push notification subscriptions and submit job queue jobs to make requests to an external push notification request processing service; and
  3. Creation of a new service to batch and submit push notification requests to vendor gateways.

The updates to Echo will include new tables (echo_push_provider and echo_push_subscription) for storing push notification subscription data.

Event Timeline

Marostegui triaged this task as Medium priority.Mar 3 2020, 6:16 AM
Marostegui moved this task from Triage to Blocked external/Not db team on the DBA board.
Marostegui added a subscriber: Marostegui.

The intent is to also release it on Q4 as well?
Will this involve schema changes? If so, to which tables are likely to be involved - the idea is to double check if they are big and assess how long the schema change (if needed) would take.

Thanks for the early heads up!

@Marostegui Yes, target for release is end of Q4. I think this will involve all new tables.

@Marostegui Yes, target for release is end of Q4. I think this will involve all new tables.

Cool, no problem with the new tables. Once you are ready for those, please let us know if they can be public or they need filtering on the Wiki replicas' side.
Going to remove the DBA tag from here, as there are no actionables for us now, but will keep myself subscribed here just in case.

Cool, then we'd need to add them to: https://github.com/wikimedia/puppet/blob/production/manifests/realm.pp#L170 once we know their names.
Either you guys can do it (and I merge it) or just let me know once you've got the tables creation ticket and I can do the patch.
We also need to restart sanitarium hosts to pick up the new replication filters. We can coordinate all that once we are closer to the date and on that specific ticket.

Thank guy!

Mholloway renamed this task from DBA review for the PushNotifications extension to DBA review for push notifications tables.May 5 2020, 8:16 PM

Change 609228 had a related patch set uploaded (by Mholloway; owner: Michael Holloway):
[operations/puppet@production] Add echo_push_subscription to $private_tables

https://gerrit.wikimedia.org/r/c/operations/puppet/ /609228

This is ready for DBA review. To elaborate on what we're doing:

We plan to create two new tables managed by the Echo extension: echo_push_provider and echo_push_subscription. We plan to put them in the x1 cluster/wikishared DB.

echo_push_provider is simply a push provider name-ID mapping; it will start out with 2 rows and will probably never grow beyond 10 rows.

MariaDB [my_wiki]> describe echo_push_provider;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| epp_id   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
| epp_name | tinyblob            | NO   |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.002 sec)

echo_push_subscription is where the action is. Based on the usage estimates in T247438, we would expect this table to grow to ~3.85M rows with the initial rollout to Android, and an additional ~1.65M rows after adoption on iOS, for a total of ~5.5M rows on initial rollout. Later, when we roll out web push, the number of rows could grow into the tens of millions.

MariaDB [my_wiki]> describe echo_push_subscription;
+------------------+---------------------+------+-----+---------------------+-------------------------------+
| Field            | Type                | Null | Key | Default             | Extra                         |
+------------------+---------------------+------+-----+---------------------+-------------------------------+
| eps_id           | int(10) unsigned    | NO   | PRI | NULL                | auto_increment                |
| eps_user         | int(10) unsigned    | NO   | MUL | NULL                |                               |
| eps_token        | blob                | NO   |     | NULL                |                               |
| eps_token_sha256 | binary(64)          | NO   | UNI | NULL                |                               |
| eps_provider     | tinyint(3) unsigned | NO   | MUL | NULL                |                               |
| eps_updated      | timestamp           | NO   |     | current_timestamp() | on update current_timestamp() |
| eps_data         | blob                | YES  |     | NULL                |                               |
+------------------+---------------------+------+-----+---------------------+-------------------------------+
7 rows in set (0.002 sec)

MariaDB [my_wiki]> show indexes from echo_push_subscription;
+------------------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name                       | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| echo_push_subscription |          0 | PRIMARY                        |            1 | eps_id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| echo_push_subscription |          0 | eps_token_sha256               |            1 | eps_token_sha256 | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| echo_push_subscription |          1 | eps_provider                   |            1 | eps_provider     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| echo_push_subscription |          1 | echo_push_subscription_user_id |            1 | eps_user         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+------------------------+------------+--------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.001 sec)

The queries we'll be running are as follows (see PHP code in includes/Push/SubscriptionManager.php):

  • Add a subscription

Query:

INSERT IGNORE INTO echo_push_subscription (eps_user, eps_provider, eps_token, eps_token_sha256, eps_updated) VALUES (1, 1, 'a', 'a', 20200702000000);

Describe:

MariaDB [my_wiki]> DESCRIBE INSERT IGNORE INTO echo_push_subscription (eps_user, eps_provider, eps_token, eps_token_sha256, eps_updated) VALUES (1, 1, 'a', 'a', 20200702000000);
+------+-------------+------------------------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+------------------------+------+---------------+------+---------+------+------+-------+
|    1 | INSERT      | echo_push_subscription | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL  |
+------+-------------+------------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.001 sec)
  • Delete a subscription

Query:

DELETE FROM echo_push_subscription WHERE eps_user = 1 AND eps_token = 'a';

Describe:

MariaDB [my_wiki]> DESCRIBE DELETE FROM echo_push_subscription WHERE eps_user = 1 AND eps_token = 'a';
+------+-------------+------------------------+-------+--------------------------------+--------------------------------+---------+------+------+-------------+
| id   | select_type | table                  | type  | possible_keys                  | key                            | key_len | ref  | rows | Extra       |
+------+-------------+------------------------+-------+--------------------------------+--------------------------------+---------+------+------+-------------+
|    1 | SIMPLE      | echo_push_subscription | range | echo_push_subscription_user_id | echo_push_subscription_user_id | 4       | NULL | 1    | Using where |
+------+-------------+------------------------+-------+--------------------------------+--------------------------------+---------+------+------+-------------+
1 row in set (0.001 sec)
  • Get all subscriptions for a user

Query:

SELECT * FROM echo_push_subscription INNER JOIN echo_push_provider ON eps_provider = epp_id WHERE eps_user = 1;

Describe:

MariaDB [my_wiki]> DESCRIBE SELECT * FROM echo_push_subscription INNER JOIN echo_push_provider ON eps_provider = epp_id WHERE eps_user = 1;
+------+-------------+------------------------+--------+---------------------------------------------+--------------------------------+---------+---------------------------------------------+------+-------+
| id   | select_type | table                  | type   | possible_keys                               | key                            | key_len | ref                                         | rows | Extra |
+------+-------------+------------------------+--------+---------------------------------------------+--------------------------------+---------+---------------------------------------------+------+-------+
|    1 | SIMPLE      | echo_push_subscription | ref    | eps_provider,echo_push_subscription_user_id | echo_push_subscription_user_id | 4       | const                                       | 1    |       |
|    1 | SIMPLE      | echo_push_provider     | eq_ref | PRIMARY                                     | PRIMARY                        | 1       | my_wiki.echo_push_subscription.eps_provider | 1    |       |
+------+-------------+------------------------+--------+---------------------------------------------+--------------------------------+---------+---------------------------------------------+------+-------+
2 rows in set (0.001 sec)
Mholloway renamed this task from DBA review for push notifications tables to DBA review for Echo push notifications tables.Jul 3 2020, 12:59 AM
Mholloway renamed this task from DBA review for Echo push notifications tables to DBA review for Echo push notification subscription tables.

Change 609228 merged by Marostegui:
[operations/puppet@production] Add echo_push_subscription to $private_tables

https://gerrit.wikimedia.org/r/609228

echo_push_subscription table added to replication filters on sanitarium hosts.

Hi @Marostegui, thanks for the merge!

Anticipating launching to production by mid-August, do you have a sense of when your team would be able to review and sign off on the info in T246716#6275733? Thank you!

Thanks for reaching out, this buried into a pile of emails :-)
So I am not worried about echo_push_provider indeed, given the numbers you've mentioned.

echo_push_subscription table does triggers some questions.

  • When you mention tens of millions, does this mean this table will never be purged?
  • How big can DELETE FROM echo_push_subscription WHERE eps_user = 1 AND eps_token = 'a'; could be? In terms of rows. DELETEs are very expensive, and if that delete could be deleting thousands of rows by any chance, that would need to be limited and split in batches to avoid performance and replication issues.
  • Do you a rough estimation on how many writes echo_push_subscription would be having?

Hi @Marostegui, thanks for the comments, and sorry for the silence; we've been making some updates based on what you wrote.

  • When you mention tens of millions, does this mean this table will never be purged?

We have a set of updates in progress to remove subscriptions that expire or are reported by push provider APIs to be invalid; that's T259148. We've also added enforcement of a configurable maximum number of subscriptions per (central) user (T259150). We haven't yet decided on what number to use, but I imagine it will be something like 10.

  • How big can DELETE FROM echo_push_subscription WHERE eps_user = 1 AND eps_token = 'a'; could be? In terms of rows. DELETEs are very expensive, and if that delete could be deleting thousands of rows by any chance, that would need to be limited and split in batches to avoid performance and replication issues.

Tokens are meant to be unique, and we enforce this on the DB level with a unique key constraint, so this query should only ever result in the deletion of a single row. If we add a method to delete all subscriptions for a user (i.e., with no eps_token condition), the total affected rows will be limited by the configured maximum number of subscriptions per user.

  • Do you a rough estimation on how many writes echo_push_subscription would be having?

As estimated on T259149, we expect ~2.67 average writes per minute to echo_push_subscription.

There are a couple of other recent changes that I should mention. One is that a patch is in review to add a column to store a text value; it applies only to iOS clients and should be null for all other platforms: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Echo/+/618128

The other is that we recently merged a patch (https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Echo/+/619856) that adds a query to get the total number of rows for an eps_user value; this will run each time a user attempts to create a new subscription in echo_push_subscription to check whether the user has already created the maximum number of allowed subscriptions.

Hi @Marostegui, thanks for the comments, and sorry for the silence; we've been making some updates based on what you wrote.

  • When you mention tens of millions, does this mean this table will never be purged?

We have a set of updates in progress to remove subscriptions that expire or are reported by push provider APIs to be invalid; that's T259148. We've also added enforcement of a configurable maximum number of subscriptions per (central) user (T259150). We haven't yet decided on what number to use, but I imagine it will be something like 10.

That sounds good thanks.

  • How big can DELETE FROM echo_push_subscription WHERE eps_user = 1 AND eps_token = 'a'; could be? In terms of rows. DELETEs are very expensive, and if that delete could be deleting thousands of rows by any chance, that would need to be limited and split in batches to avoid performance and replication issues.

Tokens are meant to be unique, and we enforce this on the DB level with a unique key constraint, so this query should only ever result in the deletion of a single row. If we add a method to delete all subscriptions for a user (i.e., with no eps_token condition), the total affected rows will be limited by the configured maximum number of subscriptions per user.

Excellent, that shouldn't be a problem them. Great news.

  • Do you a rough estimation on how many writes echo_push_subscription would be having?

As estimated on T259149, we expect ~2.67 average writes per minute to echo_push_subscription.

Just to be sure, that is around 3 writes per minute, and not 2.67k right? :-)
If it is around 3 writes per minute, that's perfectly ok.

There are a couple of other recent changes that I should mention. One is that a patch is in review to add a column to store a text value; it applies only to iOS clients and should be null for all other platforms: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Echo/+/618128

Any specific reason you are choosing text and not blob? Just curious

Great! Looks like we're close to wrapped up with this.

Just to be sure, that is around 3 writes per minute, and not 2.67k right? :-)
If it is around 3 writes per minute, that's perfectly ok.

Yes, that's around 3/min, not 3k/min!

There are a couple of other recent changes that I should mention. One is that a patch is in review to add a column to store a text value; it applies only to iOS clients and should be null for all other platforms: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Echo/+/618128

Any specific reason you are choosing text and not blob? Just curious

No, I don't think we have any specific requirement for the TEXT type here. We'd be happy to change the data types on any of these that you think appropriate.

Also, one last thing that I noticed today is that the new eps_topic column should probably be normalized and instead take a tinyint topic identifier. The expected topic values consist of a very small number of specific strings like org.wikimedia.wikipedia (or NULL).

Great! Looks like we're close to wrapped up with this.

Just to be sure, that is around 3 writes per minute, and not 2.67k right? :-)
If it is around 3 writes per minute, that's perfectly ok.

Yes, that's around 3/min, not 3k/min!

There are a couple of other recent changes that I should mention. One is that a patch is in review to add a column to store a text value; it applies only to iOS clients and should be null for all other platforms: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/Echo/+/618128

Any specific reason you are choosing text and not blob? Just curious

No, I don't think we have any specific requirement for the TEXT type here. We'd be happy to change the data types on any of these that you think appropriate.

Not sure we use TEXT on many places, I recall using blob most of the time, Platform Engineering thoughts on consistency here for using TEXT or blob?

Change 627346 had a related patch set uploaded (by Mholloway; owner: Michael Holloway):
[mediawiki/extensions/Echo@master] Change echo_push_* column types from TEXT to BLOB

https://gerrit.wikimedia.org/r/627346

There's no particular reason we need TEXT as opposed to BLOB, so I pushed a patch to change it.

Does everything look OK otherwise? Our plan is to go live in production a week from today.

Thanks.
What's the release plan? Is this going to go full ON for all the wikis or will you enable it slowly?

Change 627346 merged by jenkins-bot:
[mediawiki/extensions/Echo@master] Change echo_push_* column types from TEXT to BLOB

https://gerrit.wikimedia.org/r/627346

Not sure we use TEXT on many places, I recall using blob most of the time, Platform Engineering thoughts on consistency here for using TEXT or blob?

@Mholloway Because wikis on WMF production use the binary collation, and AFAIK only binary collation will be supported from now on on MW, fields defined as TEXT will automatically get "converted/created as" BLOB. There is no really a difference when not using character-set strings. In other words, unless you have a special need regarding old installations, there is no difference between them, they will both produce blobs for mediawiki HEAD. On previous versions, for utf8 installations there was a difference. I think you did well and using BLOB was the right approach to prevent confusion.

@Marostegui Our plan is to enable either for all Wikipedias or all wikis at once (to be confirmed at a team meeting tomorrow). That said, I would expect the number of stored subscriptions to be small and rise slowly at first, because only app push and not web push will be supported with the initial launch, and because the app clients have yet to integrate with the API; that will be done after the initial launch on the platform side.

I should mention that we'll enable on testwiki first and do manual testing before promoting to the remainder of the target wikis. I've updated T262936: Enable app push notifications in production to reflect this.

Does that sound good? Anything else to do here, or can this task be resolved?

I am fine closing this, but please once it is released everywhere, can you comment here so we can take a look at the graphs and be more aware those days in case we see different traffic patterns?

Will do. I'll leave this open for now, so that we remember to follow up after launch. Just wanted to be sure we're clear to go live. Thanks @Marostegui and @jcrespo!

MSantos closed this task as Resolved.EditedOct 1 2020, 10:44 AM
MSantos claimed this task.
MSantos added a subscriber: MSantos.

@Marostegui in case you didn't see the announcement elsewhere, we flipped the switch yesterday, see T262936#6503632. I'm going to close this task, but please reopen it if needed. Thanks!