We are running out of disk space and noticed the ddmdb event tables and SRM event tables are the cause. We want to determine the top events & models which are responsible for this increase in DB size and then depending upon the results we want to perform cleanup of the ddmdb and SRM database.
Log into mysql - Navigate to $SPECROOT/mysql/bin:
For the Archive Manager (ddmdb):
./mysql -uroot -proot ddmdb;
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot ddmdb;
For the reporting db:
./mysql -uroot -proot reporting;
./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting;
At the mysql prompt run the following query to see the top 10 events. This query will take a long time to run on larger event databases:
EventCode, count(*) EventCount from event group by EventCode order by
EventCount desc limit 10;
To delete events by event code, use this command - just change the event code:
delete from event where type=0x10d0c;
That will delete the event.
Now, in order to reclaim the space, you will need to optimize the event table. HOWEVER, take note, you MUST have 1.5x the amount of free space to optimize the table as mysql uses temporary storage to do this.
So if your event table is 100GB, you need at least 150GB of FREE space
Assuming you have enough free disk space, you can then run:
optimize table event;
That will take quite some time. Once complete, type: