MySQL for puppet dashboard gets BIG. You can make this smaller by deleting old records. Source for this article is here.

To get the size in GB of the dashboard:

# mysql -uroot -ppassword
> SELECT CONCAT ( ROUND( ( ( SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) ) / 1024 / 1024 / 1024 ) ,2 ), " GB") as Size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dashboard_production' ;
| Size_GB |
|    9.31 |

Before we start, let’s backup the db:

/usr/bin/mysqldump --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers | gzip > /tmp/all-databases.sql.gz

This took 10min at my server.

You now want to delete old records. There is a puppet purge script at the end, but this fails with too big databases ( lock problem). For the initial cleanup, find the ID of the report you want to keep (ergo: delete older ones):

# mysql -uroot -ppassword
SELECT id FROM `reports` WHERE (time > '2013-10-14 07:56:08') LIMIT 0,1;
| id    |
| 90565 |

So we want to delete about 90000 records. We should do this in batch because of the locks problem:

for i in {1..90}; do echo "Deleting reports until${i}000"; time echo "DELETE FROM reports WHERE (id < ${i}000 )" | mysql -uroot -ppassword dashboard_production ; done

Every 1000 entries took me about a minute (virtual server).

If this is done: you are probably interested in the size, so run the first query again ;)

Now run the prune script at the bottom of this page. And backup the pruned database.

Edit the mysql settings:

add in file /etc/my.conf:
innodb_file_per_table = 1
innodb_data_file_path = ibdata:10M:autoextend:max:10G

Stop the mysql server:

# service mysqld stop

Delete old mysql data:

# mv /var/lib/mysql{,.original}

Start the mysql server:

# service mysqld stop

Restore the database:

zcat /tmp/all-databases.sql.gz | mysql -uroot -ppassword

Now restart puppet-dashboard.
Prune script:


rake RAILS_ENV=production reports:prune upto=1 unit=mon
rake RAILS_ENV=production db:raw:optimize

You might want to schedule this in a crontab ;).

By karlo