Very large resource_statuses. reports:prune:orphaned fails with Mysql error

[Erling Ringen Elvsrud]

Hi!

I use Puppet enterprise 2.7.2. In the database for Puppet Dashboard
the resource_statuses table has grown rapidly the past month (when I was on vacation).
I have experienced this earlier also and have implemented the suggested solution
here: https://projects.puppetlabs.com/issues/6717
(downloaded a new rake task reports:prune:orphaned).
I have sucessfully executed that task in the past, but now it fails with an error:
Mysql::Error: Lock wait timeout exceeded; try restarting transaction: delete from resource_statuses where report_id not in (select id from reports) limit 1000.

The resource_statuses.ibd file is 103 GB! (containing about 250 million rows).
When this happened earlier the rake task reports:prune:orphaned ran for many days, but eventually completed.

Is it possible to quickly drop the whole resource_statuses table and recreate it?

Note that I also currently work with upgrade / migration to Puppet Enterprise
3.x but need to maintain this 2.7.2 installation in paralell.

Best regards,

Erling

[Ramin K]

On 8/12/2014 1:34 AM, Erling Ringen Elvsrud wrote:
> Hi!
>
> I use Puppet enterprise 2.7.2. In the database for Puppet Dashboard
> the resource_statuses table has grown rapidly the past month (when I was
> on vacation).
> I have experienced this earlier also and have implemented the suggested
> solution
> here: https://projects.puppetlabs.com/issues/6717
> (downloaded a new rake task reports:prune:orphaned).

> I have successfully executed that task in the past, but now it fails with

> an error:
> Mysql::Error: Lock wait timeout exceeded; try restarting transaction:
> delete from resource_statuses where report_id not in (select id from
> reports) limit 1000.
>
> The resource_statuses.ibd file is 103 GB! (containing about 250 million
> rows).
> When this happened earlier the rake task reports:prune:orphaned ran for
> many days, but eventually completed.
>
> Is it possible to quickly drop the whole resource_statuses table and
> recreate it?
>
> Note that I also currently work with upgrade / migration to Puppet
> Enterprise

> 3.x but need to maintain this 2.7.2 installation in parallel.

You could try dropping the table and recreating it, but it might orphan
all the resources. If you want the longer safer way, this is how I've
done it in the past. Note that you'll need to dump and import the data
for that table in order to reclaim the space from the table.

https://ask.puppetlabs.com/question/884/how-do-i-reduce-the-space-mysql-is-using-for-puppet-dashboard/?answer=885#post-id-885

Ramin

[Ramin K]

I made an error. Because you're already using file per table the
optimize step will reclaim the space as long as you've already purged
the data.

Ramin

[Erling Ringen Elvsrud]

On Tuesday, August 12, 2014 5:58:34 PM UTC+2, Ramin K wrote:

On 8/12/2014 8:54 AM, Ramin K wrote:

[...]

 

> You could try dropping the table and recreating it, but it might orphan
> all the resources. If you want the longer safer way, this is how I've
> done it in the past. Note that you'll need to dump and import the data
> for that table in order to reclaim the space from the table.
>
> https://ask.puppetlabs.com/question/884/how-do-i-reduce-the-space-mysql-is-using-for-puppet-dashboard/?answer=885#post-id-885

I made an error. Because you're already using file per table the
optimize step will reclaim the space as long as you've already purged
the data.

Ramin

Thanks for the reply.  I have reduced the number of daily puppet runs on
all nodes from 8 to 2 in also in order to reduce the load. It looks like this
is also affecting the locking error message that I got previously and
the reports:prune:orphaned job seems to execute as it should (but very slowly).
As growth of the resource_statuses table also has slowed down I think I will
let the reports:prune:orphaned job just run and see If that solves the problem.
If not I may try to drop the table.

Erling