IBM Support

Guardium internal database full percentage is not decreasing, even after a successful purge. - Reclaim DB Space with OPTIMIZE

Troubleshooting


Problem

You note that the Guardium database is showing as full. When running the "support show db-top-tables all" command from CLI you see that the total size of the top tables is much smaller than the total disk space available for the database. You have recently purged data off the system but the data % did not decrease. - You also noticed that there is Unused (M) in some tables in "support show db-top-tables all"

Symptom

You can see if the internal database is full in a number of ways:
  • Messages in the syslog saying the db is full.
  • Output of CLI command:
    • support show db-status used %
  • Mysql Disk Usage column in GUI->Guardium Monitor->Buffer Usage Monitor report
  • You may notice a large amount of unused space with this command
    • support show db-top-tables all

Cause

The purge has removed the data from the database, but has not necessarily reduced the size of the database files. Some database tables may need to be optimized to make available the space cleared by the purge.

Diagnosing The Problem

Check the top tables and database full percentage before and after purge of data using CLI commands:

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

After the purge you notice that the top tables sizes have decreased significantly, but the database full % has not changed.

Also - you may notice a large amount of unused space. For example, the numbers below are on a small test system.  The columns, specifically the Unused(M) may show a large number of Megabytes that can be reclaimed. (eg here - GDM_CONSTRUCT_TEXT could have 7MB of space reclaimed).

Note - In live environments, it is only recommended to optimize when significant space e.g. 10s of GB can be reclaimed.

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

Appliances at all versions

  • You can optimize the internal TURBINE database tables as follows

    * NOTE The OPTIMIZE will take time depending on the size of the underlying table(s) and needs to have the inspection-core stopped . It is recommended to plan for this during a quiet time and to let the command run to completion

    1. If the appliance is a collector stop the inspection-core in the CLI.

    • stop inspection-core

      •  

    2. Start the optimize process. The process could take up to several hours to complete depending on the size of the database table(s)

    • To optimize all tables
      • CLI->diag->4. Perform Maintenance Actions->2. TURBINE Optimize.
    • To optimize specific tables only
      • This might be appropriate if you know a certain table has recently been purged heavily. It might save time in that case to optimize only one table - See the example below . The CLI command is

        support optimize tables <database name> <table name>
        e.g.
        support optimize tables TURBINE GDM_CONSTRUCT_TEXT

    3. Once the process is finished start the inspection-core

    • start inspection-core

  • Example OPTIMIZE of one table

    Here is the example of space reclamation using this method. You can see the 7MB is reclaimed once the OPTIMIZE on that table is finished.

    Note - In live environments, it is only recommended to optimize when significant space e.g. 10s of GB can be reclaimed.
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>
 

[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSMPHH","label":"IBM Security Guardium"},"ARM Category":[{"code":"a8m0z000000Gp0JAAS","label":"APPLIANCE"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
24 November 2020

UID

swg21676156

Page Feedback