Enable merge-on-read for wmf_content.mediawiki_content_history_v1
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	xcollazo
	Apr 30 2025, 1:55 PM

Project Tags

Referenced Files

None

Subscribers

Ahoelzl

Aklapper

xcollazo

Description

wmf_content.mediawiki_content_history_v1 is one of our biggest tables, containing all revisions for all wikis over all of wikitime.

On T383816: Calculate rough HDFS storage requirements for wmf_content.mediawiki_content_history_v1, we estimated the following sizes for respective Iceberg snapshot retention periods:

For wmf_content.mediawiki_content_history_v1 as it stands today:

retention period 7 days 30 days 60 days 90 days

size in TBs 36.7 89.6 158.6 227.6

For wmf_content.mediawiki_content_history_v1, assuming merge-on-read enabled with weekly table maintenance:

retention period 7 days 30 days 60 days 90 days

size in TBs 25.2 40.3 60.0 79.7

We have now fixed the blocker that prevented us from enabling merge-on-read, namely T391280: Modify table maintenance mechanism to support Iceberg's rewrite_position_delete_files(). Additionally, we now have wmf_content.mediawiki_content_current_v1 close to production. This is a much smaller table, but it does have merge-on-read enabled and it is WAD.

Thus, in this task we want to:

Enable merge-on-read on wmf_content.mediawiki_content_history_v1.
Add rewrite_position_delete_files() table maintenance to the above.
Monitor the performance of all writes, see if we eliminate the number one write latency issue: skewed data on the last shuffle when writing to the table via MERGE INTO. The skew should be eliminated when doing merge-on-read, since we no longer need to rewrite existing files, and we expect to win ~6 hours back, making the job go from ~7.6h - ~6h = 1.6 h, a 475% runtime performance increase.
Monitor down stream wmf_content.mediawiki_content_current_v1, which consumes the history table daily, for perf regressions.

Details

Related Changes in GitLab:

Title	Reference	Author	Source Branch	Dest Branch
analytics: change mw-content-history-v1 table maintenance back to weekly	repos/data-engineering/airflow-dags!1350	xcollazo	mwch-tbl-maintenance-back-to-weekly	main
Go back to copy-on-write for mw-content-history.	repos/data-engineering/mediawiki-content-pipelines!68	xcollazo	back-to-copy-on-write	main
analytics: deprioritize maintenance for mw-content-history	repos/data-engineering/airflow-dags!1292	xcollazo	deprioritize-tbl-maintenance	main
analytics: do daily instead of weekly table maintenance on mw-content-history table.	repos/data-engineering/airflow-dags!1287	xcollazo	aggressive-tbl-maintenance-for-mw-content-history	main
analytics: add rewrite_position_delete_files() for mw_content_history	repos/data-engineering/airflow-dags!1279	xcollazo	add-table-maintenance-for-deletes	main
DDL to enable merge-on-read for all mutations on mw content tables.	repos/data-engineering/mediawiki-content-pipelines!64	xcollazo	modify-tblproperties	main

Customize query in GitLab

Related Objects
Search...

Status	Assigned	Task
Open	None	T366752 Dumps 2.0 Phase III: Production level dumps (SDS 1.2)
Resolved	xcollazo	T391279 Daily updated wmf_content.mediawiki_content_current_v1
Resolved	xcollazo	T393012 Enable merge-on-read for wmf_content.mediawiki_content_history_v1

Event Timeline

xcollazo created this task.Apr 30 2025, 1:55 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 30 2025, 1:55 PM

xcollazo added a parent task: T366752: Dumps 2.0 Phase III: Production level dumps (SDS 1.2).Apr 30 2025, 1:55 PM

While preparing to do the work for this ticket, we noticed a couple discrepancies in our content tables TBLPROPERTIES that we want to fix first:

wmf_content.mediawiki_content_current_v1:

spark-sql (default)> describe formatted wmf_content.mediawiki_content_current_v1;
...
Table Properties	[current-snapshot-id=8649756776621205924,format=iceberg/parquet,write.format.default=parquet,write.merge.mode=merge-on-read,write.target-file-size-bytes=134217728]	
Time taken: 0.19 seconds, Fetched 36 row(s)

