lifeless select count(*) from bugnomination, distroseries where bugnomination.distroseries=distroseries.id and name='maverick';00:26
lifeless count00:26
lifeless  332900:26
lifeless6K for lucid. Hmm00:26
wgrantlifeless: What's enlightening about that?00:31
lifelesswgrant: I had a scary moment00:32
lifelesswgrant: I thought the +nominations page might not be batched00:32
lifelessalso we have nominations on debian sid00:33
lifelesswhich is more than a little bong00:33
StevenKlifeless: You know I found eight recipes over the weekend that reference LP branches, which is just odd00:33
lifelessStevenK: interesting. Do you think they are intentional or confused ?00:34
StevenKI think it's confusion00:34
StevenKlifeless: https://code.launchpad.net/~launchpad-pqm/launchpad/stable/+recipes for example00:36
wgrantLP'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:37
lifelessI would love a earnt-trust graduated facility00:39
wgrantCreate recipe on a Launchpad branch -> ISP banned forever00:39
StevenKThey keep attempting to build, too00:39
spmif 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:43
lifelessspm: stackexchange is an example of graduated trust00:44
spmstill sounds like targetting symptoms; not fixing the problem.00:45
lifelessspm: sometimes the problem is lack of knowledge00:45
lifelessspm: like driving a car00:46
lifelessspm: making it easier to release the brakes and accelerate to 100kmph isn't fixing the problem00:46
lifelessspm: I take your point, but I don't think this is an either-or situation00:47
LPCIBotProject db-devel build #794: FAILURE in 3 hr 20 min: https://lpci.wedontsleep.org/job/db-devel/794/01:02
* StevenK blinks01:03
StevenKThat's a little ... quick01:03
=== Ursinha is now known as Ursinha-afk
lifelessnow, 1062 /  129  BugTask:+index may be a regression02:18
lifelessthat, or something had a lock out on Bug / BugTask02:19
jtvhi 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.  :)02:20
=== lifeless changed the topic of #launchpad-dev to: Performance Tuesday | https://dev.launchpad.net/ | On call reviewer: - | Critical bugs: 235 - 0:[#######=]:256
lifelessjtv: it was last night you deployed the new script right ?02:22
lifelessjtv: does it write to the DB ?02:22
jtv(Note that the only thing "deployed" in the technical sense was a cron change)02:23
jtvTrouble at the mill?02:23
jtv(Please say no)02:23
lifelessdoes it alter bug or bugtask at all ?02:23
_mup_Bug #823028: sudden contention on Bug/BugTask tables <timeout> <Launchpad itself:Triaged> < https://launchpad.net/bugs/823028 >02:23
jtvCan do, yes.02:23
lifelessjtv: yes trouble, 2000 timeouts in todays oops report02:23
lifelessall on bug/bugtask selects02:24
jtvAnd it's not even doing anything the old script wasn't doing.  :(02:24
lifelessit may not be it02:24
jtvWell one thing it does there is close bugs.02:24
lifelessI'm simply starting to round up 'things thare are different'02:24
lifelessit may not be contention, I've retitled to remove that assumption02:25
jtvIf transactions have become longer (which frankly I don't expect, but who knows) and the bugtask selects involve status checks…02:25
lifelesswe'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 contention02:25
lifelessI think thats about the size of the option-set02:26
jtvExcuse 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
jtvNothing significant _should_ have changed w.r.t. these queries, but anything _could_.02:26
lifeless2000 yesterday, 1138 the day before, 1188 the day before, 998 the day before02:29
lifelessok, so its a big jump, but not as big as I thought02:30
jtvStill, good thing you're paying attention to it.  I'm just reading up on the bug.02:32
jtvCount queries are nasty: lock-sensitive.02:33
lifelessI didn't realise they were more lock sensitive than other selects02:33
jtvWell it's more that looking at them, it's so natural to expect them to be less lock-sensitive.02:34
lifelessthey'll need to touch every page02:34
lifelessso if there are lots of writes I'd expect contention on the page-access-lock02:34
jtvThat too, though thankfully postgres does no page locking.  :)02:34
lifelessbut they should be reading their mvcc-version of the pages02:34
jtvOh, there's a page access lock?  Didn't know that.02:34
lifelesshttp://www.postgresql.org/docs/current/static/explicit-locking.html 13.3.202:35
lifeless'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
jtvFFS SSO you know what oops I want, don't give me that stupid search page.02:35
jtvWell that doesn't sound like there can be any contention for them as such.02:36
jtvAll that trouble loading up an oops page and then it renders the referrer string all across the oops text.  How depressing.02:37
* jtv starts up another browser to do the same dance with02:37
wgrantlifeless: The new script only appeared 11 hours into yesterday.02:38
wgrantWhen did the OOPSes start?02:38
* wgrant checks appserver graphs.02:38
lifelessit looks like midnight precisely02:38
lifelessand then 120002:39
jtvAh, SSO login page, confirm a few certificates, and the unwanted search page again.02:39
wgrantlifeless: Note that it only runs for ~25 minutes of each hour.02:40
jtvStarting at 3 minutes past the hour.02:40
wgrantThe second half of each hour should have no publish-ftpmaster.02:40
lifelessthe graph I'm looking at is hour granularity02:40
wgranthttps://lpstats.canonical.com/graphs/AppServer5xxsLpnetNoRobot/20110803/20110810/ is extremely troubling.02:40
lifelesswe'd need one that was 15m granularity to rule-out the publisher02:40
wgrant2/5 through 2011-08-07, things started going bad.02:40
jtvMost of these seem to have happened at 22 past the hour.02:41
jtvAlthough a lot of the oops pages are giving me "500" errors.02:41
jtvOh, here's one at 5:41.  So that's probably not our doing.02:41
lifelessjtv: heh, poor liitle oops service02:41
lifelessand one at Aug. 8, 2011, 12:42 p.m.02:41
jtvIt's oopsing.02:41
jtvDefinitely a big spike at 6:22 though.02:42
wgrantlifeless: https://lpstats.canonical.com/graphs/AppServer5xxsLpnetNoRobot/20110809/20110810/ has the sort of granularity you are looking for.02:42
jtvwgrant: I don't suppose it's simply BPPH scans pushing bugtasks out of a cache?02:42
wgrantNot very likely.02:42
jtvHow come?02:43
wgrantThat hasn't changed.02:43
jtvWell something's changed, and we're exploring the hypothesis that it may be something in this script.02:44
lifelessjtv: bugtask is spectacularly hot, the linux vmcache would keep it in02:44
wgrantjtv: Have you looked at https://lpstats.canonical.com/graphs/AppServer5xxsLpnetNoRobot/20110803/20110810/?02:47
wgrantLook at the second half of the 7th.02:47
wgrantSomething started going wrong then.02:47
jtvSo not me then?02:47
lifelessI think the graph is in BST02:47
lifelessor vice versa02:47
lifelessbecause it has a growth at 072202:48
wgrantBST is in the graph?02:48
wgrantdevpad is still BST, but ewww.02:48
wgrantI wish we could just get a graph of BugTask:+index timeouts.02:49
wgrantLet's see...02:49
lifelesslog into the oopsdb02:49
wgrantMortals cannot.02:50
wgrantWe are restricted to obscene pipelines.02:50
lifelessmeh, remind me tomorrow and I will rt access up for you02:50
lifelesswe have no reason not to do that for anyone in the team that shows an interest02:50
jtvwgrant: something else… you said the new script didn't "ls -lR" the archive.  Are you quite sure?02:56
wgrantjtv: No, it's there. I just expected it to be in a run-parts.02:57
jtvAh.  No, IIRC it wasn't in quite the right place to join either of the existing run-parts dirs.02:58
wgrantRight, I recqall that discussion.02:58
jtvBTW 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
jtvHeat in particular.03:00
lifelessjtv: no, we still have liveness headaches there03:05
lifelessits something we need to address03:05
jtvAnd let's be honest: I just want to see if there's something to the normal forms past Boyce-Codd after all.  ☺03:06
jtvIn 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:06
lifelessjtv: you mean having a separate table ?03:08
jtvWould save a lot of vacuuming, I suspect.03:08
lifelessperhaps; plus index rewrites too03:09
jtvPretty much all the moving parts involved in updating rows.03:09
lifelessOTOH we can solve contention by queuing updates to non-latency-sensitive fields03:10
jtvThis is latency-sensitive.03:10
jtvAlso, we have a bunch of partial indexes covering those flags.03:11
jtvSo 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
lifelesswould want to avoid hitting larger row counts of both constant-and-mutable-tables03:12
StevenKlifeless: Like the query of death yesterday ...03:12
StevenKjtv: http://paste.ubuntu.com/660924/ and related03:13
jtvOh that one03:14
lifelessjtv: I got it down from 25 minutes to 6 seconds03:14
jtvNothing particularly mutable in there, is there?03:14
lifelessjtv: but not reliably03:14
jtvAh, BPPH03:14
lifelessjtv: no, and there are bad stats involved03:14
lifelessjtv: the bpn->bpr relation estimates are out by a factor of 10 for %linux%03:15
jtvNot a dramatic difference, really, compared to what can happen.03:15
StevenKYou underestimate just how many binary packages the linux source package builds :-)03:15
jtvDon't "LIKE" matches use some completely arbitrary guess?03:15
jtvOne of those cases where a table scan is probably best...03:16
jtvlifeless: did you lift the BPN search out of the join and materialize it?03:17
lifelessjtv: yes03:17
jtvGood chap.03:17
lifelessjtv: I tried a subselect, subselect with offset 0, CTE and temp table03:17
jtvCTE probably does the same as temp table, perhaps with a bit less overhead?03:18
lifelessjtv: all the gory details are in the -ops backlog03:18
lifelessjtv: CTE performed much work - different plan03:18
lifelesstemp table was < CTE was < others03:18
jtvOh, the optimizer can transform through CTEs already?03:18
lifelesseven in 8.403:19
lifelessthe temp table meant it was planning on more accurate stats03:19
jtvSo not reliable as an optimization barrier then.  Does "offset 0" still work for that?03:19
lifelessnot sure03:19
jtvAnyway, 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:21
jtvOr 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:23
jtvIn fact maybe we should see these performance knells as a trigger for building that separate storage layer.03:24
wgrantIs the expense loading them, or in row width?03:24
wgrantIf the former, we have a solution already.03:24
wgrantIf the latter, can we force them to TOAST or something?03:25
jtvI'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
jtvWhat is the solution we have for the former?  I've been angling for one for ages.03:27
lifelessjtv: what do you mean (for clarity) by separate storage layer ?03:28
jtvOne thing I had in mind was "demand-loaded properties" in Storm.03:28
wgrantjtv: See SPR.copyright.03:28
jtvlifeless: what wallyworld brought up at the time — DAO03:28
wgrantIt's a manual implementation of on-demand column loading.03:28
wgrantIt's a bit ugly, but was a huge performance improvement.03:29
jtvwgrant: 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:29
jtv(I think TranslationMessage also has a copyright field, but it's unused)03:30
jtvI 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:30
jtvAt the cost of the few places where you need the data, of course, but we have load_related/load_referencing now.03:31
wgrantjtv: SPR.copyright is often dozens of lines long.03:35
wgrantNot sure POT headers are that large.03:35
jtvSPR 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
jtvDozens of lines?  You kids today have it easy.03:35
wgrantMost of the time all you need to know about the SPR is the ID.03:35
wgrantYou can normally avoid joining through it at all.03:35
wgrantGo directly from SPPH to BPB, for example.03:35
jtvI 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
jtvIn fact I was discussing this with Simon Riggs a few weeks back.03:36
wgrantAh, true, often need SPN.03:37
wgrantBut we're going to denorm that onto SPPH shortly.03:37
jtvFunny how denormalizing like that is no longer a dirty word.03:38
jtvwgrant: 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:40
wgrantjtv: Denorming SPN and BPN onto SPPH and BPPH makes that pretty much free.03:41
wgrantAs we can have an (archive, distroseries, sourcepackagename, status) index.03:41
wgrantPossibly even a partial index.03:41
jtvBut no index-only scans.03:41
wgrantUnless we just do (archive, distroseries, sourcepackagename, status) WHERE status in (1, 2)03:42
jtvBut no index-only scans.03:46
wgrantWhy not?03:46
jtvBecause postgres doesn't do index-only scans.03:46
lifelessjtv: I think having an explicit mapping layer would be interesting; I also think that we'll get more bang for buck by the SOA project03:47
wgrantOh, of course.03:47
lifelessstill, having a small number of rows to actually consider is a good thing03:49
jtvlifeless: 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:50
lifelessjtv: sure03:51
lifelessI would, for risk management and cycle time, do either one thing : optimise storage or change the way we query03:52
StevenKwgrant, 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
wgrantStevenK: You can get the SPR ID from SPPH, then join directly to BPB.03:55
wgrantNo need to join across SPR; all you need is the ID and name.03:56
wgrantBoth of which can be on SPPH.03:56
wgrantSPR is pretty boring otherwise.03:56
wgrantA few things need the version, but that is about it.03:56
wgrantAnd by the time you want the version, you're probably already fairly selective.03:56
wgrantSo it's OK to venture into SPR.03:56
wgrantAs most queries are based on SPPH.status, rather than sorting by version.03:57
lifelessthe big thing for me is that these tables have live and historic data03:57
lifelessI'd really like to see that partitioned03:57
wgrantReally partial indices and clustering should solve everything, but I guess postgres isn't quite there.03:57
lifelessI have to disagree there03:59
wgrantIt probably also relies on either better stats or customised plans.04:00
lifelessafter 5 years, 10 releases, 3 current - 30% of thedata *at most* is live, 70% dead.04:01
lifeless10 years, 20 releases, 3 current - 15% live04:02
lifelessthe stats will degrade linearly04:02
wgrantThere's no reason that has to be bad.04:02
wgrantIt's only bad because we are relying on random stats.04:02
wgrantIf we could dictate parts of the plan, it would be easy.04:02
jtv*If* we can make sure that everything we do has good locality, so that the "dead" data can sit on disk undisturbed.04:02
wgrantRight, we'd need clustering too.04:03
jtvNot necessarily, but we'd need good locality.04:03
wgrantAll the partitioning does is adds barriers because postgres tries to be too smart.04:03
lifelessthis is a case for temporal normal form basically - but not a date based partition, its a status based partition04:03
lifeless4th?5th?6th? I forget the label.04:03
StevenKI wonder if SPRs exist without SPPHs04:34
wgrant-D  cronscripts/publishing/cron.publish-ftpmaster04:34
wgrantStevenK: Yes.04:34
wgrantStevenK: eg. stuff that was uploaded and then rejected.04:34
wgrantAnd stuff that was the victim of DB mangling grrr.04:34
StevenKI'm just wondering if I work on the SPN/BPN denormalisation if it make my query not suck04:34
wgrantDo you have a paste of the latest version?04:35
wgrantPreferably with an explain analyze.04:35
lifelessI can grab you one from staging04:35
StevenKIt's going to be faster than dogfood04:35
StevenKI'd prefer no temp tables, since this query will be used by the evilness that is pickers04:36
wgrantNow, the plan was to denorm the string name onto ?PPH.04:37
wgrantWhich probably means you'd need to determine the set of candidate strings from BPN, then look them up on BPPH.04:37
wgrantAs otherwise you have a seq scan on BPPH, unless we have awesome trigram indices.04:37
lifelessbah, thats xringd04:37
lifelessI'll run linux now04:37
StevenKBut the whole point of the SPN was so stuff isn't duplicated?04:37
wgrantStevenK: Is that a concern?04:38
StevenKIt might be04:38
wgrantLack of duplication without rationale is hardly something to fight to retain.04:38
lifelessso a %LIKE% on SPN is fast04:38
wgrantSure, because the table is tiny.04:38
lifelessit is unlikely to be fast if the strings themselves are denormed into ?PPH04:38
StevenKwgrant: I daresay space saving is 'sans rationale'04:38
lifelessbut if you put the FK onto ?PPH that should be fine04:39
jtvAnd worse than just not fast, it's impossible to estimate.04:39
jtvWith "I'm looking for these BPNs" at least the statistics have a chance.04:39
wgrantlifeless: Not so long ago you were arguing thatn BPN and SPN should be abolished.04:39
StevenKlifeless: What about a %LIKE% across BPN?04:39
wgrantIf you are OK with keeping FKs there, that's good.04:39
lifelesswgrant: yes, with trigrams or fti they should04:39
lifelesswgrant: these are separate discussions04:40
wgrantlifeless: This denorm is reasonably expensive to implement.04:40
wgrantWe probably want some idea of where we are going to go.04:40
lifelesswgrant: how is it expensive?04:40
wgrantMm, I guess it's not so bad if we have fastdowntime soon.04:40
StevenKfti across SPN simultaneously makes me happy since we can rank matches, and scared since fti is a pox04:41
LPCIBotProject devel build #958: FAILURE in 5 hr 40 min: https://lpci.wedontsleep.org/job/devel/958/04:41
jtvFTI is a hard problem.  But does it make any sense at all for package names!?04:43
StevenKSure, why not?04:45
lifelessno stemming rules for packages04:45
lifelessand no substrings in the tsearch2 fti implementation04:46
wgrantIt would be nice if we could cheaply set up a copy of a few tables from the DB to test stuff on.04:50
StevenKI was thinking that04:50
lifelessWe can test on [qa]staging04:50
lifelesse.g. with temp tables in a transaction04:51
lifelessor even on the main tables if its compatible04:51
lifelessStevenK: ^ wgrant ^04:52
wgrantlifeless: What if you try a temp table of BPPHs with matching BPNs?04:54
lifelessboostrapping that now04:55
lifelessselect * into temporary table test_bpph from binarypackagepublishinghistory;04:55
wgrantOh, I was thinking you could just get a temp table of BPPH IDs that match the BPN query.04:56
wgrantRather than duplicating a 15M row table.04:56
* lifeless shrugs04:56
lifelessmay as well understand the row width impact04:57
wgrantI was hoping to cheaply and roughly emulate the BPPH.name index.04:57
lifelessEODish, I'll be doin ghouse things for a bit etc, will pop back to do the next step04:57
wgrantk, thanks.04:57
lifelessheh thats done04:57
lifelessnow to add the column04:57
wgrantI guess sourcherry is a bit of a monster, even if it isn't wildcherry.04:58
wgrantParticularly since it's a small table.04:58
lifeless update test_bpph set name=binarypackagerelease.binarypackagename  from binarypackagerelease where test_bpph.binarypackagerelease=binarypackagerelease.id;05:00
lifelessrunning now05:00
lifeless(and yes, I know I could have done this as one step05:00
lifelessbut this has a smaller footprint - I'm betting faster overall05:00
wgrantIn case you've not done it already, I think http://paste.ubuntu.com/661666/ is about as good as it gets.05:03
wgrantie. not very, but we'll see.05:03
wgrantAs long as it goes the right way we should be OK.05:03
StevenKwgrant: Didn't you want to skip over SPR entirely?05:04
wgrantStevenK: Can't here.05:05
wgrantSince SPPH isn't involved in the second query.05:05
wgrantWe have to go through one of them.05:05
StevenKwgrant: Where did 'JOIN _1 AS' come from?05:06
wgrants/_1 AS //05:06
wgrantmiscopied from lifeless'05:06
StevenKSigh, I can't run it anyway05:07
wgranthttp://paste.ubuntu.com/661675/ has the added bonus of working and being tested.05:10
StevenKOh mawson?05:10
StevenKTempted to do the test_bpph shuffle there too05:11
wgrantOn dev.05:11
wgrantI'm not hugely tempted to rewrite 18M rows on mawson, but maybe.05:12
StevenKlifeless: I wonder if that UPDATE is done yet.05:12
StevenKjtv: QA!05:16
lifeless update test_bpph set name=binarypackagerelease.binarypackagename  from binarypackagerelease where test_bpph.binarypackagerelease=binarypackagerelease.id;05:21
lifelessUPDATE 1588609905:21
lifelessTime: 884894.389 ms05:21
lifelessthere is another approach to ths btw05:22
lifelessa dedicated query schema for the use case we are solving05:22
lifelesse.g. a fact table with spn bpn, archive05:22
lifelessI suspect that that would fly insanely fast and be tiny05:22
lifelesseven if populated for the world05:23
lifelesswill come back to that05:23
lifelesswe will probably want some indices05:24
StevenKMight need distribution too, but that can probably be deduced from archive05:24
lifelessfor your specific case we don't, but yes, we might want the schema to have it05:25
lifelessSeq Scan on test_bpph binarypackagepublishinghistory  (cost=0.00..882508.82 rows=1272 width=12)05:29
lifelessso, I'll add a partial index on status, archive05:29
wgrantNot name too?05:29
lifelesscreate index test_bpph_archive_status on test_bpph (archive, name) where status in (1,2);05:32
lifelessthis may take a little time05:32
wgrantProbably also want DAS, but we'll see.05:32
lifeless30 seconds05:33
lifelessestimates 4000 cost now; we'll see05:33
lifelesswgrant: so, the reason we expec this to be better is that the active-filter is status,archive on bpph ?05:37
jtvStevenK: 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
wgrantlifeless: Were I the planner, I would find candidate BPNs, then look up BPPHs by (archive, name, status)05:37
StevenKjtv: I'm trying to emulate wgrant's nagging05:37
jtvI could tell the difference easily.05:38
lifelesscrap stats05:38
lifeless                                             ->  Bitmap Index Scan on test_bpph_archive_status  (cost=0.00..1250.23 rows=792 width=0)05:38
lifeless                                                   Index Cond: (archive = 1)05:38
lifelessok, now we get a sensible cost out - 900K05:39
lifelessits doing a hash join05:39
jtvRarely good.05:39
lifelessforcing that off, lets see05:39
lifelessthats with hash joins forced off05:40
lifelessI think we may have bong tuning parameters somewhere05:40
lifeless12 seconds with it off, run 105:41
lifeless11 seconds for run 205:41
wgrantOr possibly I'm better at devising plans than postgres, so it should listen :)05:41
lifelesswgrant: we all are when we use domain knowledge05:41
wgrantBut we can't tell it to listen.05:41
lifelessthe trick is to figure out why pg thinks the nested loop is going to be so expensive05:41
lifelessthe %linux% may be a cause05:42
jtvWell that'd do it.05:42
lifelessit can eliminate all names shorter than linux05:42
jtvAs I said, there's no way to get conservative statistics on selectivity for a LIKE.05:42
lifelessyeah :)05:42
wgrantlifeless: What if you take the BPN query out into a CTE and hope it doesn't optimise through it?05:43
lifelesswon't work05:43
lifelesstemp table might, I'm just trying that now05:43
jtvPulling the BPN ids into the client may help.05:43
lifelessjtv: this is roughly the same as a temp table05:43
lifelessjtv: though the temp table is better05:44
wgrantset i_am_right=true;05:44
wgrantSurely that will work.05:44
jtvlifeless: there may be a sharp change in performance as the size of your temp table exceeds the number of common values we keep in our stats.05:45
lifelessjtv: yes, there can be - and passing in a big IN clause has a similar kneee05:45
lifelessjtv: but the knee for IN clauses is lower than that for temp tables.05:45
lifelessjtv: I haven't checked the code to see the why of this05:46
jtvThat's remarkable.  I would have expected the kneeee for the temp table to be dominated by our statistics config (which admittedly we've got higher than normal) and the one for the IN by projected seek costs.05:46
lifelessyou may be right; I haven't tried to model this on different pg instances05:47
lifelessjtv: but IN seems to just bail somewhere in the K's of entries region05:47
jtvWell that may just be the sensible thing to do based on random_page_cost.05:47
lifelessok, first cut with temp table was 46 ms but wrong05:48
jtvI think I can beat that number, if the answer doesn't have to be right.  :)05:48
jtvTDD implementation of factorial: if n <= 1: return 1 ; else: return 605:49
lifelessok, this works well enough05:49
lifeless1341ms total time05:50
lifeless    "test_bpph_archive_status" btree (archive, name) WHERE status = ANY (ARRAY[1, 2])05:51
lifelessI'd like to try a fact table05:51
StevenKThat 844 is hot?05:51
lifelessI was iterating on the bpn side05:52
lifelessso some of the spn side dropped out of cache05:52
wgrant(archive, distroseries, sourcepackagename, binarypackagename)?05:52
wgrantMight also be interesting to have overrides in there, possibly.05:52
StevenKlifeless: Why the OFFSET 0?05:53
lifelessStevenK: hacks the optimiser05:53
jtvOr rather, stops it from moving bits into and out of that query.05:54
jtvOptimization barrier.05:54
lifelessStevenK: forces it to optimise the subplan separately05:54
StevenKlifeless: You could also try the SPPH.name bit too05:55
lifelessthe denorm? sure, after fact table + dinner05:56
lifelessmeh, not going to do archive.purpose06:04
lifelessoh wow, DISTINCT + LIMIT 1 -> pointless work.06:07
lifelesswgrant: StevenK: this is my proposed fact table building query:06:07
StevenKYour unbuilt assumptions are a little wonky06:09
StevenKA BPB will exist, it just won't link to a BPR06:09
wgrantNot necessarily.06:10
StevenKSourcePackagePublishingHistory.status IN (1, 2) -- active ; s/active/published and pending source packages/06:10
wgrantactive is the recognised term for that.06:10
lifelessclose enough :)06:10
lifelessI ask because I get 7 rows (without the distinct) that are the same for a LIMIT 10 run of the query.06:11
lifeless id | archive | ds |  spn  |  bpn06:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21221 | 6153106:11
lifeless    |       1 | 29 | 21238 | 6156506:11
wgrantlifeless: One for each DAS?06:11
StevenKPer DAS would be my guess06:11
lifelessmakes sense06:16
lifelessrunning the full mccoy : insert into spn_lookup SELECT DISTINCT sourcepackagepublishinghistory.archive,...06:16
lifelessbah, needed a column statement or null::int at the start. Iz off again06:42
jtvcjwatson: sorry for haunting you like… something that… haunts… proverbially… (examples escape me) but if you have a moment to talk about bug 659769 I may be able to resolve it for you very soon.07:14
_mup_Bug #659769: should copy custom uploads to newly-initialised release <derivation> <lp-soyuz> <new-release-cycle-process> <soyuz-publish> <Launchpad itself:In Progress by jtv> < https://launchpad.net/bugs/659769 >07:14
lifelessok, so done07:23
lifeless460K rows07:23
lifeless1400ms with a scan07:26
lifeless550ms union with a couple of naive indices07:34
lifelessah, 440 with a non broken query07:39
lifelessjtv: is pg smart enough to split 'foo or bar' into two separate index walks ?07:42
jtvlifeless: AFAIK they start out as separate nodes, and it's the optimizer that sees if they should be joined into one.  So yes.07:43
lifeless350ms with OFFSET 0 optimiser hacks07:44
jtvWhy bother optimising?  Just use a NoSQL database.  It's web scale.07:45
lifelessbecause we want it fast now ?07:45
jtvHmm I need to work on that irony.  It evidently does not come across.07:46
lifeless114ms in bpn scan07:48
lifeless25ms in spn scan07:48
lifeless16ms in spn scan to get the names out07:49
lifelesshttp://explain.depesz.com/s/P9n and http://paste.ubuntu.com/661734/07:50
lifelessStevenK: ^ wgrant: ^07:51
stubwhat is the source query you are trying to fix?07:54
jtvGaaaaahhhh!  Kill InitializeDistroSeries tests!  Kill!  Kill!07:55
lifelessstub: an 8? 9? table monster - sourcepackagename -> release -> publication -> distroseries + the same on the binary side07:55
lifelessstub: 'what source package names are currently actively published for Ubuntu and match %linux% for either the source package name or the binary package name'07:56
stubso select spn.name from sourcepackagename as spn, sourcepackagerelease as spr, sourcepackagepublicationhistory as spph, distroseries where spn.name like '%foo%', spr.sourcepackagename=spn.id,spph.release=spr.id,distroseries.id=spph.distroseries union the binary side?07:58
lifelesshas an experiment using a temp table as part of optimising things08:00
lifelessI don't have the full baseline query handy, sorry.08:00
stubyer - I'm after the simple (slow) form of the query.08:01
lifelesssubstitute _1 into the second query on that pastebin, and you'll have it08:01
stublifeless: the distinct is unnecessary with the UNION, or perhaps leaving them in and using UNION ALL. Might help a little.08:10
lifelessstub: both sides can return the same SPN, so UNION is needed; the DISTINCT improves the side query even though its conceptually not needed - drops one of the intermediary table scans down by 60%08:11
StevenKlifeless: I quite liked the BPPH refactor results, too08:18
lifelessStevenK: yeh; they /might/ be generally applicable, OTOH they make that table wider and perhaps slower at other things08:21
adeuringgood morning08:21
lifelessStevenK: 320ms08:27
lifelessStevenK: using distinct outside and union all inside08:27
StevenKIs that SPPH.name, or the fact table?08:27
lifelessfact table08:27
lifelessand at that, 150ms of that time is just looking up the spn and bpn in their tables08:28
StevenKMy concern with a fact table is how to keep the bugger up to date08:28
lifelessthere are a few strategies; in the appserver, triggers08:29
lifelessit may not be worth it. It was spectacular with bugtask though08:29
lifelessso I thought worth testing here.08:30
StevenKSo, based on the numbers, I think the refactoring of name into {B,S}PPH is a good deal08:31
lifelessthere are 18000 linux package names in bpn08:31
lifelessonly 125 are relevant08:31
lifelessif we had trigrams I'd be keen to inline it08:31
wgrantThere should be way more than 125 relevant.08:32
wgrantThere are thousands of Linux binary names in the primary archive.08:32
lifelesswgrant: the final result with all these queries has been 12508:32
wgrantRight, that's just distinct sources, of course.08:32
lifelessStevenK: these options aren't exclusive; can do both, or neither.08:33
StevenKlifeless: Sure08:33
lifelessStevenK: either way you have a redundancy to cater for08:33
StevenKlifeless: Given the numbers for queries without either, I think it's clear we have to do one or both of them08:34
lifelessits probably least engineering to do just the bpph denorm08:34
lifelessspph isn't needed - we can get through to the spn in 300ms or so IIRC08:34
wgrantWe can probably sensibly do the fact table in triggers, unlike bugsummary, but the name denorm is still far simpler.08:35
lifelessI'd consider maintain this one in appserver code - you know when you are deleting something much more accurately than a trigger would; will be faster.08:36
wgrantAhahaha no.08:37
lifelesswhich reminds me08:37
lifelesswe seem to have some skew built up or building pu in bugsummary08:37
StevenKbigjools: O hai -- didn't you say you were moving DF to devel?08:37
wgrantlifeless: That's amusing.08:37
wgrantAnd entirely unexpected, of course...08:38
bigjoolsStevenK: I'm waiting until FDT is deployed and working08:38
lifelesswgrant: was a risk, which we did wwhat we could to address up front08:38
wgrantYay, oneiric.08:40
wgrantlightdm appears to not start automatically any more.08:40
lifelessno, thats the new version08:49
* nigelb waves08:58
wgrantMorning nigelb.09:03
rvbalifeless: Hi, I'm having a look at your latest-monthly-pageids.html thing, could you send me a large zserver trace logs file for me to be able to run the script locally. (I'm sure I have access to logs somewhere but I need your help to find them)09:04
nigelbhello wgrant09:04
nigelbWorking on my slow moving bug fix for tooltips today09:05
wgrantrvba: carob:/srv/launchpad.net-logs/lpnet/*/launchpad-trace*.log09:06
wgrantnigelb: How's that going?09:06
wgrantrvba: Today's are a gigabyte so far, however :)09:06
nigelbwgrant: slow, due to lack of time.09:07
wgrantnigelb: That's probably the best reason for it to be slow.09:07
rvbawgrant: Thanks!09:07
stublifeless: we can get trigrams - its in pg_contrib, but we have not used it yet (tied up in all our search story).09:10
nigelbwgrant: I keep having to spend 5 to 10 minutes figuring out what I was trying the last time :)09:10
wgrantlifeless: Ah, no, it's not lightdm... someone broke i915, so it's even more amusing.09:12
nigelbIntel graphics is always fun.09:12
jtvStevenK, still here?09:18
StevenKjtv: Hm?09:18
jtvI was just wondering about may_require_job vs. multi-parent.09:18
jtvAFAICT may_require_job currently returns False if the derived series has any parent that's in the same distro, on the grounds that we don't track DSDs within the same distro.09:19
jtvBut ISTM that's just a surviving workaround from the time when it didn't get passed an explicit parent series.09:19
StevenKI doubt that is a common case09:19
jtvNow that it receives both derived_series and parent_series, I suspect the check should be simply for derived_series.distribution == parent_series.distribution.09:20
lifelessstub: it would be interesting to see if trigrams got useful statistics for query plans09:35
stubwhere were you thinking of using them? scan of the spn/bpn didn't seem that bad compared to the rest of the query you were looking at. Thinking of keeping redundant copies of the packagename so the queries don't need to go that deep?09:37
lifelessstub: the query planner can't assess row counts sensibly for like '%...' queries09:37
stubfwiw, WITH cte's should let you avoid temporary tables.09:37
lifelessstub: different plans, WITH cte was 2-3 times slower than the temp table09:38
stubbecause you could create an index?09:38
stubI don't think trigrams will help assess row counts better.09:38
lifelessno, because the temp table gets fully evaluated before the plan for the next query is done09:39
lifelessso rather than seeing 1M rows, it sees 60K09:39
lifelesswhen it thinks the row count is higher than it will be (by factor of 10, for instance), it will switch to hash joins, or seq scans09:39
lifelessstub: I don't know if they do/don't. I can imagine a stats gatherer for them that will09:39
lifelessso, one place to use them would be for that.09:40
lifelessthe other place, is, as you say, to inline the search term to a middle table in the query09:40
stubyer, but this is in contrib so I doubt that exists atm.09:40
lifelesshowever, we did a simple denorm experiment putting the bpn id onto bpph09:40
bigjoolslifeless or stub, is my db patch ok to land and if so I presume db-devel for now?09:40
lifelessand that got us a 650ms query09:40
lifelesswhich is probably fine09:41
lifelesswe can get 300ms without trigrams with a fact table09:41
lifelesswith trigrams we could shave 100ms off that easy, I think.09:41
pooliedid lp just lose its session cookies?09:41
lifeless(because 100ms of that 300ms is the bpn scan)09:41
poolieoh for some reason i got an edge url from history, nm09:42
jml(also, LP still sends out edge URLs)09:42
stublifeless: I'm just putting up a branch turning off all the launchpaddatabaserevision checks. I can't think of any sane runtime checks that will work with fastdowntime that won't bite us in the arse.09:43
StevenKjml: In which cases?09:44
wgrantstub: Isn't the current one OK?09:44
wgrantstub: That is, refusing to start if patches are unapplied.09:44
stubwgrant: Only if we never use -0 patches.09:44
lifelessjml: in email? yes, if someone uses edge to do an action.09:44
wgrantWe should stop special-casing -0 patches.09:44
wgrantBut I think the -[^0] patch rules are fine.09:45
stubwgrant: we have optional patches that might not be live by the time the code is updated.09:45
lifelessstub: all things considered, I'm +109:45
wgrantPerhaps -0 patches get the -[^0] rules, and -[^0] get no rules?09:45
stubwgrant: But when would you ever use -0? You can't have a required patch because the code needs to run with the currently deployed schema and the HEAD schema.09:47
lifelessstub: a required patch would be one that is deployed09:47
wgrantstub: Note the code after the patch.09:47
wgrantA rev after the patch could reasonably expect to be running on the patch, couldn't it?09:48
lifelessstub: e.g. develop it in a pipeline, land the db patch to db-devel, get it deployed, then land the rest to devel09:48
stubSo you get no benefit of the run time check, as you couldn't get that far without managing that yourself.09:49
lifelesswhich is why I'm +1, all things considered ;) just describing a scenario where it could save some headache, if someone landed on devel too soon without the db component.09:50
lifelessof course, merging a pipe would merge the db patch to unless special care it taken09:50
stubYour code can't land on devel until your patch has been merged from db-devel or your tests will fail (and if they pass, it isn't a required patch...). So it would catch someone merging db-devel without a deployment having happened.09:52
lifelessrvba: legend!09:53
nigelbstub: that's going to go into a T-shirt ;)09:57
rvbalifeless: ;)09:57
lifelessnight all09:58
nigelbnight lifeless09:59
henningejtv: Hi! Do you have a few minutes for pre-imp chat?10:08
jtvhenninge: not really, sorry!10:08
henningejtv: np10:09
allenapgmb: Thanks for the review :)10:12
=== jtv is now known as jtv-afk
gmballenap: No problem :)10:36
=== gmb changed the topic of #launchpad-dev to: Performance Tuesday | https://dev.launchpad.net/ | On call reviewer: gmb | Critical bugs: 235 - 0:[#######=]:256
LPCIBotYippie, build fixed!10:49
LPCIBotProject db-devel build #795: FIXED in 6 hr 8 min: https://lpci.wedontsleep.org/job/db-devel/795/10:49
stubgmb: An all-red MP for you: https://code.launchpad.net/~stub/launchpad/trivial/+merge/7084011:06
gmbstub: \0/11:06
gmbstub: Approved.11:07
* gmb -> food11:15
danilosmrevell, "You've got mail!"11:17
* danilos -> food11:17
mrevellthanks danilos11:42
=== stub1 is now known as stub
gary_posterallenap, for your Dict, I imagine you already thought about wrapping an object (so that the dict API operates on the inner, "clean" object).  Just sharing a thought I had while reading mail. :-)11:54
* henninge lunches11:54
=== henninge is now known as henninge-lunch
=== Ursinha-afk is now known as Ursinha
deryckMorning, all.12:33
allenapgary_poster: I did think about that. Unfortunately the convenience of dict.name notation is lost. Also, I think JavaScript is not quite rich enough (yet) to absorb all Python idioms, and maybe I just ought to get used to it.12:38
gary_posterallenap, heh, ok cool.  You can also try a JS language compiler ;-)12:42
allenapgary_poster: Yeah, that would be pretty cool. I'd need to learn how to see the Matrix to use a debugger with it, that's the only downside :)12:45
=== henninge-lunch is now known as henninge
rvbagmb: Hi and thanks for the review! Do you think I should land it as is or, like in suggest in the MP, get stub to host the jquery plugin file alongside the other stuff that the report page uses?13:33
gmbrvba: I got as far as "uses?" and then a little message that says "incompatible encoding". My computer may be being all English and refusing to display something...13:35
rvbagmb: weird ... "uses?" is the last word of my sentence ...13:36
StevenK"think I�should" is what I saw, so something odd is there13:36
gmbrvba: Anyway, I think getting stub to host the plugin is probably the canonical best-way-of-doing-things here, so let's do that.13:37
rvbagmb: all right.13:37
gary_posterallenap, I found it interesting and amusing given our earlier banter that I just heard of this in a mailing list: http://www.infoq.com/news/2011/08/debug-languages-on-javascript-vm (would allow mapping debugging through compiled languages, and compressed JS, in Firefox and WebKit)13:41
allenapgary_poster: Hehe, that is awesome :)13:42
cr3who created the images under ./lib/canonical/launchpad/images/? I'd like to ask for a similar image :)13:59
* deryck switches offices, back online soon14:19
gary_posterbac, we have this bug: "My Ubuntu Member membership recently expired. I was expecting to get a notification by email when it came due, but I didn't. I have checked my spam trap, but there's nothing there."  Is that a regression or a feature request?15:03
bacgary_poster: i know in the past i have gotten expiration reminders.  is it configurable?15:04
gary_posterbac, no idea, looking15:06
bacgary_poster: me too.15:06
bacgary_poster: forwarded you an email15:07
bacgary_poster: teammembership-email-notification.txt is a good start15:08
bacgary_poster: team renewal policy must be ONDEMAND15:08
bacgary_poster: so it is either a regression or the team is not configured as he expectes15:09
* bac suspects the latter15:09
gary_posterbac, got it, thanks.  it is the ubuntu team AIUI but maybe it is loco.  Do you know where I can find the ONDEMAND setting for the team in the web?  I'm looking for ~yellow and have not found yet15:10
gary_posterActually bac15:10
gary_posterI see option on +edit15:10
gary_posterbut option starts with text "When someone's membership is about to expire, notify them and:"15:11
gary_posterso notification should always be sent according to that text15:11
bacgary_poster: yes, i suspect it is the second of that group15:11
gary_posterbac, you mean loco?15:11
bacgary_poster: no, i meant ONDEMAND probably corresponds to the second selection15:12
bacbut you're right, it should notify based on that description in the UI15:12
gary_posterbac OIC.  But we don't actually need ONDEMAND for the email to be sent out, right?  yeah ok. So I'll maybe try to find some logs for this and see if I can find the user there...and generally emails being sent out15:13
gary_posterthank you bac15:13
bacgary_poster: yes, looking through that doc test i see the other states should also send email15:13
sinzuijcsackett, do you have time to mumble?15:28
jcsackettsinzui: sure, just a moment.15:28
jcsackettsinzui: can you hear me?15:30
jcsackettsinzui: i just got dropped; one second.15:32
sinzuiI saw15:32
adeuringgary_poster: do you know how I can get the permission to register a new release of laze.batchnavigator on PyPI?15:56
gary_posteradeuring probably by asking me :-) what is your PyPI user name--the same?15:58
adeuringgary_poster: yes15:58
benjigmb: can I get https://code.launchpad.net/~benji/launchpad/bug-798945/+merge/70909 into your review queue?16:00
gmbbenji: Sure.16:00
gary_posteradeuring, done (sorry for slow turn around, other things going on simultaneously)16:02
adeuringgary_poster: thanks!16:02
=== matsubara is now known as matsubara-lunch
=== beuno is now known as beuno-lunch
gmbbenji: approved16:16
=== gmb changed the topic of #launchpad-dev to: Performance Tuesday | https://dev.launchpad.net/ | On call reviewer: - | Critical bugs: 235 - 0:[#######=]:256
benjigmb: thanks16:16
LPCIBotYippie, build fixed!16:31
LPCIBotProject devel build #959: FIXED in 5 hr 41 min: https://lpci.wedontsleep.org/job/devel/959/16:31
=== deryck is now known as deryck[lunch]
=== matsubara-lunch is now known as matsubara
=== beuno-lunch is now known as beuno
=== deryck[lunch] is now known as deryck
deryckmorning, lifeless19:37
baclifeless: regarding bug 34086 and the new assignee i think you should check out https://answers.launchpad.net/launchpad/+question/167455, also by that person.20:45
_mup_Bug #34086: removal of arch-all packages while there are arch-specific packages dependent on it results in uninstallable binaries <escalated> <feature> <lp-soyuz> <soyuz-publish> <Launchpad itself:In Progress by rreynoso45> < https://launchpad.net/bugs/34086 >20:45
lifelessbac: heh20:46
lifelessbac: I considered just toggling it back, but decided to be optimisting20:46
baclifeless: i think with the additional data it would be best to toggle it back20:46
lifelessyeah, doing so20:46
bacchr, ftw20:46
=== matsubara is now known as matsubara-afk
henningeCan somebody please review this for me?21:43
lifelesshenninge: wouldn't it be simpler for the user option parser to just store the string ?21:47
lifelesshenninge: thats what all the other scripts do, don't they ?21:47
henningelifeless: AFAIUI the reason behind this construct is that the option is validated during initialzation.21:48
lifelesshenninge: but its means you're in a transaction before you've taken out the script lock21:49
lifelesshenninge: this seems unwise21:49
henningelifeless: hm, true21:49
lifelessprobably harmless in this case21:49
lifelessbut I can imagine things that do lock in the db having trouble21:50
lifelessanyhow, I'm fine if you want to land this21:51
lifelessmmm, perhaos21:51
lifelessacutally no21:51
lifelessit conflicts with the inifile-shutdown thing for scripts21:51
lifelesshere is why: option parsing is in order supplied; until we consult the ini file (found via the config) to see whether the script can run, which means we need to know the config to be using...21:52
lifelesswe can't be sure that the DB will even be available21:52
lifelessperhaps I'm wrong21:52
henningelifeless: yes, it seems wiser to change that construct.21:54
henningeeven if that is a bit more work21:54
* henninge regrets not having had a proper pre-imp discussion as he had meant to ...21:55
henningelifeless: thanks! ;-)21:55
lifelessno probs!21:55
lifelessI'll paste this in the review for education of anyone looking at it21:55
henningelifeless: cool21:58
LPCIBotProject db-devel build #796: FAILURE in 5 hr 43 min: https://lpci.wedontsleep.org/job/db-devel/796/22:15
sinzuiwallyworld_, http://pastebin.ubuntu.com/662245/23:13
StevenKsinzui: select count(*) from sourcepackagepublishinghistory as spph join distroseries as ds on spph.distroseries = ds.id where status in (1, 2) and archive = 1 and ds.distribution = 1; => 207k23:38
sinzuiStevenK, https://launchpad.net/ubuntu/oneiric shows only 18542 published in development. U think you should consider using distinct23:40
lifelessStevenK: thats all series ever23:55
lifelesssinzui: distinct isn't relevant, you're comparing apples and oranges.23:56

Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!