Ask Your Question
5

How do I reduce the space Mysql is using for Puppet Dashboard?

asked 2013-04-04 17:55:11 -0600

ramindk gravatar image

The database for Puppet Dashboard is using several GB and getting larger everyday. Is there a way to get some of the space back? Also what should I be running to keep this from happening again?

edit retag flag offensive close merge delete

2 Answers

Sort by ยป oldest newest most voted
11

answered 2013-04-04 18:23:10 -0600

ramindk gravatar image

updated 2015-11-22 14:53:05 -0600

First a word on Mysql tuning

The default my.cnf that your distro installed is terrible. By default it provisions no more than 64MB for Mysql and in some cases as low as 16MB. Additionally it splits the RAM between Myisam and Innodb tables making the problem even worse. Before you do anything, fix your my.cnf. This is my recommended config

[mysqld]
# buffer pool is the most important to perf. Ideally you want to use as much as possible
# however if this is a shared machine with your Puppet master be smart with the allocation
# Ideally the buffer pool would never be smaller than 1GB
# all the other settings come from Percona and put performance over safety.
innodb_buffer_pool_size         = 1GB
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT
innodb_lock_wait_timeout        = 180
innodb_open_files               = 512
innodb_thread_concurrency       = 0

# Oddly Myisam buffers are still used for many internals of Mysql (at least in < 5.5) 
# always a decent idea to make sure there is amble space 
key_buffer = 128M

Reclaiming space from your Mysql db

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

cd /path/to/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, weeks, months, etc to match your system and its needs.

Fixing the problem of too much data is harder and requires a number of steps.

First, stop incoming reports.

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

Next, 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.

 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

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.

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 ... (more)

edit flag offensive delete link more

Comments

1

This was extremely helpful information. However, I wanted to add some updated information about reclaiming your space the hard way. According to the [mysql](http://dev.mysql.com/doc/refman/5.6/en/tablespace-enabling.html) documentation you can change the innodb_file_per_table setting and then run

mrosedale gravatar imagemrosedale ( 2014-10-16 08:49:49 -0600 )edit

Thanks for the heads up. Looks like that was first available in Mysql 5.5 which is fairly common these days. I'll update my answer. Thanks again.

ramindk gravatar imageramindk ( 2014-10-17 11:26:39 -0600 )edit

Thanks for this information. If you increase the "innodb_buffer_pool_size" value in /etc/my.cnf you can delete much more entries at once. I guess the default value is 8M so I set it to 64M and can now delete much more entries at once.

Philip_ gravatar imagePhilip_ ( 2015-06-03 06:01:46 -0600 )edit

Your Answer

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

Add Answer

Question Tools

4 followers

Stats

Asked: 2013-04-04 17:55:11 -0600

Seen: 4,474 times

Last updated: Nov 22 '15