Ask Your Question
1

clean puppet dashboard database failed

asked 2014-05-29 04:00:24 -0500

shyp gravatar image

I am trying to clean the mysql database ,

From this : http://docs.puppetlabs.com/dashboard/...

When tryting to run optimizeing to the data , what happen is my database became twice a size and no optimization happen , I also dont see any alter tables that where created in my database , How can I delete the data ...

Also ,

When try to clean old reports I am getting :

Mysql::Error: The total number of locks exceeds the lock table size: DELETE FROM reports WHERE (time < '2014-05-15 08:16:21')

Tasks: TOP => reports:prune

edit retag flag offensive close merge delete

3 Answers

Sort by ยป oldest newest most voted
0

answered 2015-11-16 10:10:22 -0500

stig gravatar image

I have the same problem. Here's the last bit of output when running the reports:prune task:

Preparing to delete from resource_statuses
2015-11-16 15:18:16: Deleting 108787 orphaned records from resource_statuses
rake aborted!   99% |####################################################################################################################  | ETA:  00:00:00
ActiveRecord::StatementInvalid: Mysql::Error: The total number of locks exceeds the lock table size: delete from resource_statuses where report_id not in (select id from reports) limit 1000

Tasks: TOP => reports:prune:orphaned
(See full trace by running task with --trace)

Every time it gets almost to the end, then failing on the last batch. Doesn't matter if there's millions of rows to be deleted or a couple thousand. Logging in and attempting to run the delete by hand yields the same result. Reducing the limit to 100 makes the query execute instantly, several times, until there's less than 100 rows to delete again, when it breaks. Same for setting the limit to 10. I eventually manage to delete all the rows by reducing the limit to 1, and running it repeatedly. However, running the reports:prune task again immediately still fails. (This table grows fast!)

Preparing to delete from resource_statuses
2015-11-16 15:21:51: Deleting 2806 orphaned records from resource_statuses
rake aborted!   71% |###################################################################################                                   | ETA:  00:00:00
ActiveRecord::StatementInvalid: Mysql::Error: The total number of locks exceeds the lock table size: delete from resource_statuses where report_id not in (select id from reports) limit 1000

Tasks: TOP => reports:prune:orphaned
(See full trace by running task with --trace)

I am slightly at a loss at how to solve it. I am wondering if I'm running up against some MySQL bug. I tried to increase the innodb_buffer_pool_size ten-fold, as described here but to no avail.

I am wondering if perhaps the issue occurs because the table grows so fast...? At any rate, the majority of the rows seems to "stay deleted" so a cleanup is definitively happening. It feels dirty that it fails just before the finish line, but I suspect it serves its purpose.

Here is some further debugging output:

mysql> select count(*), count(distinct report_id) from resource_statuses where report_id not in (select  id  from reports ) ;
+----------+---------------------------+
| count(*) | count(distinct report_id) |
+----------+---------------------------+
|      806 |                         3 |
+----------+---------------------------+
1 row in set (26.22 sec)

mysql> delete from resource_statuses where report_id not in (select  id  from reports ) limit 800;                                                          Query OK, 800 rows affected (0.08 sec)

mysql> delete from resource_statuses where report_id not in (select  id  from reports ) limit 800;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql> delete from resource_statuses where report_id not in (select  id  from reports ) ;
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql> select count(*), count(distinct report_id) from resource_statuses where report_id not in (select  id  from reports ) ;
+----------+---------------------------+
| count(*) | count(distinct report_id) |
+----------+---------------------------+
|        6 |                         1 |
+----------+---------------------------+
1 row in set (36.47 sec)

mysql> delete from resource_statuses where report_id not in (select  id  from reports ) limit 6;
Query OK, 6 rows affected (0.06 sec)

It looks like ... (more)

edit flag offensive delete link more
0

answered 2015-11-16 14:26:48 -0500

Aly gravatar image

I just completed this as I was having same problem.

Found answer here: link text

edit flag offensive delete link more
0

answered 2014-05-29 06:17:33 -0500

shyp gravatar image

updated 2015-11-22 14:38:54 -0500

ramindk gravatar image

I made a change to this parameter in my mysql : innodb_buffer_pool_size=1G and the delete is working now , I am trying to run the optimize again ...

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

Question Tools

1 follower

Stats

Asked: 2014-05-29 04:00:24 -0500

Seen: 528 times

Last updated: Nov 22 '15