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

In T246716#5949943, @Mholloway wrote:

@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.

The tables cannot be public.

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!

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)

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.

In T246716#6324971, @Marostegui 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.

In T246716#6389990, @Mholloway wrote:

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

In T246716#6324971, @Marostegui 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.

In T246716#6390063, @Mholloway wrote:

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.

In T246716#6408240, @Marostegui wrote:

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!

In T246716#6390063, @Mholloway wrote:

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).

In T246716#6414387, @Mholloway wrote:

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

In T246716#6408240, @Marostegui wrote:

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!

In T246716#6390063, @Mholloway wrote:

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.

Thanks for the heads up

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!