At the moment I have 288 hosts being managed with puppet + foreman.
Each host is running the puppet client with 30 minutes frequency. So,
per day, each machine generate 48 records in the report table. At the
end of a month it should be around 414720 records.
With almost 240000 records in the report table, the report's page
response time is 238 seconds!! Am I missing something? Is there
anything that I can do to improve the report's page performance?
Processing ReportsController#index (for 127.0.0.1 at 2011-02-14
12:12:47) [GET]
Parameters: {"action"=>"index", "controller"=>"reports"}
Rendering template within layouts/standard
Rendering reports/index
Completed in 238291ms (View: 246, DB: 238037) | 200 OK [http://abc/
reports]
Don't forget to consider, that the number of hosts being managed is
expected to grow over time…
are there a lot of changes on each node? Otherwise you might want to expire
successful/uninteresting reports from your database using the appropriate rake
task on a daily basis, see 1.
Regards,
Jochen
Reports-automatically
···
On Monday, February 14, 2011, 17:33:45 Gus wrote:
> Hi all!
>
> At the moment I have 288 hosts being managed with puppet + foreman.
> Each host is running the puppet client with 30 minutes frequency. So,
> per day, each machine generate 48 records in the report table. At the
> end of a month it should be around 414720 records.
>
> With almost 240000 records in the report table, the report's page
> response time is 238 seconds!! Am I missing something? Is there
> anything that I can do to improve the report's page performance?
>
> Processing ReportsController#index (for 127.0.0.1 at 2011-02-14
> 12:12:47) [GET]
> Parameters: {"action"=>"index", "controller"=>"reports"}
> Rendering template within layouts/standard
> Rendering reports/index
> Completed in 238291ms (View: 246, DB: 238037) | 200 OK [http://abc/
> reports]
>
>
> Don't forget to consider, that the number of hosts being managed is
> expected to grow over time...
>
> Thanks in advance,
> Gus
This sounds fairly wrong… we have a few large installations
(1000-4000) nodes with out any major performance issues.
more than that, when you view the reports page, you usually don't view
all hosts, rather a small subset (per page). so I can't see any reason
why performance would be impacted so much.
which database are you using?
Ohad
···
On Mon, 2011-02-14 at 08:33 -0800, Gus wrote:
> Hi all!
>
> At the moment I have 288 hosts being managed with puppet + foreman.
> Each host is running the puppet client with 30 minutes frequency. So,
> per day, each machine generate 48 records in the report table. At the
> end of a month it should be around 414720 records.
>
> With almost 240000 records in the report table, the report's page
> response time is 238 seconds!! Am I missing something? Is there
> anything that I can do to improve the report's page performance?
Processing ReportsController#index (for 127.0.0.1 at 2011-02-14
12:12:47) [GET]
Parameters: {“action”=>“index”, “controller”=>“reports”}
Rendering template within layouts/standard
Rendering reports/index
Completed in 238291ms (View: 246, DB: 238037) | 200 OK [http://abc/
reports]
If yes, switch to mysql and tune it a little.
We are doing Ok with million+ reports.
Ashay
···
On Mon, Feb 14, 2011 at 9:37 AM, Jochen Schalanda wrote:
> Hi Gus,
>
> are there a lot of changes on each node? Otherwise you might want to expire
> successful/uninteresting reports from your database using the appropriate rake
> task on a daily basis, see [1].
>
>
> Regards,
> Jochen
>
>
> [1]: http://theforeman.org/projects/foreman/wiki/Puppet_Reports#Expire-
> Reports-automatically
>
> On Monday, February 14, 2011, 17:33:45 Gus wrote:
>> Hi all!
>>
>> At the moment I have 288 hosts being managed with puppet + foreman.
>> Each host is running the puppet client with 30 minutes frequency. So,
>> per day, each machine generate 48 records in the report table. At the
>> end of a month it should be around 414720 records.
>>
>> With almost 240000 records in the report table, the report's page
>> response time is 238 seconds!! Am I missing something? Is there
>> anything that I can do to improve the report's page performance?
>>
>> Processing ReportsController#index (for 127.0.0.1 at 2011-02-14
>> 12:12:47) [GET]
>> Parameters: {"action"=>"index", "controller"=>"reports"}
>> Rendering template within layouts/standard
>> Rendering reports/index
>> Completed in 238291ms (View: 246, DB: 238037) | 200 OK [http://abc/
>> reports]
>>
>>
>> Don't forget to consider, that the number of hosts being managed is
>> expected to grow over time...
>>
>> Thanks in advance,
>> Gus
>
> --
> You received this message because you are subscribed to the Google Groups "Foreman users" group.
> To post to this group, send email to foreman-users@googlegroups.com.
> To unsubscribe from this group, send email to foreman-users+unsubscribe@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/foreman-users?hl=en.
>
>
At the moment I have 288 hosts being managed with puppet + foreman.
Each host is running the puppet client with 30 minutes frequency. So,
per day, each machine generate 48 records in the report table. At the
end of a month it should be around 414720 records.
With almost 240000 records in the report table, the report’s page
response time is 238 seconds!! Am I missing something? Is there
anything that I can do to improve the report’s page performance?
Processing ReportsController#index (for 127.0.0.1 at 2011-02-14
12:12:47) [GET]
Parameters: {“action”=>“index”, “controller”=>“reports”}
Rendering template within layouts/standard
Rendering reports/index
Completed in 238291ms (View: 246, DB: 238037) | 200 OK [http://abc/
reports]
This sounds fairly wrong… we have a few large installations
(1000-4000) nodes with out any major performance issues.
more than that, when you view the reports page, you usually don’t view
all hosts, rather a small subset (per page). so I can’t see any reason
why performance would be impacted so much.
>
>
> I am using mysql 5.1.
>
>
> Here are the queries made (log set to debug):
>
>
> Processing ReportsController#index (for x.x.x.x at 2011-02-14
> 15:00:04) [GET]
> Parameters: {"action"=>"index", "controller"=>"reports"}
> User Load (0.4ms) SELECT * FROM users WHERE (users.id = 2)
> SQL (95557.8ms) SELECT count(*) AS count_all FROM reports
this is interesting, I wonder why would count take such a long time…
usually count + join takes a long time, and in that case you can add a
counter value to the join object (such as a host has xxx reports instead
of counting in the db) but in this case… hmm
> CACHE (0.0ms) SELECT * FROM users WHERE (users.id = 2)
> Report Load (17988.2ms) SELECT * FROM reports ORDER BY
> reports.created_at DESC LIMIT 0, 20
Again, this should not take that long… one possible explanation is
that at some point of time we changed to look at the created_at
timestamp instead of reported_at timestamp (because some machine had
wrong clock information). so it could be that we missed adding an index
on that column.
> Host Load (4.1ms) SELECT * FROM hosts WHERE (hosts.id IN
> (579,623,337,437,580,613,581,471,615,582,472,572,616,583,617,441,299,576,422,213))
> Domain Load (0.5ms) SELECT * FROM domains WHERE (domains.id
> = 2)
> CACHE (0.0ms) SELECT count(*) AS count_all FROM reports
> Rendering template within layouts/standard
> Rendering reports/index
> Rendered reports/_list (223.8ms)
> Rendered home/_topbar (0.3ms)
> Rendered home/_menu (0.5ms)
> Report Load (1.3ms) SELECT created_at FROM reports LIMIT 1
> Rendered reports/_search_line (11.6ms)
> Rendered common/_searchbar (13.3ms)
>
>
Anyone with better MySQL foo has some other ideas?
which kind of sql teaks do you guys use?
Ohad
···
On Mon, 2011-02-14 at 16:00 -0200, Gustavo Soares wrote:
>
>
> Isn't the query SELECT count(*) AS count_all FROM `reports` used by
> the pagination plugin to get the total of pages?
>
>
> Cheers,
> Gus
>
> On Mon, Feb 14, 2011 at 3:51 PM, Ohad Levy wrote:
> On Mon, 2011-02-14 at 08:33 -0800, Gus wrote:
> > Hi all!
> >
> > At the moment I have 288 hosts being managed with puppet +
> foreman.
> > Each host is running the puppet client with 30 minutes
> frequency. So,
> > per day, each machine generate 48 records in the report
> table. At the
> > end of a month it should be around 414720 records.
> >
> > With almost 240000 records in the report table, the report's
> page
> > response time is 238 seconds!! Am I missing something? Is
> there
> > anything that I can do to improve the report's page
> performance?
>
> > Processing ReportsController#index (for 127.0.0.1 at
> 2011-02-14
> > 12:12:47) [GET]
> > Parameters: {"action"=>"index", "controller"=>"reports"}
> > Rendering template within layouts/standard
> > Rendering reports/index
> > Completed in 238291ms (View: 246, DB: 238037) | 200 OK
> [http://abc/
> > reports]
>
>
>
> This sounds fairly wrong... we have a few large installations
> (1000-4000) nodes with out any major performance issues.
>
> more than that, when you view the reports page, you usually
> don't view
> all hosts, rather a small subset (per page). so I can't see
> any reason
> why performance would be impacted so much.
>
> which database are you using?
>
> Ohad
>
> --
>
> You received this message because you are subscribed to the
> Google Groups "Foreman users" group.
> To post to this group, send email to
> foreman-users@googlegroups.com.
> To unsubscribe from this group, send email to foreman-users
> +unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/foreman-users?hl=en.
>
>
>
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Foreman users" group.
> To post to this group, send email to foreman-users@googlegroups.com.
> To unsubscribe from this group, send email to foreman-users
> +unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/foreman-users?hl=en.
Am Montag, 14. Februar 2011, 19:12:35 schrieb Ohad Levy:
Parameters: {“action”=>“index”, “controller”=>“reports”}
User Load (0.4ms) SELECT * FROM users WHERE (users.id = 2)
SQL (95557.8ms) SELECT count(*) AS count_all FROM reports
this is interesting, I wonder why would count take such a long time…
This is a known problem with InnoDB tables in MySQL. In order to calculate
the
result of COUNT(*), MySQL will perform an index scan. Every time. MySQL
won’t
use an existing index for that if you don’t tell it to use it (by appending
"use index($index_name)" to the SELECT query).
Report Load (17988.2ms) SELECT * FROM reports ORDER BY
reports.created_at DESC LIMIT 0, 20
Gus, please check if all indices for the table reports have been created
by
the DB migrations and send the output of the query "EXPLAIN reports".
Also the results of "EXPLAIN SELECT count(*) AS count_all FROM reports"
and
"SELECT * FROM reports ORDER BY reports.created_at DESC LIMIT 0, 20"
might
be useful.
> > Parameters: {"action"=>"index", "controller"=>"reports"}
> > User Load (0.4ms) SELECT * FROM users WHERE (users.id = 2)
> > SQL (95557.8ms) SELECT count(*) AS count_all FROM reports
>
> this is interesting, I wonder why would count take such a long time…
This is a known problem with InnoDB tables in MySQL. In order to calculate the
result of COUNT(*), MySQL will perform an index scan. Every time. MySQL won't
use an existing index for that if you don't tell it to use it (by appending
"use index($index_name)" to the SELECT query).
> > Report Load (17988.2ms) SELECT * FROM reports ORDER BY
> > reports.created_at DESC LIMIT 0, 20
Gus, please check if all indices for the table reports have been created by
the DB migrations and send the output of the query "EXPLAIN reports".
Also the results of "EXPLAIN SELECT count(*) AS count_all FROM reports" and
"SELECT * FROM reports ORDER BY reports.created_at DESC LIMIT 0, 20" might
be useful.
Regards
Jochen
···
Am Montag, 14. Februar 2011, 19:12:35 schrieb Ohad Levy:
>
> Can I add "use index($index_name)" to every select count made by the
> pagination plugin (will_paginate???) ?
>
> Here is the query made with the apropriated index…
>
> > select count() from reports use index(index_reports_on_host_id);
> ±---------+
> > count() |
> ±---------+
> > 238251 |
> ±---------+
> 1 row in set (0.40 sec)
>
> without the index
>
> > select count() from reports;
> ±---------+
> > count() |
> ±---------+
> > 237723 |
> ±---------+
> 1 row in set (2 min 53.57 sec)
>
>
> Gus
>
> While not exactly solving the issue, can you try this simple diff to see if
cuts most of the time?
— a/config/initializers/foreman.rb
+++ b/config/initializers/foreman.rb
@@ -79,7 +79,7 @@ class ActiveRecord::Base
end
def self.unconfigured?
count == 0
first.nil?
end
end
···
On Mon, Feb 14, 2011 at 9:24 PM, Gustavo Soares wrote:
Am Montag, 14. Februar 2011, 19:12:35 schrieb Ohad Levy:
Parameters: {“action”=>“index”, “controller”=>“reports”}
User Load (0.4ms) SELECT * FROM users WHERE (users.id = 2)
SQL (95557.8ms) SELECT count(*) AS count_all FROM reports
this is interesting, I wonder why would count take such a long time…
This is a known problem with InnoDB tables in MySQL. In order to calculate
the
result of COUNT(*), MySQL will perform an index scan. Every time. MySQL
won’t
use an existing index for that if you don’t tell it to use it (by
appending
"use index($index_name)" to the SELECT query).
Report Load (17988.2ms) SELECT * FROM reports ORDER BY
reports.created_at DESC LIMIT 0, 20
Gus, please check if all indices for the table reports have been created
by
the DB migrations and send the output of the query "EXPLAIN reports".
Also the results of "EXPLAIN SELECT count(*) AS count_all FROM reports"
and
"SELECT * FROM reports ORDER BY reports.created_at DESC LIMIT 0, 20"
might
be useful.
Try adding the following migration (which adds the correct index)
db/migrate/20110215121521_add_status_and_created_at_index_to
class AddStatusAndCreatedAtIndexToReports < ActiveRecord::Migration
def self.up
add_index :reports, [:created_at, :status]
end
def self.down
remove_index :reports, [:created_at, :status]
end
end
Ohad
···
On Mon, 2011-02-14 at 17:24 -0200, Gustavo Soares wrote:
>
>
> Can I add "use index($index_name)" to every select count made by the
> pagination plugin (will_paginate????) ?
>
>
> Here is the query made with the apropriated index...
>
>
> > select count(*) from reports use
> index(index_reports_on_host_id);
> +----------+
> > count(*) |
> +----------+
> > 238251 |
> +----------+
> 1 row in set (0.40 sec)
>
>
> without the index
>
>
> > select count(*) from reports;
> +----------+
> > count(*) |
> +----------+
> > 237723 |
> +----------+
> 1 row in set (2 min 53.57 sec)
>
>
>
>
Can I add “use index($index_name)” to every select count made by the
pagination plugin (will_paginate???) ?
Here is the query made with the apropriated index…
select count() from reports use index(index_reports_on_host_id);
±---------+
count() |
±---------+
238251 |
±---------+
1 row in set (0.40 sec)
without the index
select count() from reports;
±---------+
count() |
±---------+
237723 |
±---------+
1 row in set (2 min 53.57 sec)
Gus
While not exactly solving the issue, can you try this simple diff to see
if cuts most of the time?
— a/config/initializers/foreman.rb
+++ b/config/initializers/foreman.rb
@@ -79,7 +79,7 @@ class ActiveRecord::Base
end
def self.unconfigured?
count == 0
first.nil?
end
end
On Mon, Feb 14, 2011 at 4:41 PM, Jochen Schalanda <jochen@schalanda.name >> > wrote:
Hi,
Am Montag, 14. Februar 2011, 19:12:35 schrieb Ohad Levy:
Parameters: {“action”=>“index”, “controller”=>“reports”}
User Load (0.4ms) SELECT * FROM users WHERE (users.id = 2)
SQL (95557.8ms) SELECT count(*) AS count_all FROM reports
this is interesting, I wonder why would count take such a long time…
This is a known problem with InnoDB tables in MySQL. In order to
calculate the
result of COUNT(*), MySQL will perform an index scan. Every time. MySQL
won’t
use an existing index for that if you don’t tell it to use it (by
appending
"use index($index_name)" to the SELECT query).
Report Load (17988.2ms) SELECT * FROM reports ORDER BY
reports.created_at DESC LIMIT 0, 20
Gus, please check if all indices for the table reports have been
created by
the DB migrations and send the output of the query "EXPLAIN reports".
Also the results of “EXPLAIN SELECT count(*) AS count_all FROM reports” and
"SELECT * FROM reports ORDER BY reports.created_at DESC LIMIT 0, 20"
might
be useful.
Since there aren't any update or fk in the report and audit table (mainly
inserts and selects), the dba guy suggested to change the engine type of
both tables to myisam. This made all the difference.
···
> select count(*) from reports;
±---------+
count(*) |
±---------+
64490 |
±---------+
1 row in set (0.02 sec)
On Tue, 2011-02-15 at 10:23 -0200, Gustavo Soares wrote:
the diff hasn’t made any difference…
Please try the other email I’ve sent with the indexes… or you can
just join our irc channel (#theforeman on freenode) and we could talk
there more.
