Exclude TOC and possibly others from wasting space in page_props table
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	thiemowmde
	Fri, Sep 11, 8:40 AM

Project Tags

Referenced Files

None

Subscribers

Description

While working on https://gerrit.wikimedia.org/r/1338093 (related to T347123) we learned that all so called "double underscore magic words" are unconditionally stored as page props. This is an intended feature in many cases, e.g. for extensions like MediaWiki-extensions-Disambiguator. When __DISAMBIG__ is added to a page (either directly or via a template) that page gets marked as a disambiguation page via the disambiguation page prop.

The problem is that several page props appear to be entirely useless as a page prop and only waste space in the database, making it slower when querying other page props. One possible suggestion is a very short hard-coded list of these magic words that should not be stored as a page prop.

Core currently comes with the following "double underscore ids" in MagicWordFactory:

~~__EXPECTSHORTPAGE__, __EXPECTUNUSEDCATEGORY__, __EXPECTUNUSEDTEMPLATE__: All needed as page props in their corresponding special pages.~~
~~__HIDDENCAT__: Essential to mark hidden categories as such.~~
~~__INDEX__ and __NOINDEX__: Intended meta data about a page and used as such.~~
~~__NEWSECTIONLINK__ and __NONEWSECTIONLINK__: Used by DiscussionTools and possibly others.~~
~~__NOCONTENTCONVERT__ and __NOTITLECONVERT__: Used by Parsoid.~~

Remaining candidates:

__TOC__: This is the strongest contender here as it doesn't really change anything about a page, only the position where the TOC is rendered, and only if that new position is different from the default. External users cannot learn anything about the page by querying this as a page prop, except that the magic word was somewhere on the page. Note the page prop is a bool and not the position of the toc, i.e. it cannot even be used for what it's intended to do.
__FORCETOC__ and __NOTOC__: These change if the table of contents gets inserted into the page's HTML. I don't think Parsoid needs these as page props. Mostly irrelevant since Vector 2022 anyway.
__NOEDITSECTION__: This changes how a page gets rendered, but doesn't serve much of a purpose any more after that's done. When and why would an external process need to know if a page contains edit section links or not? The same information can be found in the page's HTML just by searching for the edit section links via a CSS selector.
__NOGALLERY__: As above.
__STATICREDIRECT__: Not sure. Only used by a job, but that job seems to look directly into the wikitext and not the page prop.

Details

Related Changes in Gerrit:

Subject	Author	Repo	Branch	Lines +/-
__NONEWSECTIONLINK__ is meaningless on non-talk pages	Thiemo Kreuz (WMDE)	mediawiki/core	master	+8 -2
Don't store number of maplinks/mapframes as page props	Thiemo Kreuz (WMDE)	mediawiki/extensions/Kartographer	master	+10 -43
Remove not needed assertions for Kartographer page props	Thiemo Kreuz (WMDE)	mediawiki/extensions/Wikibase	master	+0 -12

Customize query in gerrit

Related Objects

Mentioned Here: T149723: Differentiate between <mapframe> and <maplink> page counts in Grafana
T347123: Reduce database queries on parsercached logged-out page views (Sep 2023)

Event Timeline

thiemowmde created this task.Fri, Sep 11, 8:40 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFri, Sep 11, 8:40 AM

mysql:research@dbstore1008.eqiad.wmnet [enwiki]> select count(*) from page_props where pp_propname = 'toc';
+----------+
| count(*) |
+----------+
|   271341 |
+----------+
1 row in set (0.138 sec)

mysql:research@dbstore1008.eqiad.wmnet [enwiki]> ^DBye
ladsgroup@stat1009:~$ analytics-mysql commonswiki
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 94678372
Server version: 10.11.13-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql:research@dbstore1007.eqiad.wmnet [commonswiki]> select count(*) from page_props where pp_propname = 'toc';
+----------+
| count(*) |
+----------+
|   102582 |
+----------+
1 row in set (0.067 sec)

It's not much. So if it's work, we can let it be.

OTOH, this is the list of biggest storage consumers:

mysql:research@dbstore1007.eqiad.wmnet [commonswiki]> select pp_propname, count(*) from page_props group by pp_propname order by count(*) desc limit 10;
+---------------------------+----------+
| pp_propname               | count(*) |
+---------------------------+----------+
| kartographer_links        | 40283047 |
| jsonconfig_getdata        | 16383388 |
| unexpectedUnconnectedPage | 13994317 |
| defaultsort               |  8821794 |
| wikibase_item             |  6133821 |
| page_image_free           |  4466729 |
| kartographer_frames       |  2671848 |
| hiddencat                 |  1142972 |
| expectunusedcategory      |  1003279 |
| expectedUnconnectedPage   |   963657 |
+---------------------------+----------+
10 rows in set (1 min 25.782 sec)

