Puppet users - Aug 2011 - Dashboard table resource_statuses growing uncontrollably

I''ve "inherited" the administration of a puppet-dashboard
(version
1.1.0, installed on RHEL 5.6 from puppetlabs RPM), and have hit a
problem I''m hoping for some help with.

In short, one table, "resource_statuses" appears to be growing at a
rate far higher then the other tables:

mysql> select count(*) from nodes;
+----------+
| count(*) |
+----------+
|      107 |
+----------+

mysql> select count(*) from resource_statuses;
+-----------+
| count(*)  |
+-----------+
| 188068011 |
+-----------+

Yeah, that''s over 188 million rows.  Currently, the entire mysql data
directory for the dashboard database is taking up 51 GB; the
resource_statuses.MYD file is 47 GB of that.

We''ve got a weekly cron job that runs
"/usr/share/puppet-dashboard/
Rakefile RAILS_ENV=production reports:prune upto=2 unit=wk", and
I''ve
run it manually, but it doesn''t seem to impact that table.

Is there a "safe" way to reduce the number of rows in that table, and
limit it to keeping either X number of rows, or rows going back only
to a specific timespan (like, 2 weeks)?

Thanks in advance!

	-- Sandy

-- 
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To post to this group, send email to puppet-users@googlegroups.com.
To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.

There should probably be a rake task for this (tbh, all that stuff should be
rewritten, loading the whole Rails env to do it is ...ugly), but you can
probably (caveat emptor, I don''t know this table very well) do
something
like

*delete from resource_statuses where time <= date_sub(now(), interval 2
weeks)*

That''d delete rows older than two weeks.

-scott

On Tue, Aug 16, 2011 at 2:34 PM, Sandor W. Sklar <ssklar@gmail.com> wrote:
> I''ve "inherited" the administration of a
puppet-dashboard (version
> 1.1.0, installed on RHEL 5.6 from puppetlabs RPM), and have hit a
> problem I''m hoping for some help with.
>
> In short, one table, "resource_statuses" appears to be growing at
a
> rate far higher then the other tables:
>
> mysql> select count(*) from nodes;
> +----------+
> | count(*) |
> +----------+
> |      107 |
> +----------+
>
> mysql> select count(*) from resource_statuses;
> +-----------+
> | count(*)  |
> +-----------+
> | 188068011 |
> +-----------+
>
> Yeah, that''s over 188 million rows.  Currently, the entire mysql
data
> directory for the dashboard database is taking up 51 GB; the
> resource_statuses.MYD file is 47 GB of that.
>
> We''ve got a weekly cron job that runs
"/usr/share/puppet-dashboard/
> Rakefile RAILS_ENV=production reports:prune upto=2 unit=wk", and
I''ve
> run it manually, but it doesn''t seem to impact that table.
>
> Is there a "safe" way to reduce the number of rows in that table,
and
> limit it to keeping either X number of rows, or rows going back only
> to a specific timespan (like, 2 weeks)?
>
> Thanks in advance!
>
>        -- Sandy
>
> --
> You received this message because you are subscribed to the Google Groups
> "Puppet Users" group.
> To post to this group, send email to puppet-users@googlegroups.com.
> To unsubscribe from this group, send email to
> puppet-users+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/puppet-users?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To post to this group, send email to puppet-users@googlegroups.com.
To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.

Thanks, Scott.  After posting this, I did some googling and pretty much came up
with the same solution.  Unfortunately, because we''re using ISAM
tables, the only way I know of to reclaim the space used by the datafiles is to
do a mysqldump and then restore after clearing out the older reports.

	-- Sandy

