To inform the general work of dumps in the new architecture (Event Platform -> Spark -> XML Publishing), do a quick analysis of:
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | VirginiaPoundstone | T345988 [Epic] XML MediaWiki data dumps for right to fork | |||
| Resolved | Milimetric | T330296 Dumps 2.0 Phase I: Proof of concept for MediaWiki XML content dump via Event Platform, Iceberg and Spark | |||
| Open | None | T346147 Generate XML dumps for simplewiki | |||
| Resolved | Milimetric | T335862 Implement job to generate Dump XML files | |||
| Resolved | VirginiaPoundstone | T344690 [Spike] Quantify pages and revisions as relevant to dumps |
First, some setup.
A working table, called analyze_page_statistics with total edits, bot edits, and anon edits for each page on each wiki:
create table analyze_page_statistics as
select 'history-2023-07' as source,
wiki_db,
page_namespace_historical as page_namespace,
page_id,
count(revision_id) as edits,
sum(if(size(event_user_is_bot_by_historical) > 0, 1, 0)) as bot_edits,
sum(if(event_user_is_anonymous, 1, 0)) as anon_edits
from wmf.mediawiki_history
where snapshot='2023-07'
and not page_is_deleted
and not page_is_redirect
group by
wiki_db,
page_namespace_historical,
page_idIn previous attempts I also imported a dumps snapshot, but it was very similar so I didn't dig too hard into it. And I realized that a bunch of pages have been deleted, so I did it again (query above shows the second attempt).
With this, I can run some queries.
The above gives us 524,283,851 pages across all projects and namespaces to play with.
First I wanted to know how many revisions do the vast majority of pages have [1].
| content | p999 | max_edits | max_bot_edits | max_anon_edits |
| true | 655 | 160835 | 56352 | 34104 |
| false | 284 | 1932417 | 831710 | 361350 |
So 99.9% of content pages have fewer than 655 edits. And the most edits any content page has is 160835. Non-content pages on the other hand have an even steeper power curve, with almost two million edits as the max. On some of these pages, it looks like bots are a significant presence but not the majority, so we can ignore that dimension for now and focus on overall edits.
So let's see what namespaces, on what wikis, have pages with more than 100k edits [2]
| wiki | ns | content | max_edits |
| enwiki | 4 | 1932417 | |
| dewiki | 4 | 877030 | |
| metawiki | 2 | 751218 | |
| commonswiki | 4 | 714667 | |
| zhwiki | 4 | 556862 | |
| wikidatawiki | 4 | 509245 | |
| enwiki | 2 | 467614 | |
| ruwiki | 4 | 404976 | |
| enwiki | 11 | 395998 | |
| hewiki | 4 | 385639 | |
| eswikinews | 10 | 335821 | |
| eswiki | 4 | 318701 | |
| dewiki | 10 | 314372 | |
| nlwiki | 4 | 268003 | |
| frwiki | 4 | 266421 | |
| enwikinews | 10 | 232797 | |
| frwikinews | 10 | 227120 | |
| frwiki | 10 | 224636 | |
| fawiki | 10 | 224223 | |
| itwikinews | 10 | 217246 | |
| plwiki | 4 | 214497 | |
| test2wiki | 3 | 188785 | |
| plwiki | 102 | 186295 | |
| commonswiki | 2 | 179321 | |
| enwiki | 3 | 163316 | |
| ruwiki | 2 | 161601 | |
| metawiki | 0 | TRUE | 160835 |
| enwiki | 1 | 154359 | |
| ptwikinews | 10 | 148158 | |
| dewiki | 5 | 142461 | |
| hywiki | 10 | 133181 | |
| enwiki | 5 | 125916 | |
| svwiki | 4 | 122425 | |
| hywiktionary | 10 | 122317 | |
| eswiki | 10 | 122075 | |
| hywikiquote | 10 | 121967 | |
| dewikinews | 10 | 119601 | |
| eowikinews | 10 | 117548 | |
| enwiki | 10 | 107212 | |
| frwiki | 102 | 105516 | |
| ruwiki | 104 | 104401 | |
Some detail on the namespaces that show up here [3]:
| wikis | ns | ns name |
| metawiki | 0 | |
| enwiki | 1 | Talk |
| commonswiki ruwiki metawiki enwiki | 2 | User |
| test2wiki enwiki | 3 | User talk |
| commonswiki wikidatawiki hewiki svwiki zhwiki frwiki eswiki ruwiki dewiki plwiki nlwiki enwiki | 4 | Project |
| dewiki enwiki | 5 | Project talk |
| hywikiquote enwikinews frwiki dewiki frwikinews enwiki eswikinews hywiktionary itwikinews hywiki eowikinews eswiki fawiki ptwikinews dewikinews | 10 | Template |
| enwiki | 11 | Template talk |
| plwiki | 102 | Wikiprojekt |
| frwiki | 102 | Projet |
| ruwiki | 104 | Проект |
So a single wiki has a content page with more than 100k edits (meta, which makes sense). This is pretty convincing evidence to me that we could maybe create a simpler dump, where we only output the last 1k edits for any non-content page. And then once in a while we dump everything for those namespaces too. It's something to talk to the community about, but it feels like if we can be more mindful here we can dramatically increase the job's performance and, therefore, frequency.
For more analysis, anyone can use this table, so if you have questions, do ask!
[1] overall stats by content/not-content
with namespaces as (
select dbname, namespace, namespace_is_content
from wmf_raw.mediawiki_project_namespace_map
where snapshot = '2023-08'
)
select /*+ BROADCAST(namespaces) */
namespace_is_content,
approx_percentile(edits, 0.75) as edits_p75,
approx_percentile(edits, 0.999) as edits_p999,
max(edits) as edits_max,
approx_percentile(bot_edits, 0.75) as bot_edits_p75,
approx_percentile(bot_edits, 0.999) as bot_edits_p999,
max(bot_edits) as bot_edits_max
from analyze_page_statistics
inner join
namespaces on dbname = wiki_db
and namespace = page_namespace
group by namespace_is_content[2] what namespaces on what wikis have pages with more than 100,000 pages?
with namespaces as (
select dbname, namespace, namespace_is_content
from wmf_raw.mediawiki_project_namespace_map
where snapshot = '2023-08'
)
select /*+ BROADCAST(namespaces) */
wiki_db,
page_namespace,
namespace_is_content,
max(edits) as max_edits
from analyze_page_statistics
inner join
namespaces on namespaces.namespace = page_namespace
and namespaces.dbname = wiki_db
where edits > 100000
group by
wiki_db,
page_namespace,
namespace_is_content[3] -- namespace details
with namespaces as (
select dbname, namespace, namespace_canonical_name
from wmf_raw.mediawiki_project_namespace_map
where snapshot = '2023-08'
), wikis as (
select wiki_db,
page_namespace,
max(edits)
from analyze_page_statistics
group by wiki_db, page_namespace
having max(edits) > 100000
)
select /*+ BROADCAST(namespaces) */
collect_set(wiki_db),
page_namespace,
namespace_canonical_name
from wikis
inner join
namespaces on namespaces.namespace = page_namespace
and namespaces.dbname = wiki_db
group by
page_namespace,
namespace_canonical_nameThis is pretty convincing evidence to me that we could maybe create a simpler dump, where we only output the last 1k edits for any non-content page.
Should we open a task to investigate this before we close this one? I'm thinking we could run both jobs 1k and full dump, and observe whether there is a significant performance/time difference.