Page MenuHomePhabricator

Alter objectcache.exptime
Open, Stalled, MediumPublic

Description

Part of T42626: Standardise type of timestamp database fields (MySQL) and T230428: Migrate tables.sql to abstract schema. Making it not null, drop its default, changing it from datetime to binary(14)

  1. ALTERs to run: https://gerrit.wikimedia.org/r/c/mediawiki/core/+/636049/9/maintenance/archives/patch-objectcache-exptime-notnull.sql
  2. Where to run those changes: all.dblist
  3. When to run those changes: At any time
  4. If the schema change is backwards compatible: No
  5. If the schema change has been tested already on some of the test/beta wikis: Tested in beta cluster.
  6. if the data should be made available on the labs replicas and/or dumps: Not sure

This table is barely used and should not have much read and write on it (and be empty IIRC). It should be also truncated and locked before applying if it's not empty.

Progress:

  • s1
  • s2
  • s3
  • s4
  • s5
  • s6
  • s7
  • s8
  • wikitech
  • labtestwiki
  • pc1
  • pc2
  • pc3

Event Timeline

Marostegui triaged this task as Medium priority.Jan 21 2021, 6:39 AM
Marostegui added a subscriber: Marostegui.

@Ladsgroup this table also on parsercache, but it is also empty, so not sure if that needs to be altered too?

Marostegui added a project: DBA.
Marostegui moved this task from Triage to Ready on the DBA board.

@Ladsgroup this table also on parsercache, but it is also empty, so not sure if that needs to be altered too?

Maybe it should be dropped? but I'm too afraid to say we should do it. Definitely needs the schema change though.

@Ladsgroup this table also on parsercache, but it is also empty, so not sure if that needs to be altered too?

Maybe it should be dropped? but I'm too afraid to say we should do it. Definitely needs the schema change though.

ok, I will do the schema change, it is just one table on 3 hosts - we can investigate if it needs to be dropped in a different task
Thanks!

Marostegui moved this task from Ready to In progress on the DBA board.
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.

@Ladsgroup this table isn't empty and it is in fact written quite often.
For instance these are values for s6:

frwiki
+----------+
| count(*) |
+----------+
|   263130 |
+----------+
jawiki
+----------+
| count(*) |
+----------+
|   115963 |
+----------+
ruwiki
+----------+
| count(*) |
+----------+
|   197270 |
+----------+

And this is enwiki:

+----------+
| count(*) |
+----------+
|  2108309 |
+----------+

I would expect it not to be empty on most of the wikis.

You do mention that it needs to be truncated if it is not empty. What would be the impact if we erase its data?

ugh, it's heavily used by Echo:

wikiadmin@10.64.16.103(frwiki)> select * from objectcache limit 5;
+-------------------------------------+--------------------------+---------------------+
| keyname                             | value                    | exptime             |
+-------------------------------------+--------------------------+---------------------+
| frwiki:echo:seen:alert:time:1       | <redacted>     | 2038-01-19 03:14:07 |
| frwiki:echo:seen:alert:time:10      | <redacted>         | 2038-01-19 03:14:07 |
| frwiki:echo:seen:alert:time:1000047 | <redacted>       | 2038-01-19 03:14:07 |
| frwiki:echo:seen:alert:time:1000085 | <redacted>         | 2038-01-19 03:14:07 |
| frwiki:echo:seen:alert:time:1000151 | <redacted>      | 2038-01-19 03:14:07 |
+-------------------------------------+--------------------------+---------------------+
5 rows in set (0.00 sec)

wikiadmin@10.64.16.103(frwiki)> select * from objectcache where keyname not like 'frwiki:echo:%' limit 5;
+------------------------------------------------------------+-------+---------------------+
| keyname                                                    | value | exptime             |
+------------------------------------------------------------+-------+---------------------+
| frwiki:page-recent-delete:<redacted> | 1     | 2021-02-01 11:27:36 |
| frwiki:page-recent-delete:<redacted> | 1     | 2021-02-01 17:56:40 |
| frwiki:page-recent-delete:<redacted> | 1     | 2021-02-01 19:32:13 |
| frwiki:page-recent-delete:<redacted> | 1     | 2021-02-02 05:19:57 |
| frwiki:page-recent-delete:<redacted> | 1     | 2021-02-02 03:06:20 |
+------------------------------------------------------------+-------+---------------------+
5 rows in set (0.36 sec)

wikiadmin@10.64.16.103(frwiki)> select count(*) from objectcache where keyname not like 'frwiki:echo:%';
+----------+
| count(*) |
+----------+
|      711 |
+----------+
1 row in set (0.17 sec)

I need to investigate it.

Why the expiry of the echo is for eighteen years in the future? Are we in a war or something?

Marostegui changed the task status from Open to Stalled.Feb 1 2021, 1:02 PM

@Ladsgroup going to stall this for now until you've had time to investigate :) - no rush, we have plenty of other schema changes to do!

I haven't had time to look at it with all of the craziness of the world but it has been on my (very long) todo list since February.

No worries, just double checking tasks we want/can prepare before the switchover :)

I suggest waiting with this until https://gerrit.wikimedia.org/r/c/mediawiki/core/+/666780/24 can roll out at which point we can probably just do a one-time purge of whatever is in these tables (maybe in small groups of wikis, with a day in-between to reduce the load a bit).

Ref T212129: Move MainStash out of Redis to a simpler multi-dc aware solution.

Once that work is done, we may also be moving some of these features to use MainStash (which will then be on x2 mysql instead of Redis), instead of objectcache on core tables (as done currently via CACHE_DB or 'db-replicated', instead of MainStash).

In proving and preparing for this, we actually moved some additional features from MainStash-Redis to CACHE_DB, but I think it would make sense to then move those further to MainStash.

Doing that will implicitly de-reference much of what's there anyway, at which point we can truncate whatever is left then. (Instead of truncating multiple times, once for the alter, and then again implicitly by moving the backend pointer to mainstash).