[PATCH/dashboard 0/2] Performance enhancements

[Ian Ward Comfort]

Here are two small patches which significantly enhance Dashboard's performance
in our environment, especially with many reports. I'm not really a Rails
developer, so expert scrutiny is advised.

These patches can also be pulled from:

git://github.com/icomfort/puppet-dashboard.git performance

--
Ian Ward Comfort <icom...@stanford.edu>
Systems Team Lead, Academic Computing Services, Stanford University

app/controllers/pages_controller.rb | 1 -
.../20100811204545_add_success_to_report_index.rb | 11 +++++++++++
db/schema.rb | 4 ++--
3 files changed, 13 insertions(+), 3 deletions(-)

[Ian Ward Comfort]

Now that the sidebar shows "Ever succeeded" and "Ever failed" statistics, most
page loads generate SQL of the form:

SELECT `nodes`.* FROM `nodes` INNER JOIN `reports`
ON reports.node_id = nodes.id WHERE (reports.success = 1)
GROUP BY nodes.id;

Adjusting the index to reports(node_id,success) allows this query to run
entirely from the index, and speeds up page loads significantly, especially
with large numbers of reports. In our environment, with 45 nodes and (a
relatively modest, recently-pruned) 49,348 reports, query times changed from:

Node Load (418.1ms)
Node Load (268.4ms)
Node Load (419.1ms)
Node Load (265.5ms)

to:

Node Load (156.9ms)
Node Load (6.3ms)
Node Load (157.7ms)
Node Load (6.3ms)
---

.../20100811204545_add_success_to_report_index.rb | 11 +++++++++++
db/schema.rb | 4 ++--

2 files changed, 13 insertions(+), 2 deletions(-)
create mode 100644 db/migrate/20100811204545_add_success_to_report_index.rb

diff --git a/db/migrate/20100811204545_add_success_to_report_index.rb b/db/migrate/20100811204545_add_success_to_report_index.rb
new file mode 100644
index 0000000..ab2618d
--- /dev/null
+++ b/db/migrate/20100811204545_add_success_to_report_index.rb
@@ -0,0 +1,11 @@
+class AddSuccessToReportIndex < ActiveRecord::Migration
+ def self.up
+ remove_index :reports, :node_id
+ add_index :reports, [:node_id, :success]
+ end
+
+ def self.down
+ remove_index :reports, [:node_id, :success]
+ add_index :reports, :node_id
+ end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 27a9963..28fe799 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -9,7 +9,7 @@
#
# It's strongly recommended to check this file into your version control system.

-ActiveRecord::Schema.define(:version => 20100726070117) do
+ActiveRecord::Schema.define(:version => 20100811204545) do

create_table "assignments", :force => true do |t|
t.integer "node_id"
@@ -88,7 +88,7 @@ ActiveRecord::Schema.define(:version => 20100726070117) do
t.datetime "time"
end

- add_index "reports", ["node_id"], :name => "index_reports_on_node_id"
+ add_index "reports", ["node_id", "success"], :name => "index_reports_on_node_id_and_success"
add_index "reports", ["time"], :name => "index_reports_on_time"

create_table "services", :force => true do |t|
--
1.7.2.1

[Ian Ward Comfort]

The @reports variable is not used, and (in at least some versions of Rails)
the :order => 'time ASC' used to generate it causes an expensive database
query which sorts by time first in ascending, then in descending order (due to
the ordering on Report's default_scope). Remove this query entirely to improve
performance.
---
app/controllers/pages_controller.rb | 1 -
1 files changed, 0 insertions(+), 1 deletions(-)

diff --git a/app/controllers/pages_controller.rb b/app/controllers/pages_controller.rb
index c1e79a8..6cc0276 100644
--- a/app/controllers/pages_controller.rb
+++ b/app/controllers/pages_controller.rb
@@ -1,7 +1,6 @@
class PagesController < ApplicationController
def home
@statuses = Status.by_interval(:limit => 30)
- @reports = Report.all(:limit => 20, :order => 'time ASC')

@currently_failing_nodes = Node.by_currentness_and_successfulness(true, false)
@unreported_nodes = Node.unreported
--
1.7.2.1

[Rein Henrichs]

+1, thanks Ward!
--

Rein Henrichs
http://puppetlabs.com

"We all know Linux is great...it does infinite loops in 5 seconds."
(Linus Torvalds about the superiority of Linux on the Amterdam
Linux Symposium)

[Igal Koshevoy]

Ian,

Many thanks for submitting these patches, improving the performance of
the Dashboard is a big deal and I'm glad to see this.

I’ll review your suggestions in the next few days and try to include
them in the next Puppet Dashboard release.

I've created a ticket for tracking this task at http://projects.puppetlabs.com/issues/4524
-- can you please add yourself as a watcher so we can continue the
discussion there?

-igal

On Aug 11, 2:38 pm, Ian Ward Comfort <icomf...@stanford.edu> wrote:
> Here are two small patches which significantly enhance Dashboard's performance
> in our environment, especially with many reports. I'm not really a Rails
> developer, so expert scrutiny is advised.
>
> These patches can also be pulled from:
>
>   git://github.com/icomfort/puppet-dashboard.git performance
>
> --

> Ian Ward Comfort <icomf...@stanford.edu>

[Ian Ward Comfort]

On 11 Aug 2010, at 5:18 PM, Igal Koshevoy wrote:
> Many thanks for submitting these patches, improving the performance of the Dashboard is a big deal and I'm glad to see this.
>
> I’ll review your suggestions in the next few days and try to include them in the next Puppet Dashboard release.
>
> I've created a ticket for tracking this task at http://projects.puppetlabs.com/issues/4524 -- can you please add yourself as a watcher so we can continue the discussion there?

Sure, done.

--
Ian Ward Comfort <icom...@stanford.edu>