wmf_content.mediawiki_content_history_v1:

spark-sql (default)> describe formatted wmf_content.mediawiki_content_history_v1;
...
Table Properties	[current-snapshot-id=8044191397808821822,format=iceberg/parquet,sort-order=wiki_id ASC NULLS FIRST, page_id ASC NULLS FIRST, revision_dt ASC NULLS FIRST,write.distribution-mode=range,write.format.default=parquet,write.metadata.delete-after-commit.enabled=true,write.metadata.previous-versions-max=10,write.target-file-size-bytes=134217728]	
Time taken: 0.109 seconds, Fetched 38 row(s)

Discrepancies:

We forgot to set write.metadata.delete-after-commit.enabled = true for wmf_content.mediawiki_content_current_v1. We want this enabled to avoid hoarding old metadata files.
The default of write.metadata.previous-versions-max = 100 is reasonable, but on wmf_content.mediawiki_content_history_v1 we have it set at write.metadata.previous-versions-max = 10, for no particular reason. Let's change that to the default.
merge-on-read is currently enabled on wmf_content.mediawiki_content_current_v1 only via write.merge.mode=merge-on-read. This means that MERGE INTO commands will apply with merge-on-read, but UPDATES and DELETEs will not. Although we currently only write to this table via MERGE INTO, to avoid potential confusion in the future, let's set them all to merge-on-read. We will do the same for wmf_content.mediawiki_content_history_v1 (for this table we do MERGE INTOs and also DELETEs).

So the final ALTERs so far look like so:

For wmf_content.mediawiki_content_current_v1:

ALTER TABLE wmf_content.mediawiki_content_current_v1 SET TBLPROPERTIES (
    'write.metadata.delete-after-commit.enabled'='true',
    'write.delete.mode'='merge-on-read',
    'write.merge.mode'='merge-on-read',
    'write.update.mode'='merge-on-read',
);

For wmf_content.mediawiki_content_history_v1:

ALTER TABLE wmf_content.mediawiki_content_history_v1 SET TBLPROPERTIES (
    'write.delete.mode'='merge-on-read',
    'write.merge.mode'='merge-on-read',
    'write.update.mode'='merge-on-read',
);

ALTER TABLE wmf_content.mediawiki_content_history_v1 UNSET TBLPROPERTIES ('write.metadata.previous-versions-max');

(Equivalent changes will also be done for the table CREATE statements in an MR)

xcollazo changed the task status from Open to In Progress.Apr 30 2025, 7:59 PM

xcollazo moved this task from Next Up to In progress on the Data-Engineering (Q4 2025 April 1st - June 30th) board.

Prepare:

ssh an-launcher1002.eqiad.wmnet

sudo -u analytics bash

kerberos-run-command analytics spark3-sql

wmf_content.mediawiki_content_current_v1:

spark-sql (default)> ALTER TABLE wmf_content.mediawiki_content_current_v1 SET TBLPROPERTIES (
                   >     'write.metadata.delete-after-commit.enabled'='true',
                   >     'write.delete.mode'='merge-on-read',
                   >     'write.merge.mode'='merge-on-read',
                   >     'write.update.mode'='merge-on-read'
                   > );
25/04/30 20:01:48 WARN BaseTransaction: Failed to load metadata for a committed snapshot, skipping clean-up
Response code
Time taken: 0.767 seconds

spark-sql (default)> describe formatted wmf_content.mediawiki_content_current_v1;
....
Table Properties	[current-snapshot-id=8649756776621205924,format=iceberg/parquet,write.delete.mode=merge-on-read,write.format.default=parquet,write.merge.mode=merge-on-read,write.metadata.delete-after-commit.enabled=true,write.target-file-size-bytes=134217728,write.update.mode=merge-on-read]	
Time taken: 0.091 seconds, Fetched 36 row(s)

wmf_content.mediawiki_content_history_v1:

