[00:20] wallyworld: ok shoot [00:20] bang [00:21] you mean with the uzi? [00:21] wgrant: we don't need it for natty [00:21] i love the uzi [00:21] wgrant: vm's for the win [00:21] lifeless: you know that wide query taking a long time to run - the daily recipe builds one..... [00:21] nope, remind me [00:22] * wallyworld looks up bug nr [00:22] bug 711072 [00:22] <_mup_> Bug #711072: RootObject:+daily-builds times out < https://launchpad.net/bugs/711072 > [00:23] ok [00:23] how can I help [00:23] tables like person, sourcepackagename, archive are only in the query to get data to display [00:24] so they could be removed to reduce the nr of joins, and subsequent queries done to look up that data [00:24] wallyworld: btw has your parameter filling thing had the test failure figured out and landed ? [00:24] wallyworld: yes, changing from wide to serial is likely to help [00:25] not yet. i updated from trunk and can now reproduce locally but it's on the back burner till after rollout. bad rev rolled back [00:25] kk [00:25] but values from those tables are used in thr oder by [00:25] matsubara has improved lp-oops to group with inferred parameter types [00:25] cool. [00:26] so if i remove those tables from the query, i will have to load all the values and do an in memory sort [00:26] wallyworld: huh, no [00:26] unless there's another way that storm can help that i don't know about [00:26] DecoratedResultSet [00:26] pre_iter_hook [00:26] done [00:27] I mean, you do need to load [00:27] but sorting should always be db [00:27] ok. i'll have to look at that hook to see how it fits in [00:27] otherwise slicing will fail [00:28] yeah, that was my problem [00:28] look in lib/lp/blueprints/model/specification.py [00:28] for pre_iter_hook [00:28] this is one I prepared earlier. [00:28] excellent, a working example. thanks [00:28] exact transform we're talking about [00:28] i knew that you would be able to help :-) [00:28] actually, this is more complex [00:29] but it shows pretty much all that you might want to do, and as I haven't seen the code you're looking at yet. [00:29] by "this" you mean the thing i am doing cf the example you gave? [00:29] the specification hook is doing more than you ma yneed [00:29] ah ok [00:29] its doing eager loading *and* object injection [00:30] a useful thing to bookmark for later for when needed [00:30] so, looking at your query [00:30] thanks for the tip, i'll take a look and go from there [00:30] why are you sorting on the things you're soring on [00:31] also note the utterly utterly insane group by [00:31] perhaps we should start with the use case ;) [00:31] the group by is needed because of the max() [00:31] the sort order was as per the requirements [00:32] the max means you're bring back too many things [00:32] which will make it slow [00:32] (and thus, its slow) [00:33] there's several build records and we only want the latest one [00:33] that sounds a bit delphic, sorry [00:33] perhaps voice would help; I dunno. [00:33] the first thing I'd want to do is to grab a bit of paper and map it out [00:34] taking out archive, person,sourcepackagename will help the base query look more sane [00:34] thumper developed the initial query in straight sql - i adapted it to storm. [00:35] it produces the correct results. how about i make the changes we've just discussed and see where that takes us [00:36] you can't take the things out that you need to sort on [00:36] yes, hence my original question :-) [00:36] ok [00:36] so, first thing is to ask if we need to sort on all those things [00:37] that's why my first thought was to load the result set and do an in memory sort [00:37] there's only around 300 records - not sure what that may grow to in the future [00:38] 16K or more [00:38] its still in beta. [00:38] hmmm. that's too many [00:38] so yes, will have to revisit the sort order question then [00:38] you have 600 ms in late evaluation of Branch [00:39] looking at https://lp-oops.canonical.com/oops.py/?oopsid=1858D164 [00:39] another 800ms in late evaluation of SourcePackageRecipeData [00:39] why doesn't that oops link work? [00:40] apache-openid brekaage [00:40] login [00:40] click again [00:41] those late evaluation times can certainly be fixed, but the main query - the count and the select - do swamp the overall number though [00:42] actually [00:42] thats 2.3 seconds in late evaluation [00:42] so the main query is more [00:42] but not like 90% [00:42] anyhow [00:42] first step, define the definitive goals [00:42] sure. i guess i'm saying that fixing the late eval stuff is necessary but not sufficient [00:43] ORDER BY SourcePackageName.name, [00:43] Person.name, [00:43] Archive.name LIMIT 76 [00:43] wallyworld: all its all necessary and nothing will be sufficient ;) [00:44] so the source package name is the driving thing [00:44] its used for pagination [00:44] i'd agree with that [00:44] personally I think that paginated lists are suboptimal [00:45] I'd rather we presented a query interface [00:45] +1 [00:45] and showed the 75 most recent builds the rest of the time [00:45] or something like that [00:45] if we did do that, the count() query would be required [00:45] well the count can be simplified [00:45] and we would shave 1/2 of the most significant execution time off [00:46] but that's not how the batch navigator works is it? [00:46] the bn simple does a count() on the underlying query [00:46] its just select count(*) from sourcepackagerecipe where daily_build is true [00:47] the batch navigator makes some simlifying assumptions [00:47] we may need to pass in an object with a custom count [00:47] not really - we only want the latest, but anyway, unless there's a hook i don't know about, the bn uses the same underlying query to do the count [00:47] spr is 1:M [00:47] if you count 1, you don't need the max [00:48] let me talk to thumper so see if we can adjust the requirements for what is displayed [00:48] wallyworld: yes, the bn will need to be given an adapted result set, not a real one. [00:48] hell no! [00:48] sorry what? [00:49] thumper: read the last 10000 lines ^^^^^^^^^^^^ :-) [00:49] wallyworld: no [00:49] thumper: wanna mumble, may be easier [00:49] thumper: listing all the source package builds as a collection seems pointless [00:49] wallyworld: I'm in a cafe without headphones [00:49] thumper: we're saying lets just give users the 75 most recently built daily ones + a search box. [00:49] lifeless: I'm not listing them as a collection [00:49] thumper: what do you call 'https://code.launchpad.net/+daily-builds' [00:49] what are you talking about? [00:50] thumper: we're talking about the +daily-builds sql times [00:50] lifeless: that is really a requirement from the community guys [00:50] and the fact that the current sort oder requires a query with a number of joins to get the data to sort on [00:50] thumper: how was it phrased though? [00:50] they wanted a listing of packages built with recipes [00:50] not search rubbish [00:51] thumper: search is hardly rubbish; who asked for this, and what did they want to achieve? [00:51] but if we are prepared to relax the sort order we can small a smaller more efficient query [00:51] s/small/use [00:51] :) [00:51] wallyworld: so we can make it better as is [00:52] wallyworld: I'm separately saying that its not actually a brilliant UI as it stands. [00:52] wallyworld: the only sort order I care about is source package name [00:52] wallyworld: everything else is extra [00:52] thumper: ok. will do that first up [00:52] wallyworld: most of the results will return only one daily recipe for a source package [00:52] at least for now [00:52] thumper: that presupposes having a collection though; so can we talk about that seriously? [00:52] there are a few packages where there are more than one [00:52] and get some numbers on the improvement [00:53] lifeless: I'd be terribly sad if you took the collection away [00:53] thumper: why? [00:53] can i ask to have a query run on production?? to get some numbers? [00:53] wallyworld: a losa [00:53] wallyworld: or stub [00:53] sure. just wanted to see if it were a kasha thing to ask for [00:53] wallyworld: the numbers haven't changed substantially though. [00:53] because I think it looks nice, and validates the work we are doing [00:54] lifeless: did you just try it? [00:54] thumper: I don't think it validates it - uptake will validate it; as far as looking nice, it looks like something where its impossible to find what a user wants to find. [00:54] thumper: I don't mean to be negative here. [00:54] i meant to see what benefit removing person and archive from the sort oder would do [00:54] wallyworld: for that sort of thing, we use qastaging until we're fairly sure we've got the good stuff [00:55] but qastaging has a really old db :-( [00:55] wallyworld: if you have a canned query I can run on qastaging, that would be great. [00:55] wallyworld: it doesn't matter for this; it has 270 recipes [00:55] lifeless: PQM still executes baz? Seriously? [00:55] wgrant: probes for it, backend lookup [00:55] dunno if it has it there. [00:56] lifeless: it should be up to the stakeholders and the strategist, so check with jorge and jml [00:56] lifeless: yes, but what about all the other data (eg in the archive table) that serves to make the query slow [00:56] lifeless: It uses it to get config-manager. [00:56] lifeless: I see no reason to defend it to you [00:56] lifeless: That seems to be what takes all the time. It already does a lightweight checkout. [00:56] thumper: I was asking who was pushing for it to be /like this/ - I wasn't asking you to defend it; I don't know how we got here conversation wise. [00:56] thumper: I'll be delighted to talk to jorge and jono [00:57] wgrant: it doesn't spend 30 minutes talk to baz, I assure you [00:57] lifeless: The logging is a bit crap, then :/ [00:57] No, it spends 30 minutes spinning [00:58] wallyworld: so the point I was making was that a search interface generates a very specific lookup [00:58] lifeless: https://wiki.ubuntu.com/DailyBuilds/AvailableDailyBuilds was the justification [00:58] Feb 03 23:26:09 pqm [140001309406976] INFO: running: baz get /home/pqm/archives/rocketfuel/lp-production-configs/trunk/config-manager /srv/pqm.canonical.com/chroot/home/pqm/pqm-workdir/home/devel [00:58] wallyworld: so the intermediary tables will be very small [00:58] Then it disappears for 17 minutes. [00:58] Every time. [00:58] thats in bzr land at that point [00:58] the bzr backend doesn't log such a message [00:58] thumper: thanks [00:59] Oh yay, another db-devel conflict. [00:59] lifeless: sorry for being touchy... lots of stuff going on [00:59] lifeless: sure [00:59] thumper: so, looking at that; the goal is to avoid duplicate effort; making it real easy to find dailies of audacity (e.g. search / linking on the source package side) would seem best to me. [00:59] thumper: no worries; it is friday after all. [01:00] lifeless: perhaps a tag cloud for package names would be better? [01:00] given how much we love tag clouds :) [01:00] so, I propose to mail jml and jorge, cc thumper and wallyworld and propose a different UI which can give us a much simpler problem to solve [01:00] although determining size / shade would be interesting [01:00] thumper: thats a cool idea too [01:00] thumper: search is the cheapest for us to do [01:00] thumper: I don't know that its the best; tag clouds are pretty interesting too. [01:01] thumper: I suspect we need discoverability as a primary mechanism though. [01:02] thumper: is there anything I can help with ? [re lots of stuff going on] [01:02] not unless you want to read CVs and interview people [01:02] am I allowed to shoot myself first ? [01:03] thumper: btw, the guy i mentioned wasn't interested [01:03] sure [01:03] lifeless: Depends. Where? [01:05] wallyworld: ok, so lets see what we can do [01:06] wallyworld: first thing, we *don't* want to return all the previous builds in the intermediary tables [01:06] \0/ [01:06] wallyworld: but the thing before that [01:06] web-link branch of leonard's has landed in devel [01:06] wallyworld: is to see what the current plan is [01:06] wallyworld: do you have a query I can run [01:06] * wallyworld looks [01:07] lifeless: the current implementation uses this query - https://pastebin.canonical.com/42856/ [01:08] lifeless: but i don't mind working through the issue if you have other stuff to do [01:09] nothing I can't procrastinate over. [01:11] so i'll immediately get a win by removing the person and archive from the sort [01:11] spm: could you please do an explain analyze on http://pastebin.com/nASE7Tq6 ? [01:11] spm: on prod [01:11] wallyworld: getting the explain should tell us a lot about what its doing [01:12] then we could get a rs with only the latest builds and use that to filter the main query - so a subselect instead of a big join, not sure how postgres performs for that [01:12] wallyworld: it depends [01:13] jcsackett: ping [01:13] if its uncorrelated and huge relative to final rows, poorly; if its correlated and about as selective as outer, poorly. [01:13] hello wallyworld. [01:14] jcsackett: hi. i just need a quick tick and flick on a mp to fix a small layout issue. can i reassign to you? [01:14] https://code.launchpad.net/~wallyworld/launchpad/recipe-related-branches-layout/+merge/48463 [01:14] it's only like 2 lines of pt [01:15] * jcsackett realizes he's still listed as OCR. [01:15] sorry if you weren't [01:15] jcsackett: You can't escape. [01:15] yeah, i can take a look at it, wallyworld; but you'll need to find someone to follow up, since my mentor is definitely not on shift. === jcsackett changed the topic of #launchpad-dev to: https://dev.launchpad.net/| PQM is open | firefighting: - | On call reviewer: - | https://code.launchpad.net/launchpad-project/+activereviews [01:15] ah ok [01:16] wallyworld, you want me to still take a look? i don't mind (need the reviews) but as i said, r=jcsackett isn't enough to land something. :-) [01:16] jcsackett: no, it's cool. thanks [01:16] wallyworld: dig. sorry for the confusion. i need to be better about removing myself from OCR. :-P [01:17] jcsackett: no problem :-) [01:17] StevenK: Do you have any suggestions for getting a newer archive to gina on mawson? [01:17] lifeless: while waiting for that explain, can you quickly approve this 2 line pt change? [01:18] https://code.launchpad.net/~wallyworld/launchpad/recipe-related-branches-layout/+merge/48463 [01:18] wallyworld: lifeless: https://pastebin.canonical.com/42858/ [01:19] spm: thanks [01:20] wallyworld: sadly I don't know the css classes we use well enough to say if thats sane or not [01:20] I seem to recall a lot of discussion about tables vs nontables in this [01:20] Project devel build (416): STILL FAILING in 6 hr 17 min: https://hudson.wedontsleep.org/job/devel/416/ [01:20] * Launchpad Patch Queue Manager: [no-qa] [r=allenap][ui=none][bug=707103] Clean-up of lint after [01:20] bugs-with-blueprints-bug-707103. [01:20] * Launchpad Patch Queue Manager: [bug=75976] [r=abentley][ui=none] When someone asks to merge two [01:20] accounts, Launchpad sends an email to the primary account asking for [01:20] confirmation. That email was hard to understand and badly [01:20] written. This branch rewrites that email's text and introduces [01:20] a test that the email is sent with the correct text. [01:20] * Launchpad Patch Queue Manager: [r=mthaddon, stub][bug=712282, [01:20] 712285][no-qa] Improve nightly.sh transparency, [01:20] and remove update-pkgcache.py from it. [01:20] wallyworld: clearly its trivial textually. [01:21] lifeless: the css - should have been there from the start - cut'n.paste error. the other collapsible field sets use it [01:22] wallyworld: want to mumble on the explain [01:22] lifeless: ok [01:22] well, try mumbling, it may still be epic fail fo rme [01:22] or skype [01:23] I can hear you [01:53] spm: please analyse this on prod - http://pastebin.com/3ZA20XvA [01:53] sadly staging/qastaging are not fresh enough [01:55] lifeless: https://pastebin.canonical.com/42859/ [01:55] thanks! [01:55] there will be more [01:57] wallyworld: http://www.postgresql.org/docs/8.4/static/tutorial-window.html [01:58] rank() OVER (PARTITION BY BuildFarmJob.date_finished DESC) FROM === Ursinha is now known as Ursinha-gone [02:12] StevenK: pong [02:17] wgrant: ping [02:17] lifeless: Hi. [02:17] wgrant: hi [02:17] you know wallyworld and I are looking at a slow query [02:17] its filtering all binary package builds that are recipes [02:17] The +daily-builds one? [02:17] in state 1 [02:18] yeah [02:18] we had an idea [02:18] what if we narrow this to 'published' [02:18] it might consider less data? [02:18] we have binary package build and build farm job already in there [02:19] So we're finding BPBs that are built from an SPR that has an SPR? [02:19] have a look at one of the pastebins [02:20] the goal is 'all source package recipes showing the date of the most recent successful binary build' [02:21] one thing that isn't clear to me/us is whether fullybuilt in this case means 'the bzr-builder worked' or 'all binaries were build' [02:22] It's the binary build status. [02:22] Note that your last pastebin doesn't restrict to builds in the daily archive, so manual builds to other archives will show up. [02:23] indeed [02:23] what table was that on - BPB ? [02:23] PackageBuild [02:25] * wgrant silences mirror-prober.sh. [02:25] These noisy scripts irk me. [02:26] * spm gives wgrant 100 awesome points [02:29] PackageBuild ON (PackageBuild.id = BinaryPackageBuild.package_build and PackageBuild.archive = SourcePackageRecipe.daily_build_archive) [02:29] lifeless: I think so. [02:29] I'm not sure which direction is clearer. [02:30] Should we go from daily recipes to builds, or find builds that have daily recipes? [02:31] wallyworld, ping? [02:31] gary_poster: hey [02:31] hey [02:31] I really want ~gary/launchpad/bug548-db-2 to get on db-devel before we close pqm or mark things closed or so on. Some tests failed, and in order to fix them, I had to make enough changes that they warrant their own separate review. So... [02:31] I'm wondering how to get that to happen/if I can get that to happen [02:32] in order for that to work, I'll need a review, and I'll need to land [02:32] with tests passing.. [02:32] gary_poster: just give me a sec : otp [02:32] oh, sure [02:33] whats the mp url ? [02:33] gary_poster: well i am talking to lifeless atm :-) [02:33] he may want to help [02:33] thank you both, getting the mp up now [02:34] gary_poster: there's still 9 hours or so before we close :-) [02:34] plenty of time! [02:34] :-) yes, but I wanted to stop working hours ago :-) [02:34] yeah. understood :-) [02:35] gary_poster: so i can land it for you once it passed review [02:35] thank you! [02:35] gary_poster: what about qa? is it clear how to do that? [02:35] it is no-qa [02:35] even better! [02:35] :-) [02:36] gary_poster: so i think your work here is done :-) [02:36] assuming it passes review :-) [02:36] gary_poster: whats the mp url ? [02:36] lol, thank you. Well, I need to get the MP done [02:38] lifeless: https://code.launchpad.net/~gary/launchpad/bug548-db-2-tests/+merge/48570 but diff is still coming [02:39] the mp message is, uh, one of my more hurried :-P [02:39] and it shows :-) [02:40] ok diff has emerged lifeless [02:40] uh? [02:40] I did not touch the lib/canonical/widgets thing :-/ [02:42] gary_poster: You're getting a conflict there when merging locally? [02:42] If so, ignore. [02:42] Delete the widgets.moved directory. [02:42] wgrant, it is something in the MP-generated diff [02:43] not in my local diff [02:43] OK, that's not normal. [02:43] I'm trying a local merge of my branch into a up-to-date-db-devel... [02:45] wgrant, conflict on my db-devel pull. I assume this was correct? [02:45] rm -rf lib/canonical/widgets.moved [02:45] bzr resolved lib/canonical/widgets.moved lib/canonical/widgets lib/canonical/widgets.moved/tests [02:45] gary_poster: Yes, that's right. [02:45] cool [02:46] I just merged that branch into db-devel. [02:46] cool, thanks [02:46] lib/canonical/widgets is still there... [02:46] So the LP diff is odd. [02:46] I resolved that conflict last night. [02:47] My branch merges cleanly into db-devel touching only the files I expect, so it looks like it is a diff-generation odity in the MP [02:47] launchpad_qastaging=> select count(*) from sourcepackagerecipe where build_daily [02:47] oddity [02:47] ; [02:47] Time: 1.512 ms [02:50] lifeless, I never asked explicitly--are you reviewing the branch or should I ask around? [02:51] be thar a reviewer in these here parrts? [02:52] wallyworld: are you a reviewer, by chance? [02:52] I will, one sec [02:52] oh thanks [02:53] gary_poster: no. not yet. soon hopefully [02:53] :-) cool [02:55] gary_poster: hi [02:55] hey [02:55] isn't with logged_in_user() sufficient ? [02:56] sorry [02:56] person_loggefd_in [02:56] *bah* person_logged_in [02:56] this is about the dbuser, not the application user, so I don't think so. Do I misremember my helpers? looking for it [02:56] ah right [02:56] layers ;) [02:56] right :-) [02:57] and db, rather than application [02:57] so [02:57] a few thoughts [02:57] I'll sketch here [02:57] the restore_name seems a bit unrelated [02:57] to...? [02:57] the problem you're solving [02:58] in the sense that we should always look at _dbuser? [02:58] run-some-code-as-a-user [02:58] right [02:58] completely agree [02:58] I'm saying having it do a push/pop and if someone wants to change after that, they can do that separately - would make this code leaner [02:59] (it was probably some kind of misthought subliminal reaction to disliking the fact that I was looking at an underpants attribute.) Yeah push/pop was my only intent really [02:59] given the lack of tests for the helper, my inclination is to suggest making it as lean as possible [03:00] I like the helper. [03:00] I'm curious why your tests failed [03:00] a few existing tests failed that were creating things without a dbuser that actually had privileges to do so [03:01] is it that LaunchpadZopelessLayer runs as an unusual user? [03:01] by adding an additional obeject that we were creating [03:01] gary_poster: so, you're creating a PersonSetting whenever a Person is created? [03:01] gary_poster: rather than lazy-creating? [03:02] unusual user: yeah, it is usually run as the user that a given script needs [03:02] which is often not pwerful enough to create stuff [03:02] if you tickle the database enough then things will flush [03:02] so essentially my change exposed (some) problems that were already existing [03:03] (I think there are a lot more like these that have not yet been triggered) [03:04] not lazy-creating: yes, I dislike write on read, and didn't want the complexity of look-for-the-underlying-object-and-if-it-is-not-there-look-for-the-interfaces-default. [03:04] (that is, write-on-write is ok, but I thought simplicity had a virtue) [03:05] gary_poster: I dislike write-on-read too; That implies creating a million rows when the db deploy happens though [03:05] anyhow [03:05] approved [03:05] I would like it if you could shrink the helper. [03:05] more so if you add tests. [03:05] yes, I wondered about that but that was the design stub made, so I figured he had thought it through. [03:06] I shrunk the helper locally already, after discussion. lemme run a smoketest... [03:06] gary_poster: At a guess - I suspect he intended lazy create. [03:06] no, he made the trigger [03:07] heh, ok. [03:07]