[00:11] Hmm [00:12] So pillarname is the most read table on the system by around 50%. And the main query that used it was 30x slower than it needed to be. [00:12] Our performance situation is hilarious. [00:13] == Most Bloated Tables == [00:13] logintoken || 96% || 555 MB of 576 MB [00:14] Is autovacuum broken? [00:14] no [00:17] Oh-ho-ho [00:17] I think it's all slony [00:18] The performance regression in week 47 last year (the week of Nov 19) coincides with slony jumping from 20% to 85-110% master CPU usage. [00:18] I wonder if that's when we upgraded. [00:19] Compare https://devpad.canonical.com/~lpqateam/dbr/weekly/db-report-2011-11-11-2011-11-18.txt and https://devpad.canonical.com/~lpqateam/dbr/weekly/db-report-2011-11-18-2011-11-25.txt [00:20] No major write load changes. [00:22] The upgrade was the 18th. [00:22] To slony2 [00:22] That is slightly suspicious, if you ask me. [00:27] * StevenK blinks at FormFields.select() [00:28] It's second argument is *names, and when I pass it a list, (Pdb) p names [00:28] (['name', 'displayname', 'visibility', 'contactemail', 'teamdescription', 'subscriptionpolicy', 'defaultmembershipperiod', 'renewal_policy', 'defaultrenewalperiod', 'teamowner'],) [00:36] StevenK: who is a lp.dev user with launchpad.Commercial? [00:37] admin ? [00:37] wallyworld_: commercial-member@canonical.com [00:37] or admin, yeah [00:37] thanks [00:40] wallyworld_, sinzui: Why BugTaskVisibilityPolicy? [00:40] Isn't it global to all artifacts? [00:41] wgrant: it's not [00:41] unless something went wrong with the landing [00:41] Oh, the commit message was wrong. [00:41] The code is right. [00:42] bugger, didn't change it when i changed the code [00:42] StevenK: i think the rule in checkAllowVisibility() is wrong? if ff is on, it no longer allows lp.Commercial if ff is on [00:42] I had also intended that it be AccessPolicyType, but we can discuss that. [00:42] Since it's not an actual policy. [00:42] sure, easy to change [00:43] Just a class of policy. === Ursinha_ is now known as Guest63174 [00:46] wallyworld_: Damn good point [00:46] wallyworld_: I can subtly fix that in this branch if you want [00:46] StevenK: yes please :-) [00:46] and add a test [00:52] StevenK: just noticed, the public/private field on Team+edit suffers the same issue as the private_bugs field [00:53] StevenK: when you edit a private team, the public/private field says Public [00:53] it's not being initialised from the context properly [00:54] Right [00:55] StevenK: lp:~wallyworld/launchpad/private_bugs-init-932721 has my solution [00:55] to the problem which works [00:57] StevenK: thats a list in a tuple [00:57] lifeless: Well, yes. [00:58] lifeless: I needed a * [00:58] Which blows the list apart into arguments, I think [00:59] if thing = ([foo,bar],) [01:00] quux(*thing) == quux([foo,bar]) [01:01] thing = [foo, bar] [01:01] then [01:01] quux(*thing) == quux(foo, bar) [01:01] Right, so I was right [01:01] (but your names above was ([foo, bar],) [01:02] lifeless: I was calling select(list), where it was 'def select(self, *names)' [01:02] Calling select(list) got me ([foo, bar],) [01:02] Calling select(*list) got me (foo, bar) [01:02] kk [01:13] * StevenK grumbles at IPerson.checkAllowVisibility() [02:16] what is the intent of the register merge button doing ajax rather than a direct post? [02:17] is it supposed to generate the diff immediately? [02:17] No [02:18] It is supposed to tell you when stuff is wrong so you can fix it [02:19] It'll be unbroken in a few hours. [02:33] StevenK: ah ok [02:33] StevenK: 'wrong' ? [02:35] wallyworld_: ^ [02:36] Requesting a review from someone who can't see the branches. [02:39] StevenK: ? [02:39] it will warn you if you are about to expose private branches [02:39] and ask you to confirm [02:43] ah ok [02:55] mwhudson: since now nominated reviewers are subscribed to the source/target branch if the branch(es) would otherwise have been invisible to the reviewer [03:02] BAH [03:02] I see what broke the test [03:02] We only want to render the context for edit [03:03] Otherwise it looks for IPersonSet.visibility, and then the toys get de-pramed [03:04] wallyworld_: I think I'm done with your changes. [03:04] StevenK: excellent [03:05] wallyworld_: I'll get you to review the changes I added, if you don't mind. [03:05] np [03:05] But that will about ten, since the kettle is calling [03:05] can relate to that [03:29] wallyworld_: Sorry, I also cleaned up after lunch, but r14801 only on https://code.launchpad.net/~stevenk/launchpad/better-name-field/+merge/93317 [03:29] * wallyworld_ looks [03:29] wallyworld_: Feel free to add your +1 on the MP if you want. [03:32] StevenK: Do you plan a fastdowntime tonight? [03:33] StevenK: why not use check_permission('launchpad.Commercial', self)? it was just the placement that was wrong, not the method call itself [03:33] wallyworld_: Because it wasn't working, and check_permission() in model code is always a bit suspect [03:33] wgrant: That sounds like a good plan [03:34] I think the mass index death patch is first [03:34] It is indeed. [03:34] * StevenK reaches for staging logs [03:34] Hmm [03:34] Now, unity, give me back my coding terminal. [03:34] Please? [03:34] wgrant: gvim? :-P [03:34] It's still there in Alt-Tab, but has otherwise disappeared. [03:35] StevenK: ah, didn't notice it was in model. in such circumstances, i would instantiate and call the security adaptor.' [03:35] wallyworld_: Example? [03:35] so that we know the same security checks are used [03:35] wallyworld_: I quite like the use of IPersonRoles [03:35] IPersonRoles is the right thing to do here. [03:36] StevenK: except that it used to be launchpad.Commercial [03:36] and now it's different [03:36] in_commercial_admin() is identical [03:36] For this purpose [03:37] so we really want to allow in_admin as well? [03:37] Both should be allowed [03:37] .in_admin == ~admins [03:37] sure, but it's new behaviour [03:38] just checking that it's really required [03:38] Not really, now it's explicit [03:38] admins have lp.Commercial [03:38] right, didn't know that [03:39] admins also have lp.Moderate and I think they have lp.Special [03:39] No [03:39] lp.Special exists solely to exclude admins. [03:39] That's its entire purpose. [03:39] Scoff [03:40] which is a bit backwards way of doing it [03:40] its on the wrong dimension [03:41] StevenK: r=me [03:42] wallyworld_: Thanks! [03:42] wallyworld_: Did you want to scribble the MP, or you don't care? [03:42] np. be good to get all this stuff landed [03:42] StevenK: already have :-) [03:45] wgrant: I'm not sure I trust dbupgrade.log [03:45] StevenK: Oh? [03:45] The times in there should be accurate. [03:46] 2012-02-15 11:15:36 INFO 2209-10-0 applied just now in 0.1 seconds [03:46] yes, any ? [03:47] I don't believe it. That patch drops 63 indices [03:50] dropping an index is cheap [03:50] So stub said, delete a file, sync, move on [03:51] little more than that but yeah [03:51] (metadata updates, locks, etc) [03:51] I'd just expect dropping 63 to take more than 100ms [03:51] did you confirm my question about FK indices ? [03:52] StevenK: its one transaction, so only a few syncs, not one per index. [03:52] I saw it, I didn't think we were dropping any [03:52] branch.owner ? [03:53] I haven't gone looking for others but I'd expect that to be the index we need for efficient referential-integrity queries [03:54] But we don't select via owner. The index has been untouched [03:54] It was branch__owner_name__idx that was dropped [03:54] Not owner [03:55] owner_name probably hasn't been used since unique_name was introduced. [03:55] that index was probably a hang over from before source package branches [03:55] ah, cool. [03:55] StevenK: the indexes I'm concerned about *wouldn't be used* [03:55] or at least, very rarely [03:55] Person merges would use them. [03:55] lifeless: So, I trust stub's judgement on this [03:56] sure [03:56] I will ask if he checked [03:56] which is all I was asking you [03:56] that someone took the time to remember this step [03:56] wgrant, I, and stub spent a few hours talking over all of them [03:56] I disagree with the removal of some. [03:56] eg the ones on incrementaldiff [03:57] They're only unused because the feature is disabled. [03:57] But I don't care enough to revert the patch. [03:57] Index creation can be done without downtime [03:57] They're easy to add back later if we don't want to delete the feature. [03:57] Right. [03:57] wgrant: FDT request up [03:58] * StevenK glares at his laptop [03:58] If I double a text file on my desktop to bring up gedit, Unity crashes [03:58] If I use gnome-do to bring up the same file, it doesn't [03:59] * wgrant blames nautilus [03:59] And thumper. [03:59] It's oneiric, so his care factor is probably a large negative number. [04:49] * StevenK stabs _lock_actions more. [04:55] Why does buildmailman.py take so long to run? [04:56] Because mailman is large [04:56] and wtf [04:56] ImportError: Bad magic number in /home/cody-somerville/Projects/launchpad/lp-branches/devel/lib/lp/services/timeline/timeline.pyc [04:58] fixie fixie [04:58] anyhow, either launchpad has gotten slower to start or my machine has gotten slower :( [04:59] hmm... ImportError: No module named convoy.meta. Am I missing a dep? [04:59] Yes [05:00] launchpad-developer-dependencies depends on it [05:02] What is convoy anyhow? [05:04] and can I copy the lucid build with binaries of it into natty series of ppa? [05:05] Why are you developing LP on Natty? [05:05] I was going to remove the Natty packages from the PPA [05:06] :( cause I like Natty [05:07] cody-somerville: We offically support the latest LTS and the latest release only. [05:08] However, I'll fix the convoy recipe to build for Natty. [05:08] \o/ [05:08] StevenK, You're the best. [05:09] cody-somerville: And to answer your question: Convoy is a WSGI app for loading multiple files in the same request. [05:09] Our plan is to use it for JS rather than the horrible launchpad.js [05:09] Is it faster? [05:10] It will result in domReady happening quicker, because your browser no longer has to digest 3MiB of JS [05:12] cody-somerville: launchpad.js is a concatenation of every JS file in our tree + YUI. convoy means that we only send down the files that are needed for each page in one request [05:13] Cool. Are you taking advantage of xsendfile header? [05:14] No, we're not using XSendFile [05:21] stub: I found some more queries today which have massive planner overhead due to the stats things. [05:22] stub: Should we drop the target down to something more sensible except for problematic cols? [05:22] wgrant: I'm considering that, yes. Unfortunately, for the problematic issues we would need to set it to a value we know is too low (100). [05:23] stub: What if we drop to like 500 and see how it goes? [05:23] I'm trying to assemble a test case for upstream, so the more the merrier. [05:23] stub: Also, slony 2 is a performance regression. [05:24] Dropping it to 500 push the issue below the radar, but it will still be lurking. Not sure if that is the best idea. [05:24] The unidentified global performance regression from November coincides exactly with the slony 2 upgrade, which coincides with 15x more sl_log_* tuple reads and slony going from 15% DB CPU usage to 110% [05:24] So I have a collection of ideas I'm not happy with :) [05:24] True [05:25] wgrant: There is a known regression in Slony that is fixed with the next patch level that matches what you are seeing. [05:25] http://www.slony.info/bugzilla/show_bug.cgi?id=167 is possibly relevant [05:25] Yeah [05:25] We should upgrade to 2.1, or possibly increase the intervals back to their defaults. [05:25] See if that alleviates it. [05:26] Is it affecting perceived performance? atm we have CPU and disk to spare I think, and the sl_log_ entries are going to end up in RAM anyway. [05:27] Compare https://devpad.canonical.com/~lpqateam/dbr/daily/db-report-2011-11-16.txt and https://devpad.canonical.com/~lpqateam/dbr/daily/db-report-2011-11-18.txt [05:27] I agree that it shouldn't be a problem. [05:27] But the PPR graphs say otherwise. [05:27] https://lpstats.canonical.com/graphs/PPR/20111017/20120217/ [05:28] Week 47 is the slony upgrade [05:28] web increases by ~0.8s during that week and stays there [05:29] It's possibly not related. [05:29] But slony eating more than a core sounds undesirable anyway. === almaisan-away is now known as al-maisan [05:48] wgrant: I have a suspicion we did a point release upgrade of PostgreSQL to 8.4.9 at the same time [05:50] 21:18 < mthaddon> stub: any objections if we upgrade postgresql while we're at it? (security update) [05:50] Indeed. [05:50] Hmm... or would have have been the previous month? That point release was out 26 sept, but not sure how long it took for the packages to be made, dribble through to lucid and actually get deployed. [05:50] Buildbot broke in late October [05:50] Right. So the PPR graph is quite probably the planner issue. [05:51] Yeah [05:51] Fix join selectivity estimation for unique columns (Tom Lane) [05:51] This fixes an erroneous planner heuristic that could lead to poor estimates of the result size of a join. [05:51] That doesn't look irrelevant. [05:52] It was suspected when I've discussed this on IRC, but I'll need to prove it to Tom. [05:52] Fortunately we have a nice sacrificial sourcherry where we can reproduce the issue... [05:53] Not really sure how PostgreSQL downgrades work :) [05:53] No, but patching out the planner changes does :) [05:55] * stub gets dragged out to lunch [06:34] stub: Setting branch.owner and teamparticipation.{person,team} to 2500 is enough to get it to 40ms on DF [06:34] For one query I tested. [06:36] https://pastebin.canonical.com/60335/ is the one. [06:38] Ouch [06:38] *Up* to 50ms if I remove the second part of the UNION [07:01] stub: http://paste.ubuntu.com/844047/ is a reasonably minimal (but LP prod-specific) example with timings. [07:02] So it gets pretty serious pretty quickly. [07:09] And reproduced all those results to within 1ms on clones of those tables with only the relevant columns and no indices. [07:12] wgrant: You are *upping* the stats to improve the planner time? [07:13] 2500 is the current production and staging default === al-maisan is now known as almaisan-away [07:28] aloha [07:36] stub: No, this is on DF, which defaults to 100 [07:37] The first column is the list of columns that I changed to 2500 and reanalyzed. [07:38] wgrant: "Setting branch.owner and teamparticipation.{person,team} to 2500 is enough to get it to 40ms on DF". Did upping the stats to 2500 improve planner time to 40ms, or are you talking about query time? [07:38] stub: enough to get it to 40ms from 1ms [07:38] Remember that on DF planning is quick [07:38] oic [07:38] because of the tiny stats [07:53] stub: Hm, did you tweak stuff on prod? I have yesterday's 200ms query taking ~15ms now. [07:55] I applied a patch changing the statistics target on the TeamMembership.person column to 100 [07:56] Ah, assumed that wasn't live, since it was on db-devel. [07:56] (all columns on that table could happily go to 100, but I didn't want to be invasive yet as there is a reasonable chance I'll be changing the default) [07:56] Guess I should have landed it on devel now we have twiddled the pqm rule. [07:57] Yeah, otherwise scheduling fastdowntime gets confusing. [07:57] For us mortals without access to prod LDR :) [07:58] Should wire that up on a web page in Launchpad. We never made a /+admin or similar jump off page did we? [07:58] I've been considering that for a while. [07:59] Just needs to be something trivial and text line +opstats, I guess. [07:59] s/line/like/ [08:00] There's still significant overhead on a lot of queries, but it's vastly reduced on yesterday's. [08:01] Heh [08:02] My pillarname fix worked well [08:02] Reads are down by 99% [08:11] Wow [08:12] pillarname has in fact dropped off the DBR entirely, from being the top read table by like 50% [08:19] wgrant: When do Bug.access_policy and branch.access_policy get dropped or repaired? [08:20] wgrant: What was the pillarname fix? [08:22] stub: PillarNameSet.getByName was doing a full seq scan on pillarname [08:22] That's the API that's, you know, used to traverse to products and distros. [08:22] Yer. Always wondering why that table was so popular for reads :) [08:23] It's probably read by more requests than any other table. [08:23] But the reads should each be roughly one tuple. [08:23] stub: Hmm, I should probably drop them with the rest of the schema changes. [08:23] Forgot that detail. [08:24] branch.access_policy will probably come back at some point, but bug.access_policy is dead to us. [08:29] I love it when a branch goes through ec2 first time [08:29] Yay for 4 changes as well as knowing what tests are impacted [08:39] stub: Thanks [08:40] Those columns are gone in -2 [08:49] wgrant: So people get access to an artifact either via being granted access to a policy or being granted directly to the artifact? [08:50] stub: Right. Same as the old schema, just with multiple policies per artifact, and modelled slightly differently with denormalisation. [08:52] So to see an Ubuntu security bug you need to either be in ubuntu-security (which has a grant for the ubuntu security policy), or have a specific grant for the bug. [08:52] wgrant: I don't understand why any of the 'FK to be added later' columns need to have their foreign keys added later rather than now. [08:53] stub: Person merging and branch deletion need special code to handle foreign keys to their tables. [08:53] ok [08:53] -3 adds that code, -4 will add the foreign keys [08:53] The ugliest part of fastdowntime :( [08:54] We really should rework that code to make it easier, but I'm not quite sure how. [08:54] Hey, you guys wanted it like this :-) [08:54] Pfft. [08:55] You could add the columns to the 'ignore these columns' sets in -1 and -3, but probably no real gain [08:56] well... one less fast downtime [08:56] Ah, yes, could this time, because there's an initial patch. [08:57] Might as well do that. [08:57] I'm pretty certain person merge has the ignores stuff you need. I don't know about the branch removal code. [08:58] Probably not - ignores doesn't make sense there except for this sort of db patch juggling. [08:58] Branch removal doesn't actually check. It just has a doctest that lists all the FKs. [08:58] Oh, twitch [08:58] That's a pointless test [08:58] Oh? [08:59] How does listing all FKs on branch help in any way? [08:59] Because it will fail if you've added one, hinting that you need to add new rules to handle it. [09:00] AccessPolicyArtifact needs a PRIMARY KEY [09:05] Curses, you're right. [09:05] morning [09:05] Morning czajkowski. [09:06] has anything happens since last friday with launhcadlib , a user could login on friday and today is seeing errors and not not able to use it [09:06] czajkowski: The user is using staging. staging's database gets reset over the weekend, so the credentials from last week are no longer valid. [09:07] wgrant: thank you [09:07] They may need to use a keyring manager to remove the cached credentials. [09:07] nods [09:07] thanks for explaining [09:07] np [09:08] czajkowski: That's one thing to keep in mind -- "I'm having a problem with LP" covers a multitude of sins, you should get into the habit of asking if they're using production, edge, staging or qastaging if they don't say so themselves. [09:08] (in this case the error showed it was staging) [09:08] StevenK: wgrant nods ok [09:09] czajkowski: mrevell can explain the gory history about that four instances (and there is a fifth, but non developers tend not use it) if you care. :-) [09:10] StevenK: czajkowski: s/edge// [09:11] thanks folks [09:11] Oh, right, edge redirects now [09:11] lifeless, StevenK: Not for the API [09:16] How long does the Soyuz test suite take to run these days? [09:16] Like an hour? [09:17] buildmaster doesn't add much [09:17] omg. [09:17] cody-somerville: The entire testsuite on buildbot is now taking about 5.5 hours [09:18] lol. I just ran it in 123.1 seconds - Tests with failures: runTest [09:19] Okay. So I'm looking to export the authorized_size attribute on PPAs. StevenK: I'll buy you a beer at UDS if you do it for me :) [09:19] stub: Declared the PK, dropped an index that was redundant with it, and renamed one which had an obsolete name. [09:19] cody-somerville: And if I'm not at UDS? :-) [09:20] It's a trap, because we don't go to UDS :) [09:20] lol [09:20] cody-somerville: To export it read-only, it's fairly easy [09:20] To *change* it over the API, that's a little harder [09:20] I want to be able to change it (provided one has the necessary permissions) [09:21] ta. just looking at the triggers [09:21] Yes, I thought you might. :-) [09:21] Isn't it just an int field? [09:24] cody-somerville: So, you can wrap it in exported(), and write a bunch of tests to see :-) [09:25] If only that was easier :( [09:25] wgrant: SECURITY DEFINER functions also need to add a 'SET search_path TO public' clause for security reasons (patch-2209-00-5.sql has an example). [09:25] cody-somerville: webservice tests aren't too bad [09:25] when I run make schema, I get 'ProgrammingError: text search configuration "default" does not exist' [09:26] Not that it should be possible to exploit that in our setup, but defence in depth and all that. [09:27] Yep [09:27] cody-somerville: Have you run launchpad-database-setup? [09:27] It's in utilities/ [09:27] I have in the past [09:28] You probably need to again [09:29] ugh. it destroyed my database, lol. [09:32] cody-somerville: In a good way? [09:32] That script scares me [09:33] No. I thought I could provide a different account name as argument and it would just destroy stuff belonging to that [09:33] but nope. it just went ahead and dropped the entire cluster [09:34] That's why it gives you a warning :) [09:35] Oh, but it lies. [09:35] Because it indeed drops the whole cluster. [09:35] 'THIS SCRIPT WILL DESTROY ALL POSTGRESQL DATA for the given user' [09:36] That isn't a lie, it just doesn't tell you everything [09:36] I gave it a non-existent user as an argument, lol [09:37] welp, at leasts the tests are running correctly now [09:37] stub: Is there any way we can tell what's reading 1.5 million branch tuples/sec? [09:37] That seems pretty unlikely to be legit. [09:39] wgrant: Those stats are not linked to particular queries or connections unfortunately. [09:39] Yeah. [09:39] Will see if the go away in 15 minutes when everything's disabled. [09:39] wgrant: The only way I can think of would be to turn on statement + plan logging and analyze that. [09:39] That will narrow it down to the webapp. === almaisan-away is now known as al-maisan [09:46] stub: Ah, thanks for the approval. [09:55] Unless it's translations_import_queue_gardener it must be the webapp... [10:07] Folks, I know I'm being dumb, but what do I need to do to make this go away: [10:07] Traceback (most recent call last): [10:07] File "utilities/js-deps", line 4, in [10:07] from convoy.meta import main [10:07] ImportError: No module named convoy.meta [10:07] (when running `make`) [10:08] Upgrade your dependencies [10:08] StevenK, That's what I thought... I'll try again. [10:08] gmb: apt dependencies [10:08] Make sure launchpad-developer-dependencies is installed [10:08] And that you're not running natty, which IIRC you are. [10:08] Ahahahahahahhaahahasdhashdaqhrwuqwhrquiwhrqui3hr1ui32hr1ui3rh13iurh3stabstabstabstabdie [10:08] Haha [10:08] wgrant, I'm on Precise. [10:09] Oh, OK [10:09] For juju goodness. [10:09] And lxc [10:09] One of the other MacBookers was on Natty at the 'dome [10:09] and other crazy-ass stuff. [10:09] wgrant, Yeah, but I'm running in a VM; I'm too lazy to do the work to get Ubuntu working properly on the metal. [10:09] Heh [10:11] And whaddaya know, lp-dev-deps isn't installed any more. [10:11] Goodness knows why. [10:11] Thanks fellas. [10:15] gmb: release upgrade removes it [10:16] bigjools, Yeah, I must've forgotten to re-add it afterwards. [10:52] bigjools: testtools 0.9.14 released, fixing the subunit incompatibility. [10:53]