Puppet Dashboard, mysql ibdata cleanup

Syncing a 100-200 hosts every 30 minutes and reporting to the dashboard creates a huge amount of data. This reflects in the /var/lib/mysql/ibdata file which can easily grow to 15-20GB. In this blog post I’ll show how to shrink ibdata and how to write a cleanup job for puppet-dashboard.

1. If you are not sure how big your tables are you can login into your mysql database by using the command below (I this example I have no mysql password)

#mysql -u root

2. Then you can check table sizes with this command

mysql> SELECT TABLE_SCHEMA AS ‘Database_name’, TABLE_NAME AS ‘Table_Name’,CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),2),” Mb”) AS Size FROM INFORMATION_SCHEMA.TABLES;

OUTPUT:
+——————–+—————————————+———–+
| Database_name      | Table_Name                            | Size      |
+——————–+—————————————+———–+
| information_schema | CHARACTER_SETS                   | 0.00 Mb   |
| information_schema | COLLATIONS                               | 0.00 Mb   |

…………….. cut …….
| dashboard          | metrics                                                    | 299.33 Mb |
| dashboard          | node_class_memberships                    | 0.02 Mb   |
| dashboard          | node_classes                                            | 0.02 Mb   |
| dashboard          | node_group_class_memberships       | 0.02 Mb   |
| dashboard          | node_group_edges                                 | 0.02 Mb   |
| dashboard          | node_group_memberships                  | 0.02 Mb   |
| dashboard          | node_groups                                            | 0.02 Mb   |
| dashboard          | nodes                                                          | 0.02 Mb   |
| dashboard          | old_reports                                               | 0.02 Mb   |
| dashboard          | parameters                                                | 0.02 Mb   |
| dashboard          | report_logs                                               | 121.14 Mb |
| dashboard          | reports                                                       | 3.30 Mb   |
| dashboard          | resource_events                                      | 81.11 Mb  |
| dashboard          | resource_statuses                       | 11456.50 Mb |
| dashboard          | schema_migrations                               | 0.02 Mb   |
| dashboard          | timeline_events                                      | 32.56 Mb  |
……. cut ……..

3. You could also check the filesize of ibdata in /var/lib/mysql/

4. In my example the table resource_statuses is massive.  So i want to shrink it. Go to /tmp.

#cd /tmp

5. Stop puppet-dashboard

#/etc/init.d/puppet-dashboard stop

6. Dump the content of your mysql instance

# /usr/bin/mysqldump –extended-insert –all-databases –add-drop-database –disable-keys –flush-privileges –quick –routines –triggers > all-databases.sql

7. stop the mysqld service

#/etc/init.d/mysqld stop

8. Delete the databases in /var/lib/mysql

# rm -rf /var/lib/mysql/*

9.  Before we restart mysql we will add some parameters that we want one file per table instead of one huge ibdata file. Open my.cnf in /etc/ directory and add the following parameters:

#vi /etc/my.cnf

Add:

innodb_buffer_pool_size=512M
innodb_file_per_table = 1
innodb_data_file_path = ibdata:10M:autoextend:max:10G
10. Restart mysql
#/etc/init.d mysql restart

11. Restore your data

#cd /tmp

#mysql -u root < “all-databases.sql”

12. Create a cleanup script for puppet-dashboard

# vi /usr/share/puppet-dashboard/bin/purgeDashboardDatabase.sh

Add that content:

#!/bin/bash
DBOARD_DIR=/usr/share/puppet-dashboard

cd ${DBOARD_DIR}
rake RAILS_ENV=production reports:prune upto=1 unit=mon
rake RAILS_ENV=production db:raw:optimize

13. Add a cronjob

#crontab -e

and add:

0 0 * * * /usr/share/puppet-dashboard/bin/purgeDashboardDatabase.sh > /dev/null

14. Restart puppet dashboard

#/etc/init.d puppet-dashboard restart