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 <ssk...@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+unsubscr...@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+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/puppet-users?hl=en.
