[00:25] ahhhhhh [00:26] select count(*) from bugnomination, distroseries where bugnomination.distroseries=distroseries.id and name='maverick'; [00:26] count [00:26] ------- [00:26] 3329 [00:26] 6K for lucid. Hmm [00:31] lifeless: What's enlightening about that? [00:32] wgrant: I had a scary moment [00:32] wgrant: I thought the +nominations page might not be batched [00:33] also we have nominations on debian sid [00:33] which is more than a little bong [00:33] lifeless: You know I found eight recipes over the weekend that reference LP branches, which is just odd [00:34] StevenK: interesting. Do you think they are intentional or confused ? [00:34] I think it's confusion [00:35] win [00:36] lifeless: https://code.launchpad.net/~launchpad-pqm/launchpad/stable/+recipes for example [00:37] LP's policy on trusting people to not do nasty things (eg. change bug details) seems to be flawed, because an awful lot of people seem to default to clicking on everything until something happens. [00:39] I would love a earnt-trust graduated facility [00:39] Create recipe on a Launchpad branch -> ISP banned forever [00:39] Haha [00:39] They keep attempting to build, too [00:43] if I may take the contrary position - if people are clicking (apparently) randomly to achieve something; that sounds like a failure to make it easy for them to figure out what they want and need; vs any issue with trust. I'd suggest further that raising barriers to doing something (trust) will only exacerbate the problem. [00:44] spm: stackexchange is an example of graduated trust [00:45] still sounds like targetting symptoms; not fixing the problem. [00:45] spm: sometimes the problem is lack of knowledge [00:46] spm: like driving a car [00:46] spm: making it easier to release the brakes and accelerate to 100kmph isn't fixing the problem [00:47] spm: I take your point, but I don't think this is an either-or situation [01:02] Project db-devel build #794: FAILURE in 3 hr 20 min: https://lpci.wedontsleep.org/job/db-devel/794/ [01:03] * StevenK blinks [01:03] That's a little ... quick [01:41] biab === Ursinha is now known as Ursinha-afk [02:18] now, 1062 / 129 BugTask:+index may be a regression [02:19] that, or something had a lock out on Bug / BugTask [02:20] hi wgrant—did I miss any further disasters? There were some distressing messages from the script after we fixed the permissions issues, but it looks like most of them had been coming out of the old script since 2006. :) === lifeless changed the topic of #launchpad-dev to: Performance Tuesday | https://dev.launchpad.net/ | On call reviewer: - | Critical bugs: 235 - 0:[#######=]:256 [02:22] jtv: it was last night you deployed the new script right ? [02:22] yup [02:22] jtv: does it write to the DB ? [02:22] Yes [02:22] mmm [02:23] (Note that the only thing "deployed" in the technical sense was a cron change) [02:23] Trouble at the mill? [02:23] (Please say no) [02:23] does it alter bug or bugtask at all ? [02:23] https://bugs.launchpad.net/launchpad/+bug/823028 [02:23] <_mup_> Bug #823028: sudden contention on Bug/BugTask tables < https://launchpad.net/bugs/823028 > [02:23] Can do, yes. [02:23] jtv: yes trouble, 2000 timeouts in todays oops report [02:24] all on bug/bugtask selects [02:24] And it's not even doing anything the old script wasn't doing. :( [02:24] it may not be it [02:24] Well one thing it does there is close bugs. [02:24] I'm simply starting to round up 'things thare are different' [02:25] it may not be contention, I've retitled to remove that assumption [02:25] If transactions have become longer (which frankly I don't expect, but who knows) and the bugtask selects involve status checks… [02:25] we've either tipped over a index bloat threshold causing a plan change to poor plans, changed a query in a poor way, or run into contention [02:26] I think thats about the size of the option-set [02:26] Excuse me while I delete more bug spam that hit me _right after_ deleting the overnight batch, and then update my spam filters and _then_ go back to debugging akismet. :( [02:26] Nothing significant _should_ have changed w.r.t. these queries, but anything _could_. [02:29] 2000 yesterday, 1138 the day before, 1188 the day before, 998 the day before [02:30] ok, so its a big jump, but not as big as I thought [02:31] Back. [02:32] Still, good thing you're paying attention to it. I'm just reading up on the bug. [02:33] Count queries are nasty: lock-sensitive. [02:33] oh? [02:33] I didn't realise they were more lock sensitive than other selects [02:34] Well it's more that looking at them, it's so natural to expect them to be less lock-sensitive. [02:34] they'll need to touch every page [02:34] so if there are lots of writes I'd expect contention on the page-access-lock [02:34] That too, though thankfully postgres does no page locking. :) [02:34] but they should be reading their mvcc-version of the pages [02:34] Oh, there's a page access lock? Didn't know that. [02:35] http://www.postgresql.org/docs/current/static/explicit-locking.html 13.3.2 [02:35] 'In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks, but they are mentioned here for completeness.' [02:35] FFS SSO you know what oops I want, don't give me that stupid search page. [02:36] Well that doesn't sound like there can be any contention for them as such. [02:37] All that trouble loading up an oops page and then it renders the referrer string all across the oops text. How depressing. [02:37] win [02:37] * jtv starts up another browser to do the same dance with [02:38] lifeless: The new script only appeared 11 hours into yesterday. [02:38] When did the OOPSes start? [02:38] * wgrant checks appserver graphs. [02:38] it looks like midnight precisely [02:39] and then 1200 [02:39] Ah, SSO login page, confirm a few certificates, and the unwanted search page again. [02:39] Progress. [02:40] lifeless: Note that it only runs for ~25 minutes of each hour. [02:40] Starting at 3 minutes past the hour. [02:40] The second half of each hour should have no publish-ftpmaster. [02:40] sure [02:40] the graph I'm looking at is hour granularity [02:40] https://lpstats.canonical.com/graphs/AppServer5xxsLpnetNoRobot/20110803/20110810/ is extremely troubling. [02:40] we'd need one that was 15m granularity to rule-out the publisher [02:40] 2/5 through 2011-08-07, things started going bad. [02:41] Most of these seem to have happened at 22 past the hour. [02:41] Although a lot of the oops pages are giving me "500" errors. [02:41] Oh, here's one at 5:41. So that's probably not our doing. [02:41] jtv: heh, poor liitle oops service [02:41] and one at Aug. 8, 2011, 12:42 p.m. [02:41] It's oopsing. [02:42] Definitely a big spike at 6:22 though. [02:42] lifeless: https://lpstats.canonical.com/graphs/AppServer5xxsLpnetNoRobot/20110809/20110810/ has the sort of granularity you are looking for. [02:42] wgrant: I don't suppose it's simply BPPH scans pushing bugtasks out of a cache? [02:42] Not very likely. [02:43] How come? [02:43] That hasn't changed. [02:44] Well something's changed, and we're exploring the hypothesis that it may be something in this script. [02:44] jtv: bugtask is spectacularly hot, the linux vmcache would keep it in [02:47] jtv: Have you looked at https://lpstats.canonical.com/graphs/AppServer5xxsLpnetNoRobot/20110803/20110810/? [02:47] Look at the second half of the 7th. [02:47] Something started going wrong then. [02:47] So not me then? [02:47] I think the graph is in BST [02:47] or vice versa [02:48] because it has a growth at 0722 [02:48] BST is in the graph? [02:48] devpad is still BST, but ewww. [02:49] Argh. [02:49] I wish we could just get a graph of BugTask:+index timeouts. [02:49] Let's see... [02:49] log into the oopsdb [02:50] Mortals cannot. [02:50] We are restricted to obscene pipelines. [02:50] meh, remind me tomorrow and I will rt access up for you [02:50] we have no reason not to do that for anyone in the team that shows an interest [02:50] Maybe. [02:56] wgrant: something else… you said the new script didn't "ls -lR" the archive. Are you quite sure? [02:57] jtv: No, it's there. I just expected it to be in a run-parts. [02:58] Ah. No, IIRC it wasn't in quite the right place to join either of the existing run-parts dirs. [02:58] Right, I recqall that discussion. [03:00] BTW lifeless, I notice that we still have tons of fields on bugtask that look highly mutable… do we still update those directly on the bugtask or have you changed how that works? [03:00] Heat in particular. [03:05] jtv: no, we still have liveness headaches there [03:05] its something we need to address [03:06] And let's be honest: I just want to see if there's something to the normal forms past Boyce-Codd after all. ☺ [03:06] :P [03:06] In Translations I think it could make a real difference as well, not copying translationmessages around all the time just because some status flags move around. [03:08] jtv: you mean having a separate table ? [03:08] Yes. [03:08] Would save a lot of vacuuming, I suspect. [03:09] perhaps; plus index rewrites too [03:09] Yup. [03:09] Pretty much all the moving parts involved in updating rows. [03:10] OTOH we can solve contention by queuing updates to non-latency-sensitive fields [03:10] This is latency-sensitive. [03:11] Also, we have a bunch of partial indexes covering those flags. [03:11] So updating one flag also searches for and updates another TM that currently holds the flag, and each of them gets taken out of one partial index and into another. [03:11] yah [03:12] would want to avoid hitting larger row counts of both constant-and-mutable-tables [03:12] lifeless: Like the query of death yesterday ... [03:13] QoD? [03:13] jtv: http://paste.ubuntu.com/660924/ and related [03:14] Oh that one [03:14] jtv: I got it down from 25 minutes to 6 seconds [03:14] Nothing particularly mutable in there, is there? [03:14] jtv: but not reliably [03:14] Ah, BPPH [03:14] Oh [03:14] jtv: no, and there are bad stats involved [03:15] jtv: the bpn->bpr relation estimates are out by a factor of 10 for %linux% [03:15] Not a dramatic difference, really, compared to what can happen. [03:15] You underestimate just how many binary packages the linux source package builds :-) [03:15] Don't "LIKE" matches use some completely arbitrary guess? [03:16] One of those cases where a table scan is probably best... [03:17] lifeless: did you lift the BPN search out of the join and materialize it? [03:17] jtv: yes [03:17] Good chap. [03:17] jtv: I tried a subselect, subselect with offset 0, CTE and temp table [03:18] CTE probably does the same as temp table, perhaps with a bit less overhead? [03:18] jtv: all the gory details are in the -ops backlog [03:18] jtv: CTE performed much work - different plan [03:18] bah [03:18] temp table was < CTE was < others [03:18] Oh, the optimizer can transform through CTEs already? [03:18] yeah [03:19] even in 8.4 [03:19] the temp table meant it was planning on more accurate stats [03:19] So not reliable as an optimization barrier then. Does "offset 0" still work for that? [03:19] not sure [03:21] Anyway, I hope that we can use separation of model and storage layers as a way to free the hottest parts of our schema from the OO-style bundling of static information with mutable status. [03:23] Or look at an old pet peeve of mine: POTemplate. Quite costly to retrieve, queried all over the place for lots of reasons, and the main performance suspect is a Unicode header column that we only ever need in 2 places. [03:24] In fact maybe we should see these performance knells as a trigger for building that separate storage layer. [03:24] Is the expense loading them, or in row width? [03:24] If the former, we have a solution already. [03:25] If the latter, can we force them to TOAST or something? [03:27] I'm not sure. I've never had the time to investigate it. IIRC I suspected a bit of both; certainly row width could be much much less and this is a pretty hot table. [03:27] What is the solution we have for the former? I've been angling for one for ages. [03:28] jtv: what do you mean (for clarity) by separate storage layer ? [03:28] One thing I had in mind was "demand-loaded properties" in Storm. [03:28] jtv: See SPR.copyright. [03:28] lifeless: what wallyworld brought up at the time — DAO [03:28] It's a manual implementation of on-demand column loading. [03:29] It's a bit ugly, but was a huge performance improvement. [03:29] wgrant: ah, I started doing that exact same thing with POTemplate at one point, but time pressure didn't allow me to get very far with it. [03:30] (I think TranslationMessage also has a copyright field, but it's unused) [03:30] I think lifting that sort of thing out of the table into a "rarely needed static background information" table could be even better because it also improves locality of search queries etc. [03:31] At the cost of the few places where you need the data, of course, but we have load_related/load_referencing now. [03:35] jtv: SPR.copyright is often dozens of lines long. [03:35] Not sure POT headers are that large. [03:35] SPR is another one of those tables that on the one hand we use all the time as a waystation in joins and the subject of searches, but on the other hand it holds lots of detailed information. And I suspect it'd break down quite neatly into a lean, hot dude and a cool, fat bloke. [03:35] Dozens of lines? You kids today have it easy. [03:35] Most of the time all you need to know about the SPR is the ID. [03:35] You can normally avoid joining through it at all. [03:35] Go directly from SPPH to BPB, for example. [03:36] I don't notice those cases much, because I try not to join with tables in that way in the first place. :) But think of the cases where you need just id and spn. [03:36] In fact I was discussing this with Simon Riggs a few weeks back. [03:37] Ah, true, often need SPN. [03:37] But we're going to denorm that onto SPPH shortly. [03:37] Nice. [03:38] Funny how denormalizing like that is no longer a dirty word. [03:40] wgrant: while we're here… one problem we keep running into is "find the latest SPPH (or BPPH, I suppose) for a given package in a given distroseries." Would a separate cache for those buy us anything? [03:41] jtv: Denorming SPN and BPN onto SPPH and BPPH makes that pretty much free. [03:41] As we can have an (archive, distroseries, sourcepackagename, status) index. [03:41] Possibly even a partial index. [03:41] But no index-only scans. [03:42] Unless we just do (archive, distroseries, sourcepackagename, status) WHERE status in (1, 2) [03:46] But no index-only scans. [03:46] Why not? [03:46] Because postgres doesn't do index-only scans. [03:47] jtv: I think having an explicit mapping layer would be interesting; I also think that we'll get more bang for buck by the SOA project [03:47] Oh, of course. [03:49] still, having a small number of rows to actually consider is a good thing [03:50] lifeless: I suspect they're just different ways of looking at what in this specific case would be very much the same thing: properly isolate responsibility for querying and retrieving these objects, then use the elbow room afforded by that isolation to optimize storage for use. I wasn't thinking so much of a very formal layer as of a gradual extension of our development patterns to suit that optimization. [03:51] jtv: sure [03:52] I would, for risk management and cycle time, do either one thing : optimise storage or change the way we query [03:55] wgrant, lifeless: I fail to see how denormalising SPN and BPN helps in this case. I need to reach for the SPR and then the BPR via the BPB anyway? [03:55] StevenK: You can get the SPR ID from SPPH, then join directly to BPB. [03:56] No need to join across SPR; all you need is the ID and name. [03:56] Both of which can be on SPPH. [03:56] SPR is pretty boring otherwise. [03:56] A few things need the version, but that is about it. [03:56] And by the time you want the version, you're probably already fairly selective. [03:56] So it's OK to venture into SPR. [03:57] As most queries are based on SPPH.status, rather than sorting by version. [03:57] the big thing for me is that these tables have live and historic data [03:57] I'd really like to see that partitioned [03:57] Really partial indices and clustering should solve everything, but I guess postgres isn't quite there. [03:59] well [03:59] I have to disagree there [04:00] It probably also relies on either better stats or customised plans. [04:01] after 5 years, 10 releases, 3 current - 30% of thedata *at most* is live, 70% dead. [04:02] And? [04:02] 10 years, 20 releases, 3 current - 15% live [04:02] etc [04:02] the stats will degrade linearly [04:02] There's no reason that has to be bad. [04:02] It's only bad because we are relying on random stats. [04:02] If we could dictate parts of the plan, it would be easy. [04:02] *If* we can make sure that everything we do has good locality, so that the "dead" data can sit on disk undisturbed. [04:03] Right, we'd need clustering too. [04:03] Not necessarily, but we'd need good locality. [04:03] All the partitioning does is adds barriers because postgres tries to be too smart. [04:03] this is a case for temporal normal form basically - but not a date based partition, its a status based partition [04:03] 4th?5th?6th? I forget the label. [04:34] I wonder if SPRs exist without SPPHs [04:34] -D cronscripts/publishing/cron.publish-ftpmaster [04:34] Yay. [04:34] StevenK: Yes. [04:34] StevenK: eg. stuff that was uploaded and then rejected. [04:34] And stuff that was the victim of DB mangling grrr. [04:34] I'm just wondering if I work on the SPN/BPN denormalisation if it make my query not suck [04:35] Do you have a paste of the latest version? [04:35] Preferably with an explain analyze. [04:35] I can grab you one from staging [04:35] It's going to be faster than dogfood [04:36] I'd prefer no temp tables, since this query will be used by the evilness that is pickers [04:37] Now, the plan was to denorm the string name onto ?PPH. [04:37] http://paste.ubuntu.com/661655/ [04:37] Which probably means you'd need to determine the set of candidate strings from BPN, then look them up on BPPH. [04:37] As otherwise you have a seq scan on BPPH, unless we have awesome trigram indices. [04:37] bah, thats xringd [04:37] I'll run linux now [04:37] But the whole point of the SPN was so stuff isn't duplicated? [04:38] StevenK: Is that a concern? [04:38] It might be [04:38] Lack of duplication without rationale is hardly something to fight to retain. [04:38] so a %LIKE% on SPN is fast [04:38] Sure, because the table is tiny. [04:38] it is unlikely to be fast if the strings themselves are denormed into ?PPH [04:38] wgrant: I daresay space saving is 'sans rationale' [04:39] but if you put the FK onto ?PPH that should be fine [04:39] And worse than just not fast, it's impossible to estimate. [04:39] With "I'm looking for these BPNs" at least the statistics have a chance. [04:39] lifeless: Not so long ago you were arguing thatn BPN and SPN should be abolished. [04:39] lifeless: What about a %LIKE% across BPN? [04:39] If you are OK with keeping FKs there, that's good. [04:39] wgrant: yes, with trigrams or fti they should [04:40] wgrant: these are separate discussions [04:40] lifeless: This denorm is reasonably expensive to implement. [04:40] We probably want some idea of where we are going to go. [04:40] wgrant: how is it expensive? [04:40] Mm, I guess it's not so bad if we have fastdowntime soon. [04:41] fti across SPN simultaneously makes me happy since we can rank matches, and scared since fti is a pox [04:41] Project devel build #958: FAILURE in 5 hr 40 min: https://lpci.wedontsleep.org/job/devel/958/ [04:43] FTI is a hard problem. But does it make any sense at all for package names!? [04:45] Sure, why not? [04:45] no stemming rules for packages [04:46] and no substrings in the tsearch2 fti implementation [04:50] It would be nice if we could cheaply set up a copy of a few tables from the DB to test stuff on. [04:50] I was thinking that [04:50] We can test on [qa]staging [04:51] e.g. with temp tables in a transaction [04:51] or even on the main tables if its compatible [04:52] http://paste.ubuntu.com/661660/ [04:52] StevenK: ^ wgrant ^ [04:54] lifeless: What if you try a temp table of BPPHs with matching BPNs? [04:55] boostrapping that now [04:55] select * into temporary table test_bpph from binarypackagepublishinghistory; [04:56] Oh, I was thinking you could just get a temp table of BPPH IDs that match the BPN query. [04:56] Rather than duplicating a 15M row table. [04:56] * lifeless shrugs [04:57] may as well understand the row width impact [04:57] I was hoping to cheaply and roughly emulate the BPPH.name index. [04:57] EODish, I'll be doin ghouse things for a bit etc, will pop back to do the next step [04:57] k, thanks. [04:57] heh thats done [04:57] now to add the column [04:58] I guess sourcherry is a bit of a monster, even if it isn't wildcherry. [04:58] Haha [04:58] Particularly since it's a small table. [04:58] s/small/narrow/ [05:00] update test_bpph set name=binarypackagerelease.binarypackagename from binarypackagerelease where test_bpph.binarypackagerelease=binarypackagerelease.id; [05:00] running now [05:00] (and yes, I know I could have done this as one step [05:00] but this has a smaller footprint - I'm betting faster overall [05:03] In case you've not done it already, I think http://paste.ubuntu.com/661666/ is about as good as it gets. [05:03] ie. not very, but we'll see. [05:03] As long as it goes the right way we should be OK. [05:04] wgrant: Didn't you want to skip over SPR entirely? [05:05] StevenK: Can't here. [05:05] Since SPPH isn't involved in the second query. [05:05] Pity [05:05] We have to go through one of them. [05:06] wgrant: Where did 'JOIN _1 AS' come from? [05:06] s/_1 AS // [05:06] miscopied from lifeless' [05:07] Sigh, I can't run it anyway [05:10] http://paste.ubuntu.com/661675/ has the added bonus of working and being tested. [05:10] Oh mawson? [05:11] s/h/n/ [05:11] Tempted to do the test_bpph shuffle there too [05:11] On dev. [05:12] I'm not hugely tempted to rewrite 18M rows on mawson, but maybe. [05:12] lifeless: I wonder if that UPDATE is done yet. [05:16] jtv: QA! [05:21] update test_bpph set name=binarypackagerelease.binarypackagename from binarypackagerelease where test_bpph.binarypackagerelease=binarypackagerelease.id; [05:21] UPDATE 15886099 [05:21] Time: 884894.389 ms [05:22] there is another approach to ths btw [05:22] a dedicated query schema for the use case we are solving [05:22] e.g. a fact table with spn bpn, archive [05:22] I suspect that that would fly insanely fast and be tiny [05:23] even if populated for the world [05:23] will come back to that [05:24] we will probably want some indices [05:24] Might need distribution too, but that can probably be deduced from archive [05:25] for your specific case we don't, but yes, we might want the schema to have it [05:29] Seq Scan on test_bpph binarypackagepublishinghistory (cost=0.00..882508.82 rows=1272 width=12) [05:29] so, I'll add a partial index on status, archive [05:29] Not name too? [05:30] sure [05:32] create index test_bpph_archive_status on test_bpph (archive, name) where status in (1,2); [05:32] this may take a little time [05:32] Probably also want DAS, but we'll see. [05:33] 30 seconds [05:33] estimates 4000 cost now; we'll see [05:37] wgrant: so, the reason we expec this to be better is that the active-filter is status,archive on bpph ? [05:37] StevenK: are you trying to tell me in your very personal way that one of my branches has been touched by the qa tagger? [05:37] lifeless: Were I the planner, I would find candidate BPNs, then look up BPPHs by (archive, name, status) [05:37] jtv: I'm trying to emulate wgrant's nagging [05:38] I could tell the difference easily. [05:38] crap stats [05:38] -> Bitmap Index Scan on test_bpph_archive_status (cost=0.00..1250.23 rows=792 width=0) [05:38] Index Cond: (archive = 1) [05:38] analyzing [05:39] ok, now we get a sensible cost out - 900K [05:39] its doing a hash join [05:39] Rarely good. [05:39] forcing that off, lets see [05:39] instant [05:40] Hm? [05:40] 500ms [05:40] Nice [05:40] thats with hash joins forced off [05:40] I think we may have bong tuning parameters somewhere [05:41] 12 seconds with it off, run 1 [05:41] 11 seconds for run 2 [05:41] Or possibly I'm better at devising plans than postgres, so it should listen :) [05:41] wgrant: we all are when we use domain knowledge [05:41] Exactly. [05:41] But we can't tell it to listen. [05:41] the trick is to figure out why pg thinks the nested loop is going to be so expensive [05:42] the %linux% may be a cause [05:42] Well that'd do it. [05:42] it can eliminate all names shorter than linux [05:42] As I said, there's no way to get conservative statistics on selectivity for a LIKE. [05:42] yeah :) [05:43] lifeless: What if you take the BPN query out into a CTE and hope it doesn't optimise through it? [05:43] won't work [05:43] temp table might, I'm just trying that now [05:43] Pulling the BPN ids into the client may help. [05:43] jtv: this is roughly the same as a temp table [05:44] jtv: though the temp table is better [05:44]