19:02:41 <SotK> #startmeeting storyboard
19:02:42 <openstack> Meeting started Wed Sep 11 19:02:41 2019 UTC and is due to finish in 60 minutes.  The chair is SotK. Information about MeetBot at http://wiki.debian.org/MeetBot.
19:02:43 <openstack> Useful Commands: #action #agreed #help #info #idea #link #topic #startvote.
19:02:45 <openstack> The meeting name has been set to 'storyboard'
19:03:01 <SotK> #link https://wiki.openstack.org/wiki/Meetings/StoryBoard Agenda
19:03:55 <SotK> I don't think there's anything to announce this week, and assume nothing new on the migrations front too
19:04:10 <SotK> #topic PTG + Forum
19:05:20 <fungi> diablo_rojo: you're up!
19:05:45 <diablo_rojo> Oh jeez that was fast.
19:05:50 <diablo_rojo> I wrote a thing a while back
19:06:09 <diablo_rojo> if it looks alright. I will submit it.
19:06:44 <fungi> #link https://etherpad.openstack.org/p/storyboard-shanghai-ptg-planning
19:06:48 <fungi> that thing?
19:08:45 <diablo_rojo> Thats the one
19:09:00 <SotK> looks good to me
19:09:15 <fungi> seems great. thanks!
19:09:17 <fungi> ship it
19:11:53 <diablo_rojo> Cool.
19:12:10 <diablo_rojo> At some point we should discuss an outline for both of the different onboardings
19:12:26 <diablo_rojo> But that can wait to see if the Forum session gets selected.
19:12:39 <diablo_rojo> Thats all I had on this topic for now.
19:13:05 <SotK> thanks :)
19:13:06 <SotK> #topic In Progress Work
19:15:20 <SotK> I've not done a huge amount other than a quick look over the slow query digest
19:16:39 <fungi> any first impressions?
19:16:55 <fungi> i'm happy to refresh it again too if need be
19:17:05 <diablo_rojo> mordred, around by chance?
19:17:50 <fungi> #link http://files.openstack.org/user/fungi/storyboard/ slow query analysis
19:18:02 <fungi> i think mordred is still out of pocket until next week
19:19:32 <fungi> i think that digest-output.txt.gz is telling us that 50.5% of the total query time was spent on "SELECT stories tasks stories"
19:22:12 <fungi> not quite sure what that abbreviation means
19:22:22 <fungi> (it doesn't seem to be actual sql)
19:22:59 <SotK> I think its just the tables being queried, the full query is further down in the file
19:23:21 <fungi> yeah, i'm guessing that's the one marked "Query 1"
19:23:28 <SotK> yep
19:23:34 <fungi> average exec time is 91s
19:23:42 <SotK> I think that query is the generic story getting query, but I'm not certain
19:23:53 <SotK> its certainly ugly and slow
19:25:06 <fungi> the query itself wraps 22 lines on my 80-column terminal, yes
19:25:18 <diablo_rojo> Ouch
19:25:41 <fungi> well, that's the explain expansion of it
19:25:53 <SotK> I've not done enough database optimising to immediately know which the worst parts are, but I'll try to experiment with ways to simplify it
19:26:05 <fungi> so basically as verbose an interpretation of the query as possible, but that's indeed still big
19:26:51 <fungi> i wonder whether there's some way to decorate queries in the application so that we get useful backtracking breadcrumbs in the query logs
19:27:34 <fungi> corvus: if you're around, maybe you know whether that's a thing?
19:28:37 <corvus> ack; catching up
19:30:22 <corvus> that is an excellent idea; i don't know of a way to do that, but i don't consider my knowledge there exhaustive.  i'd probably look into not using the slow query log and instead doing something in python
19:31:22 <fungi> hrm, like wrap all orm calls in some timer code?
19:31:31 <corvus> yeah, something like that
19:31:56 <fungi> that would indeed give us the ability to, say, raise non-terminating exceptions in the application logs which include tracebacks
19:32:13 <fungi> whenever a query runs over a certain threshold
19:32:38 <fungi> though maybe harder to identify frequently-run queries which in aggregate add up to a lot of waiting
19:32:39 <corvus> #link https://docs.sqlalchemy.org/en/13/faq/performance.html
19:32:54 <corvus> that may not be too hard
19:33:10 <fungi> yeah, i guess they could be bucketed
19:33:21 <fungi> that's a very helpful-looking document. thanks!!!
19:34:13 <SotK> indeed, I can try some of the things described there this weekend sometime hopefully
19:35:00 <fungi> that would be awesome
19:35:14 <fungi> i'll try to digest the recommendations there too
19:35:38 <fungi> we can probably couple it with slow query logging to correlate the views from the db end and teh app end
19:36:47 <fungi> one other thing i noticed when turning the slow log back on... it took a while to start registering any queries slower than 1s, and days to start hitting any over 20s, but eventually started regularly logging some which took minutes
19:37:16 <fungi> it may just be coincidence, but i wonder whether we're seeing some sort of degradation over time
19:37:47 <fungi> as i did restart mysqld in the process of troubleshooting whether i had properly reenabled the slow log
19:38:51 <diablo_rojo> Huh
19:38:54 <SotK> interesting, sounds like maybe yes
19:39:02 <diablo_rojo> that does sound likely
19:41:04 <fungi> checking our cacti graphs, that production server is fairly under-utilized, but it does use the majority of available memory for cache
19:41:08 <fungi> #link http://cacti.openstack.org/cacti/graph.php?action=view&local_graph_id=66356&rra_id=all
19:42:01 <fungi> not really sure if giving it more memory would help or not
19:42:45 <fungi> according to top, apache is using more resident memory than mysqld, but mysqld is using more virtual memory
19:43:11 <fungi> also rabbit is using rather a lot of virtual memory
19:43:57 <fungi> i wonder if cache memory for mysqld is getting starved out over time by one of its competitors there
19:44:44 <fungi> i suppose i could do some experiments tracking query times before and after restarts of apache and rabbit
19:44:57 <fungi> and ultimately, also restarts of mysql
19:45:03 <SotK> seems worth trying
19:46:03 <fungi> i've put those experiments on my to do list
19:46:29 <SotK> thanks :)
19:47:05 <SotK> #topic Open Discussion
19:47:06 <diablo_rojo> thanks fungi :)
19:47:15 <diablo_rojo> I got nothing for open discussion.
19:47:26 <SotK> fungi: you also mentioned external group management in the channel earlier
19:47:28 <fungi> so one thing i'm finding is increasing in urgency is group management
19:47:31 <fungi> yeah
19:47:51 <diablo_rojo> Makes sense.
19:48:01 <fungi> there are an increasing number of openstack projects which are under vmt oversight and migrating to storyboard
19:48:09 <fungi> we have several at my last count
19:49:02 <diablo_rojo> Thats good news I suppose. Though I suppose we should write some script or something to import the groups.
19:49:11 <diablo_rojo> Maybe an addition onto the current migration script?
19:49:39 <fungi> well, before that even, i want to start publishing recommended practices for manitaining core security reviewer groups and for lp we already have a self-managed solution for that. for sb i think what we've discussed in the past is either synchronizing groups from gerrit or creating them from structured data files in git
19:49:58 <SotK> that matches my memory
19:50:19 <fungi> the challenge i've run up aganist in trying to plan that mechanism (either of them) is that we don't necessarily have preexisting accounts in sb
19:50:49 <fungi> so i'm trying to figure out how best to deal with that
19:51:17 <SotK> will a similar method to the one used by the migration scripts work
19:51:29 <fungi> but also, we need a good discoverable unique handle for each user which we can use to perform lookups
19:51:41 <SotK> ?
19:51:50 <SotK> yeah
19:51:52 <diablo_rojo> I guess we skip the ones that don't have accounts
19:51:55 <diablo_rojo> and add them later?
19:52:03 <SotK> I guess the closest we have to that is openid
19:52:23 <fungi> yeah, i think i can say that users need to create an account in sb before they'll be added to the group by whatever automated process we settle on
19:52:36 <diablo_rojo> That works for me
19:53:03 <SotK> then as long as they use the same openid for gerrit and storyboard it should be reasonably easy to match them right?
19:53:09 <fungi> but right we'd for example need to map an openid which the person creating the list of users (think... a ptl) can find and add to the list
19:53:13 <SotK> unless gerrit makes it hard to find users' openids
19:54:00 <SotK> oh I was only considering the "mirror gerrit groups" approach
19:54:03 <fungi> well, yeah, gerrit doesn't expose openids last i checked so that would need a direct db query (and recreatnig from scratch when users move into notedb in later gerrit versions)
19:54:27 <SotK> far less than ideal then
19:54:29 <SotK> hmm
19:54:51 <fungi> also using gerrit as a proxy for group management means creating groups in gerrit which are unused by acls, and we don't (currently) have any self-service automation for that either
19:55:05 <SotK> maybe we need to revisit the "unique nicknames" thing we've talked about in the past
19:55:26 <fungi> right, or make it possible to look up openids in sb
19:55:36 <fungi> for the structured data file in git approach anyway
19:56:00 <fungi> alternatively, we can do the same thing gerrit does and assume e-mail addresses are unique to a single user
19:56:21 <fungi> and then work out a way to add users to a group via e-mail address lookup
19:57:16 <SotK> you mean make it possible for PTLs to find the openids of their team members' storyboard accounts?
19:57:31 <fungi> even just exposing a unique account id number somewhwere could do the trick though, as long as we added api support to convert that to an account or to be able to use it directly in group member addition calls
19:58:02 <SotK> I think that's already possible with the API, we just need to add some sensible UI for viewing information about users
19:58:09 <fungi> but yeah, whatever unique identifier we settle on, we need some means for a non-admin sb user to figure out another sb user's unique id
19:59:05 <SotK> people have requested a "user profile" type page in the past, probably we should bump that idea up the priority list since it also implies a way to find users
19:59:13 <fungi> if we had that, i think i have enough to put together some simple automation driven from structured data in git and a workflow around it
19:59:50 <fungi> anyway, i mainly wanted to explain the current challenges, and get folks thinking if there are any elegant solutions
20:00:04 * SotK will apply some thought to it
20:00:07 <fungi> or at least to help identify which solution would be most preferable
20:00:11 <fungi> thanks!
20:00:15 <fungi> and looks like we're out of time
20:00:20 <SotK> yep
20:00:24 <SotK> thanks for coming folks!
20:00:28 <SotK> #endmeeting