Sig-release: Active spreadsheet for CI Signal

[jberkus]

This spec is for @nikopen

The spreadsheet should pull all issues from Kubernetes/Kubernetes with "kind/failing-test" or "kind/flake" created since 2018-10-01

Should include the following information:

• issue #
• link
• issue title
• which kind(s) it has (failing-test and/or flake)
• sig(s) tagged
• priority
• status (open/closed)

Ideally, I'd also like to have some additional human-populated columns (followup status, notes), but wouldn't those get overwritten every day?

[nikopen]

You can fully control which fields are auto-populated. Each row represents an issue and new issues are concatenated on a new bottom row. I'll add two non-auto-populated columns for note taking, like the Notes column in the prototype

/assign

[jberkus]

For the issue of not being able to pull fails and flakes with the same query, maybe have two tabs? one for fails, one for flakes? There would be some duplication, but that's tolerable.

[nikopen]

Done: https://docs.google.com/spreadsheets/d/1jBshIerjPEqFvLLW67brOLexXqeBXF3hJQWPYcJVAb8/edit#gid=1229781058

Talking with @AishSundar the idea was to have many sheets under one link - bug triage, ci signal, and a big tab having all v1.13 issues - tried it, however the big v1.13 tab hits rate limits of how many queries can google sheets do in parallel, can't fit with the others. That might be mitigated in the near future, if a way that queries less is found (currently each active row queries github 7 times).

Is it beneficial to have bug triage + ci signal fetching issues under the same link, in separate tabs? Or in different links/spreadsheets?
cc @AishSundar @tpepper

[AishSundar]

@nikopen thanks for pulling CI SIgnal queries into the sheet. Super helpful.

Just to clarify what we discussed earlier, I dont think I wanted a big tab of all the issues. I dont think that will be easily parsable or give us any new signal.

Instead, similar to how we have multiple tabs for CI Signal, I think for Bug triage it will be useful to have separate tabs for"PR queries" and "Issue queries" as listed in the Bug Triage handbook across different stages of the release.

Eg: During pre-code slush we can have

• Issue tab running is:open is:issue milestone:v1.11 -label:"kind/feature" -label:"kind/failing-test"
• PR tab running is:open is:pr milestone:v1.11 -label:"kind/feature" -label:"kind/failing-test"

The queries will change as we enter Code slush and freeze.

[AishSundar]

/cc @spiffxp @tpepper

[nikopen]

Altered the Issue tab query and added a new tab for PRs, but started to get rate limited - fields go blank. Moved the PR tab out of the sheet for now.

It seems that G Sheets throttle when there are about 200+ fields that directly query websites.

The solution is to transfer some of the heavy loading to custom functions / javascript and scrape all fields of a link at once, then send the fetched data to the fields where they get regex filtered (currently each field does the whole operation by itself). Custom functions use different rate-limits, so this way we can have quite a lot of data on many tabs of a sheet - GoogleAppsScript runs in google servers for free, like an automatic serverless service.

[AishSundar]

Thanks a lot Niko. Is there a link to the PR spreadhseet?

Also would it possible to somehow show the query each tab is running (and keep that info updated as and when we change queries)? this will help folks easily identify if there are some gaps in our searches and correct accordingly.

[AishSundar]

Also could you plz give the [email protected] edit access to the sheet?

[nikopen]

It's the same bug triage sheet.

So the VCS currently used for this project is my favorite VCS: notepad. I'll be soon transferring the functions here , slowly adding some Javascript/GAS and then maybe moving them somewhere in the sig-release repo in the following weeks with documentation. It's still WIP.

[jberkus]

@nikopen it's the same sheet, but I don't have edit permissions.

[nikopen]

[email protected] edit access was there for almost a week but it seems it didn't work...

Just gave the release team edit access manually, e-mails found on the contact sheet.

[AishSundar]

I have edit access now thanks.

I dont see a "Bug Triage PR" tab in the same spreadsheet.

[nikopen]

As said above, when the PR tab is present, it hits usage limits, making random fields empty across the sheets, and the workaround is to transfer some queries to 'google apps script', a subset of javascript with a custom library and functions. Some work is already done, but still needs more time.

I put the PR tab back in, with some of the fields updated via the 'gas' scripts.
It's still WIP

[nikopen]

Another solution is to transfer the spreadsheet on a paid Gapps account (CNCF if applicable? it has no cost but paid accounts have higher limits) or transfer some sheets to another google account to share the load and avoid throttling.

