Misp: correlations.ibd too large

[Spj28]

I have the latest MISP version 2.4.85 on a Linux linode. MISP was installed on the system couple of months ago. There is no auto feed update (didn't work for me) or scheduled job running on the system. I noticed that the disk usage was 100% and upon researching found that /var/lib/mysql/misp/correlations.ibd is above 80 GB (Total disk 98 GB).

1. Why is this file getting bigger when no updates are happening?
2. Is it possible to optimize MISP and add an upper limit to disk space?

Currently I am unable to access the web interface. This is the error I receive:

_You have tripped the cross-site request forgery protection of MISP
CSRF error: This happens usually when you try to resubmit the same form with invalidated CSRF tokens or you had a form open too long and the CSRF tokens simply expired. Just go back to the previous page and refresh the form (by reloading the same url) so that the tokens get refreshed._

I am thinking all these could be multiple isolated issues but my main concern is the disk space currently.

Appreciate any help.

[iglocska]

I have no idea how that happened to be honest. Just checked my instances and I'm sitting at 335 megs with a pretty large database - especially without pulling in new data that table should never grow...

I'll try to look into how to reproduce it, but it sounds weird as hell.

[iglocska]

Could you shut down apache, log into mysql, check the number of correlations? Also it's really weird, my suspicion is this:

Auto feed update did work end up working after all and you might have some feeds set to be pulled into new events each time - if you set this with a low interval, it might end up pulling the same feed over and over, creating a highly correlating data-set. If we can verify this that would already be a good start to resolving it.

[Spj28]

Here is the count in the correlations table:
| count(id) |
+-----------+
247557920

[iglocska]

That is a ridiculously high number :| can you also count the number of attributes that you have?

[iglocska]

One thing you could do:

Clear out all the correlations (truncate correlations;) Log into your MISP and clear out all the crazy duplicate events coming from feeds, edit the feed rules (to delte merge into the same events instead of creating new ones on each pull).

[Spj28]

Thanks. I cleared the correlations. But can you please guide me with removing the duplicate events. Not clear how to do that. Is it from the console or web interface?

[iglocska]

Via the web interface. Basically have a look if you don't have a bunch of events pulled in regular intervals with the same title and almost the same contents (the event info field should have the feed name in it).

MISP allows you to basically pull a new copy of a feed in whatever interval you tell it to, but in most cases this is something crazy heavy (the individual feed pull events will correlate with one another over and over). The best strategies for ingesting feeds are to either use the delta merge feature and let MISP update an existing event with changes to the feed, or alternatively to just cache the feeds (depending on whether you want the contents as being part of your working data set or just as a way to correlate data).

[Spj28]

I made all the recommended changes but the correlation DB used up all the free disk space on the server. Of the 90 GB disk space it used 71 GB. Is there a solution yet on adding a limit to the DB size? How do I ensure there are no duplicates? I need to make some free space on the disk. What is the best way to achieve it? Thanks in advance for your help.

[iglocska]

Did you delete the events that were created from feeds? You still have 90 gigs worth of correlations?

[Spj28]

Thanks iglocska. I edited every feed and made sure that delta merge was enabled as I noticed it was unchecked in some feeds. Would it be better if I delete all events and start from scratch? If so, what is the best and quick way to achieve it? I have nearly 22K events. Manually deleting from UI will take a while as I can delete only 60 events at a time.

[iglocska]

Woah. Hmph, good question. There is the nuclear option which is to purge all the data from the DB directly (we have a cleanup tool for this) - but that is of course nasty. Do you have any data in there that you would like to keep? Is this your operational instance? Worst case it's possible to script it using PyMISP.

[Spj28]

There are no events that were customized if that is what you are asking and this is still a dev instance. So we will not loose anything if starting from scratch.

[Spj28]

I truncated correlations table and the size was few KB last week. I have the following feed settings. Source Format is "Freetext Parsed Feed" and Target Event is "New Event Each Pull". "Delta Merge" is enabled. I noticed that no events are being pulled since May 3 yet the correlations.ibd is growing daily and now it's 26 GB in less than a week. Shouldn't old events be deleted with the delta merge feature? I read through the document and unclear as to what I am missing. Please point me in the right direction.

Thanks in advance!!

[iglocska]

New event each pull is the issue. Please use fixed event. Also, even if you
prefer the correlations, the correlations attributes are still there -
editing one will generate correlations to ask existing copies.

On Mon, May 7, 2018, 16:40 Spj28 [e-mail] wrote:

I truncated correlations table and the size was few KB last week. I have
the following feed settings. Source Format is "Freetext Parsed Feed" and
Target Event is "New Event Each Pull". "Delta Merge" is enabled. I noticed
that no events are being pulled since May 3 yet the correlations.ibd is
growing daily and now it's 26 GB in less than a week. Shouldn't old events
be deleted with the delta merge feature? I read through the document and
unclear as to what I am missing. Please point me in the right direction.

Thanks in advance!!

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/MISP/MISP/issues/2800#issuecomment-387086613, or mute
the thread
https://github.com/notifications/unsubscribe-auth/ADf6wH-P5FqolZCD9VQrqbiFeV4GyJAlks5twFz2gaJpZM4RZraT
.

[Nymeria1]

Hi all,
Our MISP does not free space.
In our instances the file /var/lib/mysql/misp/correlations.ibd is very big. About 347 GB.

What is this file?
How can we manage the space in the DB to avoid this problem?
Could you help us to setting correctly the feed's option?

[iglocska]

That is the correlations table's database. 347GB sounds extremely large, could you check the number of correlations you have?

mysql -u misp -p misp
SELECT count(*) from correlations;

[Nymeria1]

Thanks iglocska for your answer.
The number of correlation is 1098975162

[iglocska]

Eek. By any chance are you pulling the same feed over and over into a new event? This could be a symptom of that...

[Nymeria1]

We have a problem about automatic pulling and for this reason we added the api call to force pulling every hour in crontab

[Nymeria1]

Can I delete the data from this table?
Will the correlation be restored later?
I imagine to avoid the problem I have to delete api call in crontab..

[iglocska]

You can delete it and rerun it after, but I would also clean up the event table. Basically make sure that feeds only get pulled into fixed events not "new event each pull", delete the hourly duplicate events then re-correlate the instance.

[Nymeria1]

Ok,
So I have to do:

1. Delete data from correlation's table
2. Delete data from event's table
3. Launch pull via GUI and delete from crontab
4. Verify option not "new event each pull in feed option?

Many thanks for your help

[iglocska]

1. truncate correlations table
2. delete events that are duplicate feed pulls via the UI / via the API
   (not via the DB, or else all the attributes, tags, objects, proposals, etc
   will stay orphaned in the DB)
3. change the pull settings for the feed in the UI/API to "fixed event"
   from "new event each pull"
4. Crontab is fine

On Thu, Jun 7, 2018 at 2:27 PM, Nymeria1 [e-mail] wrote:

Ok,
So I have to do:

1. Delete data from correlation's table
2. Delete data from event's table
3. Launch pull via GUI and delete from crontab
4. Verify option not "new event each pull in feed option?

Many thanks for your help

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/MISP/MISP/issues/2800#issuecomment-395402179, or mute
the thread
https://github.com/notifications/unsubscribe-auth/ADf6wOnGwK-6duo4ptbvFIQ9TW9CjNn8ks5t6RwYgaJpZM4RZraT
.

[Nymeria1]

Many thanks for your help iglocska.
Problem solved.

[iglocska]

Perfect, thanks for the feedback. Will close the issue for now but we really need to create a detailed walkthrough for this as it's a recurring issue, so will keep it in mind. Ping us if you run into other issues!