Merge lp:~stub/launchpad/bugsummary into lp:launchpad/db-devel

Proposed by Stuart Bishop
Status: Merged
Approved by: Stuart Bishop
Approved revision: 10694
Merged at revision: 10832
Proposed branch: lp:~stub/launchpad/bugsummary
Merge into: lp:launchpad/db-devel
Prerequisite: lp:~stub/launchpad/db-cleanups
Diff against target: 176 lines (+160/-1)
2 files modified
database/schema/patch-2208-76-4.sql (+159/-0)
database/schema/security.cfg (+1/-1)
To merge this branch: bzr merge lp:~stub/launchpad/bugsummary
Reviewer Review Type Date Requested Status
Stuart Bishop (community) db Approve
Review via email: mp+69632@code.launchpad.net

Commit message

[r=stub][bug=810104] Speed up BugSummaryJournal rollup

Description of the change

= Summary =

Rolling up the BugSummaryJournal entries into the BugSummary table is slow.

== Proposed fix ==

Rewrite the logic in constructs that PG optimizes better in this case.

Move removal of 'count 0' rows to once per rollup, rather than once per decrement.

== Pre-implementation notes ==

== Implementation details ==

The old code is in patch-2208-75-0.sql

== Tests ==

== Demo and Q/A ==

= Launchpad lint =

Checking for conflicts and issues in changed files.

Linting changed files:
  database/schema/patch-2208-76-4.sql

To post a comment you must log in.
Revision history for this message
Stuart Bishop (stub) :
review: Approve (db)
lp:~stub/launchpad/bugsummary updated
10695. By Stuart Bishop

Add a batchsize for future garbo job

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== added file 'database/schema/patch-2208-76-4.sql'
--- database/schema/patch-2208-76-4.sql 1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-76-4.sql 2011-07-28 13:00:58 +0000
@@ -0,0 +1,159 @@
1-- Copyright 2011 Canonical Ltd. This software is licensed under the
2-- GNU Affero General Public License version 3 (see the file LICENSE).
3
4SET client_min_messages=ERROR;
5
6DROP FUNCTION bugsummary_rollup_journal();
7
8CREATE OR REPLACE FUNCTION bugsummary_rollup_journal(batchsize integer=NULL)
9RETURNS VOID
10LANGUAGE plpgsql VOLATILE
11CALLED ON NULL INPUT
12SECURITY DEFINER SET search_path TO public AS
13$$
14DECLARE
15 d bugsummary%ROWTYPE;
16 max_id integer;
17BEGIN
18 -- Lock so we don't content with other invokations of this
19 -- function. We can happily lock the BugSummary table for writes
20 -- as this function is the only thing that updates that table.
21 -- BugSummaryJournal remains unlocked so nothing should be blocked.
22 LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
23
24 IF batchsize IS NULL THEN
25 SELECT MAX(id) INTO max_id FROM BugSummaryJournal;
26 ELSE
27 SELECT MAX(id) INTO max_id FROM (
28 SELECT id FROM BugSummaryJournal ORDER BY id LIMIT batchsize
29 ) AS Whatever;
30 END IF;
31
32 FOR d IN
33 SELECT
34 NULL as id,
35 SUM(count),
36 product,
37 productseries,
38 distribution,
39 distroseries,
40 sourcepackagename,
41 viewed_by,
42 tag,
43 status,
44 milestone,
45 importance,
46 has_patch,
47 fixed_upstream
48 FROM BugSummaryJournal
49 WHERE id <= max_id
50 GROUP BY
51 product, productseries, distribution, distroseries,
52 sourcepackagename, viewed_by, tag, status, milestone,
53 importance, has_patch, fixed_upstream
54 HAVING sum(count) <> 0
55 LOOP
56 IF d.count < 0 THEN
57 PERFORM bug_summary_dec(d);
58 ELSIF d.count > 0 THEN
59 PERFORM bug_summary_inc(d);
60 END IF;
61 END LOOP;
62
63 -- Clean out any counts we reduced to 0.
64 DELETE FROM BugSummary WHERE count=0;
65 -- Clean out the journal entries we have handled.
66 DELETE FROM BugSummaryJournal WHERE id <= max_id;
67END;
68$$;
69
70COMMENT ON FUNCTION bugsummary_rollup_journal(integer) IS
71'Collate and migrate rows from BugSummaryJournal to BugSummary';
72
73
74CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary) RETURNS VOID
75LANGUAGE SQL AS
76$$
77 -- We own the row reference, so in the absence of bugs this cannot
78 -- fail - just decrement the row.
79 UPDATE BugSummary SET count = count + $1.count
80 WHERE
81 ((product IS NULL AND $1.product IS NULL)
82 OR product = $1.product)
83 AND ((productseries IS NULL AND $1.productseries IS NULL)
84 OR productseries = $1.productseries)
85 AND ((distribution IS NULL AND $1.distribution IS NULL)
86 OR distribution = $1.distribution)
87 AND ((distroseries IS NULL AND $1.distroseries IS NULL)
88 OR distroseries = $1.distroseries)
89 AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
90 OR sourcepackagename = $1.sourcepackagename)
91 AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
92 OR viewed_by = $1.viewed_by)
93 AND ((tag IS NULL AND $1.tag IS NULL)
94 OR tag = $1.tag)
95 AND status = $1.status
96 AND ((milestone IS NULL AND $1.milestone IS NULL)
97 OR milestone = $1.milestone)
98 AND importance = $1.importance
99 AND has_patch = $1.has_patch
100 AND fixed_upstream = $1.fixed_upstream;
101$$;
102
103CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary) RETURNS VOID
104LANGUAGE plpgsql AS
105$$
106BEGIN
107 -- Shameless adaption from postgresql manual
108 LOOP
109 -- first try to update the row
110 UPDATE BugSummary SET count = count + d.count
111 WHERE
112 ((product IS NULL AND $1.product IS NULL)
113 OR product = $1.product)
114 AND ((productseries IS NULL AND $1.productseries IS NULL)
115 OR productseries = $1.productseries)
116 AND ((distribution IS NULL AND $1.distribution IS NULL)
117 OR distribution = $1.distribution)
118 AND ((distroseries IS NULL AND $1.distroseries IS NULL)
119 OR distroseries = $1.distroseries)
120 AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
121 OR sourcepackagename = $1.sourcepackagename)
122 AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
123 OR viewed_by = $1.viewed_by)
124 AND ((tag IS NULL AND $1.tag IS NULL)
125 OR tag = $1.tag)
126 AND status = $1.status
127 AND ((milestone IS NULL AND $1.milestone IS NULL)
128 OR milestone = $1.milestone)
129 AND importance = $1.importance
130 AND has_patch = $1.has_patch
131 AND fixed_upstream = $1.fixed_upstream;
132 IF found THEN
133 RETURN;
134 END IF;
135 -- not there, so try to insert the key
136 -- if someone else inserts the same key concurrently,
137 -- we could get a unique-key failure
138 BEGIN
139 INSERT INTO BugSummary(
140 count, product, productseries, distribution,
141 distroseries, sourcepackagename, viewed_by, tag,
142 status, milestone,
143 importance, has_patch, fixed_upstream)
144 VALUES (
145 d.count, d.product, d.productseries, d.distribution,
146 d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
147 d.status, d.milestone,
148 d.importance, d.has_patch, d.fixed_upstream);
149 RETURN;
150 EXCEPTION WHEN unique_violation THEN
151 -- do nothing, and loop to try the UPDATE again
152 END;
153 END LOOP;
154END;
155$$;
156
157
158
159INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 76, 4);
0160
=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg 2011-07-25 08:48:47 +0000
+++ database/schema/security.cfg 2011-07-28 13:00:58 +0000
@@ -143,7 +143,7 @@
143public.bugnotificationrecipientarchive = SELECT, UPDATE143public.bugnotificationrecipientarchive = SELECT, UPDATE
144public.bugsummary = SELECT144public.bugsummary = SELECT
145public.bugsummaryjournal = SELECT145public.bugsummaryjournal = SELECT
146public.bugsummary_rollup_journal() = EXECUTE146public.bugsummary_rollup_journal(integer) = EXECUTE
147public.bugtag = SELECT, INSERT, DELETE147public.bugtag = SELECT, INSERT, DELETE
148public.bugtrackercomponent = SELECT, INSERT, UPDATE148public.bugtrackercomponent = SELECT, INSERT, UPDATE
149public.bugtrackercomponentgroup = SELECT, INSERT, UPDATE149public.bugtrackercomponentgroup = SELECT, INSERT, UPDATE

Subscribers

People subscribed via source and target branches

to status/vote changes: