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 triaged this task as Normal priority.Dec 21 2018, 10:28 AM
Marostegui created this task.
Restricted Application removed a project: Patch-For-Review. · View Herald TranscriptDec 21 2018, 10:28 AM
Restricted Application added a subscriber: Cosine02. · View Herald Transcript
elukey added a comment.Jan 3 2019, 9:13 AM

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 :(

Sure, fine by me!

Milimetric lowered the priority of this task from Normal 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.

leila added a comment.Jan 15 2019, 5:50 PM

@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.

elukey added a subscriber: Halfak.Jan 15 2019, 5:53 PM

Thanks all!

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

diego added a subscriber: diego.Jan 15 2019, 6:01 PM

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

elukey updated the task description. (Show Details)Jan 16 2019, 8:27 AM

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 closed this task as Resolved.Jan 16 2019, 11:20 AM
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!