NETXMS Query taking too much resources

[isoftcom]

Hi All,

The Query "INSERT INTO tdata_122 (item_id,tdata_timestamp,record_id) VALUES (:1,:2,:3)" is very expensive, it causes all sorts of problems with the Database including the creation of deadlocks and waits. Isn't there a way we can optimize it so as to make is less expensive on resources.

[alk]

It's simple insert with 3 field and single composite index, it should not be expensive.
Please show explain / query plan.
What DB you are using?
How many objects in the system?
How big the database is?
What is retention time for tables DCIs on node 122?

[isoftcom]

I understand that the tdata_ table holds the DCI value history, my database is big with a lot of objects in the system sitting on oracle 11g database. The problem is the value history are alot and are taking a lot of resources when pushing these history to the database. Is there way on how to tell netxms not to generate a lot of these history?

[alk]

@isoftcom said in NETXMS Query taking too much resources:

Is there way on how to tell netxms not to generate a lot of these history?

Sure.

1. Increase collection interval for the table DCI (it's once a minute by default)
2. Decrease retention time for the table DCI (1 month by default)

You can change these settings per-DCI.

To find out which DCI consume most of the space, you can query table in question:

select item_id,count(*) c from tdata_122 group by item_id order by c desc;

ID is the same as shown in data collection editor for the node

[isoftcom]

Hi Alex

Thanks for the Help, let me try to do what you have advised and monitor the difference.