Shrink ESM 6.11 MySQL DB

[fpieressa]

Hi team, as the ibdata1 file is too big, I need to shrink the MySQL Database, as I know it requires to backup de arcsight DB, delete it, remove ibdata* files, restore the DB and finally start it.

My problem is with the "event" table, that use an ARC_LOGGER engine (not a Innodb engine).

Is there any procedure to shrink the MySQL arcsight database?

Thanks!

[Ionut Daniel Mosoiu]

Hello,

can you please run and the command "$ ls -larth"  inside of the /opt/arcsight/logger/data/mysql directory to provideus the value of ibdata1 ?

Best Regards,

Daniel

[fpieressa]

Hi! I have tried in our Lab a standard shrink procedure, the ibdata1 had 30 GB and now it has 3 GB.. we have verified with a count that all the tables have the same number of rows, and events are still available 🙂

[Ionut Daniel Mosoiu]

Hello,

at did you export the system tables in the lab and tried there to apply the procedure?

My advice it's to backup those file into another location but after you stop all the ESM processes.

The shrink procedure could work for you but at the same time could break your system.

By the way, the ibdata1 and 2 are storing the information related to the content. So reducing the size of it can create issues storing the data of the AL, Data Monitors,  Trends, definition/data.

Also, my advice it's to look into CC web page Administration> Storage and Archive and Search for System Storage section at  Current Size utilization from the web page. If the Current Size is 30 GB then I am afraid that you cannot reduce to 3 GB.

Best Regards,

Daniel

Note: the reduction of the size of the  ibdata1 and 2 cannot be done i the files have the size declared in my.cnf file.

There is no procedure yet how to get the data from them and recreated them with a lower size.

The situation it's related to the case when the files are occupying less then the definition from my.cnf file.

[brettpladna81]

do you have a procedure documented? Our ibdata2 file is 935GB.

[Ionut Daniel Mosoiu]

Hello if your  ibdata2 is at that value 935GB this procedure will not help you.

No there is no official document about this because it is not supported. 

The procedure described by me will help you to retain the space allocated for the ibdata file if that file still below the declared value.

I hopes that clarification will help .

For your case unfortunate the only solution is to add more space to /opt/ partition.  At the end there is a reason why the ibdata2 reached that value right ?

Daniel