Not sure if there is an additional task for this (other than T94028: DB master connections requested by Flow on GET/HEAD requests), but we've talked about changing the backend to drop the Index layer as a solution to this.

Is this on anyone's plate to work on. The use of merge() is not just slow (like master queries) but totally broken for multi DC MW. I wonder if it would just be easier to remove the caching layers mostly rather than try to replace them.

Stripping out the caching layer is probably the easiest way to go about this, and tbh i don't think the caching layer in Flow ended up being incredibly useful.

In T94029#1634640, @aaron wrote:

Is this on anyone's plate to work on. The use of merge() is not just slow (like master queries) but totally broken for multi DC MW. I wonder if it would just be easier to remove the caching layers mostly rather than try to replace them.

Yes. I already scheduled a meeting on Wednesday to discuss this. This is both because it's still blocking multi-DC and because other issues are still occasionally cropping up (though not that often). E.g.

• There were issues when making a schema change for T107204: Separate reference tables by wiki.
• The Index solution to make schema changes (rEFLWb8ea7b84be25: Fix removal/addition of categorylinks) easier through normalization worked, but caused another issue with history (T111494) that I'm now solving.
• There are still other rare issues with Index, e.g. T112230: History pagination does not work properly (I think that pre-dated the normalization fix).

I think where we ended up in the meeting was that we should gradually reduce the memcached time to live, preferably while keeping performance metrics, and rewriting the database fallback queries.

The other possible approach is to replace merge with delete. WANObjectCache has special provisions to allow a delete() to win even if a stale set() happens shortly after the delete(), but before the slave DB is up to date (hence, stale).

In T94029#1690380, @Mattflaschen wrote:

The other possible approach is to replace merge with delete. WANObjectCache has special provisions to allow a delete() to win even if a stale set() happens shortly after the delete(), but before the slave DB is up to date (hence, stale).

It can't use merge(), so this seems sensible.

Implementing WANObjectCache is likely going to be more work than I had anticipated.
We use BufferedBagOStuff, which:

• keeps all data fetched from/stored to cache in memory for follow-up calls - we rely on that behavior (= request same data more than once)
• it lets us to commit() all cache operations at once

We would probably have to refactor significant pieces of code. And since we're considering throwing away the cache layer...

I just submitted https://gerrit.wikimedia.org/r/#/c/247575/, which will make our code stop backfilling cache from DB.
That also means we no longer have to read from DB_MASTER (which we did to ensure data in cache is current)
And this also means we should be able to start lowering $wgFlowCacheTime until we can set $wgFlowUseMemcache = false.
Cache will gradually hold less data & we can monitor how badly that affects the DB.

Current summary (https://tendril.wikimedia.org/host/view/db1029.eqiad.wmnet/3306)

db1029 : replication family

Slow query log: https://tendril.wikimedia.org/report/slow_queries?host=family%3Adb1029

Blocked on https://gerrit.wikimedia.org/r/#/c/247575/. Only after that has merged, we can start lowering cache TTL to examine if getting rid of the cache is viable.

In T94029#1758417, @matthiasmullie wrote:

Blocked on https://gerrit.wikimedia.org/r/#/c/247575/. Only after that has merged, we can start lowering cache TTL to examine if getting rid of the cache is viable.

It's been merged. I'm moving it back into dev so you can continue with the next steps.

Change 249402 had a related patch set uploaded (by Matthias Mullie):
Expire Flow caches after 1 day

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

Moved back to blocked. First patch to lower cache TTL is on Gerrit, but I'd like to first see the impact of https://gerrit.wikimedia.org/r/#/c/247575/, once it hits production. That'll take another week.

Current summary (https://tendril.wikimedia.org/host/view/db1029.eqiad.wmnet/3306)

db1029 : replication family

Summary (https://tendril.wikimedia.org/host/view/db1029.eqiad.wmnet/3306)
Slow query log: https://tendril.wikimedia.org/report/slow_queries?host=family%3Adb1029

Prior to https://gerrit.wikimedia.org/r/#/c/247575/:

After:

Traffic diff
DB_MASTER:

Queries to master have decreased a lot.
I'm currently not seeing worrying stats or slow queries on flow_* columns.
Let's proceed & lower cache TTL: https://gerrit.wikimedia.org/r/249402

I'd like to mitigate at least some of T118434: Reduce Flow DB queries on Special:Contributions before we do this. Right now, that bug's impact is probably reduced in production because of the caches.

See T114550#1830388.

In today's meeting, we decided to try the WANCache delete()/locally populate from slave approach.

Was this closed by mistake?

We decided there were too many tasks about this. This was originally a spike to investigate this. There are remaining tasks about it, e.g. T120009: Flow: Use WAN cache delete() and replica-based filling to avoid merge().

Updated the dependencies.

Change 249402 abandoned by Mattflaschen:
Expire Flow caches after 1 day

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