Page MenuHomePhabricator
Create Task
Maniphest T433990

Optimize echo tables in x1
Open, Needs TriagePublic
Actions

Description

After T383948: Echo notification expiration should be time-based, not hardcoded 2000 being implemented and some time passing, I have deleted around 100M rows from echo tables in the past week. Per my estimations, it should have cut the size of these tables to half. So doing a quick optimize table could save some decent chunk of storage.

The tables are:

| echo_email_batch                       |
| echo_event                             |
| echo_notification                      |
| echo_target_page

Related Objects

Mentioned In
rSCHCH327212c39d9f: Add optimize_echo_tables.py
Mentioned Here
P95913 dbctl commit (dc=all): 'Repooling after maintenance db2249 (T433990)'
P95910 dbctl commit (dc=all): 'Repooling after maintenance db2249 (T433990)'
P95909 dbctl commit (dc=all): 'Depooling db2249 (T433990)'
P95908 dbctl commit (dc=all): 'Repooling after maintenance db2231 (T433990)'
P95904 dbctl commit (dc=all): 'Repooling after maintenance db2231 (T433990)'
P95903 dbctl commit (dc=all): 'Depooling db2231 (T433990)'
P95902 dbctl commit (dc=all): 'Repooling after maintenance db2215 (T433990)'
P95899 dbctl commit (dc=all): 'Repooling after maintenance db2215 (T433990)'
P95898 dbctl commit (dc=all): 'Depooling db2215 (T433990)'
P95897 dbctl commit (dc=all): 'Repooling after maintenance db2196 (T433990)'
P95894 dbctl commit (dc=all): 'Repooling after maintenance db2196 (T433990)'
P95893 dbctl commit (dc=all): 'Depooling db2196 (T433990)'
P95892 dbctl commit (dc=all): 'Repooling after maintenance db2186 (T433990)'
P95889 dbctl commit (dc=all): 'Repooling after maintenance db2186 (T433990)'
P95888 dbctl commit (dc=all): 'Depooling db2186 (T433990)'
P95887 dbctl commit (dc=all): 'Repooling after maintenance db1264 (T433990)'
P95884 dbctl commit (dc=all): 'Repooling after maintenance db1264 (T433990)'
P95883 dbctl commit (dc=all): 'Depooling db1264 (T433990)'
P95882 dbctl commit (dc=all): 'Repooling after maintenance db1237 (T433990)'
P95879 dbctl commit (dc=all): 'Repooling after maintenance db1237 (T433990)'
P95878 dbctl commit (dc=all): 'Depooling db1237 (T433990)'
P95877 dbctl commit (dc=all): 'Repooling after maintenance db1203 (T433990)'
P95874 dbctl commit (dc=all): 'Repooling after maintenance db1203 (T433990)'
P95872 dbctl commit (dc=all): 'Depooling db1203 (T433990)'
P95871 dbctl commit (dc=all): 'Repooling after maintenance db1179 (T433990)'
P95868 dbctl commit (dc=all): 'Repooling after maintenance db1179 (T433990)'
P95867 dbctl commit (dc=all): 'Depooling db1179 (T433990)'
P95866 dbctl commit (dc=all): 'Repooling after maintenance db1179 (T433990)'
P95865 dbctl commit (dc=all): 'Depooling db1179 (T433990)'
T383948: Echo notification expiration should be time-based, not hardcoded 2000

Event Timeline

Ladsgroup created this task.Tue, Aug 4, 3:41 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptTue, Aug 4, 3:41 PM
CodeReviewBot added a project: Patch-For-Review.Tue, Aug 4, 3:50 PM

ladsgroup opened https://gitlab.wikimedia.org/repos/sre/schema-changes/-/merge_requests/68

Add optimize_echo_tables.py

CodeReviewBot added a comment.Tue, Aug 4, 4:18 PM

ladsgroup merged https://gitlab.wikimedia.org/repos/sre/schema-changes/-/merge_requests/68

Add optimize_echo_tables.py

Ladsgroup claimed this task.Tue, Aug 4, 4:22 PM
Ladsgroup moved this task from Triage to In progress on the DBA board.
Stashbot added a comment.Tue, Aug 4, 4:22 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T16:22:37Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db1179 (T433990)', diff saved to https://phabricator.wikimedia.org/P95865 and previous config saved to /var/cache/conftool/dbconfig/20260804-162236-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 4:24 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T16:24:25Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1179 (T433990)', diff saved to https://phabricator.wikimedia.org/P95866 and previous config saved to /var/cache/conftool/dbconfig/20260804-162424-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 4:27 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T16:27:38Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db1179 (T433990)', diff saved to https://phabricator.wikimedia.org/P95867 and previous config saved to /var/cache/conftool/dbconfig/20260804-162736-ladsgroup.json

