Resolving internal database full problems

The Guardium internal database full percentage is not decreasing, even after a successful purge. Reclaim database space with OPTIMIZE.

Symptoms

You can see whether the internal database is full in a number of ways:

You recently purged data off the system but the data % did not decrease.
The response to the CLI command support show db-top-tables all shows that the total size of the largest tables is much smaller than the total disk space available for the database.
The syslog has messages that report a full database.
Output of CLI command: support show db-status used %
Mysql Disk Usage column in GUI > Guardium Monitor > Buffer Usage Monitor report.
The output to the CLI command support show db-top-tables all shows a large amount of unused space (Unused (M)) in some tables.

Causes

The purge removed the data from the database, but did not necessarily reduce the size of the database files. Some database tables might need to be optimized to make the newly cleared space available.

Diagnosing the problem

Check the largest tables and database full percentage before and after you purge data by using CLI commands:

support show db-status used %
support show db-top-tables all

After the purge the largest tables sizes decreased significantly, but the database full % did not change. Also, the database might have a large amount of unused space. The numbers in this example are on a small test system. The columns, specifically the Unused(M), shows how many Megabytes can be reclaimed. In this example, 7 MB of space can be reclaimed from GDM_CONSTRUCT_TEXT.

xxx.yyy.zzz.com> support show db-top-tables all
 Table Size (M) | I/D % |  Unused(M) | Est. Rows | Name
 -------------- | ----- |  --------- | --------- | ----------
           1616 |    28 |          0 |   6038005 | REPORT_RESULT_DATA_ROW
            121 |    51 |          7 |    336557 | GDM_CONSTRUCT_TEXT
             13 |   223 |          4 |     21834 | GDM_CONSTRUCT_INSTANCE
              8 |    20 |          0 |     65149 | DB_ERROR_TEXT

Resolving the problem

Note: In live environments, it is only recommended to optimize when significant space (for example, 20 GB or more) can be reclaimed.

Optimize the internal TURBINE database tables as follows.

Note: The time to complete OPTIMIZE depends on the size of the underlying tables. And OPTIMIZE needs to stop the inspection-core. Run OPTIMIZE during a quiet time and let the command run to completion.

If the appliance is a collector, stop the inspection-core in the CLI.
```
stop inspection-core
```
Start the optimize process. The process might take up to several hours to complete depending on the size of the database tables. You can optimize the database in one of two ways.
- Optimize all tables, in the CLI, enter: diag, 4. Perform Maintenance Actions, 2. TURBINE Optimize.
- Optimize specific tables only. This might be appropriate if you know that a certain table has recently been heavily purged, and you can save time optimizing only one table. The CLI command is:
  support optimize tables <database name> <table name>
  For example:
  support optimize tables TURBINE GDM_CONSTRUCT_TEXT
When the optimize is complete, enter:
```
start inspection-core
```

Example OPTIMIZE of one table that uses this method

You can see that 7 MB is reclaimed after the OPTIMIZE on that table is finished.

xxx.yyy.zzz.com> support show db-top-tables all
 Table Size (M) | I/D % |  Unused(M) | Est. Rows | Name
 -------------- | ----- |  --------- | --------- | ----------
           1616 |    28 |          0 |   6038005 | REPORT_RESULT_DATA_ROW
            121 |    51 |          7 |    336557 | GDM_CONSTRUCT_TEXT
             13 |   223 |          4 |     21834 | GDM_CONSTRUCT_INSTANCE
              8 |    20 |          0 |     65149 | DB_ERROR_TEXT
             

..etc...

 No tables with more than 80% of free space used found.
ok

xxx.yyy.zzz.com> support optimize tables TURBINE GDM_CONSTRUCT_TEXT
This process can take some time, please wait...
Processing GDM_CONSTRUCT_TEXT...
TURBINE.GDM_CONSTRUCT_TEXT optimize note Table does not support optimize, doing recreate + analyze instead
TURBINE.GDM_CONSTRUCT_TEXT optimize status OK
ok


xxx.yyy.zzz.com> support show db-top-tables all
 Table Size (M) | I/D % |  Unused(M) | Est. Rows | Name
 -------------- | ----- |  --------- | --------- | ----------
           1616 |    28 |          0 |   6038005 | REPORT_RESULT_DATA_ROW
            122 |    51 |          0 |    350893 | GDM_CONSTRUCT_TEXT
             13 |   223 |          4 |     21834 | GDM_CONSTRUCT_INSTANCE
              8 |    20 |          0 |     65149 | DB_ERROR_TEXT
              


..etc..


 No tables with more than 80% of free space used found.
ok
xxx.yyy.zzz.com>