Page MenuHomePhabricator

Clean up staging db
Closed, ResolvedPublic

Description

Feel free to discard this task if you don't think this is worth the time.

These are the current list of tables we have on dbstore1002 for the staging DB.
They do look very old and in most cases maybe we can just take a backup and kill them? In order not to migrate useless things to the new dbstore scheme?

List updated after T212493#4883535

+------------------------------------------------+---------------------+---------------------+------------+
| table_name                                     | create_time         | update_time         | table_rows |
+------------------------------------------------+---------------------+---------------------+------------+
| woe_wmf_staff_account                          | 2016-12-23 10:16:36 | NULL                |        535 |
| woe_wikidb                                     | 2016-12-23 10:16:36 | NULL                |        901 |
| wmde_analytics_echoStatusNotif_users_yesterday | 2017-09-13 03:31:42 | NULL                |      68616 |
| nlwiki_moodbar_feedback_response               | 2017-10-06 08:18:45 | NULL                |       1191 |
| nlwiki_moodbar_feedback                        | 2017-10-06 08:18:44 | NULL                |       7543 |
| wmde_analytics_echoStatusNotif_users_today     | 2017-09-13 03:31:30 | NULL                |          0 |
| frwikisource_moodbar_feedback_response         | 2017-10-06 08:18:45 | NULL                |         83 |
| wmde_analytics_betafeature_users_yesterday     | 2017-09-13 03:31:19 | NULL                |    3953730 |
| frwikisource_moodbar_feedback                  | 2017-10-06 08:18:45 | NULL                |        164 |
| wmde_analytics_betafeature_users_today         | 2017-07-14 10:43:47 | NULL                |          0 |
| itwikivoyage_moodbar_feedback_response         | 2017-10-06 08:18:45 | NULL                |          0 |
| itwikivoyage_moodbar_feedback                  | 2017-10-06 08:18:45 | NULL                |         21 |
| enwiki_moodbar_feedback_response               | 2017-10-06 08:18:44 | NULL                |      16406 |
| enwiki_moodbar_feedback                        | 2017-10-06 08:18:40 | NULL                |      63304 |
| tawiki_moodbar_feedback_response               | 2017-10-06 08:18:45 | NULL                |          2 |
| incubatorwiki_moodbar_feedback_response        | 2017-10-06 08:18:45 | NULL                |        105 |
| tawiki_moodbar_feedback                        | 2017-10-06 08:18:45 | NULL                |          2 |
| incubatorwiki_moodbar_feedback                 | 2017-10-06 08:18:45 | NULL                |       1296 |
| templateanalytics                              | 2017-04-07 14:31:35 | NULL                |          0 |
| sewikimedia_moodbar_feedback_response          | 2017-10-06 08:18:45 | NULL                |          5 |
| sewikimedia_moodbar_feedback                   | 2017-10-06 08:18:45 | NULL                |         13 |
| woe_wmf_volunteer_account                      | 2016-12-23 10:16:36 | NULL                |          3 |
| page_creation_enwiki_201503                    | 2015-03-26 15:14:35 | 2015-03-26 15:14:35 |    3993530 |
| toku_test2                                     | 2015-12-08 19:49:10 | 2015-12-08 19:49:10 |          1 |
| mediawiki_history_sample                       | 2017-12-11 14:45:30 | 2017-12-14 03:49:43 |    1214447 |
| nettrom_creations_from_page_sources            | 2018-03-26 23:47:46 | 2018-03-26 23:52:13 |       8122 |
| nettrom_creations_from_page                    | 2018-03-26 23:47:15 | 2018-03-26 23:52:13 |       8122 |
| nettrom_creations_from_revision_sources        | 2018-03-26 23:47:50 | 2018-03-26 23:54:17 |       8122 |
| nettrom_creations_from_revision                | 2018-03-26 23:47:19 | 2018-03-26 23:54:17 |       8122 |
| tbayer_pagertest                               | 2018-11-08 08:49:25 | 2018-11-08 08:53:51 |        100 |
| cx_abuse_filter_daily                          | 2018-11-27 15:29:01 | 2018-11-27 15:29:01 |          1 |
| annual_readership_growth                       | 2014-11-19 17:04:56 | 2019-01-14 10:04:00 |        237 |
| anon_user_edits_sample                         | 2016-08-12 23:58:32 | 2019-01-14 10:04:00 |       1000 |
| article_stats                                  | 2015-01-20 13:51:14 | 2019-01-14 10:04:12 |    3746601 |
| by_wiki                                        | 2016-12-15 00:59:24 | 2019-01-14 10:04:26 |    2593707 |
| cites_enwiki_20150602                          | 2015-07-12 23:39:45 | 2019-01-14 10:04:41 |    3431532 |
| countries                                      | 2014-11-17 21:33:16 | 2019-01-14 10:04:42 |        250 |
| coop_learner                                   | 2015-04-11 16:49:14 | 2019-01-14 10:04:42 |         54 |
| coop_invite                                    | 2015-04-11 16:43:51 | 2019-01-14 10:04:42 |        907 |
| content_namespaces                             | 2019-01-07 20:59:09 | 2019-01-14 10:04:42 |         70 |
| congress_edits                                 | 2014-07-31 16:04:27 | 2019-01-14 10:04:42 |     291871 |
| click_stream                                   | 2014-11-04 00:31:55 | 2019-01-14 10:04:42 |         55 |
| cites_nlwiki_20150602                          | 2015-07-17 18:42:28 | 2019-01-14 10:04:42 |     103419 |
| crosswiki_unified_bot                          | 2017-04-27 19:14:28 | 2019-01-14 10:04:43 |       6522 |
| ec_role_user_sample                            | 2015-09-18 12:43:27 | 2019-01-14 10:04:55 |        800 |
| editor_data                                    | 2014-07-28 21:18:47 | 2019-01-14 10:05:53 |   22802057 |
| editor_day                                     | 2014-07-28 12:45:19 | 2019-01-14 10:14:00 |  123251796 |
| enwiki_monthly_aq                              | 2016-12-17 17:32:16 | 2019-01-14 10:45:27 |  441992726 |
| enwiki_pagelinks_ns0                           | 2015-03-08 19:44:11 | 2019-01-14 11:12:49 |  383063217 |
| enwiki_page_ns0                                | 2015-03-13 00:50:48 | 2019-01-14 11:13:31 |   11580334 |
| enwiki_reverted_20140820                       | 2017-03-04 20:27:53 | 2019-01-14 11:19:49 |   65434198 |
| events_first_namespace_edit                    | 2014-09-23 15:06:14 | 2019-01-14 11:19:56 |          0 |
| enwiki_unified_bot_20170315                    | 2017-03-15 22:58:23 | 2019-01-14 11:19:56 |       1660 |
| events_first_namespace_edit_old                | 2014-09-16 23:14:21 | 2019-01-14 11:20:31 |   11242304 |
| events_first_sandbox_edit                      | 2014-10-03 18:32:13 | 2019-01-14 11:20:34 |     673925 |
| events_first_sandbox_edit_old                  | 2014-09-18 14:43:10 | 2019-01-14 11:20:36 |     672478 |
| events_sampled_user                            | 2014-10-03 20:12:12 | 2019-01-14 11:20:37 |     110772 |
| events_sampled_first_sandbox_edit              | 2014-09-17 13:20:17 | 2019-01-14 11:20:37 |        985 |
| events_sandbox_edit                            | 2014-09-18 14:21:34 | 2019-01-14 11:21:02 |    5954057 |
| flagged_bot                                    | 2014-09-05 20:25:21 | 2019-01-14 11:21:03 |      16231 |
| experimental_user_stats_20140721               | 2014-07-17 23:33:11 | 2019-01-14 11:21:03 |      25689 |
| frwiki_pagelinks_ns0                           | 2015-03-07 00:45:15 | 2019-01-14 11:28:18 |  104132375 |
| frwiki_page_ns0                                | 2015-03-07 01:09:16 | 2019-01-14 11:28:31 |    2965491 |
| help_portal_newcomer                           | 2015-01-12 15:20:14 | 2019-01-14 11:28:32 |        737 |
| handcoded_user_code                            | 2014-11-26 00:30:35 | 2019-01-14 11:28:32 |       1063 |
| handcoded_user                                 | 2014-11-26 00:47:24 | 2019-01-14 11:28:32 |       1063 |
| han_cats                                       | 2014-09-29 14:27:09 | 2019-01-14 11:28:32 |       2665 |
| halfak_eligible_user                           | 2015-02-12 00:50:09 | 2019-01-14 11:28:32 |      12043 |
| importance_classification                      | 2014-10-23 15:30:20 | 2019-01-14 11:29:02 |    7497006 |
| impressions_hive                               | 2014-11-19 22:17:53 | 2019-01-14 11:34:36 |   45804868 |
| inlink_count                                   | 2015-12-30 18:41:29 | 2019-01-14 11:41:10 |   12151507 |
| learner_profile                                | 2015-04-11 16:24:16 | 2019-01-14 11:41:20 |          0 |
| local_user_info                                | 2015-07-08 21:54:20 | 2019-01-14 11:47:53 |  106390490 |
| magio_users                                    | 2014-09-01 17:15:19 | 2019-01-14 11:49:59 |       2620 |
| magio_user                                     | 2014-09-01 17:16:54 | 2019-01-14 11:49:59 |       2620 |
| mc                                             | 2014-11-19 23:45:42 | 2019-01-14 11:50:55 |   14079223 |
| mediaviewer_optout                             | 2014-07-23 21:35:47 | 2019-01-14 11:51:52 |      11528 |
| mep_word_persistence                           | 2015-12-03 16:35:56 | 2019-01-14 12:34:42 |  435986042 |
| missing_labs_new_user_20140921                 | 2014-10-10 17:55:04 | 2019-01-14 12:58:58 |       1185 |
| monthly_page_edits                             | 2015-01-05 16:26:18 | 2019-01-14 13:00:12 |   16415049 |
| mwcites_enwiki_20150112                        | 2015-02-18 22:09:47 | 2019-01-14 13:00:18 |    1291553 |
| new_users_labs_20140921                        | 2014-10-10 17:51:39 | 2019-01-14 13:00:33 |       6842 |
| nlwiki_reverted                                | 2016-04-08 20:32:39 | 2019-01-14 13:00:34 |      19894 |
| organic_inlink_count                           | 2015-01-08 17:18:24 | 2019-01-14 13:01:06 |   11464477 |
| organic_link                                   | 2015-01-07 13:24:48 | 2019-01-14 13:12:06 |  209134095 |
| overall_control_month_stats                    | 2014-07-30 23:43:03 | 2019-01-14 13:21:55 |     407344 |
| overall_token_stats_cleaned                    | 2014-09-16 20:14:04 | 2019-01-14 13:21:57 |    1028955 |
| overall_token_stats                            | 2014-07-30 19:58:35 | 2019-01-14 13:22:00 |    1028955 |
| page_name_views_dupes                          | 2015-12-31 08:20:05 | 2019-01-14 13:31:12 |  127987744 |
| pageviews04                                    | 2015-01-14 19:16:16 | 2019-01-14 13:33:05 |   43488232 |
| pageviews05                                    | 2015-04-06 07:26:48 | 2019-01-14 13:35:19 |   51109873 |
| pageviews_by_country_language                  | 2015-02-06 08:31:35 | 2019-01-14 13:35:21 |       1228 |
| pageviews                                      | 2014-11-18 21:19:17 | 2019-01-14 13:37:20 |   43568621 |
| pageviews_per_project_country_v2               | 2015-02-24 18:23:56 | 2019-01-14 13:37:21 |      12274 |
| pentaho04                                      | 2015-01-14 20:49:48 | 2019-01-14 13:37:26 |    1530114 |
| pentahoviews05                                 | 2015-04-06 09:05:31 | 2019-01-14 13:37:32 |    1828373 |
| pentahoviews_countries                         | 2014-11-20 22:07:10 | 2019-01-14 13:37:38 |    1679403 |
| pentahoviews                                   | 2014-11-18 22:14:19 | 2019-01-14 13:37:44 |    1679403 |
| rad_labeled_user                               | 2016-11-07 15:08:44 | 2019-01-14 13:37:44 |       1063 |
| record_impression                              | 2014-10-31 17:10:33 | 2019-01-14 13:38:48 |   13167822 |
| referer_data                                   | 2014-09-14 15:01:36 | 2019-01-14 13:39:59 |   31056556 |
| resolved_inlink_count                          | 2015-12-30 18:47:51 | 2019-01-14 13:40:14 |    5044527 |
| resolved_view_count                            | 2016-01-12 15:38:09 | 2019-01-14 13:40:27 |          0 |
| resolved_organic_inlink_count                  | 2015-01-08 17:24:16 | 2019-01-14 13:40:27 |    4782450 |
| revert_20150301_commonswiki                    | 2015-04-01 14:07:43 | 2019-01-14 13:40:33 |    1460090 |
| revert_20150301_dewiki                         | 2015-04-01 14:04:42 | 2019-01-14 13:41:30 |   14081776 |
| revert_20150301_ptwiki                         | 2015-04-01 14:07:39 | 2019-01-14 13:41:47 |    4948260 |
| revert_20150301_wikidatawiki                   | 2015-04-01 14:07:49 | 2019-01-14 13:41:48 |          0 |
| revert_20150304_enwiki                         | 2015-03-31 18:48:23 | 2019-01-14 13:47:49 |   87573047 |
| rev_reverted_20k_sample                        | 2015-05-14 04:16:24 | 2019-01-14 13:47:51 |      47289 |
| rev_ids_20k_sample                             | 2015-05-16 15:06:03 | 2019-01-14 13:47:51 |      80000 |
| sessions_enwiki_20150801                       | 2016-02-27 23:20:49 | 2019-01-14 13:56:47 |  154379416 |
| tbayer_readnavevents_20160107                  | 2016-05-27 20:40:33 | 2019-01-14 13:57:05 |    7200310 |
| tbayer_readnavsessions_20160108005             | 2016-04-29 08:56:11 | 2019-01-14 13:57:12 |          0 |
| tbayer_readnavsessions_20160107                | 2016-04-29 09:07:13 | 2019-01-14 13:57:12 |    1498379 |
| tbayer_readnavtimesessions_20160107            | 2016-06-02 16:32:19 | 2019-01-14 13:57:15 |    1498379 |
| tbayer_test2                                   | 2016-04-07 21:34:11 | 2019-01-14 13:57:19 |          8 |
| tbayer_test1                                   | 2016-03-23 01:25:56 | 2019-01-14 13:57:19 |         28 |
| tbayer_readnavtimesessions5sec_20160107        | 2016-06-02 19:28:12 | 2019-01-14 13:57:19 |    1498379 |
| tbayer_test3                                   | 2016-04-29 08:30:14 | 2019-01-14 13:57:23 |    1510999 |
| temp3                                          | 2015-01-13 00:32:59 | 2019-01-14 13:57:23 |       2978 |
| temp                                           | 2014-11-13 23:29:50 | 2019-01-14 13:57:36 |    4686386 |
| theodora                                       | 2015-03-29 21:20:46 | 2019-01-14 13:57:37 |         79 |
| th2_experimental_user                          | 2015-09-28 21:18:20 | 2019-01-14 13:57:37 |      14766 |
| tgr_uw_terminating_errors                      | 2014-09-24 18:02:33 | 2019-01-14 13:57:37 |        110 |
| tgr_revdel_tmp                                 | 2015-03-05 03:27:44 | 2019-01-14 13:57:37 |        343 |
| tgr_gather_user_requests                       | 2016-03-18 17:11:46 | 2019-01-14 13:57:37 |        123 |
| th_link_additions                              | 2015-11-24 20:23:41 | 2019-01-14 13:57:39 |     444682 |
| th_subst_template_additions                    | 2015-11-25 14:00:01 | 2019-01-14 14:07:32 |   94939126 |
| top_2016_by_month                              | 2016-12-14 01:18:45 | 2019-01-14 14:07:43 |        240 |
| tr_experimental_user_revision                  | 2014-09-30 21:14:37 | 2019-01-14 14:07:44 |      50696 |
| tr_experimental_user                           | 2014-09-30 21:07:24 | 2019-01-14 14:07:44 |      41033 |
| user_registration_approx                       | 2014-11-13 22:54:01 | 2019-01-14 14:08:52 |   21283152 |
| ve2_pilot_users                                | 2015-05-28 08:37:34 | 2019-01-14 14:08:52 |       4189 |
| ve2_experimental_users                         | 2015-06-08 21:09:51 | 2019-01-14 14:08:52 |      26971 |
| ve2_experimental_user_revision_stats           | 2015-09-30 16:49:50 | 2019-01-14 14:08:52 |      61541 |
| wikidata_nonbot_reverted_sample                | 2016-01-31 20:26:41 | 2019-01-14 14:08:54 |     488536 |
| wikidata_nonbot_sample                         | 2016-01-30 18:53:51 | 2019-01-14 14:08:56 |    1000000 |
| wiki_month_registrations                       | 2015-05-08 21:37:56 | 2019-01-14 14:08:56 |      11184 |
| wiki_info                                      | 2014-06-03 04:13:38 | 2019-01-14 14:08:56 |        797 |
| woe_wiki_edit_count                            | 2016-12-23 10:20:52 | 2019-01-14 14:08:57 |      70877 |
| yearly_page_edits                              | 2015-01-05 16:51:44 | 2019-01-14 14:09:28 |    8541686 |
| editor_month                                   | 2016-12-05 23:58:25 | 2019-01-15 18:20:43 |   57913761 |
+------------------------------------------------+---------------------+---------------------+------------+

Event Timeline

Marostegui created this task.
Restricted Application added a subscriber: Stang. · View Herald Transcript

If possible I'd prefer to skip this part and keep the staging db as it is, from what I can see it is not that huge.. The main issue is that getting all those tables reviewed before the April deadline will surely drain a lot of time :(

Milimetric lowered the priority of this task from Medium to Low.Jan 7 2019, 4:45 PM
Milimetric moved this task from Incoming to Operational Excellence on the Analytics board.

May a suggest a different route? Let's migrate the mediawiki replicated tables first- then migrate the staging ones on a per-case bases. After all, it makes no sense to copy them to, which of the new servers? Once we check the replication works as intended, we can ask what to keep and what to remover. In some cases, users may prefer to regenerate them from "fresh data"? Just an idea.

So the staging db is kinda problematic to clean up, since it is difficult to figure out owners and reach out to people. I have already started to ask to people to review/drop the old tables, but as precautionary step I'd migrate everything to one of the new dbstore hosts and then figure out later on if something else can be dropped.

@Neil_P._Quinn_WMF @Halfak @Milimetric @mforns @leila @nettrom_WMF @DarTar @Tbayer @Addshore would you mind to review the tables in the description and see if anything is definitely not needed and can be dropped?

@elukey Definitely the tables prefixed with mforns_ can be deleted.

@elukey Please delete tables that include leila and leizi in their name. And my apologies that I didn't clean up after myself. I will do better in the future.

@elukey same for all dartar_* tables, they can safely be removed.

It looks like I only have a few staging tables because I've been cleaning up as I go, but I checked and dropped ve_experiment_expanded and neilpquinn_VE_experiment_revs

Hi! I'm not sure what is this, but for sure you can delete diego_tmp.
Thanks

I went ahead and deleted all tables starting with "nettrom_" except the four tables referenced in T190434#4085830.

I also checked with @JKatzWMF and dropped jkatz_foo, jkatz_foosss26, and editor_stats_JK_test.

dropped all milimetric_ tables, and gave up on my dreams of figuring out what exactly is going on with mediawiki's revision table.

Thanks all for your clean up! So remaining tables to drop:

| diego_tmp                  |
| leila_artrecom_user_gender         |
| leila_artrecom_user_gender_en      |
| leila_design_research_participants |
| leila_wikigrok_test3_pageCampResp  |
| leila_wikigrok_test3_responses     |
| leizi_wikigrok_actions         |
| leizi_wikigrok_invalid_entries |
| leizi_wikigrok_responses       |
| leizi_wikigrok_retention       |
| dartar_arwiki_desktop_regs           |
| dartar_arwiki_desktop_regs_1d        |
| dartar_arwiki_mobile_regs            |
| dartar_arwiki_mobile_regs_1d         |
| dartar_dewiki_desktop_regs           |
| dartar_dewiki_desktop_regs_1d        |
| dartar_dewiki_mobile_regs            |
| dartar_dewiki_mobile_regs_1d         |
| dartar_enwiki_app_regs_0714          |
| dartar_enwiki_app_regs_0714_nae      |
| dartar_enwiki_app_regs_0714_snae     |
| dartar_enwiki_desktop_regs           |
| dartar_enwiki_desktop_regs_0714      |
| dartar_enwiki_desktop_regs_0714_nae  |
| dartar_enwiki_desktop_regs_0714_snae |
| dartar_enwiki_desktop_regs_1d        |
| dartar_enwiki_ipad_1d                |
| dartar_enwiki_ipad_regs              |
| dartar_enwiki_mobile_regs            |
| dartar_enwiki_mobile_regs_0714       |
| dartar_enwiki_mobile_regs_0714_nae   |
| dartar_enwiki_mobile_regs_0714_snae  |
| dartar_enwiki_mobile_regs_1d         |
| dartar_enwiki_mobile_regs_t          |
| dartar_eswiki_desktop_regs           |
| dartar_eswiki_desktop_regs_1d        |
| dartar_enwiki_mobile_regs_0714_snae  |
| dartar_enwiki_mobile_regs_1d         |
| dartar_enwiki_mobile_regs_t          |
| dartar_eswiki_desktop_regs           |
| dartar_eswiki_desktop_regs_1d        |
| dartar_eswiki_mobile_regs            |
| dartar_eswiki_mobile_regs_1d         |
| dartar_frwiki_desktop_regs           |
| dartar_frwiki_desktop_regs_1d        |
| dartar_frwiki_mobile_regs            |
| dartar_frwiki_mobile_regs_1d         |
| dartar_itwiki_desktop_regs           |
| dartar_itwiki_desktop_regs_1d        |
| dartar_itwiki_mobile_regs            |
| dartar_itwiki_mobile_regs_1d         |
| dartar_jawiki_desktop_regs           |
| dartar_jawiki_desktop_regs_1d        |
| dartar_jawiki_mobile_regs            |
| dartar_jawiki_mobile_regs_1d         |
| dartar_mobile_nru                    |
| dartar_ruwiki_desktop_regs           |
| dartar_ruwiki_desktop_regs_1d        |
| dartar_ruwiki_mobile_regs            |
| dartar_ruwiki_mobile_regs_1d         |
| dartar_ve_daily                      |
| dartar_zhwiki_desktop_regs           |
| dartar_zhwiki_desktop_regs_1d        |
| dartar_zhwiki_mobile_regs            |
| dartar_zhwiki_mobile_regs_1d         |
| mforns_bucketize_test        |
| mforns_warehouse_edit        |
| mforns_warehouse_page        |
| mforns_warehouse_user        |

Mentioned in SAL (#wikimedia-operations) [2019-01-16T08:11:52Z] <elukey> drop unneeded tables from the staging db on dbstore1002 according to T212493#4883535

The wmde_prefixed tables are still in use and are written to every day :)

wmde_analytics_echoStatusNotif_users_yesterday2017-09-13 03:31:42NULL68616
wmde_analytics_echoStatusNotif_users_today2017-09-13 03:31:30NULL0
wmde_analytics_betafeature_users_yesterday2017-09-13 03:31:19NULL3953730
wmde_analytics_betafeature_users_today2017-07-14 10:43:47NULL0

Table creation happens in these legacy scripts that still run every day:

@Marostegui I don't see any more "easy" way to ping people about those tables, do you think that this clean up was ok (and the task can be closed) or that we'd need something more?

Marostegui assigned this task to elukey.

I think the clean up work was good. Most of the tables that are left are in use.
Thanks!