Maintenance_bot removed a project: Patch-For-Review.Tue, Aug 4, 4:30 PM
Ladsgroup mentioned this in rSCHCH327212c39d9f: Add optimize_echo_tables.py.Tue, Aug 4, 4:39 PM
Stashbot added a comment.Tue, Aug 4, 5:34 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T17:34:00Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1179 (T433990)', diff saved to https://phabricator.wikimedia.org/P95868 and previous config saved to /var/cache/conftool/dbconfig/20260804-173359-ladsgroup.json

Ladsgroup added a comment.Tue, Aug 4, 5:57 PM

37GB saved on db1179: https://grafana.wikimedia.org/d/000000377/host-overview?orgId=1&refresh=5m&var-server=db1179&var-datasource=000000026&var-cluster=analytics&from=2026-08-04T15:48:42.526Z&to=2026-08-04T17:56:26.645Z&timezone=utc&viewPanel=panel-28

Stashbot added a comment.Tue, Aug 4, 6:06 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T18:06:19Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1179 (T433990)', diff saved to https://phabricator.wikimedia.org/P95871 and previous config saved to /var/cache/conftool/dbconfig/20260804-180618-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 6:07 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T18:07:22Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db1203 (T433990)', diff saved to https://phabricator.wikimedia.org/P95872 and previous config saved to /var/cache/conftool/dbconfig/20260804-180721-ladsgroup.json

Ladsgroup added a comment.Tue, Aug 4, 6:32 PM

Before:

root@db2186:/srv/sqldata# ls -Ssh */echo_*.ibd | head --lines 30
  26G enwiki/echo_event.ibd
  15G enwiki/echo_notification.ibd
  11G commonswiki/echo_event.ibd
 6.7G wikishared/echo_unread_wikis.ibd
 4.5G commonswiki/echo_notification.ibd
 4.0G frwiki/echo_event.ibd
 3.7G dewiki/echo_event.ibd
 3.4G frwiki/echo_notification.ibd
 3.3G ruwiki/echo_event.ibd
 3.0G zhwiki/echo_notification.ibd
 2.3G eswiki/echo_event.ibd
 2.2G fawiki/echo_event.ibd
 2.0G itwiki/echo_event.ibd
 2.0G ptwiki/echo_event.ibd
 2.0G ukwiki/echo_event.ibd
 2.0G zhwiki/echo_event.ibd
 1.9G dewiki/echo_notification.ibd
 1.8G eswiki/echo_notification.ibd
 1.7G enwiki/echo_target_page.ibd
 1.6G arwiki/echo_notification.ibd
 1.6G arwiki/echo_event.ibd
 1.6G ruwiki/echo_notification.ibd
 1.4G ptwiki/echo_notification.ibd
 1.4G itwiki/echo_notification.ibd
 1.3G plwiki/echo_event.ibd
 1.3G jawiki/echo_event.ibd
 1.2G wikidatawiki/echo_event.ibd
 1.2G wikishared/echo_push_subscription.ibd
 1.0G commonswiki/echo_target_page.ibd
 980M fawiki/echo_notification.ibd
root@db2186:/srv/sqldata# find . -wholename "*/echo_*.ibd" -ls | awk '{total += $7} END {print total}'
164282023936

(=153GB)

After:

root@db1179:/srv/sqldata# ls -Ssh */echo_*.ibd | head --lines 30
7.4G enwiki/echo_event.ibd
3.3G wikishared/echo_unread_wikis.ibd
3.2G commonswiki/echo_event.ibd
2.9G enwiki/echo_notification.ibd
1.2G ruwiki/echo_event.ibd
1.2G wikishared/echo_push_subscription.ibd
1.1G frwiki/echo_event.ibd
1.1G dewiki/echo_event.ibd
865M commonswiki/echo_notification.ibd
825M fawiki/echo_event.ibd
809M zhwiki/echo_event.ibd
773M ukwiki/echo_event.ibd
733M frwiki/echo_notification.ibd
661M ptwiki/echo_event.ibd
617M eswiki/echo_event.ibd
593M enwiki/echo_target_page.ibd
557M itwiki/echo_event.ibd
493M wikidatawiki/echo_event.ibd
469M plwiki/echo_event.ibd
464M zhwiki/echo_notification.ibd
445M arwiki/echo_event.ibd
441M dewiki/echo_notification.ibd
369M ruwiki/echo_notification.ibd
365M jawiki/echo_event.ibd
337M arwiki/echo_notification.ibd
329M ptwiki/echo_notification.ibd
325M eswiki/echo_notification.ibd
301M commonswiki/echo_target_page.ibd
289M itwiki/echo_notification.ibd
285M hewiki/echo_event.ibd
root@db1179:/srv/sqldata# find . -wholename "*/echo_*.ibd" -ls | awk '{total += $7} END {print total}'
47006203904

(=44GB)

