flaggedrevs_statistics is never purged
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	DannyS712
	Dec 2 2020, 2:55 AM

Project Tags

Referenced Files

None

Subscribers

Description

New rows are added to the flaggedrevs_statistics table every time FlaggedRevsStats::updateCache is called, but the old values are never removed; only the values for the latest timestamp are ever needed. Can the old rows be purged?

For reference, there are currently 1611863 rows in the enwikibooks table, 1611818 of which are for timestamps that are not the latest and thus are never used, and 45 of which are used until the table is updated again.

MariaDB [enwikibooks_p]> SELECT COUNT(*) FROM flaggedrevs_statistics;
+----------+
| COUNT(*) |
+----------+
|  1611863 |
+----------+
1 row in set (0.50 sec)

MariaDB [enwikibooks_p]> SELECT MAX(frs_timestamp) FROM flaggedrevs_statistics;
+--------------------+
| MAX(frs_timestamp) |
+--------------------+
| 20201202020039     |
+--------------------+
1 row in set (0.00 sec)

MariaDB [enwikibooks_p]> SELECT COUNT(*) FROM flaggedrevs_statistics WHERE frs_timestamp != 20201202020039;
+----------+
| COUNT(*) |
+----------+
|  1611818 |
+----------+
1 row in set (1.59 sec)

MariaDB [enwikibooks_p]> SELECT * FROM flaggedrevs_statistics WHERE frs_timestamp = 20201202020039;
+----------------+-------------------------------------+--------------+
| frs_timestamp  | frs_stat_key                        | frs_stat_val |
+----------------+-------------------------------------+--------------+
| 20201202020039 | pendingLag-average                  |     42779539 |
| 20201202020039 | reviewLag-anon-average              |      1425172 |
| 20201202020039 | reviewLag-anon-median               |       961228 |
| 20201202020039 | reviewLag-anon-percentile:35        |       682757 |
| 20201202020039 | reviewLag-anon-percentile:45        |       878580 |
| 20201202020039 | reviewLag-anon-percentile:55        |      1146432 |
| 20201202020039 | reviewLag-anon-percentile:65        |      1659521 |
| 20201202020039 | reviewLag-anon-percentile:75        |      1813621 |
| 20201202020039 | reviewLag-anon-percentile:85        |      3447574 |
| 20201202020039 | reviewLag-anon-percentile:90        |      3877841 |
| 20201202020039 | reviewLag-anon-percentile:95        |      4657014 |
| 20201202020039 | reviewLag-anon-sampleEndTimestamp   |   1235078705 |
| 20201202020039 | reviewLag-anon-sampleSize           |           62 |
| 20201202020039 | reviewLag-anon-sampleStartTimestamp |   1234473905 |
| 20201202020039 | reviewLag-user-average              |       857294 |
| 20201202020039 | reviewLag-user-median               |       708265 |
| 20201202020039 | reviewLag-user-percentile:35        |       666534 |
| 20201202020039 | reviewLag-user-percentile:45        |       701822 |
| 20201202020039 | reviewLag-user-percentile:55        |       778604 |
| 20201202020039 | reviewLag-user-percentile:65        |       859203 |
| 20201202020039 | reviewLag-user-percentile:75        |       956896 |
| 20201202020039 | reviewLag-user-percentile:85        |      1090743 |
| 20201202020039 | reviewLag-user-percentile:90        |      1184455 |
| 20201202020039 | reviewLag-user-percentile:95        |      1382784 |
| 20201202020039 | reviewLag-user-sampleEndTimestamp   |   1235078705 |
| 20201202020039 | reviewLag-user-sampleSize           |          455 |
| 20201202020039 | reviewLag-user-sampleStartTimestamp |   1234473905 |
| 20201202020039 | reviewedPages-NS:0                  |        65228 |
| 20201202020039 | reviewedPages-NS:10                 |         8819 |
| 20201202020039 | reviewedPages-NS:102                |         4029 |
| 20201202020039 | reviewedPages-NS:110                |         2294 |
| 20201202020039 | reviewedPages-NS:6                  |         1007 |
| 20201202020039 | reviewedPages-NS:828                |           97 |
| 20201202020039 | syncedPages-NS:0                    |        63647 |
| 20201202020039 | syncedPages-NS:10                   |         8817 |
| 20201202020039 | syncedPages-NS:102                  |         4026 |
| 20201202020039 | syncedPages-NS:110                  |         2294 |
| 20201202020039 | syncedPages-NS:6                    |         1007 |
| 20201202020039 | syncedPages-NS:828                  |           97 |
| 20201202020039 | totalPages-NS:0                     |        81959 |
| 20201202020039 | totalPages-NS:10                    |         8921 |
| 20201202020039 | totalPages-NS:102                   |         4037 |
| 20201202020039 | totalPages-NS:110                   |         2294 |
| 20201202020039 | totalPages-NS:6                     |         1771 |
| 20201202020039 | totalPages-NS:828                   |           97 |
+----------------+-------------------------------------+--------------+
45 rows in set (0.86 sec)

Related Objects

Mentioned Here: T177951: Add flagged revision status statistics to Wikistats 2.0
T163107: Flagged revs Special:ValidationStatistics update doesn't work

Event Timeline

DannyS712 created this task.Dec 2 2020, 2:55 AM

Restricted Application added a project: User-DannyS712. · View Herald TranscriptDec 2 2020, 2:55 AM

Restricted Application added a subscriber: Aklapper. · View Herald Transcript

DannyS712 moved this task from Unsorted to Reports on the User-DannyS712 board.Dec 2 2020, 2:55 AM

Please don't purge them. If one wants to do timelines or graphs (Like i do) of how the revieweing have been developed or wants to compare different language versions he or she needs historical data.

Example

https://commons.wikimedia.org/wiki/File:PendingLag-average_tools.wmflabs.org_fiwiki-tools_fr_stats_report_9.png

I guess this is from some never finished feature, maybe (as it was never really exposed much in FR itself?)... @aaron ? Or is it used by a cloud tool?

We do run FlaggedRevs/maintenance/updateStats.php in cron on Wikimedia wikis...

I'm struggling to see any reason why we can't just nuke these old rows, unless they're being kept for some sort of historical value, or statistics analysis that isn't immediately obvious.

You can find https://commons.wikimedia.org/wiki/File:Flagged_revs_pendingLag-average_201506.png by some searching though, and in fact, there is a category https://commons.wikimedia.org/wiki/Category:Flagged_Revisions_statistics . It wouldn't surprise me if there's more usagesa around

If they are being used, needs better documentation.

If they're not being... I think just adding a (batched) row delete, with waiting for slavelag into either the end of FlaggedRevsStats::updateCache, and/or into a seperate function and then called by the maintenance script after it has run FlaggedRevsStats::updateCache

I note this isn't a major use of disk space... It seems to be using a 90-100 MB for enwikibooks, so not an urgent issue. And if it was, I guess the DBA would've flagged it before

MariaDB [enwikibooks]> select distinct(frs_stat_key) from flaggedrevs_statistics;
+-------------------------------------+
| frs_stat_key                        |
+-------------------------------------+
| pendingLag-average                  |
| reviewLag-anon-average              |
| reviewLag-anon-median               |
| reviewLag-anon-percentile:35        |
| reviewLag-anon-percentile:45        |
| reviewLag-anon-percentile:55        |
| reviewLag-anon-percentile:65        |
| reviewLag-anon-percentile:75        |
| reviewLag-anon-percentile:85        |
| reviewLag-anon-percentile:90        |
| reviewLag-anon-percentile:95        |
| reviewLag-anon-sampleEndTimestamp   |
| reviewLag-anon-sampleSize           |
| reviewLag-anon-sampleStartTimestamp |
| reviewLag-average                   |
| reviewLag-median                    |
| reviewLag-percentile:35             |
| reviewLag-percentile:45             |
| reviewLag-percentile:55             |
| reviewLag-percentile:65             |
| reviewLag-percentile:75             |
| reviewLag-percentile:85             |
| reviewLag-percentile:90             |
| reviewLag-percentile:95             |
| reviewLag-sampleEndTimestamp        |
| reviewLag-sampleSize                |
| reviewLag-sampleStartTimestamp      |
| reviewLag-user-average              |
| reviewLag-user-median               |
| reviewLag-user-percentile:35        |
| reviewLag-user-percentile:45        |
| reviewLag-user-percentile:55        |
| reviewLag-user-percentile:65        |
| reviewLag-user-percentile:75        |
| reviewLag-user-percentile:85        |
| reviewLag-user-percentile:90        |
| reviewLag-user-percentile:95        |
| reviewLag-user-sampleEndTimestamp   |
| reviewLag-user-sampleSize           |
| reviewLag-user-sampleStartTimestamp |
| reviewedPages-NS:0                  |
| reviewedPages-NS:10                 |
| reviewedPages-NS:102                |
| reviewedPages-NS:110                |
| reviewedPages-NS:6                  |
| reviewedPages-NS:828                |
| syncedPages-NS:0                    |
| syncedPages-NS:10                   |
| syncedPages-NS:102                  |
| syncedPages-NS:110                  |
| syncedPages-NS:6                    |
| syncedPages-NS:828                  |
| totalPages-NS:0                     |
| totalPages-NS:10                    |
| totalPages-NS:102                   |
| totalPages-NS:110                   |
| totalPages-NS:6                     |
| totalPages-NS:828                   |
+-------------------------------------+
58 rows in set (0.00 sec)

MariaDB [enwikibooks]> select distinct(frs_stat_key), count(frs_stat_key) from flaggedrevs_statistics group by frs_stat_key;
+-------------------------------------+---------------------+
| frs_stat_key                        | count(frs_stat_key) |
+-------------------------------------+---------------------+
| pendingLag-average                  |               36757 |
| reviewLag-anon-average              |               34470 |
| reviewLag-anon-median               |               34470 |
| reviewLag-anon-percentile:35        |               34470 |
| reviewLag-anon-percentile:45        |               34470 |
| reviewLag-anon-percentile:55        |               34470 |
| reviewLag-anon-percentile:65        |               34470 |
| reviewLag-anon-percentile:75        |               34470 |
| reviewLag-anon-percentile:85        |               34470 |
| reviewLag-anon-percentile:90        |               34470 |
| reviewLag-anon-percentile:95        |               34470 |
| reviewLag-anon-sampleEndTimestamp   |               34470 |
| reviewLag-anon-sampleSize           |               34470 |
| reviewLag-anon-sampleStartTimestamp |               34470 |
| reviewLag-average                   |                2287 |
| reviewLag-median                    |                2287 |
| reviewLag-percentile:35             |                2287 |
| reviewLag-percentile:45             |                2287 |
| reviewLag-percentile:55             |                2287 |
| reviewLag-percentile:65             |                2287 |
| reviewLag-percentile:75             |                2287 |
| reviewLag-percentile:85             |                2287 |
| reviewLag-percentile:90             |                2287 |
| reviewLag-percentile:95             |                2287 |
| reviewLag-sampleEndTimestamp        |                2287 |
| reviewLag-sampleSize                |                2287 |
| reviewLag-sampleStartTimestamp      |                2287 |
| reviewLag-user-average              |               34470 |
| reviewLag-user-median               |               34470 |
| reviewLag-user-percentile:35        |               34470 |
| reviewLag-user-percentile:45        |               34470 |
| reviewLag-user-percentile:55        |               34470 |
| reviewLag-user-percentile:65        |               34470 |
| reviewLag-user-percentile:75        |               34470 |
| reviewLag-user-percentile:85        |               34470 |
| reviewLag-user-percentile:90        |               34470 |
| reviewLag-user-percentile:95        |               34470 |
| reviewLag-user-sampleEndTimestamp   |               34470 |
| reviewLag-user-sampleSize           |               34470 |
| reviewLag-user-sampleStartTimestamp |               34470 |
| reviewedPages-NS:0                  |               36757 |
| reviewedPages-NS:10                 |               36757 |
| reviewedPages-NS:102                |               36757 |
| reviewedPages-NS:110                |               36757 |
| reviewedPages-NS:6                  |               36757 |
| reviewedPages-NS:828                |               32615 |
| syncedPages-NS:0                    |               36757 |
| syncedPages-NS:10                   |               36757 |
| syncedPages-NS:102                  |               36757 |
| syncedPages-NS:110                  |               36757 |
| syncedPages-NS:6                    |               36757 |
| syncedPages-NS:828                  |               32615 |
| totalPages-NS:0                     |               36757 |
| totalPages-NS:10                    |               36757 |
| totalPages-NS:102                   |               36757 |
| totalPages-NS:110                   |               36757 |
| totalPages-NS:6                     |               36757 |
| totalPages-NS:828                   |               32615 |
+-------------------------------------+---------------------+
58 rows in set (0.49 sec)

I think the most important use case is statistics analysis. Also afaik i think it is needed also WMF/Community figures out what to do with the FlaggedRevs as we don't really have much a data how the the FlaggedRevs is used.

If usage of FlaggedRevs will be continued in WMF wikis then personally I would like to see that T177951 would happen and the historical data would be migrated to Wikistats 2.0 and then the old code could be dropped, but again this would need decision from WMF to implement this.

And for current uses, least I am using them for the graphs. Though, this is not realtime as it used to aggregate data from other tables too than flaggedrevs_statistics so i have updated the stats when I have needed them.

https://fiwiki-tools.toolforge.org/fr_stats_example.html?db=fiwiki_p,huwiki_p,dewiki_p,plwiki_p&frs_key=pendingLag-average

Not an immediate concern from the DBA side at the moment.
On enwiki flaggedrevs_statistics is 52M on disk.
On s2, the biggest is at plwiki with 116M.
On s5, the biggest is dewiki: 268M
On s6, ruwiki: 220M
On s7, ukwiki 304M and huwiki 220M.
On ukwiki the first row is from 20120423200734 and has 1422674 rows, so not too bad.

Sizes are reasonable, so no strong opinions here on whether they should be purged or not.

Marostegui removed a project: DBA.Dec 2 2020, 6:50 AM

LSobanski subscribed.Dec 2 2020, 9:47 AM

One use case for the historical data.

https://meta.wikimedia.org/wiki/Requests_for_comment/Flagged_revisions_should_display_latest_versions

flaggedrevs_statistics is never purgedOpen, Needs TriagePublicActions

Description

Related Objects

Event Timeline

flaggedrevs_statistics is never purged
Open, Needs TriagePublic
Actions