Wednesday, August 17, 2016

UPDATED!!! HOWTO Cleanup Puppet Reports and DB

If the database for Puppet Dashboard is using several GB and getting larger everyday, this is a way to get some of the space back.

There are two rake jobs you should be running everyday as part of daily maintenance for Puppet Dashboard.

cd /usr/share/puppet-dashboard
env RAILS_ENV=production rake reports:prune upto=5 unit=day
env RAILS_ENV=production rake reports:prune:orphaned


You can change the RAILS_ENV and number of day (day), weeks (wk), months (mon), etc to match your system and its needs.

1. Stop incoming reports:

cd /path/to/puppet-dashboard
env RAILS_ENV=production script/delayed_job -p dashboard -m stop


2. Start deleting reports in small batches

Keep working your way in towards the length of time you want to keep reports for. The reason for this is Innodb tables have poor performance when deleting more than 10k rows at a time. If you try to deleting a few hundred thousand rows, it will timeout and you'll have to break it up into smaller deletes anyway. Also the Ruby rake process will use probably use all your RAM and likely get killed off by the kernel before it finishes. Something like this progression should work for most people, but if you have many months of data you may want to start with a month or two of your earliest records. In our case, we are keeping just 2 weeks reports (14 days).

env RAILS_ENV=production rake reports:prune upto=6 unit=mon
env RAILS_ENV=production rake reports:prune upto=4 unit=mon
env RAILS_ENV=production rake reports:prune upto=2 unit=mon
env RAILS_ENV=production rake reports:prune upto=3 unit=wk
env RAILS_ENV=production rake reports:prune upto=1 unit=wk
env RAILS_ENV=production rake reports:prune upto=5 unit=day


3. Determine the best method to reclaim space from MySQL

There are two methods to reclaim space depending on how MySQL was configured. Run this command to determine if "innodb_file_per_table" is enabled. It should be set to "ON" if it is.
NOTE: I recommend to use innodb on your MySQL for cases like this one.

mysqladmin variables -u root -p | grep innodb_file_per_table

You can also do a listing of the database to see if there are larger data files. The table most likely to be large is resource_statuses.ibd.

ls -lah /var/lib/mysql/dashboard_production
...
-rw-rw---- 1 mysql mysql      8.9K Jan 08 12:50 resource_statuses.frm
-rw-rw---- 1 mysql mysql       15G Jan 08 12:50 resource_statuses.ibd
...


4. Reclaiming space the easy way

If MySQL was configured with innodb_file_per_table and your Dashoard DB shows that your data is in large table files, do the following:

mysql -u root -p
use puppet_dashboard;
OPTIMIZE TABLE resource_statuses;

This will create a new table based on the current data and copy it into place. If you do a listing while this is in progress you should see something like this:

-rw-rw---- 1 mysql mysql       8.9K Jan  08 12:50 resource_statuses.frm
-rw-rw---- 1 mysql mysql        15G Jan  08 12:50 resource_statuses.ibd
-rw-rw---- 1 mysql mysql       8.9K Jan  08 12:50 #sql-379_415.frm
-rw-rw---- 1 mysql mysql       238M Jan  08 12:51 #sql-379_415.ibd


And when it finished it'll copy the tmp file into place. In this case we went from 15GB to 708MB.

-rw-rw---- 1 mysql mysql 8.9K Jan 08 13:01 resource_statuses.frm
-rw-rw---- 1 mysql mysql 708M Jan 08 13:03 resource_statuses.ibd



The optimization of the Database can also be done via rails:

root@pmaster01:~# cd /usr/share/puppet-dashboard
root@pmaster01:/usr/share/puppet-dashboard# env RAILS_ENV=production rake db:raw:optimize
Optimizing tables, this may take a while:
* delayed_job_failures
* delayed_jobs
* metrics
* node_class_memberships
* node_classes
* node_group_class_memberships
* node_group_edges
* node_group_memberships
* node_groups
* nodes
* old_reports
* parameters
* report_logs
* reports
* resource_events
* resource_statuses
* schema_migrations
* timeline_events
root@pmaster01:/usr/share/puppet-dashboard#

5. Truncate Tables:

When this Method does not work, I found that a truncate of the Table "resource_statuses" also worked in this way:

First make a dump of the table:
root@pmaster01:~# mysqldump dashboard_production resource_statuses > resource_statuses_20160817-1418.sql

Stop the Dashboard Workers:
root@pmaster01:~# /etc/scripts/dashboard_workers.sh stop
root@pmaster01:~# /etc/scripts/dashboard_workers.sh status
STATUS is NOT OK: 0 workers are running
root@pmaster01:~#

Now let's go for the truncate:

root@pmaster01:~# mysql dashboard_production
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2111
Server version: 5.5.37-0+wheezy1 (Debian)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
+--------------------------------+
| Tables_in_dashboard_production |
+--------------------------------+
| delayed_job_failures           |
| delayed_jobs                   |
| metrics                        |
| node_class_memberships         |
| node_classes                   |
| node_group_class_memberships   |
| node_group_edges               |
| node_group_memberships         |
| node_groups                    |
| nodes                          |
| old_reports                    |
| parameters                     |
| report_logs                    |
| reports                        |
| resource_events                |
| resource_statuses              |
| schema_migrations              |
| timeline_events                |
+--------------------------------+
18 rows in set (0.00 sec)
mysql> truncate table resource_statuses;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`dashboard_production`.`resource_events`, CONSTRAINT `fk_resource_events_resource_status_id` FOREIGN KEY (`resource_status_id`) REFERENCES `dashboard_production`.`resource_statuses` (`id`))

The error above is common in tables like this one with foreign keys; that's why we will run the truncate in the following way:

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table resource_statuses;
Query OK, 0 rows affected (0.46 sec)
mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)
mysql>
Now Start the Dasboard Workers again

root@pmaster01:~# /etc/scripts/dashboard_workers.sh start
root@pmaster01:~# /etc/scripts/dashboard_workers.sh status
Check the actual size of the Table:

root@pmaster01:~# l -h /var/lib/mysql/dashboard_production/resource_statuses*
-rw-rw---- 1 mysql mysql 8.9K Oct  7 12:21 resource_statuses.frm
-rw-rw---- 1 mysql mysql 9.0M Oct  7 12:48 resource_statuses.ibd
We passed from a Table Size of 1.7 GB to 9 MB.

6. Reclaiming space the hard way


If your system was not configured with innodb_file_per_table or all the current data resides in a large ibdata file, the only way to reclaim space is to wipe the entire installation and reimport all the data.
The overall method should be something like: First configure innodb_file_per_table, dump all the databases, then stop Mysql, delete /var/lib/mysql, run mysql_install_db to create /var/lib/mysql again, start MySQL, and finally reimport the data. There will be no need to the optimize steps because of the data import.

7. Finally, Restart the delayed_job:

cd /path/to/puppet-dashboard
env RAILS_ENV=production script/delayed_job -p dashboard -n 2 -m start


8. Daily Reports Cleanup and DB Maintenance:

For a daily Reports Cleanup you can create a simple BASH script who search the Reports on /var/lib/puppet/reports by time (mtime +14 in our case), remove them and then cleanup the DB with (upto=2 unit=wk) and set it in your crontab.
An example of the script can be:

#!/bin/bash
REPORTS=`find /var/lib/puppet/reports -type f -mtime +14`
for i in $REPORTS; do rm -f $i; done

cd /usr/share/puppet-dashboard

env RAILS_ENV=production rake reports:prune upto=2 unit=wk