Stashbot added a comment.Tue, Aug 4, 7:27 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T19:27:39Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1203 (T433990)', diff saved to https://phabricator.wikimedia.org/P95874 and previous config saved to /var/cache/conftool/dbconfig/20260804-192738-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 7:59 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T19:59:58Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1203 (T433990)', diff saved to https://phabricator.wikimedia.org/P95877 and previous config saved to /var/cache/conftool/dbconfig/20260804-195957-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 10:23 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T22:23:46Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db1237 (T433990)', diff saved to https://phabricator.wikimedia.org/P95878 and previous config saved to /var/cache/conftool/dbconfig/20260804-222345-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 11:11 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T23:11:45Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1237 (T433990)', diff saved to https://phabricator.wikimedia.org/P95879 and previous config saved to /var/cache/conftool/dbconfig/20260804-231144-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 11:44 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T23:44:06Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1237 (T433990)', diff saved to https://phabricator.wikimedia.org/P95882 and previous config saved to /var/cache/conftool/dbconfig/20260804-234405-ladsgroup.json

Stashbot added a comment.Tue, Aug 4, 11:45 PM

Mentioned in SAL (#wikimedia-operations) [2026-08-04T23:45:09Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db1264 (T433990)', diff saved to https://phabricator.wikimedia.org/P95883 and previous config saved to /var/cache/conftool/dbconfig/20260804-234508-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 12:01 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T00:01:49Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1264 (T433990)', diff saved to https://phabricator.wikimedia.org/P95884 and previous config saved to /var/cache/conftool/dbconfig/20260805-000148-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 12:34 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T00:34:09Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db1264 (T433990)', diff saved to https://phabricator.wikimedia.org/P95887 and previous config saved to /var/cache/conftool/dbconfig/20260805-003408-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 1:30 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T01:30:30Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db2186 (T433990)', diff saved to https://phabricator.wikimedia.org/P95888 and previous config saved to /var/cache/conftool/dbconfig/20260805-013029-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 2:00 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T02:00:52Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2186 (T433990)', diff saved to https://phabricator.wikimedia.org/P95889 and previous config saved to /var/cache/conftool/dbconfig/20260805-020051-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 2:33 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T02:33:11Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2186 (T433990)', diff saved to https://phabricator.wikimedia.org/P95892 and previous config saved to /var/cache/conftool/dbconfig/20260805-023310-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 2:34 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T02:34:14Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db2196 (T433990)', diff saved to https://phabricator.wikimedia.org/P95893 and previous config saved to /var/cache/conftool/dbconfig/20260805-023413-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 3:08 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T03:08:16Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2196 (T433990)', diff saved to https://phabricator.wikimedia.org/P95894 and previous config saved to /var/cache/conftool/dbconfig/20260805-030815-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 3:40 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T03:40:37Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2196 (T433990)', diff saved to https://phabricator.wikimedia.org/P95897 and previous config saved to /var/cache/conftool/dbconfig/20260805-034036-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 5:19 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T05:19:40Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db2215 (T433990)', diff saved to https://phabricator.wikimedia.org/P95898 and previous config saved to /var/cache/conftool/dbconfig/20260805-051939-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 5:49 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T05:49:19Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2215 (T433990)', diff saved to https://phabricator.wikimedia.org/P95899 and previous config saved to /var/cache/conftool/dbconfig/20260805-054918-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 6:21 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T06:21:39Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2215 (T433990)', diff saved to https://phabricator.wikimedia.org/P95902 and previous config saved to /var/cache/conftool/dbconfig/20260805-062137-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 6:22 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T06:22:42Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db2231 (T433990)', diff saved to https://phabricator.wikimedia.org/P95903 and previous config saved to /var/cache/conftool/dbconfig/20260805-062240-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 6:52 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T06:52:07Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2231 (T433990)', diff saved to https://phabricator.wikimedia.org/P95904 and previous config saved to /var/cache/conftool/dbconfig/20260805-065206-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 7:24 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T07:24:28Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2231 (T433990)', diff saved to https://phabricator.wikimedia.org/P95908 and previous config saved to /var/cache/conftool/dbconfig/20260805-072426-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 7:25 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T07:25:31Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Depooling db2249 (T433990)', diff saved to https://phabricator.wikimedia.org/P95909 and previous config saved to /var/cache/conftool/dbconfig/20260805-072529-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 7:56 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T07:56:51Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2249 (T433990)', diff saved to https://phabricator.wikimedia.org/P95910 and previous config saved to /var/cache/conftool/dbconfig/20260805-075650-ladsgroup.json

Stashbot added a comment.Wed, Aug 5, 8:29 AM

Mentioned in SAL (#wikimedia-operations) [2026-08-05T08:29:10Z] <ladsgroup@cumin1003> dbctl commit (dc=all): 'Repooling after maintenance db2249 (T433990)', diff saved to https://phabricator.wikimedia.org/P95913 and previous config saved to /var/cache/conftool/dbconfig/20260805-082908-ladsgroup.json

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits