Re: [GSoC] Profiling SQL query (Was: [GSoC] Reg Blends Web Sentinel)

[Akshita Jha <zenith158@gmail.com>]

On Thu, Mar 12, 2015 at 8:30 PM, Andreas Tille <andreas@an3as.eu> wrote:

I think your hint was of great help!  Droping the GROUP BY increased the
speed by an order of magnitude.  I also droped the other similarly
redundant GROUP BY (see my last commit).  This has caused a drastical
performance kick. if you compare the old result:


$ cat logs/*bugs_udd.out
...
Time: 281.581650019
Query: EXECUTE query_bug_packages('debian-games')
Rendering bugs from UDD for Blend 'debian-games' took 299 seconds
Time: 34.5106248856
Query: EXECUTE query_bug_packages('debian-gis')
Rendering bugs from UDD for Blend 'debian-gis' took 37 seconds
...
Time: 868.058537006
Query: EXECUTE query_bug_packages('debian-junior')
Rendering bugs from UDD for Blend 'debian-junior' took 892 seconds
...
Rendering bugs from UDD for Blend 'debian-med' took 27 seconds
Time: 193.018954039
Query: EXECUTE query_bug_packages('debian-multimedia')
Rendering bugs from UDD for Blend 'debian-multimedia' took 198 seconds
...
Time: 183.071342945
Query: EXECUTE query_bug_packages('debian-science')
Rendering bugs from UDD for Blend 'debian-science' took 194 seconds


cat logs/*bugs_udd.out
...
Rendering bugs from UDD for Blend 'debian-games' took 57 seconds
Rendering bugs from UDD for Blend 'debian-gis' took 9 seconds
Time: 70.1499569416
Query: EXECUTE query_bug_packages('debian-junior')
Rendering bugs from UDD for Blend 'debian-junior' took 86 seconds
...
Rendering bugs from UDD for Blend 'debian-med' took 28 seconds
Rendering bugs from UDD for Blend 'debian-multimedia' took 28 seconds
...
Rendering bugs from UDD for Blend 'debian-science' took 31 seconds


So if you compare the time from Debian Junior 892 versus 86 seconds
that's the amount of time which is perfectly acceptable.

Now I was also able to run debian-edu on my box in a sensible time
frame:

Time: 178.415522814
Query: EXECUTE query_bugs('debian-edu')
Time: 1252.45552516
Query: EXECUTE query_bug_packages('debian-edu')
Rendering bugs from UDD for Blend 'debian-edu' took 1437 seconds

I think this would be also acceptable to run on production UDD.
However, I wonder whether you could do some further review after this
issue should be clarified.  Perhaps we get another factor 2 or 4 for
speeding up to make sure we will not uselessly stress test Debian
hardware.

I have been working to improve the performance of "query_bug_packages".

I tried using array_agg(DISTINCT ....)[1] instead of "PARTITION BY() then selecting row_number()=1". Also, I tried selecting the MAX() and MIN() after using "ORDER BY s.version and bdp.property" respectively in the two sub-queries. However, the timing after using both the above, increased as the issue here was that we needed to use GROUP BY ,and GROUP BY is really expensive.

I also tried converting 'version' to binary by using 'strip_binary_upload' but here also the timing increased slightly.

Do you have any other ideas in mind that I can experiment with? 

Thanking You,

Akshita Jha