MISP MYSQL database growing too large and starting to get out of control? API calls getting slower and slower? Maybe its time to start doing some spring cleaning! Find below some help in getting things under control.
MySQL Log table
There is no inbuilt way to easily manage the growth on your MISP log table inside the database. Steve Clement posted a script here though it is a blunt instrument in that it clears the entire log table prior to a set date. Some people prefer not to touch the log table as it holds a record of CRUD activity on events and attributes but realistically unless you are running a forensics shop your MISP database is full of a lot of short life IOCs with most being redundant within a week of being ingested.
I have found running the below TSQL commands to delete specific information from the MISP database older than 2 months helps claw back some space and performance and i’ve yet to have any negative impact from. The optimize command compacts the table and releases the now unused space. The last line referring to access_logs is depending on if you have configured your MISP installation to log to the access_logs table aswell.
delete from logs where model='Server' and created<date_sub(now(),interval 2 month); delete from logs where model='User' and action='auth' and created<date_sub(now(),interval 2 month); delete from logs where model='Attribute' and created<date_sub(now(),interval 2 month); delete from logs where model='ShadowAttribute' and created<date_sub(now(),interval 2 month); delete from access_logs where created <date_sub(now(),interval 1 month); optimize table logs;
If your log table has grown out of control and you do not have sufficent disk space to do an optimize (as this requires the same amount of free disk space as the existing logs tables uses) then you can go down the nuclear path and run the below command which will delete all data, release disk space and reset auto increment ids (I have done before with no adverse affects)
truncate table logs
Attributes table
Also instead of attributes being hard deleted when no longer needed in an event they will instead be marked as deleted and hidden from the UI. Depending on how you have configured your MISP installation, this can lead to a large amount of space being used up and impacting query times. For instance if your feeds are configured to overwrite the same event each day, then that event will have large portions of attibutes hidden in the MISP database with the deleted field=1. To find out how the split between attributes deleted and not run the below command.
select count(*), deleted from attributes group by deleted;
The ones which show as deleted=1 are no longer needed. You can delete these by running the below command
delete from attributes where deleted=1; optimize table logs;
Other Space Saving Options
Check your MISP tmp directory space usage by using the below query
sudo du -lh --max-depth=1 /var/www/MISP/app
If you see a large amount of space against the tmp directory you probably have stix log files which can be deleted. I have found it common for over a GB of tmp files to accumulate here before you are aware.
sudo rm /var/www/MISP/app/tmp/MISP*
