UIM DB appears to be growing very rapidly and processing is slowing down

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe

Issue/Introduction

QOS data INSERTs appear to be very slow. DBA informed the UIM admin that the DB is growing much more rapidly over the past few months.

Environment

Release : UIM any release

Component : UIM - DATA_ENGINE

Cause

- QOS data collection from some probes

Resolution

data_engine settings adjusted.

- auto indexing was disabled. This should always remain disabled if partitioning is enabled.

Set:

- thread_count_insert = 24

- partitioning is already enabled

- data_management_timeout = 65536

- data_engine_id was not set to 1 and on the Primary hub it must be.

- Set data_engine loglevel back to 0

- data_engine hub_bulk_size was increased to 1750. data_engine INSERT speed increased as evidenced by examining the data_engine log at loglevel 3.

- Ran queries to reveal where all the QOS data was coming from, e.g., which probes/QoS. The query listed below is equivalent to clicking the data_engine Status button. Note that sometimes pressing the Status button returns no data in the window and it remains empty/white.

 

If that happens you can run the queries below to generate the same results.

--Uncomment and run the create table first

--create table #t (id bigint, name varchar(max), rname varchar (max), hname varchar(max), dname varchar(max), bname varchar(max), rrows bigint, hrows int, drows int, brows int, rsize int, hsize int, dsize int, bsize int) insert #t (name,rname,rrows,id, hname, dname, bname) SELECT sqd.name, object_name(i.object_id), sum(p.rows) as rowCnt, sqd.qos_def_id, replace(object_name(i.object_id),'RN','HN'), replace (object_name(i.object_id),'RN','DN'), replace(object_name (i.object_id),'RN','BN') FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN s_qos_definition sqd on sqd.qos_def_id = cast(replace(object_name (i.object_id),'RN_QOS_DATA_','') AS int) WHERE object_name(i.object_id) like 'RN_QOS_DATA_%' AND i.index_id <= 1 GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id

-- Then run the select statement

SELECT sqd.name AS 'QoS Name', object_name(i.object_id) AS 'Raw Data Table', sum(p.rows) as rowCnt, sqd.qos_def_id, replace(object_name(i.object_id),'RN','HN'), replace (object_name(i.object_id),'RN','DN'), replace(object_name (i.object_id),'RN','BN') FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN s_qos_definition sqd on sqd.qos_def_id = cast(replace(object_name (i.object_id),'RN_QOS_DATA_','') AS int) WHERE object_name(i.object_id) like 'RN_QOS_DATA_%' AND i.index_id <= 1 GROUP BY sqd.name,i.object_id, i.[name], sqd.qos_def_id ORDER BY rowCnt DESC

Example results:

Once the above queries are run, you can decide what QOS you want to continue collecting, or disable, or change the monitoring interval for, so it doesn't collect specific QOS, or collect it as frequently.