=== cinerama_ is now known as cinerama [03:33] morning folks [03:50] hahhahahahahaahahahahahahahahaha [03:50] ha [03:54] wgrant: yo, soyuz question [03:54] https://lp-oops.canonical.com/oops.py/?oopsid=1904K1614 [03:54] queries 10 and 12 [03:57] lifeless: What about them? [03:57] why does the file size count not have the status enum check [03:58] lifeless: It uses dateremoved instead. [03:58] Which is similar, but not quite the same. [03:58] dateremoved lags a bit. [03:58] But it represents the actual size on disk. [03:58] uhm [03:58] would it be incorrect to put the status enum in ? [03:58] It depends on your definition of correctness. [03:59] It would make it no longer reflect the archive disk usage. [04:00] lifeless: Erm, 6to4 doesn't do that. [04:00] Maybe you mean NAT64. [04:00] Which is in no way widely supported. [04:01] And then you probably need DNS64 too. [04:01] Pure IPv6 is not a very friendly environment at the moment. [04:01] ah [04:01] there used to be a 6 -> 4 thingy [04:01] Still, World IPv6 Day soon. [04:01] 6-7 years ago [04:01] Yeah. [04:01] That was deprecated. [04:02] And replaced by NAT64. [04:02] But neither has ever been widely supported. [04:02] Doable, but not common. [04:02] I feel for the guy [04:02] but its totally not a bug in lp's code [04:02] I just love how The Plan for IPv6 is "Oh, everyone will move. At some point." [04:02] StevenK: What more plan can there be? [04:02] People have to stop being hideously lazy. [04:02] And just do it. [04:02] I just like to imagine IPv6 causing DNS servers the world over to scream “AAAA!” [04:02] It's not that hard. [04:03] spiv: Boo. Hiss. [04:03] wgrant: No, it just costs time and lots of money. [04:04] StevenK: And they've had more than a decade to do it. [04:04] And telecommunication companies prefer to *recieve* lots of money and spend very little. [04:04] wgrant: thats -barely- 2 equipment refreshes of core switch gear [04:04] lifeless: Sure. But most companies are not even thinking about it yet. [04:05] wgrant: and its not been /stable/ for all that long [04:05] anyhow [04:05] archive:+repository-size [04:05] not ip6 [04:05] kgo [04:06] At least aarnet's tunnel broker is reasonable. [04:06] lifeless: What about it? [04:08] wgrant: thats the oops I'm looking at [04:08] https://bugs.launchpad.net/launchpad/+bug/739070 [04:08] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [04:08] 9+ seconds to query file sizes [04:08] Yes. [04:09] lifeless: Would the status filter improve it significantly? [04:09] I'm not 100% sure [04:09] exploring the space atm [04:09] is it 2 for binaries as well? === Ursinha is now known as Ursinha-afk [04:10] Yes. [04:10] PackagePublishingStatus is universal. [04:10] ~/launchpad/lp-branches/bfj-different-urls$ bzr push [04:10] Using saved push location: lp:~wgrant/launchpad/bfj-different-urls [04:10] bzr: ERROR: Server sent an unexpected error: ('error', '') [04:10] I don't think that's meant to happen. [04:10] wgrant: would 2+3 be equivalent ? [04:10] lifeless: No. [04:11] lifeless: What's 3? Superseded? [04:11] (it's working this time) [04:11] wgrant: yes [04:11] 4 is deleted [04:12] 1 is pending [04:12] 5 Obsolete. [04:12] Right. [04:12] I never remember the 3/4 order. [04:12] So, 3/4/5 are all potentially removed. [04:12] In the beginning there was Pending/Published/PendingRemoval/Removed. [04:12] anyhow [04:12] Then ArchiveRemovalRedesign happend, and the removedness was moved into dateremoved instead. [04:12] with (2) its a 50ms query hot [04:12] With status indicating the reason for removal. [04:12] Hmm. [04:13] With dateremoved how is it hot? [04:13] 73ms [04:13] :P [04:22] look at the plan [04:22] "securebinarypackagepublishinghistory__archive__status__idx" btree (archive, status) [04:22] is the index it is using [04:23] so it reads every row ever in that archive [04:26] wgrant: ^ [04:28] wgrant: ping [04:28] create index bpph__dateremoved__for__size__idx on binarypackagepublishinghistory using btree (dateremoved) WHERE dateremoved IS NULL; [04:28] can you create that on dogfood [04:28] and get me an explain analyze for the query in https://bugs.launchpad.net/launchpad/+bug/739070/comments/1 ? [04:28] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [04:31] lifeless: Ah, didn't see the plans in the bug. [04:31] Let's see. [04:33] lifeless: The index is building. [04:34] I suspect we'll want (archive) where dateremoved is null [04:34] or [04:35] we should make dateremoved go away [04:35] either folding it into the status enums [04:35] or dropping the librarian reference or whatever [04:43] lifeless: -> Index Scan using temp_bpph_archive_dateremoved_idx on binarypackagepublishinghistory (cost=0.00..1112.40 rows=391 width=4) (actual time=56.562..107.930 rows=915 loops=1) [04:43] Index Cond: ((archive = 14516) AND (dateremoved IS NULL)) [04:43] So the bpph scan is cheap with an index [04:44] 34ms hot. [04:44] Is that dateremoved check a status check in disguise? [04:44] 10s cold, but DF. [04:44] jtv: Yes. [04:44] lifeless: There is a stay of execution for [bs]pph, we'd need to think very carefully about it. [04:44] StevenK: We don't need to remove it [04:45] StevenK: but we do need to model it. Modelling it as a separate field may be worse than not. [04:45] wgrant: the query was 10s cold ? did you get the full explain analyze? [04:46] We could add another DBItem to PackagePublishingStatus -- PURGED or so [04:46] lifeless: I had it, but then I made the mistake of pressing PgUp, which DF does not enjoy at all. [04:46] StevenK: Doesn't quite work. [04:46] StevenK: PPS has three separate end states. [04:46] (whether this is sane or not is in question) [04:47] lifeless: But cold data from DF is entirely useless. [04:47] lifeless: The numbers, at least. [04:47] The plan is OK. [04:47] wgrant: can I see? [04:47] wgrant: cold data still tells me where time is going, and thats useful. [04:47] wgrant: so I dispute entirely useless [04:47] wgrant: oh, I see, lost. [04:47] kk [04:47] Yeah. [04:48] Get one from qastaging, I guess. [04:48] select count(*) from branchrevision; then try again ? [04:48] It doesn't tell you where the time goes, really. [04:48] It tells you that DF has like 4MB of RAM. [04:48] 4MB. 'lol' [04:48] 640k surely === Ursinha-afk is now known as Ursinha [04:52] ugh, timeline only works if you run upstream builds of chromium? wtf [04:53] lifeless: Hm? [04:53] Works for me. [04:53] "Pfffttt, Speed Tracer is not working. [04:53] Please double check a couple of things: [04:53] You must start Chrome with the flag: --enable-extension-timeline-api [04:53] You must be running the Chrome Dev channel. [04:53] For more details, see our getting started docs." [04:53] Oh. Not an upstream build. [04:53] I have enable-extension-timeline-api in CHROMIUM_FLAGS in /etc/chromium-browser/default [04:53] Just not a stable build. [04:54] webdevs are stable users too [04:54] And error message that includes "Pfffttt," Kwality [04:54] eg. ppa:chromium-daily/beta or so. [04:54] I question the sanity of this [04:54] lifeless: *That* makes you question the sanity of Chromium?" [04:54] I have yet to see any *compelling* reason to switch to Chromium, TBH. [04:54] StevenK: I use Chromium. [04:55] It's not bad. [04:55] However, I question Google's motives. [04:55] Firefox 4 is OK, except not with fglrx. [04:55] wgrant: so can has plan? [04:55] lifeless: The hot one? [04:56] the cold one after querying branchrevision a lot [04:56] It's been running that select for 8 minutes, still hot as ever. [04:56] argh hhaa [04:56] ok [04:56] Yes. [04:56] hot will be fine [04:56] http://paste.ubuntu.com/602605/ [04:57] funny, 3.6 was broke with nvidia, 4.0 is broke with fglrx [04:57] Our test suite is slow. [05:00] wgrant: https://bugs.launchpad.net/launchpad/+bug/739070/comments/4 [05:00] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [05:01] lifeless: 19ms [05:01] this is 1/2 the estimated cost [05:01] which is daft but there you go [05:01] http://paste.ubuntu.com/602606/ [05:02] why are we bringing back the rows ? [05:03] Which rows? [05:03] these rows [05:03] why aren't we summing [05:03] NFI [05:03] But it's only a single column. [05:03] So it's not that bad. [05:03] (yes, it should be fixed) [05:03] Oh. This query is not a single column. [05:04] Ahh, this is query 11, not query 10. [05:06] wgrant: https://bugs.launchpad.net/launchpad/+bug/739070/comments/5 [05:06] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [05:06] wgrant: actually I'm in https://lp-oops.canonical.com/oops.py/?oopsid=1948BB197 [05:07] query 21 [05:07] 8929ms SQL-launchpad-main-master [05:07] SELECT DISTINCT LibraryFileContent ... [05:07] lifeless: Still 19ms. [05:08] spm: can you run https://bugs.launchpad.net/launchpad/+bug/739070/comments/5 on the readonlymode replica - *one* run, I need the explain analyze (looking for cold cache effects) [05:08] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [05:08] oki [05:23] why does packagefile exist? [05:24] https://bugs.launchpad.net/launchpad/+bug/739070/comments/6 [05:24] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [05:25] wgrant: ^ [05:25] StevenK: ^ [05:25] lifeless: packagefile? You mean BinaryPackageFile? [05:25] yes [05:25] There are two [05:25] and I assume sourcepackagefile [05:26] SourcePackageReleaseFile is essential. [05:26] It's SourcePackageReleaseFile [05:26] (Usually called SPRF) [05:26] BinaryPackageFile is for potential expansion to multi-file binaries. [05:26] (yes, seriously, one has Release and one does not) [05:26] ok [05:26] I can see the expansion factor [05:26] but [05:27] if we makde bpph be an owner of LFC (wrapping the LFA fields in it, and glued into the gc process) we'd save 2.2 seconds of overhead. [05:28] aaaaaaaaaaaaaaa [05:28] aaaaaaaaaaaaaaaaaa [05:28] So, an SPR can have any number of SPRFs. [05:28] And there are often lots of BPPHs for a BPR. [05:28] sure [05:28] but the whole point of LFA is to reference-count-own LFC [05:29] ignore SPRF for now, I see why the 1:M is needed [05:29] we could roll up some stuff there too, but separate problem. [05:29] No point solving BPPH without SPPH. [05:29] wgrant: 3:1 bpph -> spph rows [05:29] or more [05:30] A constant factor of three is not very compelling. [05:30] wgrant: solving a problem is solving a problem [05:30] so the question is, can we solve [05:30] or [05:30] do we need to precalculate [05:43] * StevenK murders gina.txt [05:52] stub: hi [05:54] Speaking of stub, where did his wonderful "prejoin" decorator go? [05:54] jtv: deleted [05:54] in favour of what? [05:54] DecoratedResultSet [05:55] stub: is 2.8 seconds 'about right' for reading in 1000 cold rows from libraryfilecontent? [05:56] With cold rows, how would you know? [05:56] yo [05:56] jtv: https://bugs.launchpad.net/launchpad/+bug/739070/comments/11 [05:56] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [05:56] jtv: science :> [05:56] lifeless: I don't think is very good [05:57] It's still considerably less than you'd expect from a full seek per row. [05:57] stub: https://bugs.launchpad.net/launchpad/+bug/739070/comments/6 [05:57] <_mup_> Bug #739070: Archive:+repository-size timeout < https://launchpad.net/bugs/739070 > [05:57] lifeless: no I don't mean how do you know it's 2.8 seconds for 1000 rows, but how would you know whether it's "about right." [05:57] jtv: we're seeing 2.5ms per row, thats plausible with many redundant spindles [05:58] jtv: I would ask someone familiar with our db's guts :> [05:59] Regardless, it's all a question of access patterns isn't it? It could probably be 5x worse, it could probably be 500x better. [05:59] jtv: not really [06:00] jtv: if this is slower than we'd anticipate given our hardware and prior experience, then we can go digging for issues like table/index bloat, contention whatever [06:00] jtv: if this is approx as fast as we'd expect on an average day, then we're wasting time if we do that and we should instead look at how to either avoid it being cold or avoid accessing the rows [06:00] wgrant: Want to hear something awesome? [06:01] wgrant: gina.txt has been running for 20 *minutes* locally. [06:01] So that is 695 index lookups and row lookups, so pulling a minimum of 1400 blocks off the disk random access [06:01] lifeless: what I'm trying to say is that cold random reads have so many unknowns that this sample size seems inadequate. [06:01] StevenK: It normally only takes 60 seconds. I think you have a bug. [06:01] wgrant: Or I've broken it into pieces. [06:02] And since it's a doctest, I have to printf-debug [06:02] StevenK: how do you go from 695 -> 1400 blocks ? [06:02] bah [06:02] stub: ^ [06:02] stub: heuristic of 2:1 (index + table) ? [06:02] (It would be pretty nice if postgres could at least profile distribution of I/O latencies) [06:03] jtv: that would be awesome [06:03] jtv: I agree that there are many unknowns [06:03] Read a bucket in the index == pull one block from disk. Read that row from disk == pull one block from disk. [06:04] (The stupid thing is, we've been thinking of I/O costs in terms of FS cache misses, which is impossible to predict and may soon be an outdated concept anyway. If we could just look at it from an empirical standpoint, we might win something.) [06:04] its about 150 bytes per row min, so what 25 per page [06:04] on a 14M row set we're very likely to get one page per row [06:04] So 700*2 == 1400, == the minimum if we somehow magically only load up wanted information from the index [06:05] stub: kk, thats cool, i have the same basic model [06:05] stub: was just checking [06:05] Ok. I was assuming the size of the table would make it unlikely to get multiple rows in a page. [06:05] yeah, an assumption I agree with [06:05] the BPPH table is very spread over time [06:05] jtv: thats not my model of IO costs [06:06] Not "we" personally; I mean the world. [06:06] The index should be much more tightly packed too. [06:06] jtv: ah; so I don't think the world thinks of IO that way per se ;) - see for instance the scale free algorithms work [06:06] jtv: which models IO as tiers of progressively bigger and slower cache [06:07] Well okay, the database world. [06:07] And that model is still wrong, is my point. [06:07] lifeless: Oh... and the toast lookups for the md5 and sha1 hashes which are stored as text, so potentially another 4 lookups [06:07] jtv: they seem to have /very/ good results doing this - noting that they start with the CPU L1 cache and work out [06:08] stub: even if we're not looking at the columns (as my rephrased cheaper-estimate query doesn't) [06:08] Yes, but that's cache-oblivious algorithms isn't it? I'm talking database. [06:08] jtv: it is, but database will get those algorithms eventually I suspect [06:08] lifeless: I think that will avoid loading the data from the toast tables, yes. [06:08] that or mlock() the entire table [06:08] (And I'm not talking cache-oblivious database either: we can worry about that once that world finally catches on to space-filling curves) [06:09] jtv: I think its percona that have a space-filling curve index [06:09] lifeless: There have long been cache-oblivious algorithms for databases. I worked on a cache-oblivious join algorithm 2 jobs ago. [06:09] jtv: very similar to bzr's index /packing logic in fact [06:09] jtv: \o/ [06:11] stub: so what I'm trying to assess, is there some way to get this down to < 2 seconds reliably (we have to do a parallel query for the source package data) [06:11] But when it comes to buffer management and individual query optimization, my point is is we can't afford to keep thinking "what if this misses in FS cache and the seek is long" because it just gets too complex: SSD, on-disk cache, bad blocks, network latency. [06:11] stub: or should we be maintaining the archive size as a cache [06:12] stub: its currently 9 seconds with the original query, 6.6 seconds in my rephrased one [06:13] StevenK, wgrant: should the "synchronize the simple updates" button on +localpackagediffs update blacklisted differences as well? [06:13] stub: and we're seeing ~ 7 of these a day where its stuttering (presumably on IO) [06:13] jtv: No. [06:14] lifeless: So your theory is this query will often be cold due to access patterns so the lfc lookups will sometimes be too slow? I'm trying to think if we have other pages accessing more than a few dozen lfc rows. [06:15] More of the ones I can think of are dealing with bulk packaging data and are slow too [06:15] stub: bug attachments show their size in the comment, for some bizarre reason [06:15] very busy bug pages could access hundreds; *but* we will paginate those eventually [06:15] so not relevant here [06:15] lifeless: That's also the only non-packaging one I can think of. [06:16] lifeless: Bug attachments are fine as it would be rare for a page to have more than a dozen attachments. [06:16] Some have hundreds. [06:16] wgrant: I'll take your word for it. [06:16] stub: its rare, but I can link you to the bugs :> [06:16] jtv: That is sort of the point of blacklisting. [06:16] stub: I don't think its *often* cold, but I think its cold *often enough* [06:17] specifically I don't think we do this query on this dataset anywhere else except *perhaps* on package upload (for quota checking) [06:17] wgrant: well, unless maybe we have automated syncing and then users start blacklisting dsds they feel will behave correctly without human attention. [06:17] jtv: Then we've redefined the term. [06:17] wgrant: so it sort of requires a conscious decision that blacklisting means "I don't want this change" and not "I don't want to see this change." [06:17] yeah. so caching the archive size is fine for this query. I'm wondering if there is something more generic we can do to solve slowness in similar pages. The packaging tables always seem to be an issue - large, and the users want complex reports. [06:17] jtv: At the moment the Ubuntu sync blacklist blacklists the sync. [06:18] stub: so in this particular case; there is a missing index that would drop the rows examined by 6000 in the packaging table [06:18] stub: I don't know if its worth adding [06:18] I don't think it will solve the problem [06:18] jtv: It effectively means "I don't care" [06:18] wgrant: fine by me; the "I'll take your word for it" still stands but in light of yesterday's conversation I thought I'd at least ask. :) [06:19] Heh. [06:19] StevenK: ^^ [06:19] StevenK: that seems to contradict wgrant's take. [06:19] Do you agree? [06:19] Oh, he's here. [06:19] Alas, yes. [06:19] [06:19] StevenK: You mean "I don't care what the parent thinks"? [06:19] stub: ok, I'll update this bug to say 'needs cached figures' [06:19] More correctly: "I don't care about this difference" [06:20] wgrant is showing his age [06:20] "I don't care what my parents think" [06:21] "I'll hack on Launchpad even if my father thinks that Web 2.0 is wrong! I'll show them!" [06:21] StevenK: humans may not think they care, but it'd be nice to have a clear notion of whether they have a right to be disappointed if the blacklisted difference stops the package from updating. [06:21] jtv: Yes, a blacklisted difference stops a sync [06:21] Of course the button I'm building will sort of eliminate an excuse for blacklisting packages that should be updated. [06:21] * jtv updates his tests. [06:22] Keep in mind there are two blacklist states [06:22] lifeless: So that query is broken anyway, because the distinct on is lacking an order by so it isn't doing what the author thought it is doing [06:23] stub: my one ? [06:23] the one in the bug comment you linked me to [06:23] stub: my rephrased one is the one with the subselect [06:23] stub: it doesn't need the order by [06:23] Ran 1 tests with 1 failures and 0 errors in 35 minutes 35.459 seconds. [06:23] stub: look at the plan: it orders to do the distinct and unique automatically [06:23] WHEEE! [06:24] lifeless: That is fine if you happen to get the plan you are expecting. [06:25] StevenK: not too worried about the two blacklist states; I'm whitelisting, not blacklisting statuses. Are we confused yet? :) [06:25] lifeless: So we really need the order by, esp if we want this to survive future PG upgrades. [06:26] (and with luck, it will be a noop with the expected plan) [06:26] stub: I'd like to learn more about this [06:26] stub: which docs should I read? [06:26] jtv: Don't make me kick you. [06:27] lifeless: This is documented in the DISTINCT ON section of the SELECT page in the SQL Reference guide for PostgreSQL [06:27] Sorry - 'DISTINCT clause' section [06:27] file:///usr/share/doc/postgresql-doc-8.4/html/sql-select.html [06:30] stub: so, I read that [06:30] stub: and it doesn't conflict with what I've done [06:30] stub: because - 'Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first' [06:31] thats fine, we're selecting identical rows, so whichever row comes first is ok [06:31] stub: anyhow, I can add a sort in [06:32] stub: but we don't use that crafted query /anyway/ because I was seeing if we could make it fast without caching etc [06:32] lifeless: nm. You are correct I think. [06:33] I wonder if it changed, or if I'm thinking of a similar statement? I'm sure there was one that if you neglected the ORDER BY you can get duplicate results [06:36] lifeless: So in this case you can use the SQL standard "SELECT DISTINCT libraryfile, filesize" rather than SELECT DISTINCT ON (libraryfile) filesize, but the cost saving is statistically insignificant. [06:37] stub: interesting; we don't need libraryfile at all anyhow, just the summed sizes [06:37] lifeless: Should we be using rabbitmq instead of triggers to maintain BugSummary btw? [06:38] stub: it would remove all the races [06:38] stub: I have a branch for rabbitmq but ec2 threw it out [06:38] stub: I don't know why yet and haven't gotten back to it [06:38] (now that I think the DB patch is done, and just waiting on tests to confirm this hypothesis :) ) [06:39] :> [06:39] stub: thanks! [06:39] I've written up my theory in https://bugs.launchpad.net/launchpad/+bug/739070 [06:39] <_mup_> Bug #739070: Archive:+repository-size timeout retrieving many hundreds of package sizes < https://launchpad.net/bugs/739070 > [06:39] just the top of the bug summary [06:41] lifeless: I needed to change the structure a bit - we might be race free now with the simpler triggers that I thought were needed to catch some potential modifications [06:41] bug_fti || 76% || 612 MB of 798 MB [06:42] wgrant: I fixed gina! [06:42] stub: that would be cool, but the case of 'person subscribes and the bug is made public' - how will that be race free? [06:42] StevenK: Oh? [06:42] wgrant: Well, the test now takes 65 seconds, rather than 2135. [06:42] That's more normal. [06:42] What did you break? [06:43] wgrant: gina needed access to DSP [06:43] Ah. [06:43] Since she creates DSDJs [06:43] Yes. [06:43]