On Aug 16, 2011, at 5:44 PM, Scott Smith wrote:
> There should probably be a rake task for this (tbh, all that stuff should
be rewritten, loading the whole Rails env to do it is ...ugly), but you can
probably (caveat emptor, I don''t know this table very well) do
something like
> 
> delete from resource_statuses where time <= date_sub(now(), interval 2
weeks)
> 
> That''d delete rows older than two weeks.
> 
> -scott
> 
> On Tue, Aug 16, 2011 at 2:34 PM, Sandor W. Sklar <ssklar@gmail.com>
wrote:
> I''ve "inherited" the administration of a
puppet-dashboard (version
> 1.1.0, installed on RHEL 5.6 from puppetlabs RPM), and have hit a
> problem I''m hoping for some help with.
> 
> In short, one table, "resource_statuses" appears to be growing at
a
> rate far higher then the other tables:
> 
> mysql> select count(*) from nodes;
> +----------+
> | count(*) |
> +----------+
> |      107 |
> +----------+
> 
> mysql> select count(*) from resource_statuses;
> +-----------+
> | count(*)  |
> +-----------+
> | 188068011 |
> +-----------+
> 
> Yeah, that''s over 188 million rows.  Currently, the entire mysql
data
> directory for the dashboard database is taking up 51 GB; the
> resource_statuses.MYD file is 47 GB of that.
> 
> We''ve got a weekly cron job that runs
"/usr/share/puppet-dashboard/
> Rakefile RAILS_ENV=production reports:prune upto=2 unit=wk", and
I''ve
> run it manually, but it doesn''t seem to impact that table.
> 
> Is there a "safe" way to reduce the number of rows in that table,
and
> limit it to keeping either X number of rows, or rows going back only
> to a specific timespan (like, 2 weeks)?
> 
> Thanks in advance!
> 
>        -- Sandy
> 
> --
> You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
> To post to this group, send email to puppet-users@googlegroups.com.
> To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
> 
> 
-- 
Sandor W. Sklar
Unix Systems Administrator
Digital Libraries Systems & Services
Stanford University Libraries

-- 
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To post to this group, send email to puppet-users@googlegroups.com.
To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.

Try running optimize on it.

-scott

On Tue, Aug 16, 2011 at 5:53 PM, Sandor W. Sklar <ssklar@gmail.com> wrote:
> Thanks, Scott.  After posting this, I did some googling and pretty much
> came up with the same solution.  Unfortunately, because we''re
using ISAM
> tables, the only way I know of to reclaim the space used by the datafiles
is
> to do a mysqldump and then restore after clearing out the older reports.
>
>        -- Sandy
>
> On Aug 16, 2011, at 5:44 PM, Scott Smith wrote:
>
> > There should probably be a rake task for this (tbh, all that stuff
should
> be rewritten, loading the whole Rails env to do it is ...ugly), but you can
> probably (caveat emptor, I don''t know this table very well) do
something
> like
> >
> > delete from resource_statuses where time <= date_sub(now(),
interval 2
> weeks)
> >
> > That''d delete rows older than two weeks.
> >
> > -scott
> >
> > On Tue, Aug 16, 2011 at 2:34 PM, Sandor W. Sklar
<ssklar@gmail.com>
> wrote:
> > I''ve "inherited" the administration of a
puppet-dashboard (version
> > 1.1.0, installed on RHEL 5.6 from puppetlabs RPM), and have hit a
> > problem I''m hoping for some help with.
> >
> > In short, one table, "resource_statuses" appears to be
growing at a
> > rate far higher then the other tables:
> >
> > mysql> select count(*) from nodes;
> > +----------+
> > | count(*) |
> > +----------+
> > |      107 |
> > +----------+
> >
> > mysql> select count(*) from resource_statuses;
> > +-----------+
> > | count(*)  |
> > +-----------+
> > | 188068011 |
> > +-----------+
> >
> > Yeah, that''s over 188 million rows.  Currently, the entire
mysql data
> > directory for the dashboard database is taking up 51 GB; the
> > resource_statuses.MYD file is 47 GB of that.
> >
> > We''ve got a weekly cron job that runs
"/usr/share/puppet-dashboard/
> > Rakefile RAILS_ENV=production reports:prune upto=2 unit=wk", and
I''ve
> > run it manually, but it doesn''t seem to impact that table.
> >
> > Is there a "safe" way to reduce the number of rows in that
table, and
> > limit it to keeping either X number of rows, or rows going back only
> > to a specific timespan (like, 2 weeks)?
> >
> > Thanks in advance!
> >
> >        -- Sandy
> >
> > --
> > You received this message because you are subscribed to the Google
Groups
> "Puppet Users" group.
> > To post to this group, send email to puppet-users@googlegroups.com.
> > To unsubscribe from this group, send email to
> puppet-users+unsubscribe@googlegroups.com.
> > For more options, visit this group at
> http://groups.google.com/group/puppet-users?hl=en.
> >
> >
>
> --
> Sandor W. Sklar
> Unix Systems Administrator
> Digital Libraries Systems & Services
> Stanford University Libraries
>
> --
> You received this message because you are subscribed to the Google Groups
> "Puppet Users" group.
> To post to this group, send email to puppet-users@googlegroups.com.
> To unsubscribe from this group, send email to
> puppet-users+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/puppet-users?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To post to this group, send email to puppet-users@googlegroups.com.
To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.

