Network Management

 View Only
last person joined: 13 hours ago 

Keep an informative eye on your network with HPE Aruba Networking network management solutions
Expand all | Collapse all

Size of databases IMC

This thread has been viewed 27 times
  • 1.  Size of databases IMC

    Posted May 25, 2023 09:53 AM
      |   view attached

    I did not find a convenient method that would allow reducing the size of databases such as vxlan.db Perf.db, Config.db, syslog.db due to the deletion of old data.  Maybe I missed something...

    I came across many posts like: 
    https://community.hpe.com/t5/imc/imc-7-0-deleting-old-performance-data-ms-sql-perf-db/td-p/6240603
    https://community.hpe.com/t5/imc/how-to-shrink-perf-db/td-p/5943371
    https://community.hpe.com/t5/imc/imc-disk-full/td-p/7101883#.ZFppSHZByUk

    I agree with the last comment Riborg in terms of service.
    https://community.hpe.com/t5/imc/imc-database-maintenance-or-clean-up-script/td-p/7079322

    Even after the jhooverpfcu incident, there was no concrete solution or post-incident response from HPE engineers. There is still no solution to the issue.
    https://community.hpe.com/t5/imc/no-bandwidth-utilization-data-in-new-imc-7-3-installation/m-p/7048883

    The moment is very acute for systems that have been in exploitation for a long time. Because you need to delete information for the past 6 years and leave the past.



  • 2.  RE: Size of databases IMC

    EMPLOYEE
    Posted May 25, 2023 10:40 AM

    Hello,

    First of all, make sure you are running one of the latest IMC versions, eg. E0708+. There have been some issues in the past related to those two particular DBs, config_db and perf_db. I don't recall exactly for the config_db issue, but there was a table that grew really large due to a bug. As for perf_db, there was an issue on one version with performance indexes becoming duplicated.

    These are both fixed with the latest versions, and the DBs should automatically clean up the tables if they were affected. Note that although the table size is reduced, you would still need to run a 'shrink' on the DBs to release the allocated space back to the OS and thus reduce the size of the actual DB file.

    Second, IMC generally manages the size of its databases automatically via the System > System Configuration > Data Export feature. This cleans up old entries in the DBs for various different logs and exports these records to text files. Syslog_DB is one of those that is managed via the settings here.

    However this does not apply to config_db, as that is the 'main' configuration database of IMC where details like the device list and other relatively static configurations are stored, which are not expected to grow steadily over time.

    It also doesn't apply to perf_db, mostly because the performance data gathered every 5 minutes by default is automatically aggregated over longer periods of time. For example if you look at any performance data from the 'Last Year', you'll notice it's shown (and stored) only as an average and maximum value on a daily basis. Though the DB will of course still keep growing over time, it should not get close to 50GB like yours unless you have thousands of devices or you are monitoring too many indexes.

    To verify that, you could check the Resource > Performance Management > Monitoring Settings page. Click on Re-collect button, then check the 'Total number of collecting units' value. How many collecting units are shown for your installation?



    ------------------------------
    Justin Guse
    ------------------------------



  • 3.  RE: Size of databases IMC

    Posted May 26, 2023 04:17 AM

    Good afternoon, thanks for your reply. Currently, 1255 devices have been added to the system. I provide information at your request.
    Also made changes to the Data Export Settings earlier, but that didn't help. I also provide information on the version of IMC PLAT 7.3 (E0708P3) additional modules in the photo.




  • 4.  RE: Size of databases IMC

    EMPLOYEE
    Posted May 26, 2023 10:26 AM

    Hello,

    For config_db size, I would suggest running a query to find and list the largest tables, for example:

    USE config_db;
    
    SELECT 
        s.name AS SchemaName,
        t.name AS TableName,
        SUM(p.rows) AS [RowCount],
        SUM(a.total_pages) * 8 AS TotalSpaceKB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM 
        sys.tables t
    INNER JOIN 
        sys.indexes i ON t.object_id = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        t.is_ms_shipped = 0
    GROUP BY 
        s.name,
        t.name
    ORDER BY 
        SUM(a.total_pages) DESC;


    Please share here which tables are largest.

    For perf_db, I think the size is not necessarily unexpected given the number of devices and indexes monitored. My suggestion would be to try and reduce the number of monitored index instances, by monitoring only those which you really need - especially when it comes to interfaces. For example monitoring a lot of redundant indexes across many ports can lead to too many collection units. Such as Interface Transmitting/Receiving Rate in bps and Bandwidth Usage in % which provide the same data in different formats.

    If you find indexes you don't need to monitor, you could batch remove them from all devices using the Performance Management > Performance Option > Default Monitoring Indexes feature. Uncheck all the indexes except the one(s) you want to remove - very important, make sure you do this right, as any index left selected will be removed! - and then click on Cancel Indexes. A pop-up will appear showing some progress as all instances of the selected monitoring index are removed from your devices. I would suggest cancelling only one index at a time, so that you don't overburden the process with many devices. Note that it could take a few days before you see some space cleared up on the DB.

    See also my answer here about the qvdm.conf variable to control how long original 5-min performance data is stored: https://community.hpe.com/t5/imc/data-gathering-in-hpe-imc-7-3/td-p/7036359

    Hope that helps.



    ------------------------------
    Justin Guse
    ------------------------------



  • 5.  RE: Size of databases IMC

    Posted Jun 21, 2023 05:37 AM

    Good afternoon, sorry for the long answer. There are currently 18,819 monitoring units. They were selected correctly for the needs. I am very grateful for the script you provided, I am attaching its output to the photo.

    "Note that it could take a few days before you see some space cleared up on the DB." This is useful information that is not entirely obvious. After making the changes, there was no obvious reduction in space, but the next day the space became vacant.

    "See also my answer here about the qvdm.conf variable to control how long original 5-min performance data is stored: https://community.hpe.com/t5/imc/data-gathering-in-hpe-imc-7-3/td-p/7036359" The key caught, but it is not a way out.