DX Unified Infrastructure Management

 View Only
Expand all | Collapse all

data_engine maintanance execution time

  • 1.  data_engine maintanance execution time

    Posted Oct 17, 2019 03:19 PM
    Hi guys,

    It is possible to check how long the maintenance performed by the data_engine probe lasts?



    Our disk goes into 100% utilization from 03:00 am, which is the beginning of the maintenance window, so we are having performance problems in UMP and REST API.



    Thanks.


  • 2.  RE: data_engine maintanance execution time

    Broadcom Employee
    Posted Oct 17, 2019 03:44 PM
    probably the best way would be to use raw edit to set the following
    table_maintenance_loglevel = 1

    Then when maintenance is don you and review the tbnLogging table and this should have entries for the start and stop of maintenance.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 3.  RE: data_engine maintanance execution time

    Posted Oct 17, 2019 04:14 PM
    Thanks @Gene Howard,

    I verified and the key "table_maintenance_loglevel" are OK, with value "1", but the newer register in "tbnLogging" is 2019-04-18. ​





  • 4.  RE: data_engine maintanance execution time

    Broadcom Employee
    Posted Oct 17, 2019 04:18 PM
    do a truncate on the table first.
    Did you launch admin manually?
    just setting this key will not do anything until maintenance is started.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 5.  RE: data_engine maintanance execution time

    Posted Oct 17, 2019 04:32 PM
    @Gene Howard,

    I truncated the table.



    I didn't understand what you meant "Did you launch admin manually?". ​


  • 6.  RE: data_engine maintanance execution time

    Broadcom Employee
    Posted Oct 17, 2019 04:51 PM
    Sorry I was not clear.
    The table is only populated when maintenance is run.
    So changing the logging value and restarting the data_engine will not do anything.
    Maintenance by default runs on a schedule and does not start until the middle of the night so it would not be expected to see anything in the table until after this was enabled and maintenance has started.

    You can use a Probe Utility (PU) call back to start the maintenance job immediately.
    I would not suggest doing this during business hours if you are expecting it to cause a performance issue.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 7.  RE: data_engine maintanance execution time

    Posted Oct 18, 2019 05:02 AM
    I have a log watcher on the data_engine.log and receive:
    logmon_data_engine: MaintEnd Oct 18 10:05:55:408 [3496] 1 de: Admin - done in 238 seconds ~= ( 3 minutes, 58 seconds )
    (I think you need a loglevel > 0 to have this message)


  • 8.  RE: data_engine maintanance execution time

    Posted Oct 18, 2019 09:04 AM
    Hi @Gene Howard,

    Today I checked the table "tbnLogging", but no information was recorded.

    Disk usage showed the same behavior, reaching 100% utilization at the same time as maintenance is set to start at 03:00 am.


    I will follow the tip of @Luc Christiaens, leave loglevel at 0, and then check if the message will be logged in the log.




  • 9.  RE: data_engine maintanance execution time

    Broadcom Employee
    Posted Oct 18, 2019 09:26 AM
    Hi Felipe,

    Data Engine Maintenance process debug:

    Set the following options in data_engine.cfg via Raw Configure.

    loglevel = 6
    table_maintenance_loglevel = 6
    set the "logsize" key to 200000 so you can also watch/search the log.

    On the day before maintenance is scheduled to run, execute this SQL query on the UIM database:

    TRUNCATE TABLE TBNLOGGING;

    After the scheduled maintenance examine data_engine logs, and query output:

    SELECT * FROM TBNLOGGING ORDER BY EVENT DESC;

    The TBNLOGGING table will log the actual results of the stored procedures which are used during maintenance at loglevel 6. This will reveal exactly what is going on under the hood when maintenance runs, so we will be able to see if there are timeouts, stored procedures failing, etc.

    tbnlogging table should have an entry for spn_de_DataMaint indicating that the data_engine maintenance has completed.

    To check the table results/activity, you can execute this query but note that table maintenance may take some time to finish and you'll see rows being added in the query output as the maintenance run continues when you run:

    select * FROM tbnLogging where Event like '%spn_de_DataMaint%' order by id DESC

    Below is an example from the tbnlogging table during data_engine partitioning activity:

    > spn_de_PartitionAdmin__CreateIndexes(DN_QOS_DATA_0007 DN_QOS_DATA_0007 DN psnDN_QOS_DATA_0007)
    - create partition scheme psnDN_QOS_DATA_0007 as partition pfnDN_QOS_DATA_0007 all to ([primary])
    - create partition function pfnDN_QOS_DATA_0007(datetime) as range right for values ('20160306','20160307','20160308','20160309','20160310','20160311','20160312','20160313','20160314','20160315','20160316','20160317','20160318','20160319','20160320','20160321','20160322','20160323','20160324','20160325','20160326','20160327','20160328','20160329','20160330','20160331','20160401','20160402','20160403','20160404','20160405','20160406','20160407','20160408','20160409','20160410','20160411','20160412','20160413','20160414','20160415','20160416','20160417','20160418','20160419','20160420')
    - Final RetentionTime = 30 Extra partitions = 15
    - tbn_de_DataMaintConfig RetentionTime = 30 Extra partitions = 15
    > spn_de_PartitionAdmin__PartitionTable(DN_QOS_DATA_0007 DN)
    > spn_de_DataMaint_DeleteOldData(DN, 7, Apr 5 2016 12:00AM)
    < spn_de_DataMaint_DeleteOldData(HN, 7, Apr 5 2016 12:00AM) ---> OK
    < spn_de_PartitionAdmin__ShiftTable(HN_QOS_DATA_0007,HN,2016-04-05 00:00:00) deleted null rows ---> OK

    ***What you will see in the tbnlogging table is an entry like this: "spn_de_DataMaint <" and "spn_de_DataMaint >".
    The "<" and "> indicate the start and end of maintenance. It may have an entry for each qos id, and the ids will be sorted in ascending order, so when the last one completes, it is done.

    Note that there is no 'final' log message or entry.

    Steve

    ------------------------------
    [Designation]
    [City]
    ------------------------------



  • 10.  RE: data_engine maintanance execution time

    Posted Oct 18, 2019 02:55 PM
    Thanks @Stephen Danseglio​,

    I will perform this procedure next Monday, and report the result.


  • 11.  RE: data_engine maintanance execution time

    Posted Oct 18, 2019 03:33 PM
      |   view attached
    Hello Felipe, so we used to have the data_engine run on a nightly basis @ 3:00AM but it would never finish. Went thru cases and in the end now we actually have it run on a hourly basis. That way the admin clean up process has little to do instead of just a massive amount of data to clean up which puts a huge strain on the db.
    There is also a query that support provided that can tell you what QoS isn't cleaned up as it should be. Meaning if its over the the raw # of days, or if its past the max # of hourly what's actually not getting cleaned up.
    My results give me:
    No unmaintained data found in RN tables
    No unmaintained data found in HN tables
    No unmaintained data found in DN tables


    ​​​​

    ------------------------------
    Daniel Blanco
    Enterprise Tools Architect
    Alphaserve Technologies
    ------------------------------



  • 12.  RE: data_engine maintanance execution time

    Posted Oct 21, 2019 12:51 PM
    Can you let me know whether your database drive is getting full or Transaction logs is getting full ? I hope it must be Transaction logs .. if means i have faced the same issue already .


  • 13.  RE: data_engine maintanance execution time

    Posted Oct 21, 2019 01:47 PM
    Hi @Daniel Blanco,

    ​I executed the sql script, it returned 377 sanples for RN tables, see below;


    Hi @Issac08

    These are files from databases.

    We've had problems with the log file before, and managed to solve it with the help of a DBA, but I don't think we have these problems today.


    Today I will set the logs to level 6 and tomorrow I will show you the logs from the tbnLogging table.

    Thanks again for the support





  • 14.  RE: data_engine maintanance execution time
    Best Answer

    Posted Oct 22, 2019 09:55 AM
    Edited by Diane Craddock Oct 23, 2019 09:42 AM
    That's actually not that bad. Only 377 entries need to be cleaned up. 
    I'd just re-run the data_engine's admin process more frequently and I bet it will finish and set it to run hourly.

    If the problem is that your disk I/O is spiking during the admin process then that's a hardware limitation.

    What you can try doing since it's the RN tables that have the unfinished entries you can try raising the RN (raw) roll up # to a higher amount, run the admin process, then lower it again, run the admin process etc till you get to the # you want to save RAW data at.

    You can then run the QoS_Check query to check if the # improves each time. 

    Then set the admin process to hourly and you should be good. 
    ​​​


  • 15.  RE: data_engine maintanance execution time

    Posted Oct 23, 2019 01:14 PM
    Hi guys,

    Below is the result, I can verify that the maintenance process started at 03:00 am and ended at 09:54 pm.

    Is it correct?

    Start


    End



  • 16.  RE: data_engine maintanance execution time

    Broadcom Employee
    Posted Oct 23, 2019 01:25 PM
    yes this looks fine.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 17.  RE: data_engine maintanance execution time

    Broadcom Employee
    Posted Oct 23, 2019 01:33 PM
    Yes, 3:34 to 9:54

    ------------------------------
    [Designation]
    [City]
    ------------------------------