mysql:research@dbstore1007.eqiad.wmnet [commonswiki]> ^DBye
ladsgroup@stat1009:~$ analytics-mysql enwiki
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 34154567
Server version: 10.11.13-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql:research@dbstore1008.eqiad.wmnet [enwiki]> select pp_propname, count(*) from page_props group by pp_propname order by count(*) desc limit 10;

+---------------------------+----------+
| pp_propname               | count(*) |
+---------------------------+----------+
| wikibase_item             | 10337549 |
| wikibase-shortdesc        |  6962184 |
| defaultsort               |  6101508 |
| nonewsectionlink          |  4596750 |
| noindex                   |  3438442 |
| page_image_free           |  2808493 |
| expectshortpage           |  2059635 |
| displaytitle              |  1365585 |
| unexpectedUnconnectedPage |  1121364 |
| expectedUnconnectedPage   |  1042810 |
+---------------------------+----------+
10 rows in set (23.237 sec)

Maybe there is something in them to be cleaned up easily.

Krinkle added a project: MediaWiki-Core-Platform-Team (Radar).Fri, Sep 11, 9:43 AM

Thanks a lot for the numbers! Here are some ideas:

defaultsort: How often is this identical to the title? We could skip and not store this when it's identical anyway.
displaytitle: This also looks like it's automatically (?) set by a template and possibly identical to the title in many cases. We could skip such cases as well.
kartographer_links and kartographer_frames:
1. The value for these should never be zero (never "0" and never an empty string). That would be pointless and should not be stored in the first place. Can we check if this is the case?
2. It might be possible to drop these two entirely. I don't see them being used outside of tests. The patch that introduced these page props was https://gerrit.wikimedia.org/r/323325, linked to T149723. The comments in T149723 sound like this was either never used or is obsolete by now. Maybe some Grafana board used to show these numbers – but I suspect nobody is looking at them any more. Maybe @MaxSem remembers?
~~wikibase-shortdesc and page_image_free: Can we check if we are accidentally storing empty strings for any of these?~~
- I checked. PageImages already contains code to avoid empty strings. Wikibase contains a comment that empty strings are intentional.

Change #1339668 had a related patch set uploaded (by Thiemo Kreuz (WMDE); author: Thiemo Kreuz (WMDE)):

[mediawiki/extensions/Kartographer@master] [POC] Don't store number of maplinks/mapframes as page props

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

gerritbot added a project: Patch-For-Review.Fri, Sep 11, 10:19 AM

Change #1339671 had a related patch set uploaded (by Thiemo Kreuz (WMDE); author: Thiemo Kreuz (WMDE)):

[mediawiki/extensions/Wikibase@master] Remove not needed assertions for Kartographer page props

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

This might be interesting:

mysql:research@dbstore1008.eqiad.wmnet [enwiki]> select pp_propname, count(*) from page_props where pp_value is null or pp_value = '' or pp_value = '0' or pp_value = 0 group by pp_propname order by count(*) desc limit 10
;
+-------------------------+----------+
| pp_propname             | count(*) |
+-------------------------+----------+
| wikibase_item           | 10337636 |
| wikibase-shortdesc      |  6274815 |
| defaultsort             |  5942210 |
| nonewsectionlink        |  4596876 |
| noindex                 |  3438575 |
| page_image_free         |  2704054 |
| expectshortpage         |  2084059 |
| displaytitle            |  1364845 |
| expectedUnconnectedPage |  1042835 |
| page_image              |   666304 |
+-------------------------+----------+
10 rows in set, 65535 warnings (2 min 27.792 sec)

And

mysql:research@dbstore1007.eqiad.wmnet [commonswiki]> select pp_propname, count(*) from page_props where pp_value is null or pp_value = '' or pp_value = '0' or pp_value = 0 group by pp_propname order by count(*) desc limit 10
    -> ;

+-------------------------+----------+
| pp_propname             | count(*) |
+-------------------------+----------+
| defaultsort             |  8554900 |
| wikibase_item           |  6133883 |
| page_image_free         |  4281411 |
| hiddencat               |  1143002 |
| expectunusedcategory    |  1003252 |
| expectedUnconnectedPage |   963662 |
| noeditsection           |   493178 |
| displaytitle            |   340447 |
| notoc                   |   161602 |
| noindex                 |   127229 |
+-------------------------+----------+
10 rows in set, 65535 warnings (6 min 59.058 sec)

I guess for defaultsort empty string has a meaning but not sure. Definitely not wikibase items sthough.

In T437686#12311813, @Ladsgroup wrote:

I guess for defaultsort empty string has a meaning but not sure.

CategoryLinksTable::setParserOutput() contains

$sortKey = $parserOutput->getPageProperty( "defaultsort" ) ?? '';

– so no, missing default sort key and empty string default sort key don’t mean different things. However, the string 0 is different, so your numbers may be misleading if they are dominated by pp_value = '0' or pp_value = 0 rows.

In T437686#12311150, @thiemowmde wrote:

__NOEDITSECTION__: This changes how a page gets rendered, but doesn't serve much of a purpose any more after that's done. When and why would an external process need to know if a page contains edit section links or not? The same information can be found in the page's HTML just by searching for the edit section links via a CSS selector.

__NOGALLERY__: As above.

These are not the same case. __NOEDITSECTION__ affects the parser output, but __NOGALLERY__ doesn’t – it affects content below the parser output. Category pages don’t need to be reparsed every time a page is added to or removed from them. However, __NOGALLERY__ is also recorded as ParserOutputFlags::NO_GALLERY, both of them are probably unnecessary.

Also, aren’t the section edit links added post-parse? Especially in the Parsoid world, where roughly the same parse result is used for VE editing (which doesn’t need edit links) and read views (which do).

page_image_free | 4281411

How is this possible? There is code in place that should make this impossible.

wikibase_item | 10337636

As above this seems impossible. Does it makes a difference when it's a blob field? When I try your queries locally the extra numeric = 0 comparison appears to return all kinds of non-numeric string values.

Ah, yeah, I think my query is messed up. I want to be 100% and that caused it to be the exact opposite:

mysql:research@dbstore1008.eqiad.wmnet [enwiki]> select pp_propname, count(*) from page_props where pp_value is null or pp_value = '' or pp_value = '0' group by pp_propname order by count(*) desc limit 10;
+--------------------------+----------+
| pp_propname              | count(*) |
+--------------------------+----------+
| nonewsectionlink         |  4597015 |
| noindex                  |  3438567 |
| expectshortpage          |  2117713 |
| expectedUnconnectedPage  |  1042816 |
| noeditsection            |   409142 |
| disambiguation           |   383554 |
| archivedtalk             |   350968 |
| expectunusedcategory     |   317040 |
| wikibase-shortdesc       |   314736 |
| wikibase-badge-Q70893996 |   278216 |
+--------------------------+----------+
10 rows in set (1 min 36.361 sec)

mysql:research@dbstore1007.eqiad.wmnet [commonswiki]> select pp_propname, count(*) from page_props where pp_value is null or pp_value = '' or pp_value = '0' group by pp_propname order by count(*) desc limit 10;
+-----------------------------+----------+
| pp_propname                 | count(*) |
+-----------------------------+----------+
| hiddencat                   |  1143057 |
| expectunusedcategory        |  1003266 |
| expectedUnconnectedPage     |   963674 |
| noeditsection               |   493178 |
| notoc                       |   161602 |
| noindex                     |   127247 |
| toc                         |   102582 |
| index                       |    71545 |
| translate-has-languages-tag |    57161 |
| unexpectedUnconnectedPage   |    47065 |
+-----------------------------+----------+
10 rows in set (4 min 23.160 sec)

• matmarex unsubscribed.Sat, Sep 12, 10:50 PM

Change #1341020 had a related patch set uploaded (by Thiemo Kreuz (WMDE); author: Thiemo Kreuz (WMDE)):

[mediawiki/core@master] __NONEWSECTIONLINK__ is meaningless on non-talk pages

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

Thanks for the updated numbers. I checked the entire list again. All these instances of "boolean" page props with empty values are legitimate, as far as I can tell. Still there are two candidates that might be worth having another look:

noeditsection: ~900,000. As said before I don't see this page prop being used anywhere.
nonewsectionlink: ~4,600,000. This is because of Template:Redirect category shell, which is used ~4,100,000 times on enwiki. The thing is:
- While DiscussionTools uses this page prop, it's meaningless on non-talk pages, and meaningless on redirect pages. Both are automatically excluded in the code.
- Core does the same. The page prop is meaningless on non-talk pages, and meaningless on redirect pages.

Change #1339671 merged by jenkins-bot:

[mediawiki/extensions/Wikibase@master] Remove not needed assertions for Kartographer page props

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

ReleaseTaggerBot added a project: MW-1.47-notes (1.47.0-wmf.21; 2026-09-22).Sat, Sep 19, 10:00 PM

Change #1339668 merged by jenkins-bot:

[mediawiki/extensions/Kartographer@master] Don't store number of maplinks/mapframes as page props

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

Change #1341020 abandoned by Thiemo Kreuz (WMDE):