Why not deploy a new schema instead? Are the already inserted user agents going to change?

Are the already inserted user agents going to change?

no

Why not deploy a new schema instead?

A new schema would not change length of column as that is hardcoded on db so we would have this same problem, other than alter would need to run in a smaller set of tables, newly created.

Changing capsule schema for all schemas is problematic as is a change that is non backwards compatible (can explain why that is)
Have in mind that there are two schemas in EL: 1) the capsule (common to all events, describes columns that are there at all times, https://meta.wikimedia.org/wiki/Schema:EventCapsule) and 2)the particular event schema. The userAgent is described on the capsule schema.

other than alter would need to run in a smaller set of tables, newly created.

For me that would be a huge win- it could be deployed in seconds, rather than weeks.

Changing capsule schema for all schemas is problematic

That seems like a limitation on how the application works, not the storage (EL is supposed to be append-only).

I will check what a "schema change of all user agent column on every table of eventlogging database" would entail, but you seem to be asking for a multi-month project changing TB of data and blocking insertion of new events, just to avoid some extra coding. Nothing against it if you take care of doing it and handling potential replication breaking, I will just warn you after I do some quick testing what to see what it would actually take in time and resources, and its dangers.

other than alter would need to run in a smaller set of tables, newly created.

Sorry, I did not explain this well-enough. A capsule change would not help little in this case but let me explain why that is: the tables are not versioned according to capsule schema but rather 'event schema' thus changing capsule in this case would involve having to issue a bunch of database statements . We would basically need to rename all the tables from, say, EventName_1234 to EventName_1234_old and have new tables be automatically created (as new records come in) as Eventname_1234. These new tables will hold a column for event_userAgent with a different length. That would work too but it seems more work on the database end that doing alters on the tables that are currently used (only those, not all of them). If that is not the case let me know.

Please let us know if you think this is still months of work, as i said we can stop system, alter tables currently used and start system again. I am not sure as to how long would alters need to run though.

Thanks for the prompt response.

ping @jcrespo Is the solution of renaming tables easier on the dabase end? that would work great for us too. Please let us know.

Not sure all details of that-but yes, renaming a table is an instant operation, doing a schema change can take up to a week per table and server.

The schema changes do not have to be that bad technology-wise, but this was designed with a very constrained design where nothing would be changed after it has data. Storing JSON + virtual columns for indexing would have been a better option, which allow arbitrary content dynamically, while still allowing fast indexed search: http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/

Excellent, let us know what you think is a good time on your end to do this and we will take an outage accordingly. For us, the sooner the better. Ideally (i think) we might not want to do all tables at the same time as it means that all tables would need to be re-created at the same time and this sounds like it might cause problems but again, not sure, it might not matter so much. Let us know what you think is the best way to proceed. Rough set of setps (cc @Ottomata for double checking)
And, again, this apply only to active tables not to all of them.

1. stop EL
2. rename active tables from Schema_version to Schema_version_ 15423246 (15423246 is capsule vs)
3. change https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79 for length of varchars to what? (@jcrespo to advice)
4. restart eventlogging, new tables should be created for incoming data with new column length

change https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79 for length of varchars to what? (@jcrespo to advice)

FYI, the comment on this line says something about setting the length to avoid problems with index field length limits. I don't think we have any indexes on userAgent, and afaik indexes on any varchar fields in EL tables would have to be manually added (right?), and I personally don't know of any we do that for these days.

So, could we just increase this limit to something like 1024, and then never add indexes on varchar columns?

ping @jcrespo. Could you give us an ETA on when we could start doing these changes?

In T160454#3102530, @Nuria wrote:

Excellent, let us know what you think is a good time on your end to do this and we will take an outage accordingly. For us, the sooner the better. Ideally (i think) we might not want to do all tables at the same time as it means that all tables would need to be re-created at the same time and this sounds like it might cause problems but again, not sure, it might not matter so much. Let us know what you think is the best way to proceed. Rough set of setps (cc @Ottomata for double checking)
And, again, this apply only to active tables not to all of them.

1. stop EL
2. rename active tables from Schema_version to Schema_version_ 15423246 (15423246 is capsule vs)

Could you give us the exact list of tables that would need to be renamed?
We'd need to rename them on both, master (db1046) and slaves (db1047 and dbstore1002)

3. change https://github.com/wikimedia/eventlogging/blob/master/eventlogging/jrm.py#L79 for length of varchars to what? (@jcrespo to advice)

That is more up to you guys than to us really, as @Ottomata mentioned, 1024 is fine by us I think.

4. restart eventlogging, new tables should be created for incoming data with new column length

Thanks!

Could you give us the exact list of tables that would need to be renamed?

Yes, will compile today and post here.

In T160454#3106348, @Nuria wrote:

Could you give us the exact list of tables that would need to be renamed?

Yes, will compile today and post here.

Great - thanks!

List of tables:

CentralAuth_5690875
ChangesListFilters_16174591
ChangesListFilters_16403617
CommandInvocation_15243810
ContentTranslation_11628043
ContentTranslationCTA_16017678
ContentTranslationError_11767097
ContentTranslationSuggestion_16344900
CookieBlock_16241436
EchoInteraction_15823738
EchoMail_5467650
Edit_13457736
EditConflict_8860941
EditorActivation_14208837
ExternalLinksChange_15716074
FlowReplies_10561344
GeoFeatures_12914994
GettingStartedRedirectImpression_7355552
GuidedTourButtonClick_13869649
GuidedTourExited_8690566
GuidedTourExternalLinkActivation_8690560
GuidedTourGuiderHidden_8690549
GuidedTourGuiderImpression_8694395
GuidedTourInternalLinkActivation_8690553
ImageMetricsCorsSupport_11686678
Kartographer_16132745
MediaViewer_10867062
MediaWikiPingback_15781718
MobileAppCategorizationAttempts_5359208
MobileAppLoginAttempts_5257721
MobileAppShareAttempts_5346170
MobileAppUploadAttempts_5334329
MobileOptionsTracking_14003392
MobileWebLanguageSwitcher_15302503
MobileWebMainMenuClickTracking_11568715
MobileWebSearch_12054448
MobileWebUIClickTracking_10742159
MobileWikiAppAppearanceSettings_10375462
MobileWikiAppArticleSuggestions_10590869
MobileWikiAppArticleSuggestions_11448426
MobileWikiAppArticleSuggestions_12443791
MobileWikiAppArticleSuggestions_15302212
MobileWikiAppCreateAccount_8240702
MobileWikiAppCreateAccount_9135391
MobileWikiAppDailyStats_12637385
MobileWikiAppEdit_16256587
MobileWikiAppEdit_9003125
MobileWikiAppFeed_15734713
MobileWikiAppFindInPage_14586774
MobileWikiAppInstallReferrer_12601905
MobileWikiAppIntents_15237384
MobileWikiAppLangSelect_12588733
MobileWikiAppLinkPreview_12143205
MobileWikiAppLinkPreview_14095177
MobileWikiAppLinkPreview_15730939
MobileWikiAppLogin_8234533
MobileWikiAppLogin_9135390
MobileWikiAppMediaGallery_10923135
MobileWikiAppMediaGallery_12588701
MobileWikiAppNavMenu_12732211
MobileWikiAppOnboarding_9123466
MobileWikiAppPageScroll_14591606
MobileWikiAppProtectedEditAttempt_8682497
MobileWikiAppReadingLists_15520526
MobileWikiAppSavedPages_10375480
MobileWikiAppSearch_10641988
MobileWikiAppSearch_15729321
MobileWikiAppSessions_14031591
MobileWikiAppSessions_15522505
MobileWikiAppShareAFact_11331974
MobileWikiAppShareAFact_12588711
MobileWikiAppStuffHappens_8955468
MobileWikiAppTabs_12453651
MobileWikiAppToCInteraction_10375484
MobileWikiAppToCInteraction_11014396
MobileWikiAppToCInteraction_14585319
MobileWikiAppWidgets_11312870
MobileWikiAppWiktionaryPopup_15158116
MultimediaViewerAttribution_9758179
MultimediaViewerDimensions_10014238
MultimediaViewerDuration_10427980
MultimediaViewerNetworkPerformance_15573630
MultimediaViewerVersusPageFilePerformance_7907636
NavigationTiming_15485142
NavigationTiming_16305090
NewEditorEdit_6792669
PageContentSaveComplete_5588433
PageCreation_7481635
PageDeletion_7481655
PageMove_7495717
PageRestoration_7758372
Popups_16208085
Popups_16364296
PrefUpdate_5563398
ReadingDepth_16325045
RelatedArticles_14496900
RelatedArticles_16352530
SaveTiming_15396492
ServerSideAccountCreation_5487345
TestSearchSatisfaction2_15357244
TestSearchSatisfaction2_15700292
TestSearchSatisfaction2_15922352
TestSearchSatisfaction2_16270835
TranslationRecommendationAPIRequests_16261139
TranslationRecommendationUIRequests_15484897
TranslationRecommendationUserAction_15858947
UniversalLanguageSelector_7327441
UploadWizardErrorFlowEvent_11772725
UploadWizardExceptionFlowEvent_11772722
UploadWizardFlowEvent_11772723
UploadWizardStep_11772724
UploadWizardTutorialActions_5803466
UploadWizardUploadFlowEvent_11772717
WikimediaBlogVisit_5308166
WikipediaPortal_14377354
WikipediaPortal_15890769
WikipediaZeroUsage_14574251

We will need to:

• send notice to users
• update automated scripts

Change 343122 had a related patch set uploaded (by Nuria):
[eventlogging] Bumping up length of varchar columns

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

Thanks for the list of tables.
From the DBA side, this would be the only thing to execute really (assuming you just want to add the "_ 15423246" to the and as you said at: T160454#3106339) on all the hosts (db1046,db1047 and dbstore1002) once you have stopped EL, it shouldn't take more than a few seconds:

@Marostegui I think @otto can run script in our end, let us know if that is OK with you and we will take a small outage and run script tomorrow

@Nuria, Marostegui is out today- if you can wait, I would wait one extra day to be sure he can make it.

Sorry folks, am I reading this correctly: All active EL tables will be renamed and replaced by the new version (with the parsed UAs)? That would be a pretty disruptive change, which at the very least should be announced beforehand to minimize confusion about unexpected results and give people time to prepare. Not afterwards. I'm sending a followup in the existing Analytics-l thread now.

That would be a pretty disruptive change, which at the very least should be announced beforehand to minimize confusion about unexpected results and give people time to prepare.

It will be announced before hand, we still do not know when are we going to be able to do it or who is going to do it. We will announce before it happens.

I'm sending a followup in the existing Analytics-l thread now.

There is no existing thread for this work as afar as i know.

In T160454#3115312, @Nuria wrote:

That would be a pretty disruptive change, which at the very least should be announced beforehand to minimize confusion about unexpected results and give people time to prepare.

It will be announced before hand, we still do not know when are we going to be able to do it or who is going to do it. We will announce before it happens.

I'm sending a followup in the existing Analytics-l thread now.

Sent: https://lists.wikimedia.org/pipermail/analytics/2017-March/005795.html

There is no existing thread for this work as afar as i know.

I was referring to the thread that led to the creation of T153207 . But yeah, it would have been good to send further updates there as the work progressed, also about the decision to remove raw UAs entirely (i.e. the redefinition of T153207).

In T160454#3114957, @Nuria wrote:

@Marostegui I think @otto can run script in our end, let us know if that is OK with you and we will take a small outage and run script tomorrow

That is fine by me. I have changed a bit the "for" loop, to include set session sql_log_bin=0 to avoid inserting this on the binlog (although it should not really matters as those hosts do not have slaves, but just in case), it is on this comment: T160454#3109219

The file tables_to_rename.txt is present on /home/marostegui on all the hosts, db1046, db1047 and dbstore1002.

Ok, will be sending e-mail today with the change we are going and we can plan on doing it Thursday (48 hrs notice)

Change 343122 merged by Ottomata:
[eventlogging] Bumping up length of varchar columns

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

In T160454#3118049, @Nuria wrote:

Ok, will be sending e-mail today with the change we are going and we can plan on doing it Thursday (48 hrs notice)

Great, thanks! (For later reference, this announcement thread is here.)

BTW, I assume we are going to update https://meta.wikimedia.org/wiki/Schema:EventCapsule beforehand too?

BTW, I assume we are going to update https://meta.wikimedia.org/wiki/Schema:EventCapsule beforehand too?

We will be doing that once this work is finished, there is no need to do it before,

Change 344049 had a related patch set uploaded (by Nuria):
[analytics/discovery-stats] Adding renamed tables to sql union statements

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

Change 344054 had a related patch set uploaded (by Nuria):
[analytics/limn-edit-data] Adding renamed tables to sql union statements

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

Change 344055 had a related patch set uploaded (by Nuria):
[analytics/limn-flow-data] Adding renamed tables to sql union statements

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

Hi!
What's the status of this?

Happening today! :)

Mentioned in SAL (#wikimedia-operations) [2017-03-29T16:53:01Z] <marostegui> Disable puppet on db1047 and dbstore1002 for maintenance - T160454

Mentioned in SAL (#wikimedia-operations) [2017-03-29T16:55:52Z] <marostegui> Stop eventlog syncs to db1047 and dbstore1002 for maintenance - T160454

Done! https://etherpad.wikimedia.org/p/analytics-el-table-rename

Change 344054 merged by Nuria:
[analytics/limn-edit-data@master] Adding renamed tables to sql union statements

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

Change 344055 merged by Nuria:
[analytics/limn-flow-data@master] Adding renamed tables to sql union statements

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

Change 344049 merged by Milimetric:
[analytics/discovery-stats@master] Adding renamed tables to sql union statements

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

The eventlogging script on db1047 is failing due to:

Thu Mar 30 06:07:49 UTC 2017 localhost ContentTranslationError_11767097, createERROR 1005 (HY000) at line 19: Can't create table `log`.`ContentTranslationError_11767097` (errno: 24 "Too many open files")

The script on dbstore1002 works fine, where the values are a lot higher:

root@DBSTORE[(none)]> select @@hostname;
+-------------+
| @@hostname  |
+-------------+
| dbstore1002 |
+-------------+
1 row in set (0.00 sec)

root@DBSTORE[(none)]> show global variables like 'open_files%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 400000 |
+------------------+--------+
1 row in set (0.00 sec)

I wasn't even able to run lsof on db1047, it was stuck (probably due to the rename yesterday).
What I have done to fix the issue:

• Stop sync script on terbium
• Stop all slaves
• Stop mysql
• Start mysql
• Start script on terbium
• Run puppet (and check that the scripts starts fine locally

root     27866  0.0  0.0  43824  1800 ?        S    06:21   0:00 sudo -u root /usr/local/bin/eventlogging_sync.sh
root     27870  1.2  0.0  12572  1620 ?        S    06:21   0:01 /bin/bash /usr/local/bin/eventlogging_sync.sh

• Start all slaves

@Nuria feel free to close this ticket if you believe the scope for this task is done.
Thanks!

Mentioned in SAL (#wikimedia-operations) [2017-03-30T06:25:41Z] <marostegui> Logging backwards for the record: restart mysql on db1047 for maintenance - T160454

Should we increase open_files_limit or do you think this was a one-time issue due to the rename process?

I thought about it, but it has not happened until we did the rename thingy yesterday. So I would leave it for now.

Wow, ok. Thanks.