This seems to work...

--- prune_reports.rake	2011-09-08 12:42:54.433928840 -0700
+++ prune_reports.rake-new	2011-09-08 12:44:01.928583735 -0700
@@ -52,5 +52,11 @@
     puts "Deleting reports before #{cutoff}..."
     deleted_count = Report.delete_all([''time < ?'',
cutoff])
     puts "Deleted #{deleted_count} reports."
+
+    # inserted to clean up resource_statuses table simultaneously
+    puts "Deleting resource_statuses records before #{cutoff}..."
+    deleted_count = ResourceStatus.delete_all([''time < ?'',
cutoff])
+    puts "Deleted #{deleted_count} resource_status records."
+
   end
 end

On Aug 16, 2011, at 5:44 PM, Scott Smith wrote:
> There should probably be a rake task for this (tbh, all that stuff should
be rewritten, loading the whole Rails env to do it is ...ugly), but you can
probably (caveat emptor, I don''t know this table very well) do
something like
> 
> delete from resource_statuses where time <= date_sub(now(), interval 2
weeks)
> 
> That''d delete rows older than two weeks.
> 
> -scott
> 
> On Tue, Aug 16, 2011 at 2:34 PM, Sandor W. Sklar <ssklar@gmail.com>
wrote:
> I''ve "inherited" the administration of a
puppet-dashboard (version
> 1.1.0, installed on RHEL 5.6 from puppetlabs RPM), and have hit a
> problem I''m hoping for some help with.
> 
> In short, one table, "resource_statuses" appears to be growing at
a
> rate far higher then the other tables:
> 
> mysql> select count(*) from nodes;
> +----------+
> | count(*) |
> +----------+
> |      107 |
> +----------+
> 
> mysql> select count(*) from resource_statuses;
> +-----------+
> | count(*)  |
> +-----------+
> | 188068011 |
> +-----------+
> 
> Yeah, that''s over 188 million rows.  Currently, the entire mysql
data
> directory for the dashboard database is taking up 51 GB; the
> resource_statuses.MYD file is 47 GB of that.
> 
> We''ve got a weekly cron job that runs
"/usr/share/puppet-dashboard/
> Rakefile RAILS_ENV=production reports:prune upto=2 unit=wk", and
I''ve
> run it manually, but it doesn''t seem to impact that table.
> 
> Is there a "safe" way to reduce the number of rows in that table,
and
> limit it to keeping either X number of rows, or rows going back only
> to a specific timespan (like, 2 weeks)?
> 
> Thanks in advance!
> 
>        -- Sandy
> 
> --
> You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
> To post to this group, send email to puppet-users@googlegroups.com.
> To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
> 
> 
> 
> -- 
> You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
> To post to this group, send email to puppet-users@googlegroups.com.
> To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
-- 
Craig White ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ craig.white@ttiltd.com
1.800.869.6908 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ www.ttiassessments.com 

Need help communicating between generations at work to achieve your desired
success? Let us help!

-- 
You received this message because you are subscribed to the Google Groups
"Puppet Users" group.
To post to this group, send email to puppet-users@googlegroups.com.
To unsubscribe from this group, send email to
puppet-users+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.