18:06:47 #startmeeting storyboard 18:06:48 Meeting started Thu Jul 16 18:06:47 2020 UTC and is due to finish in 60 minutes. The chair is SotK. Information about MeetBot at http://wiki.debian.org/MeetBot. 18:06:49 Useful Commands: #action #agreed #help #info #idea #link #topic #startvote. 18:06:51 The meeting name has been set to 'storyboard' 18:07:15 #link https://wiki.openstack.org/wiki/Meetings/StoryBoard Agenda-ish 18:07:43 I don't think there's any announcements or migration updates, so lets go straight to in-progress 18:07:56 #topic In Progress Work 18:08:00 yes! eager to hear what performance insights you've gleaned 18:12:11 As am I :) 18:12:33 I didn't actually get round to generating a dataset comparable to the production db yet (my test data currently has 10000 stories, compared with the 28138 reported by storyboard.o.o) 18:13:19 it's within the same order of magnitude 18:13:24 indeed 18:13:58 as memory serves the worst query we have (according to the old log) was the general story browsing one 18:14:14 in aggregate, yes 18:14:39 which is approximately this: http://paste.openstack.org/show/796021/ 18:15:31 multiple joins in a nested query 18:16:24 on my test data that takes ~100ms to execute 18:16:43 Yeah that matches my recollections as well 18:17:27 part of the problem is the ORDER BY, since story_summary is the massive subquery rather than an actual table, there's no useful index there 18:18:12 oh, right 18:18:25 moving that into the filtered_stories subquery maintains the ordering in the result, and gets the time down to ~80ms 18:18:54 Step in the right direction. 18:19:07 a 20% improvement is nothing to sneeze at, but may also not be a noticeable speedup on human timescales 18:21:08 indeed, so I then moved the filtering that currently lives in filtered_stories into the story_summary subquery, removing the big join 18:21:24 that seems like it should have far greater impact 18:21:49 yeah, that got it down to ~14ms 18:21:54 yowza 18:22:20 i'm always dubious of subqueries anyway, and that's a great illustration of why 18:22:24 Holy moly. 18:25:41 so the hard part is going to be working out how best to get SQLAlchemy to emit that query using the ORM 18:26:14 how/where in the code is it currently built? 18:26:44 the `story_summary` subquery comes from https://opendev.org/opendev/storyboard/src/branch/master/storyboard/db/models.py#L465-L490 18:27:21 then the rest is https://opendev.org/opendev/storyboard/src/branch/master/storyboard/db/api/stories.py#L73-L134 18:28:15 the `_story_build_query` function called in that second link constructs the `filtered_stories` subquery 18:31:13 so basically need to dereference what _story_build_query() is doing i guess 18:31:54 mordred: ^ if you're around, can you suggest an orm-friendly alternative to how that's been designed? 18:32:08 I think really we just need to stop using StorySummary entirely 18:32:48 oh, right, we talked about that 18:32:51 or at least come up with a way to filter it directly and efficiently rather than joining to a filtered set of IDs 18:33:19 basically just replace it with a custom search, and then we can optimize the hell out of search instead 18:33:32 yeah 18:34:03 the main thing it provides is ability to aggregate the task statuses in the ORM directly 18:34:20 which we already identified need to be done differently too 18:34:47 because showing "active" stories for a project with no active tasks in those stories is counter-intuitive and probably not what the user wants anyway 18:35:27 aha, I hadn't even considered that aspect 18:35:57 yeah, we'll be able to do that much more easily in a rewritten way 18:38:15 Seems like a good way to go to me. 18:38:30 Heh yeah that makes a lot of sense 18:39:04 I'll continue work in that direction then 18:39:26 hopefully I should have a patch up for that before next week's meeting 18:39:57 my first attempt didn't work even slightly as intended, but did a good job to testing out my laptop's fans 18:40:41 Lol a nice little space heater? 18:41:09 or white noise generator 18:41:30 SotK: thanks for working on that! 18:42:02 Yes thank you so much for all your hard work on that. 18:42:03 i started looking back over what i might have done wrong with the swift acls in rackspace for storyboard-dev's attachments, but so far no real insights 18:42:11 People will be ecstatic when we get that merged. 18:42:28 yeah it should improve quality of life quite a lot hopefully 18:42:46 i think it's plumbed correctly in the sb config because i'm getting a permission failure rather than something else like auth failure or connection error 18:43:35 i think my next step is going to be trying to use client tools to write to the container 18:43:39 it'd be nice to test it on storyboard-dev when I do get a patch up, to get an idea of the kind of improvement we get on a still bigger db 18:43:48 fungi: that would be my recommendation, yeah 18:43:52 and see if i can debug by repeating the same things sb is trying to do 18:45:01 on query optimization, i wonder how we can identify what the slowdown is for rendering automatic boards/worklists 18:45:36 while not as frequently called as the story summary pages, those are the most noticeably slow views 18:46:03 agreed, I think that's what I'll investigate next 18:46:04 Aside from search yeah 18:46:08 but i'm not sure what queries are at fault, or if it's an only moderately slow query getting called an explosive number of times 18:46:22 I could see that 18:47:25 I suspect StorySummary is somewhat to blame there too 18:48:34 oh, could that be getting called to get data for the cards? 18:49:14 but also, more generally, as we optimize the more commonly called queries we free up resources so other queries don't have to compete for cpu/io 18:50:02 That would be nice if it was two birds with one stone so to speak 18:50:22 yeah, we use it in the filters because we want to support filtering by story status 18:51:01 the code there is pretty questionable in general I think, there should be plenty of room for improvement 18:51:59 (there is nothing worse than reading your own old code :D) 18:52:15 LOL yep. 18:52:21 Made sense at the time though I imagine 18:52:31 haha, it always does 18:53:10 i always think of it as hatemail my past self sent me 18:56:09 #topic Open Discussion 18:56:11 anything else? 18:56:21 not on my end 18:56:49 Nothing from me either. 18:57:24 ..aside from apologizing for not being all that helpful lately. 18:58:37 no need to apologise :) 18:58:59 anyway, time for me to make dinner, thank you for the meeting! 18:59:09 thanks SotK! 18:59:11 #endmeeting