Hi all, I have a cron that follows http://docs.puppetlabs.com/dashboard/manual/1.2/maintaining.html and cleans our puppet dashboard: [...] rake RAILS_ENV=production reports:prune upto=1 unit=wk rake RAILS_ENV=production db:raw:optimize [...] but I''ve found a table which is 22GB and I''m not able to clean its space: 22G -rw-rw---- 1 mysql mysql 22G Aug 11 17:15 resource_statuses.ibd Our DB started to work on March, so I''ve removed all table entries before July, but the table is still 22GB . mysql> delete from resource_statuses where time < "2011-07-12%"; Query OK, 76040474 rows affected (1 hour 50 min 2.07 sec) So, how may I get some free space from dashboard DB? TIA, Arnau -- 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.
Hi Arnau, This is not a Puppet Dashboard problem, it''s a MySQL "feature" of InnoDB. You''re using per-table InnoDB data files. InnoDB data files grow. They never, ever, ever shrink. So what you''ve got there is a 22GB sparse file that MySQL will fill up with more resource_statuses rows. It won''t use that space for any other table because you''ve for per-table InnoDB data files configured on. I *think* one solution is to convert the resource_statuses to MyISAM then back to InnoDB again but I''ve never done it. Another way is to dump the database out, blow away the InnoDB data files and import the dump (see http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html). Hope that helps, -Luke On Aug 11, 4:20 pm, Arnau Bria <arnaub...@pic.es> wrote:> Hi all, > > I have a cron that followshttp://docs.puppetlabs.com/dashboard/manual/1.2/maintaining.htmland > cleans our puppet dashboard: > > [...] > rake RAILS_ENV=production reports:prune upto=1 unit=wk > rake RAILS_ENV=production db:raw:optimize > [...] > > but I''ve found a table which is 22GB and I''m not able to clean its space: > > 22G -rw-rw---- 1 mysql mysql 22G Aug 11 17:15 resource_statuses.ibd > > Our DB started to work on March, so I''ve removed all table entries > before July, but the table is still 22GB . > > mysql> delete from resource_statuses where time < "2011-07-12%"; > Query OK, 76040474 rows affected (1 hour 50 min 2.07 sec) > > So, how may I get some free space from dashboard DB? > > TIA, > Arnau-- 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.
On Thu, 11 Aug 2011 08:36:41 -0700 (PDT) Luke Bigum wrote:> Hi Arnau,Hi Luke,> This is not a Puppet Dashboard problem, it''s a MySQL "feature" of > InnoDB. You''re using per-table InnoDB data files. InnoDB data files > grow. They never, ever, ever shrink. So what you''ve got there is a > 22GB sparse file that MySQL will fill up with more resource_statuses > rows. It won''t use that space for any other table because you''ve for > per-table InnoDB data files configured on.You''re right!!> I *think* one solution is to convert the resource_statuses to MyISAM > then back to InnoDB again but I''ve never done it. Another way is to > dump the database out, blow away the InnoDB data files and import the > dump (see > http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html).I''ll try to do so.> Hope that helps,it does.> -LukeCheers, Arnau -- 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.
On Thu, 11 Aug 2011 08:36:41 -0700 (PDT) Luke Bigum wrote:> Hi Arnau,Hi Luke, [...]> dump the database out, blow away the InnoDB data files and import the > dump (see > http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html).I''m dumping my dashboard data. My cron prunes de DB and keeps 1 week info. I have about ~100 nodes in that server and the dump is 6GB and grwoing... is that size normal? I mean, what''s the "normal" size for the scenario I''ve described? (I know it depends on the amount os resoruces, but do you have any approach?)> Hope that helps, > > -LukeCheers, Arnau -- 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.
FWIW, I don''t think that task touches this table. Also, I apologize for writing such a crappy rake task, can''t believe it''s still there :-P On Aug 12, 2011 6:28 AM, "Arnau Bria" <arnaubria@pic.es> wrote:> On Thu, 11 Aug 2011 08:36:41 -0700 (PDT) > Luke Bigum wrote: > >> Hi Arnau, > Hi Luke, > > [...] >> dump the database out, blow away the InnoDB data files and import the >> dump (see >>http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html ).> > I''m dumping my dashboard data. My cron prunes de DB and keeps 1 week > info. I have about ~100 nodes in that server and the dump is 6GB and > grwoing... is that size normal? I mean, what''s the "normal" size for > the scenario I''ve described? (I know it depends on the amount os > resoruces, but do you have any approach?) > > >> Hope that helps, >> >> -Luke > Cheers, > Arnau > > -- > 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 topuppet-users+unsubscribe@googlegroups.com.> For more options, visit this group athttp://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.