This is the next step after disallowing user databases on replicas. It broke some of my tools but I recently rewrote them to move joining logic into my application. I also replicate small amounts of data (e.g. page titles for a subset of pages) into my user db for joins. I found it quite off-putting at first. Volunteers are effectively forced to do substantial amounts of extra work after creating tools that "work perfectly fine". But I can understand the need to provide more scalable infrastructure with the ever growing projects. On Sat, Mar 13, 2021, 3:46 PM Yetkin Sakal via Cloud < cloud(a)lists.wikimedia.org&gt; wrote:

This might be a trivial suggestion (for me it was a game changer): Segment your large queries based on an indexed column. By that I mean, add an additional WHERE clause to process only a small subset of the entire DB (e.g. page_id >= 130000 AND page_id < 140000) and then loop in your application over many of those small fast running queries moving the window from MIN(page_id) to MAX(page_id). This made it possible to handle the amounts of data returned in my application logic and got rid of query timeouts. I'm sure this reduces pressure on the DB servers, too (as the memory requirement for these smaller queries is a lot smaller). Either way, I'd be interested in hearing other devs' experiences and tips on how to cope with the new restrictions. It's the new reality and there is little point in complaining about it IMO (not that I haven't done that already :-D) On Mon, Mar 15, 2021 at 11:58 AM Joaquin Oltra Hernandez < jhernandez(a)wikimedia.org&gt; wrote:

A little late to the party, I just learned about this change today. I maintain a number of bot tasks <https://en.wikipedia.org/wiki/User:FastilyBot> and database <https://fastilybot-reports.toolforge.org> reports <https://en.wikipedia.org/wiki/Wikipedia:Database_reports> on enwp that rely on cross-wiki joins (mostly page title joins between enwp and Commons) to function properly. I didn't find the migration instructions <https://wikitech.wikimedia.org/w/index.php?title=News/Wiki_Replicas_2020_Redesign&oldid=1905818#How_do_I_cross_reference_data_between_wikis_like_I_do_with_cross_joins_today?> very helpful; I run FastilyBot on a Raspberry Pi, and needless to say it would be grossly impractical for me to perform a "join" in the bot's code. Is there going to be a replacement for this functionality? Fastily On Mon, Mar 15, 2021 at 3:09 PM Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; wrote:

It seems to me that the Cloud team is putting in all of the effort they can. I'm not sure where they would find more time and energy to implement a better solution. I imagine any better solution wouldn't be a matter of a few extra hours, but rather finding thousands and thousands of hours to build something bespoke. This is painful. I think you raised some really good points about cross-joins with Central Auth and Commons as those are *designed* to be cross-referenced from other wikis. But ultimately, if there's no reasonable way to do it in the software (Maria DB) we have available, implementing our own solution would take several orders of magnitude more time and then we'd need even more time to maintain it. The real source of pain here is the success of Wikimedia projects. Once things get so big that you can't really use *one bigass server *to solve the problem anymore, scaling involves taking on new complexities in downstream code. In my experience, everyone is struggling with the limits that the "big data" age has put on our ability to query and analyze. No matter what, this type of transition and likely the ones that will follow, will cause a big burden on tool developers. I just don't see a good way around that even though it is a very bad situation and many volunteers won't be able to handle the burden. I guess all I'm trying to say is, don't lay this on the Cloud team being lazy. They aren't. They are one of the most volunteer focused teams at the Wikimedia Foundation and they do quite a lot to support us with the little resources they have. Your frustrations are perfectly valid though. This is a very frustrating situation. On Wed, Mar 31, 2021 at 7:01 AM Huji Lee &lt;huji.huji(a)gmail.com&gt; wrote:

Hi, cross-joins with Central Auth and Commons as those are *designed* to be cross-referenced from other wikis. But ultimately, if there's no a reasonable way to do it in the software (Maria DB) we have available, implementing our own solution would take several orders of magnitude more time and then we'd need even more time to maintain it. There are things like Spider in the MariaDB realm in the example[1,2]. It may not scale or there can be other reasons why not to use it, but I haven't really heard or seen comments on why it would be so. Also, I think there is no technical reason to replicate just some tables to all servers so that the most common use cases could be fulfilled which could have other reasons not to do it. For example, it could be just an idea that is clearly bad for reasons we don't see right now. However, there is not currently visibility for the underlying limitations which are defining our choices in the end. [1] https://mariadb.com/kb/en/spider-storage-engine-overview/ [2] https://dzone.com/articles/uses-for-mariadb-and-the-spider-storage-engine lazy. They aren't. Also, afaik based on cloud team member comments that they seemed to understand why people feel crosswiki-joins are useful and important even when they don't have any solutions currently. Br, --Kimmo Virtanen, Zache On Wed, Mar 31, 2021 at 5:59 PM Aaron Halfaker &lt;aaron.halfaker(a)gmail.com&gt; wrote: > over a path of effort for the Clouds team > > > It seems to me that the Cloud team is putting in all of the effort they > can. I'm not sure where they would find more time and energy to implement > a better solution. I imagine any better solution wouldn't be a matter of a > few extra hours, but rather finding thousands and thousands of hours to > build something bespoke. > > This is painful. I think you raised some really good points about > cross-joins with Central Auth and Commons as those are *designed* to be > cross-referenced from other wikis. But ultimately, if there's no > reasonable way to do it in the software (Maria DB) we have available, > implementing our own solution would take several orders of magnitude more > time and then we'd need even more time to maintain it. > > The real source of pain here is the success of Wikimedia projects. Once > things get so big that you can't really use *one bigass server *to solve > the problem anymore, scaling involves taking on new complexities in > downstream code. In my experience, everyone is struggling with the limits > that the "big data" age has put on our ability to query and analyze. No > matter what, this type of transition and likely the ones that will follow, > will cause a big burden on tool developers. I just don't see a good way > around that even though it is a very bad situation and many volunteers > won't be able to handle the burden. > > lazy. They aren't. They are one of the most volunteer focused teams at > the Wikimedia Foundation and they do quite a lot to support us with the > little resources they have. Your frustrations are perfectly valid though. > This is a very frustrating situation. > > On Wed, Mar 31, 2021 at 7:01 AM Huji Lee &lt;huji.huji(a)gmail.com&gt; wrote: > >> I said it before, and I say it again: *some* databases should be >> available for cross-wiki JOIN everywhere. This would at least include >> commons_p and centralauth_p but perhaps also enwiki_p and meta_p >> >> I know that we discussed it before and better long-term solutions can be >> imagined (such as a data lake, etc.) but we need a solution *now*. >> >> Sorry for coming at it a bit passionately. It just feels like we are >> choosing a path of pain for end users (let their tools break, then let us >> offer alternatives that they could adopt) over a path of effort for the >> Clouds team (let them create robust solutions for the end users and give >> them ample time to transition to the new method before turning off >> cross-wiki joins). >> >> On Wed, Mar 31, 2021 at 4:57 AM Fastily &lt;fastilywp(a)gmail.com&gt; wrote: >> >>> A little late to the party, I just learned about this change today. >>> >>> I maintain a number of bot tasks >>> <https://en.wikipedia.org/wiki/User:FastilyBot> and database >>> <https://fastilybot-reports.toolforge.org> reports >>> <https://en.wikipedia.org/wiki/Wikipedia:Database_reports> on enwp that >>> rely on cross-wiki joins (mostly page title joins between enwp and Commons) >>> to function properly. I didn't find the migration instructions >>> <https://wikitech.wikimedia.org/w/index.php?title=News/Wiki_Replicas_2020_Redesign&oldid=1905818#How_do_I_cross_reference_data_between_wikis_like_I_do_with_cross_joins_today?> >>> very helpful; I run FastilyBot on a Raspberry Pi, and needless to say it >>> would be grossly impractical for me to perform a "join" in the bot's code. >>> >>> Is there going to be a replacement for this functionality? >>> >>> Fastily >>> >>> On Mon, Mar 15, 2021 at 3:09 PM Dan Andreescu &lt;dandreescu(a)wikimedia.org&gt; >>> wrote: >>> >>>> [4] was made to figure out common use cases and possibilities to enable >>>>> them again. >>>>> >>>> ... >>>> >>>>> [4] https://phabricator.wikimedia.org/T215858 >>>>> >>>> >>>> I just want to highlight this ^ thing Joaquin said and mention that our >>>> team (Data Engineering) is also participating in brainstorming ways to >>>> bring back not just cross-wiki joins but better datasets to run these >>>> queries. We have some good ideas, so please do participate in the task and >>>> give us more input so we can pick the best solution quickly. >>>> _______________________________________________ >>>> Wikimedia Cloud Services mailing list >>>> Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) >>>> https://lists.wikimedia.org/mailman/listinfo/cloud >>>> >>> _______________________________________________ >>> Wikimedia Cloud Services mailing list >>> Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) >>> https://lists.wikimedia.org/mailman/listinfo/cloud >>> >> _______________________________________________ >> Wikimedia Cloud Services mailing list >> Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) >> https://lists.wikimedia.org/mailman/listinfo/cloud >> > _______________________________________________ > Wikimedia Cloud Services mailing list > Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org) > https://lists.wikimedia.org/mailman/listinfo/cloud >

Is it feasible to do a log analysis of the database servers to find out what tools are (were?) using cross-wiki joins? At least that would ensure that all the tool owners could be contacted directly to make sure they know this is happening.

Why not run it on WMF Cloud? In code joins will very likely work there and Cloud is supported. You are effectively asking to also support a second way here. And I think it is by no means "needless" to say that it would be "grossly impractical" to do this in the bot code. Joining page titles from commons and en.wp could for example be done by having the DB servers return a sorted stream of page titles (incrementally with limit). Your app code would advance the stream that has the lexicographically smaller title (returning equal titles). This would require little effort on the Pi, however it would transfer two big data streams (2-3 GB maybe - so feasibility would depend on your connection, but again this would be cheap on Cloud!).

That could be RAM limits on the bastion. Actually, scratch that, I’ve confirmed you were killed by the OOM killer on that bastion: Mar 31 23:29:17 tools-sgebastion-08 kernel: [2860588.199138] mysql invoked oom-killer: gfp_mask=0x6000c0(GFP_KERNEL), nodemask=(null), order=0, oom_score_adj=0 -Brooke

I'd like to continue exploring this, just not quite sure of the appropriate way forward. I gather doing work like this on the toolforge bastion hosts is frowned upon, so I guess what I should be doing is spinning up a VPS instance on https://horizon.wikimedia.org/? <https://horizon.wikimedia.org/?> I've been reading through https://wikitech.wikimedia.org/wiki/Help:Cloud_VPS_Instances <https://wikitech.wikimedia.org/wiki/Help:Cloud_VPS_Instances>, from which I gather I need to be a project admin. Is there some existing project I can join to do this exploratory work, or should I create a new project?