{"id":172,"date":"2012-01-04T14:58:45","date_gmt":"2012-01-04T19:58:45","guid":{"rendered":"http:\/\/blog.domb.net\/?p=172"},"modified":"2013-03-12T20:13:10","modified_gmt":"2013-03-13T00:13:10","slug":"puppet-dashboard-mysql-ibdata-cleanup","status":"publish","type":"post","link":"https:\/\/blog.domb.net\/?p=172","title":{"rendered":"Puppet Dashboard, mysql ibdata cleanup"},"content":{"rendered":"

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.<\/p>\n

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)<\/p>\n

#mysql -u root<\/em><\/p>\n

2. Then you can check table sizes with this command<\/p>\n

mysql><\/strong> 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;<\/em><\/p>\n

OUTPUT:
\n+——————–+—————————————+———–+
\n| Database_name\u00a0\u00a0\u00a0\u00a0\u00a0 | Table_Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Size\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n+——————–+—————————————+———–+
\n| information_schema | CHARACTER_SETS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.00 Mb\u00a0\u00a0 |
\n| information_schema | COLLATIONS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.00 Mb\u00a0\u00a0 |<\/p>\n

…………….. cut …….
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | metrics\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | 299.33 Mb |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | node_class_memberships\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | node_classes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | node_group_class_memberships\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | node_group_edges\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | node_group_memberships\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | node_groups\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | nodes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | old_reports\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | parameters\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | report_logs\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 121.14 Mb |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | reports\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 3.30 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | resource_events\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 81.11 Mb\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | resource_statuses\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 11456.50 Mb |<\/strong>
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | schema_migrations\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0.02 Mb\u00a0\u00a0 |
\n| dashboard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | timeline_events\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 32.56 Mb\u00a0 |
\n……. cut ……..<\/p>\n

3. You could also check the filesize of ibdata in \/var\/lib\/mysql\/<\/p>\n

4. In my example the table resource_statuses is massive.\u00a0<\/strong> So i want to shrink it. Go to \/tmp.<\/p>\n

#cd \/tmp<\/em><\/p>\n

5. Stop puppet-dashboard<\/p>\n

#\/etc\/init.d\/puppet-dashboard stop<\/em><\/p>\n

6. Dump the content of your mysql instance<\/p>\n

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

7. stop the mysqld service<\/p>\n

#\/etc\/init.d\/mysqld stop<\/em><\/p>\n

8. Delete the databases in \/var\/lib\/mysql<\/p>\n

# rm -rf \/var\/lib\/mysql\/*<\/em><\/p>\n

9.\u00a0 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<\/strong> in \/etc\/ directory and add the following parameters:<\/p>\n

#vi \/etc\/my.cnf<\/em><\/p>\n

Add:<\/p>\n

innodb_buffer_pool_size=512M<\/strong>
\ninnodb_file_per_table = 1<\/strong>
\ninnodb_data_file_path = ibdata:10M:autoextend:max:10G<\/strong>
\n10. Restart mysql
\n#\/etc\/init.d mysql restart<\/em><\/p>\n

11. Restore your data<\/p>\n

#cd \/tmp<\/em><\/p>\n

#mysql -u root < “all-databases.sql”<\/em><\/p>\n

12. Create a cleanup script for puppet-dashboard<\/p>\n

# vi \/usr\/share\/puppet-dashboard\/bin\/purgeDashboardDatabase.sh <\/em><\/p>\n

Add that content:<\/p>\n

#!\/bin\/bash<\/strong>
\nDBOARD_DIR=\/usr\/share\/puppet-dashboard<\/strong><\/p>\n

cd ${DBOARD_DIR}<\/strong>
\nrake RAILS_ENV=production reports:prune upto=1 unit=mon<\/strong>
\nrake RAILS_ENV=production db:raw:optimize<\/strong><\/p>\n

13. Add a cronjob<\/p>\n

#crontab -e<\/em><\/p>\n

and add:<\/p>\n

0 0 * * * \/usr\/share\/puppet-dashboard\/bin\/purgeDashboardDatabase.sh > \/dev\/null<\/p>\n

14. Restart puppet dashboard<\/p>\n

#\/etc\/init.d puppet-dashboard restart<\/em><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":2,"featured_media":166,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[6],"tags":[8],"class_list":["post-172","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-puppet","tag-puppet-2"],"_links":{"self":[{"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/posts\/172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.domb.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=172"}],"version-history":[{"count":16,"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/posts\/172\/revisions"}],"predecessor-version":[{"id":408,"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/posts\/172\/revisions\/408"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.domb.net\/index.php?rest_route=\/wp\/v2\/media\/166"}],"wp:attachment":[{"href":"https:\/\/blog.domb.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.domb.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.domb.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}