launchpad-reviewers team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

Stuart Bishop has proposed merging lp:~stub/launchpad/db-stats into lp:launchpad.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #588673 in Launchpad itself: "update-database-stats.py explodes on production"
  https://bugs.launchpad.net/launchpad/+bug/588673
  Bug #590684 in Launchpad itself: "report-database-stats.py gives bogus cpu utilization"
  https://bugs.launchpad.net/launchpad/+bug/590684
  Bug #757298 in Launchpad itself: "database bloat report ordering needs tuning"
  https://bugs.launchpad.net/launchpad/+bug/757298

For more details, see:
https://code.launchpad.net/~stub/launchpad/db-stats/+merge/57160

Fix ordering of database bloat report
-- 
https://code.launchpad.net/~stub/launchpad/db-stats/+merge/57160
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~stub/launchpad/db-stats into lp:launchpad.

=== modified file 'utilities/report-database-stats.py'
--- utilities/report-database-stats.py	2011-03-28 06:14:46 +0000
+++ utilities/report-database-stats.py	2011-04-11 13:14:47 +0000
@@ -165,8 +165,9 @@
         # We only collect these statistics daily, so add some fuzz
         # to ensure bloat information ends up on the daily reports;
         # we cannot guarantee the disk utilization statistics occur
-        # exactly 24 hours apart.
-        'where': get_where_clause(options, fuzz='6 hours'),
+        # exactly 24 hours apart. Our most recent snapshot could be 1
+        # day ago, give or take a few hours.
+        'where': get_where_clause(options, fuzz='1 day 6 hours'),
         'bloat': options.bloat,
         'min_bloat': options.min_bloat,
         'kind': kind,
@@ -181,6 +182,7 @@
                 count(*) OVER t AS num_samples,
                 last_value(table_len) OVER t AS table_len,
                 pg_size_pretty(last_value(table_len) OVER t) AS table_size,
+                last_value(dead_tuple_len + free_space) OVER t AS bloat_len,
                 pg_size_pretty(last_value(dead_tuple_len + free_space) OVER t)
                     AS bloat_size,
                 first_value(dead_tuple_percent + free_percent) OVER t
@@ -206,7 +208,7 @@
         WHERE
             table_len >= %(min_bloat)s
             AND end_bloat_percent >= %(bloat)s
-        ORDER BY bloat_size DESC
+        ORDER BY bloat_len DESC
         """ % params)
     cur.execute(query, params)
     bloat_stats = named_fetchall(cur)
@@ -316,7 +318,7 @@
         print "== Most Bloated Indexes =="
         print
         for bloated_index in index_bloat_stats[:options.limit]:
-            print "%40s || %2d%% || %s of %s" % (
+            print "%65s || %2d%% || %s of %s" % (
                 bloated_index.sub_name,
                 bloated_index.end_bloat_percent,
                 bloated_index.bloat_size,
@@ -359,7 +361,7 @@
                 # Bloat decreases are uninteresting, and would need to be in
                 # a separate table sorted in reverse anyway.
                 if bloated_index.delta_bloat_percent > 0:
-                    print "%40s || +%4.2f%% || +%s" % (
+                    print "%65s || +%4.2f%% || +%s" % (
                         bloated_index.sub_name,
                         bloated_index.delta_bloat_percent,
                         bloated_index.delta_bloat_size)