spark-sql (default)> ALTER TABLE wmf_content.mediawiki_content_history_v1 SET TBLPROPERTIES (
                   >     'write.delete.mode'='merge-on-read',
                   >     'write.merge.mode'='merge-on-read',
                   >     'write.update.mode'='merge-on-read'
                   > );
25/04/30 20:03:17 WARN BaseTransaction: Failed to load metadata for a committed snapshot, skipping clean-up
Response code
Time taken: 1.125 seconds

spark-sql (default)> ALTER TABLE wmf_content.mediawiki_content_history_v1 UNSET TBLPROPERTIES ('write.metadata.previous-versions-max');
25/04/30 20:03:31 WARN BaseTransaction: Failed to load metadata for a committed snapshot, skipping clean-up
Response code
Time taken: 0.204 seconds

spark-sql (default)> describe formatted wmf_content.mediawiki_content_history_v1;
...
Table Properties	[current-snapshot-id=8044191397808821822,format=iceberg/parquet,sort-order=wiki_id ASC NULLS FIRST, page_id ASC NULLS FIRST, revision_dt ASC NULLS FIRST,write.delete.mode=merge-on-read,write.distribution-mode=range,write.format.default=parquet,write.merge.mode=merge-on-read,write.metadata.delete-after-commit.enabled=true,write.target-file-size-bytes=134217728,write.update.mode=merge-on-read]	
Time taken: 0.369 seconds, Fetched 38 row(s)

Mentioned in SAL (#wikimedia-analytics) [2025-04-30T20:07:41Z] <xcollazo> Enabled merge-on-read on mw-content-history table and other TBLPROPERTIES changes. T393012.

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/64

DDL to enable merge-on-read for all mutations on mw content tables.

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/64

DDL to enable merge-on-read for all mutations on mw content tables.

merge-on-read is now enabled on wmf_content.mediawiki_content_history_v1.

Will monitor tomorrows run, super exited for the (potential) perf gains!

Maintenance_bot removed a project: Patch-For-Review.Apr 30 2025, 8:30 PM

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1279

analytics: add rewrite_position_delete_files() for mw_content_history

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1279

analytics: add rewrite_position_delete_files() for mw_content_history

xcollazo updated the task description. (Show Details)May 2 2025, 8:11 PM

Maintenance_bot removed a project: Patch-For-Review.May 2 2025, 8:30 PM

For a couple consecutive days now, we have received alerts regarding the HdfsRpcQueueLength metric, suggesting HDFS is under stress at times.

We enabled merge-on-read on this table on Apr 30, for it to run for the first time on May 1. The start of the HDFS stress alerts concurs with this general timeline.

More specifically, the most recent HDFS stress window, as measured by the "Current length of the call queue" metric (same metric used for the alerts), happened from:
Started 2025-05-06 02:45:00 UTC
Ended 2025-05-06 09:30:00 UTC

This concurs with the runtimes of:

The spark_process_events job of the mw_content_merge_events_to_mw_content_history_daily DAG
Started 2025-05-06, 02:32:36 UTC
Ended 2025-05-06, 05:04:23 UTC

The spark_process_changes job of the mw_content_merge_changes_to_mw_content_current_daily DAG
Started 2025-05-06, 05:26:31 UTC
Ended 2025-05-06, 09:31:57 UTC

The interesting thing is that it seems like the total amount of delete files is low compared to all data files. Below, content = 0 is a data file, while content = 1 is a delete file:

spark.sql("""
SELECT count(1) as count, content
FROM wmf_content.mediawiki_content_history_v1.files
GROUP BY content
""").show(20, truncate=False)
[Stage 32:===========================================>              (6 + 2) / 8]
+------+-------+
|count |content|
+------+-------+
|5318  |1      |
|135619|0      |
+------+-------+

However the total deletes (i.e the pointers inside the delete files) are significant:

spark.sql("""
SELECT count(1) as count
FROM wmf_content.mediawiki_content_history_v1.position_deletes
""").show(20, truncate=False)
[Stage 31:>                                                         (0 + 1) / 1]
+-------+
|count  |
+-------+
|1110118|
+-------+


spark.sql("""
SELECT count(1) as count, partition
FROM wmf_content.mediawiki_content_history_v1.position_deletes
GROUP BY partition
ORDER BY count DESC
""").show(20, truncate=False)
[Stage 20:=====================================================>(165 + 2) / 167]
+------+--------------+
|count |partition     |
+------+--------------+
|377118|{enwiki}      |
|306651|{commonswiki} |
|29441 |{frwiki}      |
|29079 |{eswiki}      |
|27853 |{arwiki}      |
|26705 |{zhwiki}      |
|26042 |{ruwiki}      |
|22574 |{itwiki}      |
|22337 |{dewiki}      |
|16615 |{wikidatawiki}|
|16354 |{jawiki}      |
|15948 |{fawiki}      |
|14942 |{ptwiki}      |
|11571 |{idwiki}      |
|8688  |{kowiki}      |
|8088  |{cawiki}      |
|8050  |{plwiki}      |
|8017  |{ukwiki}      |
|7811  |{trwiki}      |
|7704  |{hewiki}      |
+------+--------------+
only showing top 20 rows

Having said that, it is clear that for our downstream use cases of reading this table fully, the amount of deletes is too much, generating way more splits to read the table fully, which in turn also make jobs take longer downstream.

Before disabling merge-on-read though, I want to try a more aggressive table maintenance: from weekly, to daily. Such a strategy can still benefit us file size wise if we consider that each day we do 7 commits to this table.

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1287

analytics: do daily instead of weekly table maintenance on mw-content-history table.

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1287

analytics: do daily instead of weekly table maintenance on mw-content-history table.

Maintenance_bot removed a project: Patch-For-Review.May 6 2025, 8:30 PM

Total deletes now in a much better position compared to T393012#10797777:

spark.sql("""
SELECT count(1) as count
FROM wmf_content.mediawiki_content_history_v1.position_deletes
""").show(20, truncate=False)
spark.sql("""
SELECT count(1) as count
FROM wmf_content.mediawiki_content_history_v1.position_deletes
""").show(20, truncate=False)
[Stage 48:====================================================>   (32 + 2) / 34]
+------+
|count |
+------+
|198395|
+------+

spark.sql("""
SELECT count(1) as count, partition
FROM wmf_content.mediawiki_content_history_v1.position_deletes
GROUP BY partition
ORDER BY count DESC
""").show(20, truncate=False)
[Stage 51:====================================================>   (32 + 2) / 34]
+-----+------------------+
|count|partition         |
+-----+------------------+
|41562|{enwiki}          |
|23494|{commonswiki}     |
|9174 |{frwiki}          |
|4720 |{testwikidatawiki}|
|3863 |{zhwiki}          |
|3597 |{cebwiki}         |
|3440 |{bnwiki}          |
|3317 |{mrwiki}          |
|3272 |{enwikisource}    |
|3179 |{huwikibooks}     |
|3009 |{itwiki}          |
|2896 |{eswiki}          |
|2834 |{simplewiki}      |
|2678 |{frwikisource}    |
|2671 |{fawiki}          |
|2650 |{specieswiki}     |
|2441 |{dewiki}          |
|2404 |{urwiki}          |
|2325 |{ruwiki}          |
|2210 |{jawiki}          |
+-----+------------------+
only showing top 20 rows

Some evidence of HDFS stress remains. although significantly less than other periods.

I think we should give this daily maintenance strategy a couple more runs to see if it stabilizes.

I also think we should tune HDFS's dfs.namenode.handler.count from current 127 to double that, perhaps to 256 (link), but let's wait a bit before attempting that. Perhaps on its own ticket.

@xcollazo regarding the suggested increase of dfs.namenode.handler.count, what's the name nodes load in these crunch cases?

In T393012#10802411, @Ahoelzl wrote:

@xcollazo regarding the suggested increase of dfs.namenode.handler.count, what's the name nodes load in these crunch cases?

an-master1004, the current namenode, was at ~20% CPU usage, nominal RAM usage, nominal disk usage. Here is the Grafana for the event discussed in T393012#10797481: https://grafana.wikimedia.org/goto/Ooc5gwbHg?orgId=1.

This is why I think that dfs.namenode.handler.count is low considering the available resources, especially CPU.

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1292

analytics: deprioritize maintenance for mw-content-history

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1292

analytics: deprioritize maintenance for mw-content-history

Maintenance_bot removed a project: Patch-For-Review.May 8 2025, 2:30 PM

(Pulled this task in to be under T391279: Daily updated wmf_content.mediawiki_content_current_v1 as this is highly related work.)

xcollazo mentioned this in T393695: Frequent crashes of Hadoop nodemanager services with OOM errrors.May 9 2025, 2:49 PM

set "spark.sql.iceberg.locality.enabled":"true" on wmf_content.mediawiki_content_current_v1 ingest.

In T393012#10821876, @xcollazo wrote:

set "spark.sql.iceberg.locality.enabled":"true" on wmf_content.mediawiki_content_current_v1 ingest.

This did not help at all. Reverting to false.

Ok, even though there are more avenues to explore, like tuning the HDFS Namenode further, or going deep into Iceberg to figure why we produce so many FileInfo requests, I am going to throw the towel for now and revert table wmf_content.mediawiki_content_history_v1 from merge-on-read to copy-on-write.

This means the runtime of mw_content_merge_events_to_mw_content_history_daily DAG will go from the current ~45 mins back to ~8 hours.

Not ideal, but this issue has been a big time sink and we need to move on to close on T391279: Daily updated wmf_content.mediawiki_content_current_v1.

Ran the following:

$ hostname -f
an-launcher1002.eqiad.wmnet

$ sudo -u analytics bash

$ kerberos-run-command analytics spark3-sql

spark-sql (default)> ALTER TABLE wmf_content.mediawiki_content_history_v1 SET TBLPROPERTIES (
                   >     'write.delete.mode'='copy-on-write',
                   >     'write.merge.mode'='copy-on-write',
                   >     'write.update.mode'='copy-on-write'
                   > );
25/05/15 16:41:22 WARN BaseTransaction: Failed to load metadata for a committed snapshot, skipping clean-up
Response code
Time taken: 5.757 seconds

Triggered a manual table maintenance run: https://airflow.wikimedia.org/dags/table_maintenance_iceberg_daily/grid?dag_run_id=manual__2025-05-15T16%3A43%3A37.855201%2B00%3A00

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/68

Go back to copy-on-write for mw-content-history.

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/68

Go back to copy-on-write for mw-content-history.

Still to do here:

Run daily maintenance for a couple days to clear up remaining delete files
Move maintenance from daily back to weekly.

xcollazo moved this task from In progress to In Review on the Data-Engineering (Q4 2025 April 1st - June 30th) board.May 15 2025, 5:33 PM

Maintenance_bot removed a project: Patch-For-Review.May 15 2025, 6:30 PM

xcollazo moved this task from In Review to Ready to Deploy on the Data-Engineering (Q4 2025 April 1st - June 30th) board.May 15 2025, 9:16 PM

xcollazo moved this task from Ready to Deploy to Done on the Data-Engineering (Q4 2025 April 1st - June 30th) board.May 22 2025, 5:52 PM

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1350

analytics: change mw-content-history-v1 table maintenance back to weekly

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/1350

analytics: change mw-content-history-v1 table maintenance back to weekly

Maintenance_bot removed a project: Patch-For-Review.May 22 2025, 6:30 PM

In T393012#10827607, @xcollazo wrote:

Still to do here:

Run daily maintenance for a couple days to clear up remaining delete files

Move maintenance from daily back to weekly.

I think we are good with this experiment now.

xcollazo closed this task as Resolved.May 22 2025, 6:43 PM

xcollazo mentioned this in T385112: Investigate reasons for remaining inconsistencies.May 30 2025, 3:51 PM

retention period	7 days	30 days	60 days	90 days
size in TBs	36.7	89.6	158.6	227.6

Enable merge-on-read for wmf_content.mediawiki_content_history_v1Closed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Enable merge-on-read for wmf_content.mediawiki_content_history_v1
Closed, ResolvedPublic
Actions

Related Objects
Search...