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

 

Tags:

9 thoughts on “Puppet Dashboard, mysql ibdata cleanup”

  1. Thanks for posting this for other dashboard users.

    You might already be aware, but the `rake reports:prune` task had a bug in versions of Dashboard < 1.2.4 that caused resource_statuses to not get cleaned, just the reports table. The ticket discussing the problem also has some standalone scripts you can use to fix the issue if upgrading isn't an option.

    http://projects.puppetlabs.com/issues/6717

    Also, http://projects.puppetlabs.com/issues/12737 considers not storing resource statuses with no events, which most resource statuses in the database are. Feel free to comment on that ticket if you think that would be helpful.

  2. In the article

    Add:

    innodb_buffer_pool_size=512M
    innodb_file_per_table = 1
    innodb_data_file_path = ibdata:10M:autoextend:max:10G
    innodb_buffer_pool_size = 64MB

    There are two entries for innodb_buffer_pool_size. Should it be 512M or 64M?

  3. Pingback: [FIX] Clean up old puppet reports and reclaim mysql space (ibdata) » Karlo Luiten

  4. After deleting the databases in step 8, I could not restart mysql. I was able to run mysqld as the mysql user by doing “su mysql” then run “mysqld” but that crash report wasnt much help. Then I ended up running mysqld as root and seeing this error:
    150311 15:20:35 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

    After googling that, this thread helped me:
    https://bbs.archlinux.org/viewtopic.php?id=137365

    The command that got mysql starting again was:
    mysql_install_db –user=mysql –ldata=/var/lib/mysql/

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.