[00:23] -> pet store and stuff [00:24] gary-lunch: look at rev 12564 in devel to see how to do a with query with my patched storm [00:37] I tried to create a packageupload(archive, distroseries, status) index on DF. It works for queries like archive=534, distroseries=X, status=Y. But when archive=1 it refuses to use the index. I guess it thinks its stats know better. [00:38] do you have a sample query ? [00:39] EXPLAIN ANALYZE SELECT PackageUpload.archive, PackageUpload.changesfile, PackageUpload.date_created, PackageUpload.distroseries, PackageUpload.id, PackageUpload.pocket, PackageUpload.signing_key, PackageUpload.status FROM PackageUpload WHERE packageupload.distroseries = 103 AND packageupload.archive = 534 AND packageupload.status IN (0) ORDER BY PackageUpload.id DESC LIMIT 31 OFFSET 0; [00:39] That uses the index. [00:39] And is fast. [00:39] s/534/1/, no index, slow. [00:39] gotta run, bbiab [00:50] Oh. Now that is interesting. [00:51] It uses the index if distroseries=102 [00:51] But not 103. [00:53] is it the usual thing where it thinks it's going to be looking at most of the table anyway? [00:53] I presume so. [00:54] 103 does show up in pg_stats, so that may be it. But other archives that show up there don't see the same issue, so I might have to check out pg_statistics to see what it's doing. [00:54] Or harass stub. === Ursinha is now known as Ursinha-afk [01:17] cool thanks lifeless [01:17] * gary-lunch wonders how he became gary-lunch [01:18] gary-lunch: It's a bit late for lunch, isn't it? === gary-lunch is now known as gary_poster [01:18] yes indeed [01:18] Almost lunch time here, in fact. [01:19] Ah. [01:19] It will happily use packageupload(archive, distroseries, status, id). [01:20] I guess it must have decided that archive=1, distroseries=103 meant the final sort would be too expensive. [01:20] Despite the fact that there are actually 0 rows. [01:25] wgrant: Limit (cost=0.00..954.05 rows=31 width=36) (actual time=263.988..31328.809 rows=1 loops=1) [01:25] -> Index Scan Backward using distroreleasequeue_pkey on packageupload (cost=0.00..93404.35 rows=3035 width=36) (actual time=263.985..31328.804 rows=1 loops=1) [01:25] Filter: ((distroseries = 103) AND (archive = 1) AND (status = 0)) [01:25] Total runtime: 31328.871 ms [01:25] (4 rows) [01:25] 1.5 seconds hot [01:25] wgrant: thats qastaging [01:26] lifeless: Right, that's about what I see on mawson. [01:26] Except about 25% faster. [01:26] I'll talk to stub about unbreaking the indices tonight. [01:27] Since the current ones were designed before archives were introduced... [01:27] you're adding an index to match the critera more closely [01:27] whats the explain analyze of the query that (isn't using) the new index look like [01:28] wgrant: can you drop the order by ? [01:28] Time: 37.729 ms [01:28] lifeless: The current indices are not reasonable. [01:28] We need the order by. [01:28] wgrant: why? [01:28] packageupload(distroseries, status) does not make sense any more. [01:28] Since you never query PUs by series outside an archive. [01:29] wgrant: sure, I'm not debating that, just looking at whats going on [01:29] wgrant: I meant 'why do we need the order by' [01:29] lifeless: So we don't show them in arbitrary order on DistroSeries:+queue [01:30] wgrant: why would that matter ? [01:30] Because that would make batching sort of bad. [01:32] wgrant: SELECT PackageUpload.archive, PackageUpload.changesfile, PackageUpload.date_created, PackageUpload.distroseries, PackageUpload.id, PackageUpload.pocket, PackageUpload.signing_key, PackageUpload.status FROM [01:32] PackageUpload WHERE packageupload.distroseries = 103 AND packageupload.archive = 1 AND packageupload.status IN (0) ORDER BY packageupload.distroseries, packageupload.status, PackageUpload.date_created desc LIMIT 31 OFFSET 0; [01:33] lifeless: That is cheating. [01:33] wgrant: so is life [01:33] There's no index on PackageUpload.date_created, so it has no choice. [01:33] It will be slow if it returns lots of results. [01:34] wgrant: right, so we would not want to do that for every query [01:34] wgrant: but the queue isn't unlimited [01:34] wgrant: and lots could be a few thousand without perf worries [01:34] wgrant: I agree we need to improve the indices [01:35] lifeless: https://launchpad.net/ubuntu/lucid/+queue?queue_state=3&queue_text= [01:35] 130000 [01:36] wgrant: whats 3 [01:36] lifeless: Done. [01:36] we should delete them [01:36] You could argue that. [01:36] s/c/sh/ [01:37] It requires reworking lots of Soyuz. We should probably do that, but that is a *huge* *huge* thing. [01:37] We need to develop a strategy. [01:37] Project windmill build #37: STILL FAILING in 1 hr 9 min: https://hudson.wedontsleep.org/job/windmill/37/ [01:37] On how we handle history like this. [01:38] And apply it everywhere. [01:38] Windmill, you make me very sad. [01:39] StevenK: It's right, though. [01:40] wgrant: so, does the queue *post* need new indices to be fixed? [01:40] wgrant: or would my hack give breathing space? [01:40] lifeless: I personally think that some of this data is useful. The table is small, and it's extremely fast to query with the right indices. [01:40] lifeless: I don't think that hack is useful. [01:40] It would work. [01:40] ok [01:40] But an index is less of a hack, and works everywhere. [01:56] 455 Time Outs [01:56] nice [01:57] bugger [01:57] 23 / 22 POFile:+translate [01:57] 20 / 0 LanguageSet:CollectionResource:#languages [01:58] Wow. [01:58] ah [01:58] Bugger? [01:58] wgrant: things we had fixed [01:58] They weren't deployed yet, were they? [01:58] but yeah [01:58] 9 hours of them [01:58] They are now. [01:58] tomorrow will give better results again [01:58] Yep. [01:59] This is why I want 12-hourly ones :( [01:59] wgrant: I want a sliding window [01:59] Right. [01:59] plus per-deploy [01:59] But that needs oops-repository. [01:59] (I suspect) [01:59] well, can be done in the current code, just a matter of effort [02:00] Right. [02:00] I'm going to look into hbase as an alternative to cassandra [02:00] Oh? [02:00] need to send a raft of emails around [02:00] I haven't heard much about hbase... [02:00] But it's Apache too, right? [02:00] that aspect is fine [02:01] the issue with cassandra is lack of range queries [02:01] :( [02:10] lifeless: Do we expect daemons to survive DB restarts? I know eg. librarian doesn't. [02:11] wgrant: yes [02:11] wgrant: exact implementation to be figured out later [02:11] So that's a no? [02:12] today, restarting the db may wed daemons. If so thats a bug. [02:12] I'm tempted to leave poppy broken in that situation, given that most other daemons will have the same issue. [02:14] Can you see any problem with that? [02:15] It's not going to try to hold transactions open forever any more, so it shouldn't get killed :) [02:15] if you know of a specific defect, please make sure there is a high bug about it [02:16] I think it's a general defect in all daemons... [02:16] wgrant: I wouldn't assume that [02:18] Anyway, lunch, double poppy cowboy fixing, and then +copy-packages UI optimisations. [02:18] Because the actual copy operation is now fast :( [02:18] \o/ [02:19] wgrant: That timeout was due to UI? [02:24] Project windmill build #38: STILL FAILING in 46 min: https://hudson.wedontsleep.org/job/windmill/38/ [02:26] StevenK: Yes ;( [02:28] At least I think so. [02:29] It's a bit hard to tell exactly what's what. [02:29] Because the UI stuff generates a lot of queries. [02:30] It's also not copying binaries, which is a case that is less optimised. But it doesn't seem to get to the actual copies. [02:30] Erm. [02:30] "I get a timeout error when trying to copy a few (5+) packages from a ppa" [02:30] But field.selected_sources has at least 15... [02:31] I suppose that is technically still 5+... [02:31] :> [02:32] wow [02:32] Aggregate (cost=7238627.72..7238627.73 [02:32] Which query's that? [02:33] https://lp-oops.canonical.com/oops.py/?oopsid=1895B1870 [02:33] Ah. [02:35] partitioning the query into two unioned on each fti column gets [02:35] Aggregate (cost=291.55..291.56 rows=1 width=0) [02:36] I've been dreading this [02:36] but its time to bite the bullet and do it [02:36] I'll do my administrivia stuff monday [02:37] Heh [02:37] I wonder [02:37] maybe I'll just ditch the bugtask fti column [02:37] Does that just index targetnamecache? [02:40] 16 more hits [02:40] for synaptics touchpad [02:40] targetnamecache and statusexplanation [02:40] -wtf- [02:41] I thought statusexplanation was actually dead now. [02:41] Apparently not :/ [02:42] COMMENT ON COLUMN BugTask.statusexplanation IS 'A place to store bug task specific information as free text'; [02:42] You know what it is, right? [02:42] no idea [02:43] You know how the old task edit form had a comment field? [02:43] That adds a comment and sets statusexplanation. [02:43] Those changes are also logged in bugactivity. [02:43] lib/lp/bugs/scripts/bugzilla.py uses it [02:43] If that's what I think it is, it will die. [02:44] I wonder if it still works. [02:44] the task edit form still exists [02:44] browser/bugtask.py has [02:44] Yes, but it doesn't need to set statusexplanation. [02:44] if comment_on_change: [02:44] bugtask.statusexplanation = comment_on_change [02:45] select count(*) from bugtask where statusexplanation is null or statusexplanation=''; [02:45] count [02:45] -------- [02:45] 531321 [02:45] select count(*) from bugtask where statusexplanation is not null; [02:45] count [02:45] -------- [02:45] 480008 [02:45] bah [02:45] Hmmm? [02:45] select count(*) from bugtask where statusexplanation is not null and statusexplanation != ''; [02:45] count [02:45] -------- [02:45] so 300K set, 530K unset [02:45] 300198 [02:45] Right. Kill it. [02:46] It has been thoroughly deprecated for more than 5 years. [02:46] We have these wonderful things called comments to store that information now :) [02:49] bug/88545 [02:50] bug 88545 [02:50] no mup? [02:52] statusexplanation? [02:52] a discarded idea [02:57] Is it actually used in the codebase at all? [02:58] grep ftw [02:58] The only place I've seen it exposed is BugTask:+activity [02:59] lifeless: Isn't there another clean up you wanted to do with BugComment and Bug [02:59] jcsackett is on that [02:59] or at least on nearby stuff [02:59] BugMessage.visible was moved this morning. [03:00] Ah yes BugComment versus BugMessage [03:00] there is a completely redundant python class constructed from bugmessage+message [03:00] if we had separation of concerns - a persistence layer - it would make sense [03:00] but its purely a Data Object at the moment, valueless. [03:01] libwebkitgtk-1.0.0-dbg DIALCF! [03:02] StevenK: Why? [03:02] 240MiB debug packages make me cranky. [03:02] Hah. [03:03] And it's Webkit, which I have other issues with. [03:06] like, it existing? [03:07] lifeless: Is that your issue with it? [03:07] no [03:07] bundled libraries [03:07] bugs [03:07] footprint [03:07] That's one of my issues -- my major gripe of it is just how much crap is in it [03:07] I prefer WebKit to Gecko... at least once it has been deGoogled. [03:08] its a whole desktop [03:08] ie. had the hundred external libraries excised. [03:08] what could be wrong [03:08] Which gives us wonderful things like the aforementioned 240MiB debug packages. [03:08] wgrant: Oh, Gecko is no better [03:08] From the same people who gave us building UIs from XML [03:08] Oh crap, now I need to review my lunch. [03:08] Um, most GTK apps now build UIs from XML :) [03:09] wgrant: exactly [03:09] Didn't XUL start that madness? [03:09] It is unclear that it is madness. [03:10] wgrant: you saw https://code.launchpad.net/~lifeless/storm/with-without-datetime right ? [03:10] Anyway, yay for feeping creaturism [03:10] lifeless: I never looked at the code. [03:10] wgrant: thats fine [03:11] whats the script that gives committer frequencies in lp [03:12] What do you mean? Number of commits by person for a particular branch? [03:12] within a timeframe, or per month, or whatever. [03:12] yes [03:13] I can has review? https://code.launchpad.net/~lifeless/launchpad/bug-716774/+merge/52966 [03:13] I don't know of anything beyond bzr-stats... [03:14] wgrant: does PoppyFileWriter's validateGPG affect sftp uploads? [03:14] lifeless: No. [03:14] It's only used in FTP for now. [03:14] (WTF, I know, but convenient) [03:15] thanks [03:15] I guess I should start that IR at some point. [03:16] or ask me to do it [03:16] but don't sit on it [03:16] I'm sure you have better things to do. I'll do it. [03:20] wgrant: Julian says he couldn't work out how to do it for SFTP uploads. Which made me sad. :_( [03:20] :( [03:24] I'm hoping poppy-{s,}ftp now get a good hard refactoring [03:24] $DEITY knows they deserve it. [03:24] A renaming wouldn't go astray either. [03:26] Oh, and death to zope ftp would be nice, too. [03:26] lifeless: An advertised feature is no longer working. Not Critical? [03:28] wgrant: we've never had it live [03:28] wgrant: we tried and it went boom [03:28] It was working fine for like an hour :P [03:28] wgrant: that doesn't count [03:29] :( [03:29] julian blogging about it was great enthusiasm, but we probably want to wait for things to be stable before announcing [03:29] Indeed. [03:30] wgrant: point is, if we stopped work on the improvement right now, we haven't actually gone back on anything folk could have started depending on. [03:30] that said, I think we should push forward and get it going [03:30] it will make a huge difference to usability. [03:31] and that will mean happier and less confused users [03:31] lower support overhead and easier training for new ubuntu devs [03:36] thumper: Hi. [03:36] wgrant: hi [03:36] thumper: Could you mentor https://code.launchpad.net/~lifeless/launchpad/bug-716774/+merge/52966? [03:37] * thumper looks [03:37] Anyone want to review https://code.launchpad.net/~wgrant/launchpad/poppy-transaction-unbreakage/+merge/52967? [03:39] wgrant: Done. thumper ^ [03:40] OMG [03:40] my recursive query worked [03:40] http://pastebin.ubuntu.com/578642/ [03:41] According to the postgres docs it's iteration, not recursion :_) [03:41] does postgres have stored procs? [03:42] From memory, yes [03:43] hmm [03:43] wgrant: Wait. Are you sure you ran bzr add? [03:43] doing the blueprint dependencies in the db has to be better than doing X queries [03:47] thumper: yes [03:47] thumper: you can do that with the storm we have in trunk [03:47] lifeless: how? [03:47] store.with(SQL('RECURSIVE dependencies(id, name, depth) AS ( [03:47] SELECT s.id, s.name, 1 [03:47] FROM specification s [03:47] where s.id = 20428 [03:47] UNION ALL [03:48] SELECT s.id, s.name, d.depth + 1 [03:48] FROM specificationdependency sd, dependencies d, specification s [03:48] WHERE sd.specification = d.id [03:48] AND s.id = sd.dependency [03:48] )') [03:48] bah [03:48] store.with_ [03:48] so store.with_(clause).find(Specification) [03:48] or whatever [03:48] probably [03:49] so store.with_(clause).find(Specification, SQL('Specification.id in (select id from dependencies))') [03:51] thumper: care to review https://code.launchpad.net/~lifeless/launchpad/bug-716774/+merge/52966 ? [03:52] lifeless: I have [03:52] oh sweet [03:52] thanks [03:52] sorry I hadn't noticed [03:52] * lifeless ec2inates [03:52] Which just sounds ... wrong. [03:55] StevenK: What did I miss? [03:55] wgrant: It looks to me that you moved all the imports but not the actual method itself? [03:55] 211 === renamed file 'lib/lp/bugs/externalbugtracker/isolation.py' => 'lib/lp/services/database/isolation.py' [03:55] 212 === renamed file 'lib/lp/bugs/externalbugtracker/tests/test_isolation.py' => 'lib/lp/services/database/tests/test_isolation.py' [03:56] Ah ha. Objection withdrawn, the MP is still approved. [03:58] I'm actually pretty stoked about that blueprint dependency query [03:58] now to squeeze it into the code somehow [03:59] Let's hope we don't have to revert to pg 8.3 :) [03:59] Haha [04:00] wgrant: recursive queries were added in 8.3 [04:01] at least I thought so [04:01] WITH RECURSIVE was added in 8.4 [04:01] I thought WITH was added in 8.4. [04:01] ah... [04:01] Right. [04:01] I wonder what the likelyhood of that is then... [04:01] 0 [04:01] \o/ [04:08] thumper: Can haz mentor? :-) [04:08] thumper: Could you mentor https://code.launchpad.net/~wgrant/launchpad/poppy-transaction-unbreakage/+merge/52967? [04:08] Haha [04:08] * thumper looks [04:09] Thanks. [04:09] 'tis past wine o'clock on a Friday [04:09] you're lucky :0 [04:09] we're not reverting [04:09] not to 8.3 [04:09] that goose has flown [04:10] Can we revert to 9.0? That sounds like FUN. [04:11] done [04:11] 9.0 already? Well done. [04:12] Damn it, I just missed a devel build on buildbot [04:12] ... by about 30 seconds. :-( [04:12] We can't roll out until Tuesday, either :( [04:12] tuesday? [04:13] ACT public holiday on Monday, and no mthaddon next week. [04:13] Haha, awesome [04:15] * thumper wines a little [04:15] * thumper AFK [04:15] s/w/wh/ [04:16] * thumper pokes StevenK in the eye [04:16] Ow! [04:50] :( [04:51] Does BugTask.status serve any useful purpose? [05:09] uhm [05:09] its where we store triaged etc [05:11] Opinion/Invalid/Won'tFix aren't usefully separated, New is implicit, Confirmed is implied by affects, In Progress is effectively implied by assignee, Fix Committed and Fix Released are implied by MP status. [05:11] So we can replace them all with "valid" and "incomplete" flags. [05:12] or customizable bug statuses. \o/ [05:12] wgrant: Right, which works for *us*, but may not work for other projects. [05:13] StevenK: They work for anyone that uses MPs. [05:13] Unless they are abusing statuses, in which case they are wrong :) [05:13] no [05:13] its not 'wrong'. its the system speaking to you. [05:14] cody-somerville: What possible need do you have for customizing bug statuses? [05:14] Oh, OEM Services has tons since we abuse bugs for other things. [05:14] wgrant: Take a look at the statuses proposed on this page: https://dev.launchpad.net/IssueTracker [05:15] huwshimi: Mmm. Better, but still not optimal. [05:16] ugh. not better IMHO. [05:16] What, two statuses? Fixed and Unfixed? :-P [05:17] cody-somerville: Why? [05:17] The only significant change is merging Fix Committed and Fix Released. [05:17] I think cody-somerville wants *more* statuses, not less. [05:20] so workflow around bugs is a fascinating topic [05:23] indeed [05:25] OH FER. libwebkitgtk-1.0-0-dbg isn't enough, libwebkitgtk-3.0-0-dbg exists too [05:26] StevenK: Why are you dealing with them? [05:26] Local mirror sync [05:27] Ah. [05:28] :( [05:28] DF doesn't have memcached installed. [05:29] but for what its worth... I think the current set of bug statuses are alright. I'm much more interested and would get much more value (professionally and personally) from things like build-from-branch and derivative distributions. :) [05:30] cody-somerville: You know recipe builds are "done", right? [05:30] Recipes != BFB [05:30] Oh, right. [05:30] That saddens me. [05:31] lol. OO.o is in NBS [05:33] cause although tweaking the bug statuses might make LP better to some degree, it isn't going to make it more competitive against say github or OBS. [05:34] Anyhow, I should try and get some sleep. :) *waves* [05:49] * wgrant murders get(Build)StatusSummar(y|ies)(For(Builds|Source(Publication|IdsAndArchive))) [05:52] wgrant: Seriously? [05:53] Project windmill build #39: STILL FAILING in 1 hr 9 min: https://hudson.wedontsleep.org/job/windmill/39/ [05:59] StevenK: Well, bits of them at least. [06:00] Aww [06:00] SourceForge's new platform is interesting. [06:00] Rather GitHubish. [06:01] And far less awful than their old one, at first glance. [06:07] wgrant: They've just open sourced their backend too [06:07] Yeah, that's the new one. [06:07] Currently grabbing it... [06:08] Haha, of course you are. [06:11] wgrant: i can't seem to run any windmill tests locally (to fix the breakage). is there a config item i need to tweak do you know? [06:14] wallyworld: --layer=WindmillLayer [06:15] It defaults to --layer=!(MailmanLayer|WindmillLayer) [06:15] wgrant: thanks [06:16] was that what you changed to stop the tests by default? [06:16] Yeah. It was previously just !MailmanLayer [06:17] (see buildout-templates/bin/test.in) [06:32]