These are nicer solutions than apps script - it's quite time consuming and acts quite weird / some functions do not work. e.g. when it updates fields, it shifts the active sheet by itself, having to wait until it finishes what it does. not too convenient

[AishSundar]

Will it help to move out the CI Signal tabs to a separate spreadsheet? If so lets do that. If "PR Issues" needs to be in a separate one, I can lend my google account for that.

[jberkus]

@nikopen when you get to fixing this (however you do it), I'll ask you to have the spreadsheet list all failing-tests and flakes instead of just from 10/1. I thought there would be a lot of old abandoned "noise" issues, but it's turning out there aren't many.

[nikopen]

I see there are about 20-25 flakes + failing-tests, in which case it'd be better to separate bug triage and ci signal sheets.

You can create a spreadsheet on your account, give access to the team and I'll paste the structure and scripts for consumption.

One change - currently the CI query gets both open and closed issues, if the date/created< is removed it will be only getting the open issues, as there are about 800 closed ones - that means anything that closes will dissapear from the sheet with the current scripting. I guess that's fine

[jberkus]

Oh. Hmm, I do want to see the closed ones.

Can we search by last update date instead of created date? Ones with last update after 10/1 should get the ones we care about

[nikopen]

@jberkus Yep, last update seems to work on the GH query. I'll use that.

I need a new spreadsheet on another account to split the sheets, either @AishSundar or you can provide me with one?

[AishSundar]

@nikopen here's a sheet from my account - https://docs.google.com/spreadsheets/d/1TfECf8uSVnHaaCn8KTWK-sMdG3PV7jtW7FggHIUpeUU/edit#gid=0. Feel free to use it for either CI queries or for the PR issues.

[nikopen]

Thanks Aish.

@jberkus CI sheet is ready: https://docs.google.com/spreadsheets/d/1TfECf8uSVnHaaCn8KTWK-sMdG3PV7jtW7FggHIUpeUU/

There should be no issues with empty fields (I hope!)

How it works:

• Link-fetcher results sometimes get cached, so it might delay to retrieve new issues, from minutes to a few hours. It's a bit random.

• Priority and Status columns get updated every 2 hours statically via google apps script. Title, sigs and kind get updated immediately

Anything else let me know.

[jberkus]

@nikopen can I add additional columns, or will that mess things up?

If you have to add them, I'll want (in addition to the two manually updated columns you have now):

• Block? (Y/N field)
• Last Followup (date)

[nikopen]

Just added them.

Some auto fields are sensitive, e.g. priority script is hardcoded to put priority on column G,
but you can do anything you want after the last auto column (G - Priority).

The current issue is that some tickets shift -+1 row when the GH query catches a new ticket (either old that got updated or newly created). The workaround I'm using for this in the bugtriage sheet is to write the issue number in the Notes column again, so it's visually linked to the actual issue if it shifts a row. It works fine for me, shifting doesn't happen too often and it's only -+1 row.
See http://bit.ly/k8s113-bugtriage

It's fixable to have auto and manual cells synced in the same row, but needs a new script addition, possibly 'triggers'. I'm looking into it

For the above, separating Open and Closed issues would be more consistent,
i.e. having Closed show grouped at the bottom of the page.

I'm doing some surgery so it looks jangled right now, should be ready soon.

@jberkus

[nikopen]

Shifted the order of columns, so Issue# is near the manual columns. You can copy the issue# to Notes, this way the manual rows stay manually synced.

I might have found a solution for this but won't have much time for the next few days

[AishSundar]

Thanks much @nikopen for working on this consistently.

Is the following still an issue? If so do we know what the average wait time?
"Link-fetcher results sometimes get cached, so it might delay to retrieve new issues, from minutes to a few hours. It's a bit random."

Not an urgent issue, but if we were to prioritize the improvements I would put this on top.

[nikopen]

Good to know that it's a priority. Is it very noticable, i.e. you were expecting an issue to pop up and didn't for many hours?

To solve it I'll have to implement it in a completely different way... Functions cache results, and GAS is a bit unreliable/random... it will have to be querying the github API outside of google sheets so it's able to run every minute or so

[jberkus]

Nikopen: it looks like the Priority column isn't updating if there's changes to the issue priority after it's first pulled.

[nikopen]

Finally got around to fixing the 'ghosting' issue, now the rows will never go blank again 🎆

Also, added a "Kubernetes Github Commands" tab on the top:
the 'Refresh Issues/Priority' buttons do as they say on-demand.

@jberkus @AishSundar @mariantalla @mortent

[nikopen]

/close

[k8s-ci-robot]

@nikopen: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.