[00:32] matsubara-afk: the timeout feature flag was harder than anticipated, its all landed now, though [00:42] lifeless: Julian wrote a faster query last night, but it was slightly wrong. Could you try http://paste.ubuntu.com/506074/ and see if it doesn't take 10 minutes? [00:43] hmmm tmp tables :) [00:43] :( I mean [00:44] Yes. [00:44] It's a similar optimisation to the one we use in the publisher. [00:44] Which cuts 2.5 minutes down to less than two seconds. [00:44] 6 seconds to populate the temp table [00:44] should be able to express it directly. Nevertheless, if it works, it works. [00:44] One would think so. [00:45] perhaps the select needs a supersededby IS NULL in there ? [00:45] The subselect? [00:45] is supersededby indexed ? [00:45] No. [00:45] yes, the subselect [00:45] It's valid for something to be superseded by something that's also itself superseded. [00:46] It appears to be indexed. [00:46] yes, but we don't care about those do we? [00:46] only the tip of the chain [00:47] We do care about those. We are looking for things which should not be superseded but are. [00:47] It doesn't matter if the thing that superseded them is superseded or not -- it should not have superseded them. [00:47] ok [00:48] The query is still going? [00:48] yes [00:48] :( [00:49] I guess I did just increase the data size by several times. [00:55] 3662 rows [00:55] 465000.849 ms [00:56] Comfortingly an identical number to yesterday's. [00:57] Thanks. [00:57] de nada [00:58] Hmmm [00:58] http://paste.ubuntu.com/506096/ might be less crap. [01:08] * StevenK grumbles about http://paste.ubuntu.com/506100/ [01:09] StevenK: bin/kill-test-services [01:09] Or was that on ec2? [01:09] Hm, looks ec2. [01:09] wgrant: On ec2, yes [01:09] Sad. [01:10] I had a similarly opaque error on one of my runs this morning. [01:10] But it was completely different. [01:10] well the librarian is definitely gone :P [01:11] wgrant: 21 seconds to make the temp table [01:22] 3662 rows [01:22] 121149.515 ms [01:22] Aha! [01:22] Much better. Thanks. [01:39] lifeless: what's SOP for turning on sql trace for local debugging? i can add "from storm.tracer import debug; debug(True)" to bin/run (for example). is there cmd line arg i can use? [01:41] LP_SQL_DEBUG=1 [01:41] LP_SQL_DEBUG_EXTRA=1 to get backtraces [01:41] lifeless: excellent. thanks [01:41] those are environment variables [04:09] wgrant: librarian fail [04:26] lifeless: Hm? [04:27] see email [04:30] lifeless: So, that's two attempts that have failed in two different spurious ways. [04:30] Awesome. [04:39] * StevenK peers at http://ppa.launchpad.net/launchpad/ppa/ubuntu/dists/lucid/main/binary-amd64/Packages [04:39] No launchpad-dependencies at all? WTF? [04:40] Heh, is this what I think it is? [04:40] * wgrant checks. [04:40] wgrant: its almost certainly my layers work [04:41] wgrant: we'll need to figure out whats up [04:42] lifeless: Does that mean http://paste.ubuntu.com/506100/ is probably you too? [04:43] wow, Product:+bugtarget-portlet-bugfilters-stats is unhappy [04:43] stub: ^ [04:44] StevenK: I suspect you have found one of the few PPA cases of bug #653382.... [04:44] <_mup_> Bug #653382: BinaryPackagePublishingHistory._getOtherPublications fails to restrict the distroseries context [04:46] wgrant: You mean, the publisher has? [04:46] StevenK: Indeed. [04:47] StevenK: that pastebin may be related yes. [04:47] StevenK: however that looks like a racey test to me [04:48] * StevenK grumbles [04:50] eh? [04:50] stub: I suspect a fat index or something - 970 timeouts on the bugstats portlet [04:51] Got an OOPS handy? [04:51] So, I'd like to get http://paste.ubuntu.com/506212/ run on prod to see how widespread the carnage is. [04:51] lpmain_staging=> SELECT COUNT(*) FROM Bug, BugTask WHERE BugTask.bug = Bug.id AND BugTask.id IN ( SELECT BugTask.id ... taking 15 seconds [04:51] stub: theres tonnes in the oops report, but e.g. https://lp-oops.canonical.com/oops.py/?oopsid=1738C1035 [04:53] lifeless: Count(*) is expensive as hell anyway? [04:53] StevenK: yes but [04:53] StevenK: this is normally a subsecond query. [04:53] StevenK: count(*) cost is a nuanced topic, its not guaranteed cheap is the main thing to remember. [04:54] wgrant: Is that the same SQL lifeless ran on staging? [04:54] StevenK: Except that it tells me how many are in each archive, yes. [04:55] Does it give the archive name, or just the id? [04:56] Just the id. [04:57] wgrant: I wonder if it's worth twiddling the SQL for the archive name? [04:57] StevenK: I'm not allowed to possess that. [04:57] Oh, right [04:58] The only archive that I really care about the identity of is archive 1, and I know which that is. [05:02] * StevenK prods lifeless towards the SQL wgrant is talking about for a +1 [05:03] +1 [05:03] you'll need to put it on LPS etc etc [05:03] LPS for a SELECT!? [05:03] oh, ebrain [05:03] 506212? [05:03] StevenK: you don't need cross-check approval for readonly queryies [05:03] That. [05:04] unless the losa in question looks at you funny. [05:04] Ah [05:04] vs the losa in question looking funny. which is a different problem. [05:04] spm: Pls fix and make wgrant happy? [05:04] StevenK: see that's the problem. the 2nd part of that sentence. I'm not sure I wish to comply. [05:05] esp when the alt is far more entertaining for me! [05:05] s/happy/less angsty/ ? [05:05] it amounts to the same thing from my perspective. [05:05] StevenK: Hey, this is my most monumental screwup ever :P [05:06] ^^ young and experienced. Bigger and more spectacular screwups will come.... [05:06] *in*experienced. [05:06] gah. troll typo fail :-) [05:07] Bwahaha [05:07] Heh. [05:07] wgrant: http://paste.ubuntu.com/506216/ [05:07] Oh thank god. [05:08] yes. you should thank me. [05:08] Hah. [05:08] 3781 in id 1? [05:08] Yep. [05:08] That's a really good thing. [05:08] * StevenK wonders how to get the id of the Launchpad PPA [05:08] Because every broken publication in the primary archive is trivially revertable with a single query. [05:09] Since there are so few archives, I might just join against archive, and get owner.name, archive.name and archive.private. [05:11] wgrant: You were afraid it was going to be every archive? [05:11] StevenK: More than 37. [05:12] Why 37? That was the number on staging? [05:12] lifeless: That query is performing very poorly under PG 8.4 [05:13] No, I just expected there'd be more than we got. [05:14] spm: http://paste.ubuntu.com/506220/ [05:15] Hopefully there'll be no private archives. Or at least only private archives in public teams. [05:15] bug at over 7 seconds under 8.3, the query sucks already. [05:16] stub: it takes 780ms on staging [05:16] stub: isn't staging 8.4 ? [05:16] Yes. Hmm... [05:17] The production db is all freshly packed - it is only a few days old. [05:17] production 8.4 one that is (which I think is where all the timeouts are coming from - the OOPS is talking to the slave, and we have only one slave atm) [05:18] But 272 seconds is a little extreme... [05:18] rotfl [05:19] That's only nearly 5 minutes, what's the problem? :-) [05:21] Comments in the SQL like " -- We create this rather bizarre looking structure" don't inspire confidence ;) [05:22] yeah, indeed. [05:22] stub: so, staging is 8.4 and fast, prod slave is 8.4 and slow ? [05:22] yup. staging data is from about 6-7 days ago though. [05:23] spm: http://paste.ubuntu.com/506220/ [05:23] Maybe my connection will survive this time. [05:24] so one possibility is a something thrown out by new data? [05:24] wgrant: Wishful thinker [05:24] Hahaha [05:24] it seems improbably to me, given the depth of history, it would need a huge seachange to alter the right query structure [05:24] spm: Quick! Run the query while he isn't looking. [05:24] stub: any clues from analyze? [05:24] yes. The slow bit is an expensive nested loop the planner thought would get a single row but actually got over 5000, so 5000 times slower than expected. [05:24] I've already done it. just trying to confirm the results for him :-D [05:25] lifeless: http://paste.ubuntu.com/506228/ [05:26] * stub runs an analyze on hackberry for a laugh [05:28] stub: compare the plan on staging [05:29] -> Nested Loop Anti Join (cost=19392.56..26967.75 rows=1 width=4) (actual time=766.504..860.778 rows=5576 loops=1) [05:29] if thats what you were saying is the issue, staging has it too [05:34] stub: this seems to be a key, slow, but [05:34] (bit( [05:34] Bitmap Heap Scan on bugtask (cost=295.98..20874.68 rows=15471 width=24) (actual time=9.812..139.968 rows=19011 loops=1) [05:34] Recheck Cond: (status = 15) [05:34] Filter: ((assignee IS NULL) AND (milestone IS NULL)) [05:34] -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..292.11 rows=23310 width=0) (actual time=5.382..5.382 rows=24160 loops=1) [05:34] Index Cond: (status = 15) [05:34] query plans and irc, a match made in heaven [05:44] * StevenK grumbles at the continued hiding of launchpad-dependencies in the PPA [05:44] so the estimates are in the same ballpark, which means the planner meant to choose this bogus plan [05:45] full analyze hasn't helped anyway (just in case autovacuum stuffed up or the statistics were screwed after the restore) [05:45] Of course, if I was a query planner I'd take one look at that query and run away screaming [05:45] stub: I need to pop out; I'll leave this with you [05:45] gee, thanks ;) [05:46] if you need a hand I'll be back after dinner ;) [05:46] I do wonder why its choosing this plan [05:47] still, 800ms is slow anyway, perhaps the best thing is just to: [05:47] - stop filtering private bugs from the count [05:47] The nested loop I pointed out earlier - it wasn't expecting to execute that sub query so many times. [05:47] - stop trying to match up all the rules [05:48] Yes, overly precise counts have bitten us before. [05:48] - and just do a group by/count [05:48] StevenK: You can revive it if you want. [05:49] Delete it and copy it back. [05:49] wgrant: Can't we just fix it? [05:49] StevenK: Hopefully. [05:50] Need to get the fix cowboyed, check if any of them have been deathrowed yet, and then hopefully just mass-revert the status. [05:51] *Hopefully*. [06:25] lifeless: I think EdwinGrubbs' post on the dev list points out the problem -- is that your doing too? === almaisan-away is now known as al-maisan === bac changed the topic of #launchpad-dev to: Performance Tuesday! | Launchpad Development Channel | Week 3 of 10.10 | PQM is open for business | firefighting: - | https://dev.launchpad.net/ | Get the code: https://dev.launchpad.net/Getting [07:54] back [07:54] lifeless: https://code.edge.launchpad.net/~stevenk/launchpad/librarian-teardown-failure/+merge/37561 [07:54] (Per the discussion on the -dev list) [07:55] no, thats wrong. [07:55] * StevenK goes to get a drink, instead of snapping [07:55] I'm replying to the list now. [08:00] StevenK: I'm sorry that its wrong - I know infrastructure problems are frustrating. When I say wrong, I don't mean 'unstylistic', I mean 'it will cause other insidious test failures.' [08:04] lifeless: Sorry, I'm only frustrated since I've seen errors like this since Saturday, but had no time to look [08:04] StevenK: can't have been since saturday, the code landed monday night. [08:05] revno: 11667 [merge] [08:05] committer: Launchpad Patch Queue Manager [08:05] branch nick: launchpad [08:05] timestamp: Mon 2010-10-04 07:20:04 +0100 [08:05] message: [08:05] [r=mwhudson][ui=none][no-qa] Start consolidation and rationalisation of Librarian test helper code. [08:05] whatever is causing you grief since saturday is different. [08:07] Hi wgrant! ;) [08:08] General warnings. [08:08] /usr/lib/python2.6/atexit.py:24: DeprecationWarning: Attempt to tearDown inactive fixture. [08:08] lifeless: With your patch ^ [08:09] StevenK: thats fine [08:09] belt and braces [08:09] we'll get rid of that when we get rid of the atexit [08:09] which is another iteration [08:12] lifeless: i got the following errors with your patch: http://pastebin.ubuntu.com/506298/ [08:12] bac: ok, I thought it might be incomplete. [08:12] thanks [08:20] bac: can you add a print cls.__bases__ into my patch and show me the result ? [08:35] (,) [08:35] oh [08:35] bac: can you do that in LaunchpadScriptLayer.tearDown ? [08:35] (,) [08:36] really? [08:36] lifeless: yeah,it was called twice [08:36] oh right [08:36] lifeless: the prints don't show up, so i put in a breakpoint [08:36] I thought you were answering the script case [08:37] bin/test -vvt old-testing.txt -t spec-mail-exploder.txt [08:37] with [08:38] --- lib/canonical/testing/layers.py 2010-10-04 06:20:04 +0000 [08:38] +++ lib/canonical/testing/layers.py 2010-10-05 07:36:47 +0000 [08:38] @@ -1338,6 +1338,7 @@ [08:38] @classmethod [08:38] @profiled [08:38] def tearDown(cls): [08:38] + print cls.__bases__ [08:39] lifeless: i don't mind helping, but do you not have a dev env setup? [08:39] bac: I don't experience the problem. [08:40] really? wow! [08:40] as I said on list [08:40] it passed ec2test [08:40] (, ) [08:41] it died on ec2 for me twice [08:42] so, its not __bases__, that appears to be constant and fine. [08:53] ... [08:54] bac: bah, isp failure [08:56] EdwinGrubbs: around ? [08:58] lifeless: it is 3am for EdwinGrubbs [08:58] ah [08:59] for some reason I thought he was eu [08:59] ohhh [08:59] I wonder [09:00] I'm trying to figure out hth this got into the system [09:10] henninge: Hi. [09:10] wgrant: Hi, otp now ;) [09:12] bigjools: http://paste.ubuntu.com/506231/ is the list of casualties and their archives (from prod a few hours ago) [09:12] So it's mostly the primary archive, which is handy. [09:12] wgrant: context? [09:13] bigjools: the _getOtherPublications bug. [09:13] ok [09:13] I think we'll run the fix on staging first for this one [09:14] I'd say so. [09:30] wgrant: so, the new query looks the Thing. [09:30] bigjools: It also returns the same result as my original one. [09:30] always a bonus [09:30] I guess it's trawling quite a few rows [09:31] Just a few. [09:31] the question is, do we want to fix PPAs too [09:31] We should do something. Even if it's marking them Deleted instead. [09:32] hmmm [09:32] so my questions are, what happens if we do nothing, and what happens if we run this fix on them? [09:33] If we do nothing, we end up with confusing old data. [09:34] A few PPAs end up with some missing packages. [09:34] But most of those are probably obsolete anyway. [09:35] If they haven't been deathrowed already, we can just set them back to Published and everything will work. [09:35] If they have been deathrowed, we should probably just mark them Deleted, because it's messy and it's probably that nobody will notice. [09:43] However, we should get the fix cowboyed or CP'd and make the primary archive consistent first, I suspect, given what time it is. [09:43] The whole release thing on Sunday. [09:47] bigjools, wgrant: Is what you're talking about related to the broken ppa:launchpad/ppa? [09:49] allenap: Yes. [09:50] wgrant: Okay, cool. Do you know of any PPA mirrors I can use that are out of date, and thus not broken? I'm going to do a lot of twiddling thumbs today otherwise :) [09:51] allenap: You could always grab launchpad-dependencies manually.' [09:51] * wgrant finds the link. [09:51] wgrant: Ah, it's in the PPA, just not in the manifest or whatever it's called? [09:52] ppa:launchpad/ppa got broken too? [09:52] henninge, you'll want to know about this: a conflict has crept into your convert-imports branch. [09:52] henninge: lib/lp/translations/stories/translations/30-rosetta-pofile-translation-gettext-error.txt [09:53] bigjools: Lucid's launchpad-dependencies were vaporised a couple of days back, yes. [09:53] :/ [09:53] Hello [09:53] bigjools: It's the only report of breakage, interesting enough. [09:53] allenap: https://edge.launchpad.net/~launchpad/+archive/ppa/+sourcepub/1263354/+listing-archive-extra has the binaries you need. [09:53] I've just started the query on staging to see how many rows there are when restricting to archive=1 [09:54] wgrant: Thanks. [09:54] bigjools: http://paste.ubuntu.com/506220/ was the last query I had run on production. [09:54] Gives the count for each archive. [09:54] yeah I have tjat [09:56] wgrant: would you mind doing me a favor and making a query to fix the PPA publications while I do this? I think we can filter on dateremoved [09:57] bigjools: That was my thinking too. [09:57] great minds ... [09:57] Anything with dateremoved IS NOT NULL should be safe to just flip back. [09:57] Which should cover all of the primary archive cases too, hopefully. [09:57] ah very good point [09:57] nm then [09:57] I'll pop that in [09:58] jtv: thanks [09:58] Just set the status, and unset datesuperseded, datemadepending and scheduleddeletiondate [09:58] I think that should do it. [09:59] Although the last two aren't relevant for the primary archive. [09:59] If anything has those set we have other bugs. [09:59] wgrant: Did you see that test failure? [09:59] and supersededby [09:59] bigjools: erk, good point. [09:59] henninge: Yes. lifeless ran it again, and it failed spuriously another way. [09:59] :) [09:59] Along with a few other branches. [09:59] So there's something wrong with devel. [10:01] who ones merge proposals code? [10:01] s/ones/owns/ [10:01] s/who/who in the code team/ ;) [10:04] wgrant: should be fixed soon, branch is in pqm atm [10:04] lifeless: Ah, great. [10:04] danilos, henninge: we've long wanted to use the permissions system for translations, but I find myself really not wanting to do that now. What would permissions be _on_? It seems weird to invent a DummyPOFile just to check permissions on it, for instance. [10:05] jtv, what do you mean with 'now'? do not want to use "full" zope machinery with this particular branch? [10:06] danilos: I mean "now that we're sharing translations so widely." [10:06] bigjools: Should we run a query on prod to see how many have already been removed? [10:06] doing it now [10:07] With the per-archive counts? [10:07] quite a lot it seems, I get a count of 842 in the query now [10:07] after adding "bpph.dateremoved IS NOT NULL" [10:07] On staging? [10:07] yes [10:08] danilos: if it were just "are you one of the owners of this product," for instance, I'd have no problems. But if we go pofile→potemplate→productseries→product→project→translationgroup→translator, that's a lot for a permissions check. [10:08] Are any of those in the primary archive? [10:08] one sec [10:08] jtv, well, sharing aspects of it will be relatively hard to map in the zope permissions system, but ideally, we'd at least make it a simple check: "can-do-something-here" and "can-do-something-on-the-other-side" [10:08] The publications I checked hadn't had removal scheduled, so I doubt it. [10:08] jtv, ideally, we'd just check permissions on the pillar level (product/distribution), because that's where we get them from [10:09] except for pofile, of course [10:09] danilos: sure, that part's fine for me and may even hide a bit of the "if productseries" ugliness [10:09] danilos: that's the other thing—I really, _really_ don't want to give edit rights to pofile owners. [10:10] jtv, I am sure you can come up with a reasonable design, and you might be right that permissions system is not the right solution [10:10] jtv, right, we want maintainers to have edit rights on them, but other than that, just translators for appropriate language [10:11] danilos: I appreciate the trust. :) I think POFile.owner is a bit of an historical mistake; it's really "creator" except we always set it to rosetta-admins if the creator doesn't have edit rights already. [10:11] jtv, fwiw, do note that you will see tests breakage if pofile.owner can't edit a pofile (I am sure that in all our laziness we used that often :) [10:11] wgrant: Thanks for that link; my bacon has been saved. [10:11] allenap: Yeah, uh, sorry about that. [10:11] jtv, oh, pofile.owner is bordering on the useless, I'd say [10:11] danilos: there's a good point… another reason to clean up, probably. [10:12] wgrant: Ah, are you the culprit? :) [10:12] danilos: if we stop giving it any privileges, we can also set it to the actual creator rather than "set to creator _if_ it doesn't conflict with current permissions." [10:12] allenap: Yes :/ [10:12] jtv, then it'd be nice to rename it as well [10:12] danilos: it may also avoid some of those cases where someone who's not in the translation team can continue to submit bad translations. [10:12] wgrant: Hehe, oops. [10:12] danilos: yes, I'd like that too. [10:12] allenap: Rather. [10:12] jtv, true, true [10:13] jtv, so, basically, you want to drop "owner" field and create a different "created_by" field [10:13] jtv, I agree that'd be nice, but this is a cleanup job for later :) for now, you can simply ignore the owner for permissions [10:13] danilos: that's what I had in mind, yes. [10:14] jtv, cool, sounds good [10:14] The main thing right now is not to give special rights to pofile.owner, in which case the only things that matter are the person, the language, and the pillar. [10:14] I was thinking of making this a method in ITranslationsPerson, since we have so many functions about this spread out. [10:23] ok, devel should be fixed [10:25] lifeless: Could you fire of that branch again, or shall I convince someone else? [10:25] someone else per favour [10:25] hello [10:29] hello [10:31] henninge: Could you please ec2 that branch again? [10:31] bigjools: Any progress? [10:31] query still running [10:31] Er. [10:31] wgrant: just test it or try to land it? [10:31] Doesn't it only take three minutes? [10:31] henninge: The latter. [10:32] wgrant: ok [10:33] wgrant: probably on production [10:33] bigjools: This was on staging. [10:33] hmm [10:33] Although it didn't have the archive/person join, that really shouldn't be that bad. [10:33] So 250k of our 330k branches are owned by 'ubuntu-branches', which is rather lopsided and will certainly be triggering timeouts in some areas. [10:33] * bigjools kills it [10:46] Who is buildbot angry with? [10:49] wgrant: http://pastebin.ubuntu.com/506369/ [10:50] stub: any luck with the bug portlet stuff? [10:50] wgrant: which means 1100 or so packages are lost in Ubuntu [10:50] bigjools: Uh, is that dateremoved IS NOT NULL? [10:50] yes [10:50] wgrant: sorry [10:50] :/ [10:51] lifeless: I'd say 1) work out what the query is trying to do 2) rewrite it. [10:51] 1) might be hard :P [10:51] wgrant: I meant to say, that's bpph.dateremoved IS NULL [10:51] bigjools: Is that prod or staging? [10:52] wgrant: staging [10:52] bigjools: The 3700 was from prod. [10:52] ok [10:53] I need to land your fix on prod [10:53] What does http://paste.ubuntu.com/506220/ say on staging? I suspect that will show that we haven't lost anything from the primary archive. [10:53] Alternatively, what does your dateremoved IS NULL query say on prod? === jtv is now known as jtv-afk [10:54] But yes, germanium and cocoplum want that patch. [10:54] argh [10:54] lifeless: Indeed. btw. the query I'm testing is slow on staging too, although better at a little under 30s. I'd suggest it is working at all under PG 8.3 purely by accident (if you consider 7 seconds working) [10:54] I cna't remember if that query was with or without the NOT [10:55] stub: thats odd; the one I copied from the OOPS I pasted takes 800ms on staging. [10:55] bigjools: I'm fairly sure it must have been dateremoved IS NULL. [10:55] stub: perhaps I'm connected to 8.3 on staging? [10:55]