[FIX] Clean up old puppet reports and reclaim mysql space (ibdata)

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:
[mysqld]
innodb_buffer_pool_size=512M
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:

#!/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

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